엑셀로 매출채권 에이징 보고서(30, 60, 90일) 만드는 방법

연령 분석 보고서(aging report)는 미지급 송장을 연체 기간에 따라 보통 0~30일, 31~60일, 61~90일, 90일 초과 등의 구간으로 분류합니다. 보고서의 정확성은 두 가지 결정에 따라 좌우됩니다. 첫째는 연령 분석 기준을 만기일(due date)로 할 것인지 아니면 송장 발행일(invoice date)로 할 것인지입니다. 둘째는 일부 결제된 송장의 금액을 전체 금액으로 표시할 것인지 아니면 남은 잔액으로 표시할 것인지입니다.

이 두 가지를 잘못 설정하면 모든 구간의 합계가 틀려지며, 이는 보고서가 아예 없는 것보다 못합니다.

이 가이드에서는 보고서 작성이 실패하는 이유, 사람들이 사용하는 세 가지 접근 방식, 그리고 각 방식이 한계에 부딪히는 지점을 다룹니다. 이는 데이터 워크플로우에 관한 설명이며 회계 자문이 아니므로, 구체적인 처리 방식은 원장 담당자와 확인하시기 바랍니다.

연령 분석 보고서가 스프레드시트를 망가뜨리는 이유

첫 번째 문제는 날짜 기준의 문제입니다. 송장 발행일을 기준으로 연령을 분석하면 서류가 얼마나 오래되었는지를 알 수 있습니다. 만기일을 기준으로 분석하면 고객의 연체 기간이 얼마나 되었는지를 알 수 있으며, 미수금 회수 관점에서는 바로 이 숫자가 필요합니다.

두 방식 모두 타당한 이유가 있으며 서로 다른 보고서를 만들어냅니다. 문제는 어떤 방식을 사용했는지 스프레드시트의 그 누구도 기록해 두지 않았을 때 발생합니다.

두 번째 문제는 일부 결제(분할 납부)입니다. $10,000짜리 송장에서 $7,000를 수금했다면 미수금은 $3,000이며, 이 금액은 정확히 하나의 구간에 $3,000로 표시되어야 합니다. 미결제 항목(open-items) 목록이 아닌 전체 송장 목록을 기반으로 작성된 연령 분석 보고서는 알게 모르게 모든 금액을 과다 계상하게 됩니다.

세 번째 문제는 보고서가 특정 시점의 스냅샷이라는 점입니다. 구간은 오늘을 기준으로 계산되므로 어제의 파일은 이미 지난 데이터가 되며, 보고서를 새로 만들 때마다 모든 행을 다시 계산해야 합니다.

또한 까다로운 행들도 존재합니다. 마이너스 송장(credit notes), 선급금, 분쟁 중인 송장, 다중 통화 잔액 등은 각각의 규칙이 필요합니다. 그리고 이 규칙들은 다음에 파일을 여는 사람에 의해서도 깨지지 않고 유지되어야 합니다.

이 문제들은 개별적으로 보면 어렵지 않습니다. 하지만 매달 말 마감 기한에 쫓기는 상황에서 한꺼번에 들이닥치기 때문에 어렵게 느껴집니다.

이로 인해 치러야 하는 대가

실행할 수 없는 미수금 회수 목록. 구간을 나누는 목적은 누구에게 먼저 연락해야 할지 알기 위함입니다. 잔액이 과다 계상된 보고서는 담당자가 이미 수금된 돈을 쫓아다니게 만듭니다.

매달 반복되는 재작업. 구간 계산은 오늘 날짜를 기준으로 하기 때문에 연령 분석 보고서는 결코 끝이 나지 않습니다. 매 주기마다 동일한 병합(join), 동일한 수식, 동일한 수동 확인 작업을 반복해야 합니다.

원장과 일치하지 않는 합계. 구간별 합계가 미수금 잔액과 일치하지 않으면 보고서의 신뢰성이 떨어집니다. 원인을 찾는 데는 대개 보고서를 처음 만드는 것보다 더 많은 시간이 걸립니다.

연령 분석 보고서가 신뢰를 얻는 이유는 합계가 원장과 일치하기 때문입니다. 이것이 실패한다면 다른 어떤 것도 의미가 없습니다.

사람들이 시도하는 임시방편들

옵션 1: 수식을 건드리기 전에 정의부터 확립하기

시트 상단에 네 가지 사항을 적어두세요. 연령 분석의 기준이 되는 날짜, 구간의 경계, 금액이 결제 전 총액(gross)인지 결제 후 순액(net)인지, 그리고 기준일(as-of date)이 언제인지입니다.

여기에 투자하는 10분은 가장 흔하게 발생하는 분쟁을 예방해 줍니다. Journal of Accountancy에서도 설정을 먼저 올바르게 잡는 것의 중요성을 동일하게 강조하며 보고서 작성 과정을 설명하고 있습니다.

