회계용 엑셀: 필수 템플릿과 함수 공식

전용 클라우드 기반 회계 소프트웨어가 증가하고 있음에도 불구하고, Microsoft Excel은 여전히 재무 및 회계 부문에서 최고의 작업 도구로 자리 잡고 있습니다. 월말 계정 조정 준비부터 복잡한 재무 모델링에 이르기까지, 엑셀은 경직된 회계 시스템이 제공하지 못하는 유연성과 강력한 연산 능력을 제공합니다.

직접 장부를 관리하는 소상공인이든, 수천 행의 거래 데이터를 다루는 기업 회계사이든 엑셀을 마스터하는 것은 필수적인 기술입니다. 이 가이드에서는 모든 회계 실무자에게 필요한 필수 엑셀 템플릿과 함수 공식을 실용적인 설명과 구체적인 예제와 함께 살펴봅니다.

총계정원장(GL) 템플릿 설정하기

총계정원장은 모든 재무 거래가 기록되는 마스터 저장소입니다. 소규모 회사의 장부를 엑셀로 관리하고 있다면, 첫날부터 총계정원장을 올바르게 구조화하는 것이 중요합니다. 구조가 잘못된 총계정원장은 나중에 자동화된 보고서를 생성할 수 없게 만듭니다.

엑셀의 표준 총계정원장은 연속된 표 형식으로 설정해야 합니다. 데이터 사이에 행을 건너뛰거나 빈 열을 삽입하지 마세요. 이상적인 열 구조의 예는 다음과 같습니다.

날짜	거래 ID	계정 코드	적요	차변	대변	누적 잔액
2023-10-01	TRX-001	1010 (현금)	자본금 출자	$10,000		$10,000
2023-10-03	TRX-002	6010 (임대료)	10월 임대료 지급		$2,000	$8,000
2023-10-05	TRX-003	4010 (매출)	고객 A 청구서	$1,500		$9,500

동적 누적 잔액 생성하기

행을 추가할 때마다 동적으로 업데이트되는 누적 잔액을 계산하려면 이전 행의 잔액에 차변을 더하고 대변을 빼는 공식이 필요합니다. 1행이 머리글이고 2행에 첫 번째 거래가 있다고 가정할 때, 시작 잔액을 G2에 입력합니다. G3 셀에는 다음을 입력합니다.

=G2 + E3 - F3

이 수식을 아래로 드래그합니다. 데이터 아래의 빈 행에 수식의 결과가 반복해서 표시되지 않도록 하려면, 날짜 열(A)이 비어 있는지 확인하는 IF 문으로 수식을 감싸줍니다.

=IF(A3="", "", G2 + E3 - F3)

전문가 팁: 계정 코드 열의 일관성을 유지하고 오타를 방지하려면, 별도의 탭에 계정과목표를 설정하고 드롭다운 메뉴를 통한 데이터 유효성 검사를 사용하여 입력을 제어하세요. 이렇게 하면 나중에 재무제표를 작성할 때 문제 해결에 소요되는 시간을 크게 절약할 수 있습니다.

동적 재무제표(손익계산서 및 재무상태표) 작성하기

총계정원장이 제대로 구조화되면, 손익계산서와 재무상태표 생성은 계정 코드를 기준으로 데이터를 집계하는 작업이 됩니다. 이 작업에 가장 강력한 기능이 바로 SUMIFS 함수입니다.

SUMIFS를 사용하면 여러 조건(예: 특정 계정 코드와 일치하면서 동시에 특정 날짜 범위 내에 있는 경우)을 모두 충족하는 범위의 값만 합산할 수 있습니다. 자동화된 재무 보고를 위해서는 SUMIF 및 SUMIFS를 활용한 조건부 합계를 마스터하는 것이 매우 중요합니다.

단계별 가이드: 손익계산서 자동화

1. 새 시트의 A열에는 수익 및 비용 계정 이름을, B열에는 해당 계정 코드를 나열합니다.
2. C1 및 D1 셀에 보고 기간 날짜를 입력합니다(예: 시작일: 2023-10-01, 종료일: 2023-10-31).
3. E열(합계 열)에서 SUMIFS를 사용하여 날짜 범위 내 특정 계정 코드에 해당하는 차변 또는 대변의 총합계를 가져옵니다.

다음은 "GL"이라는 시트에서 10월의 계정 코드 "4010"에 해당하는 대변 열(수익)을 합산하는 구문입니다.

=SUMIFS(GL!$F:$F, GL!$C:$C, 4010, GL!$A:$A, ">="&$C$1, GL!$A:$A, "<="&$D$1)

이 수식이 어떤 역할을 하는지 분석해 보겠습니다.

• GL!$F:$F: 합산하려는 범위입니다(대변 열).
• GL!$C:$C: 첫 번째 조건 범위입니다(계정 코드 열).
• 4010: 찾고자 하는 특정 계정 코드입니다.
• GL!$A:$A: 두 번째 조건 범위입니다(날짜 열).
• ">="&$C$1: 날짜가 시작일보다 크거나 같아야 함을 엑셀에 알려줍니다.
• "<="&$D$1: 날짜가 종료일보다 작거나 같아야 함을 엑셀에 알려줍니다.

은행 계정 대사 작업 간소화

은행 계정 조정(대사)은 회사의 회계 장부상 잔액과 은행 거래내역서의 정보를 일치시키는 과정입니다. 엑셀은 금액 불일치, 누락된 수표 또는 중복 청구된 은행 수수료를 찾아내는 데 매우 유용합니다.

방대한 거래 목록을 대사하는 가장 빠른 방법은 은행 거래내역서를 엑셀로 내보내어 내부 원장과 나란히 배치하는 것입니다. 그런 다음 찾기 함수를 사용하여 일치하는 금액이나 참조 번호를 찾습니다.

많은 회계사들이 VLOOKUP을 흔히 사용하지만, INDEX MATCH 찾기 방식으로 전환하면 훨씬 더 많은 유연성을 얻을 수 있습니다. 특히 찾고자 하는 값(예: 수표 번호)이 표의 첫 번째 열에 없는 경우 유용합니다.

정확한 일치 확인 및 조건부 서식 사용하기

두 목록을 날짜와 금액 기준으로 정렬했다면 장부 금액에서 은행 금액을 빼면 됩니다. 결과가 0이면 두 금액이 일치한다는 의미입니다.

=Book_Amount - Bank_Amount

그런 다음 조건부 서식(셀 강조 규칙 > 다음 값과 같음 > 0)을 적용하여 일치하는 모든 행을 녹색으로 표시하면, 강조 표시되지 않은 나머지 항목(조정 대상 항목)을 즉시 눈에 띄게 만들 수 있습니다.

매출채권 및 매입채무 관리 (채권 연령 보고서)

현금 흐름은 모든 비즈니스의 생명줄입니다. 매출채권(받을 돈)과 매입채무(갚을 돈)를 추적하는 것은 매일 해야 하는 작업입니다. 엑셀로 연령 조사 보고서를 작성하면 어떤 청구서가 만기 전인지, 기한이 지났는지, 또는 심각하게 연체되었는지를 파악하는 데 도움이 됩니다.

연령 조사 보고서를 작성하려면 현재 날짜와 청구서 만기일 사이의 차이를 계산한 다음, 그 숫자를 범주(예: 0~30일, 31~60일, 61~90일, 90일 이상)로 묶어야 합니다.

예제: 연령 계산 수식 만들기

A열에 청구서 번호, B열에 고객 이름, C열에 만기일, D열에 미결제 잔액이 있다고 가정해 보겠습니다. E열에서는 연체 일수를 계산하려고 합니다.

=TODAY() - C2

TODAY() 함수는 항상 현재 날짜를 반환합니다. 결과가 음수라면 아직 청구서 만기가 도래하지 않은 것입니다. 다음으로, F열에서 연체 일수를 범주화합니다. 논리 테스트와 중첩 IF를 사용하면 이러한 연체 청구서를 완벽하게 범주화할 수 있습니다.

=IF(E2<0, "Not Due", IF(E2<=30, "1-30 Days", IF(E2<=60, "31-60 Days", IF(E2<=90, "61-90 Days", "Over 90 Days"))))

데이터 범주화가 끝나면 피벗 테이블을 삽입하여 고객 및 연령 범주별 미수금 잔액을 요약할 수 있으며, 이를 통해 경영진은 수금 우선순위를 명확하게 파악할 수 있습니다.

모든 회계사에게 필요한 5가지 필수 엑셀 함수

기본 산술 외에도 현대 회계에서는 감가상각, 미지급금 및 예측을 관리하기 위해 몇 가지 특수 함수가 필요합니다.

1. EOMONTH (월말): 미지급금을 예상하고 표준 월별 머리글을 만드는 데 필수적입니다. =EOMONTH(A2, 0)은 A2 날짜가 속한 달의 마지막 날을 반환합니다. 0을 1로 변경하면 다음 달의 마지막 날을 알 수 있습니다.
2. EDATE: 대출이나 투자의 만기일을 계산하는 데 안성맞춤입니다. 날짜에 지정된 개월 수를 더합니다. =EDATE(Start_Date, 12)는 정확히 12개월을 더합니다.
3. PMT (지급액): 일정한 지급액과 일정한 이자율을 기반으로 대출 지급액을 계산하는 데 사용됩니다. 부채 상환에 중요합니다. 구문: =PMT(rate, nper, pv).
4. SLN (정액법 감가상각): 한 기간 동안 자산의 정액법 감가상각액을 반환합니다. 구문: =SLN(cost, salvage, life).
5. XLOOKUP: VLOOKUP 및 INDEX MATCH를 대체하는 최신 함수입니다. 왼쪽 방향으로 검색할 수 있고, 오류 발생 시 기본 메시지를 지정할 수 있으며, 아래에서 위로 검색할 수도 있습니다. Microsoft 365를 사용 중이라면 적극 권장합니다.