또한 이는 소스 데이터를 결정하는 기준이 됩니다. 지금까지 발행된 모든 송장 목록이 아니라, 남은 잔액이 포함된 미결제 항목(open-items) 추출 데이터가 필요합니다.

한계는 정의 자체만으로는 아무것도 계산되지 않는다는 점입니다. 단지 잘못된 계산을 하는 것을 막아줄 뿐입니다.

옵션 2: 구간 열을 만든 다음 합계를 피벗하기

연체 일수를 기준일에서 만기일을 뺀 값으로 계산한 다음, 그 숫자를 구간 레이블에 매핑합니다. TODAY 함수는 실시간 기준일을 제공하며, DATEDIF 함수는 두 날짜 사이의 일수를 반환합니다.

레이블 지정의 경우, 6개월 뒤에 다시 볼 때 중첩된 IF 문보다 IFS 함수가 더 읽기 쉽습니다. 그런 다음 SUMIFS 함수를 사용하여 고객 및 구간별로 합계를 구하면 행별로 계산 과정을 쉽게 검증할 수 있습니다.

보고서를 배포할 때는 TODAY 대신 고정된 기준일을 직접 입력하여 사용하세요. 다음 주에 열었을 때 알아서 연령을 재계산해 버리는 파일은 이미 다른 사람의 수신함에 들어가 있는 버전과 숫자가 달라져 혼선을 줄 수 있습니다.

한계는 데이터의 양과 예외 케이스입니다. 수식은 유지되지만 마이너스 송장, 일부 결제, 분쟁 건 등은 여전히 수동으로 처리해야 합니다.

옵션 3: 숫자 데이터 옆에 규칙 탭 유지하기

까다로운 결정 사항들을 한곳에 모아두세요. 마이너스 송장을 어떻게 상계 처리할지, 분쟁 중인 송장을 제외할지 아니면 표시만 해둘지, 외화 잔액은 어떻게 어떤 환율로 환산할지 등을 정리합니다.

이렇게 해야 다른 사람이 보고서를 실행하더라도 오류 없이 유지될 수 있습니다. 하지만 월말 마감 기한이 촉박할 때 가장 먼저 건너뛰게 되는 탭이기도 합니다.

한계는 규칙 탭이 판단 기준을 문서화할 뿐, 이를 자동으로 적용해 주지는 않는다는 점입니다. 결국 매 주기마다 누군가가 각 규칙을 직접 적용해야 합니다. 이에 필요한 대조 작업은 스프레드시트에서 거래 내역을 조정하는 방법에 관한 가이드에서 다루고 있습니다.

공통적인 한계. 세 가지 옵션 모두 정제된 미결제 항목 추출 데이터에서 시작한다고 가정합니다. 소스 데이터가 가공되지 않은 송장 내보내기 파일과 별도의 결제 파일인 경우, 진짜 까다로운 작업은 구간을 나누기 전에 이 두 파일을 병합하는 일입니다.

Powerdrill Bloom으로 연령 분석 보고서를 만드는 방법

1단계: 송장 및 결제 데이터 업로드하기

미결제 항목 추출 파일 또는 송장 및 결제 파일을 함께 업로드합니다. Powerdrill Bloom은 데이터를 가져오는 즉시 열 프로파일링을 수행하므로, 구간을 계산하기 전에 누락된 만기일, 빈 금액, 중복된 송장 번호 등을 미리 찾아낼 수 있습니다.

2단계: 자연어로 구간 분류 규칙 설명하기

규칙을 직접 구축하는 대신 말로 설명하세요. 특정 날짜 기준의 만기일을 기준으로 연령을 분석하겠다고 입력합니다. 구간의 경계를 지정하고, 금액은 수금된 결제액을 제외한 순액이어야 한다고 명시합니다.

그런 다음 동일한 단계에서 검증을 요청하세요. 송장 금액을 초과하여 결제된 송장이 있는지, 송장 발행일보다 만기일이 더 빠른 송장이 있는지 물어봅니다. 마지막으로 구간별 합계가 미수금 잔액과 일치하는지 확인을 요청합니다.

3단계: 차트, 보고서 또는 프레젠테이션 자료로 내보내기

고객별 연령 분석 표, 구간 분포 차트, 또는 가장 오래된 잔액 순으로 정렬된 미수금 회수 목록을 내보냅니다.

매달 보고서를 새로 만드는 것보다 이 방식이 더 나은 이유

	수동 방식	Powerdrill Bloom
송장과 결제 내역 병합	파일별로 조회(Lookup) 수식 사용	두 파일을 모두 업로드하고 질문하기
기준일 변경	재계산 및 재검증	새로운 날짜 지정하기
일부 결제액 상계 처리	수동으로 잔액 열 생성	결제액을 제외한 잔액 요청하기
합계와 원장 대조	매 주기마다 수동으로 확인	합계가 일치하는지 질문하기

표 중간에 있는 행들이 바로 한 달 중 가장 많은 시간을 잡아먹는 부분입니다. 구간을 나누는 것은 단순한 산수일 뿐이며, 정제된 미결제 항목 목록을 얻는 것이 진짜 작업입니다.

흔히 하는 실수들

만기일을 기준으로 해야 하는데 송장 발행일을 기준으로 연령을 분석하는 경우. 미수금 회수 목적이라면 거의 항상 만기일 기준이 맞습니다. 어떤 방식을 선택하든 보고서에 명시해 두세요.

남은 잔액 대신 송장 금액 전체를 표시하는 경우. 일부 결제된 송장은 미지급 잔액을 기준으로 구간에 들어가야 합니다. 전체 금액을 표시하면 모든 합계가 부풀려집니다.

배포된 파일에서 TODAY 함수가 연령을 재계산하도록 방치하는 경우. 보고서를 보내기 전에 기준일을 고정(Freeze)하세요. 그렇지 않으면 두 사람이 같은 파일에서 서로 다른 숫자를 보게 될 수 있습니다.

마이너스 송장(credit notes)을 무시하는 경우. 적용되지 않은 마이너스 송장은 고객의 미수금 잔액을 줄여주는 역할을 합니다. 이를 누락하면 미수금 잔액이 실제보다 더 나빠 보입니다.

송장 기준이 아닌 고객 기준으로 구간을 나누는 경우. 구간 분류는 송장별로 먼저 수행한 다음 고객별로 합산해야 합니다. 고객의 연령을 평균 내면 가장 오래된 항목(가장 먼저 해결해야 할 항목)이 가려지게 됩니다.

원장과 대조해 보지 않는 경우. 구간별 합계는 미수금 통제 계정 잔액과 일치해야 합니다. 이 확인 과정을 건너뛰면 보고서는 그저 장식품에 불과합니다.

매 주기마다 처음부터 다시 만드는 경우. 규칙은 매달 바뀌지 않으며 데이터만 바뀝니다. 규칙은 그대로 유지하고 내보낸 데이터만 교체하세요. 이는 예산 대비 실적 보고서를 작성할 때와 동일한 원칙입니다.

결론

연령 분석 기준일을 결정하고, 남은 잔액을 사용하며, 기준일을 고정하고, 합계를 원장과 대조하세요. 이 네 가지가 사람들이 신뢰하고 실행에 옮기는 보고서와 사람들이 의구심을 품고 논쟁하게 만드는 표의 차이를 만듭니다.

이 작업에 많은 비용이 드는 이유는 모든 것이 오늘 날짜를 기준으로 하기 때문에 결코 끝나지 않기 때문입니다. 매 주기마다 데이터 병합과 검증 작업이 반복됩니다.

월말 마감 시간의 대부분을 여기에 쓰고 계신다면, 송장 및 결제 내보내기 파일에 Powerdrill Bloom을 사용해 보세요. 또한 PDF 재무제표를 차트로 변환하는 방법에 대한 가이드와 AI 현금 흐름 분석 페이지도 참고해 보시기 바랍니다.

자주 묻는 질문

매출채권 연령 분석 보고서의 표준 구간은 어떻게 되나요?

대부분의 보고서는 0~30일, 31~60일, 61~90일, 90일 초과 구간을 사용하며, 종종 '당기' 또는 '미도래' 열을 포함하기도 합니다. 구간의 경계는 규칙이라기보다는 관례이므로 어떤 기준을 사용했는지 명시하는 것이 좋습니다.

송장 발행일과 만기일 중 어떤 것을 기준으로 연령을 분석해야 하나요?

고객의 연체 기간을 파악하려는 경우(미수금 회수의 일반적인 목적)에는 만기일을 사용하세요. 서류가 얼마나 오래되었는지 파악하려는 경우에는 송장 발행일을 사용하세요.

일부 결제 건은 어떻게 처리하나요?

원래의 송장 금액이 아닌 남은 잔액을 표시하고, 해당 잔액을 하나의 구간에 배치합니다. 송장 목록 대신 미결제 항목 추출 데이터를 사용하면 이 작업이 자동으로 처리됩니다.

어떤 Excel 함수가 필요한가요?

기준일 지정을 위한 TODAY 또는 고정된 날짜, 연체 일수 계산을 위한 DATEDIF 함수가 필요합니다. IFS 함수는 구간 레이블을 지정하고, SUMIFS 함수는 고객 및 구간별 합계를 구합니다. 함수 자체는 복잡하지 않으며, 정의를 내리는 과정이 까다로운 부분입니다.

보고서는 얼마나 자주 새로 만들어야 하나요?

모든 구간은 기준일을 기준으로 하므로 최소 매달 작성해야 하며, 미수금 회수 활동이 활발한 경우 매주 작성하는 것이 좋습니다. 배포하는 각 버전마다 해당 기준일을 고정해 두세요.