파워 쿼리로 단순 반복 작업 자동화하기

매월 회계 소프트웨어의 데이터를 엑셀 템플릿으로 복사하고 붙여넣는 작업은 번거롭고 사람의 실수가 발생하기 쉽습니다. 매달 QuickBooks, Xero 또는 은행에서 내보낸 CSV 파일의 형식을 수동으로 맞추고 있다면, 이제 작업 방식을 업그레이드할 때입니다.

파워 쿼리를 사용하여 전문가처럼 데이터를 가져오고 변환할 수 있습니다. 파워 쿼리를 사용하면 원본 데이터 파일(예: 월별 CSV 덤프)에 대한 연결을 구축할 수 있습니다. 불필요한 상단 행 자동 삭제, 텍스트를 날짜로 변경, 빈 계정 번호 채우기, 열 피벗 해제 등의 규칙을 설정할 수 있습니다. 다음 달에는 새 CSV 파일을 폴더에 넣고 엑셀에서 "새로 고침"을 누르기만 하면 모든 서식 지정 단계가 즉시 적용됩니다.

AI를 활용해 회계 함수를 더 빠르게 작성하기

복잡하고 깊게 중첩된 함수를 외우는 것은 숙련된 재무 전문가에게도 벅찬 일일 수 있습니다. 복잡한 찾기, 연령별 IF 문, 복잡한 감가상각 계산을 위한 정확한 구문이 기억나지 않아 어려움을 겪어본 적이 있다면 GPTExcel와 같은 도구가 도움이 될 수 있습니다. "잔존 가치를 무시하고 5년 동안 자산의 정액법 감가상각을 계산해 줘"처럼 일상적인 언어로 필요한 내용을 설명하기만 하면 정확하게 작동하는 공식을 즉시 얻을 수 있습니다.

엑셀 구조에 대한 탄탄한 기본 지식과 최신 AI 지원을 결합하면 훨씬 짧은 시간 안에 오류 없는 신뢰할 수 있는 회계 템플릿을 만들 수 있습니다.

자주 묻는 질문

회계 공식이 실수로 삭제되지 않도록 보호하려면 어떻게 해야 하나요?

엑셀의 "시트 보호" 기능을 활용하여 템플릿을 보호할 수 있습니다. 먼저 거래 내역 등 데이터 입력이 허용되는 셀을 강조 표시한 다음, 마우스 오른쪽 버튼을 클릭하여 '셀 서식'을 선택하고 '보호' 탭으로 이동하여 "잠금"을 선택 해제합니다. 그런 다음 리본의 '검토' 탭으로 이동하여 "시트 보호"를 클릭합니다. 그러면 수식은 잠기지만 사용자는 계속 데이터를 입력할 수 있습니다.

엑셀이 QuickBooks와 같은 전용 회계 소프트웨어를 완전히 대체할 수 있나요?

아주 작은 소규모 기업이나 신생 기업은 엑셀을 사용하여 기본적인 수입과 지출을 추적할 수 있지만, 전용 회계 소프트웨어의 영구적인 대체품으로는 권장되지 않습니다. 전용 소프트웨어는 복식 부기 회계 규칙을 엄격하게 준수하고 철저한 감사 추적을 유지하며 복잡한 세무 보고를 기본적으로 처리합니다. 엑셀은 기본 회계 시스템을 보완하는 분석 및 보고용으로 사용하는 것이 가장 좋습니다.

대량의 총계정원장 데이터를 요약하는 가장 좋은 방법은 무엇인가나요?

피벗 테이블은 수천 행의 원장 데이터를 요약하는 가장 효율적인 방법입니다. 피벗 테이블을 삽입한 후 "계정 이름"을 '행' 필드로, "날짜"(월별 그룹화)를 '열' 필드로, "금액"을 '값' 필드로 드래그하면 수식을 하나도 작성하지 않고도 즉시 교차 집계된 재무 요약을 생성할 수 있습니다.

원장에서 중복된 거래 항목을 찾으려면 어떻게 해야 하나요?

가장 빠른 방법은 조건부 서식을 사용하는 것입니다. 거래 참조(예: 수표 번호 또는 청구서 ID)가 포함된 열을 강조 표시하고 '홈' 탭으로 이동한 다음, 조건부 서식을 클릭하고 '셀 강조 규칙'을 선택한 다음 "중복 값"을 선택합니다. 엑셀은 두 번 이상 입력된 모든 거래를 즉시 강조 표시합니다.