거래처가 늘어나면 매출은 발생했는데 아직 받지 못한 돈이 얼마인지 파악하기 어려워집니다. 특히 여러 거래처에 외상으로 상품을 공급하거나 대금을 나누어 받는 사업자는 미수금 관리가 중요합니다.
엑셀과 챗GPT를 활용하면 거래처별 매출금액, 입금액, 미수금, 입금기한을 자동으로 계산하는 관리표를 만들 수 있습니다.
이번 글에서는 엑셀 초보자도 직접 따라 할 수 있도록 거래처별 미수금 관리표를 만들고, SUMIFS 함수와 IF 함수를 활용해 미수금 합계와 입금 상태까지 자동으로 표시하는 방법을 알아보겠습니다.
미수금이란 무엇이며 왜 관리해야 할까?
미수금은 상품이나 서비스를 제공했지만 아직 받지 못한 금액을 말합니다. 실무에서는 외상매출금과 기타 미수금을 구분하기도 하지만, 여기서는 거래처에서 아직 회수하지 못한 판매대금을 관리하는 예제로 설명하겠습니다.
예를 들어 거래처에 150만 원어치 상품을 공급하고 100만 원만 입금받았다면 남은 미수금은 50만 원입니다.
미수금 계산식은 다음과 같습니다.
미수금 = 매출금액 – 입금금액
거래처별 미수금을 관리하면 다음과 같은 정보를 확인할 수 있습니다.
- 거래처별 미수금 잔액
- 전체 미수금 합계
- 입금이 완료된 거래
- 입금기한이 지난 거래
- 추가 입금이 필요한 거래처
특히 입금기한을 함께 기록하면 대금 회수가 늦어지는 거래를 빠르게 찾아낼 수 있습니다.
챗GPT로 엑셀 미수금 관리표 만드는 방법
먼저 챗GPT에 다음과 같이 입력합니다.
챗GPT 입력 예시
“엑셀에서 거래처별 미수금을 관리하려고 합니다. 거래일, 거래처명, 매출금액, 입금금액, 미수금, 입금기한, 입금상태를 입력할 수 있는 표를 만들고, 미수금과 입금상태는 자동으로 계산되도록 엑셀 수식을 알려주세요.”
챗GPT는 필요한 열 구성과 계산 수식을 제안할 수 있습니다. 이를 참고해 엑셀에 다음과 같은 관리표를 만듭니다.
| 거래일 | 거래처명 | 매출금액 | 입금금액 | 미수금 | 입금기한 | 입금상태 |
|---|---|---|---|---|---|---|
| 10/1 | 대한상사 | 1,500,000 | 1,000,000 | 500,000 | 10/15 | 연체 |
| 10/3 | 미래유통 | 2,000,000 | 2,000,000 | 0 | 10/20 | 입금완료 |
| 10/5 | 한빛물산 | 800,000 | 300,000 | 500,000 | 11/5 | 미수금 |
| 10/7 | 중앙상회 | 1,200,000 | 0 | 1,200,000 | 11/10 | 미수금 |
| 합계 | 5,500,000 | 3,300,000 | 2,200,000 |
표의 입금상태는 2026년 10월 30일을 기준으로 한 예시입니다.
실제 엑셀에서는 A열부터 G열까지 순서대로 입력하고, 첫 번째 거래 데이터는 2행부터 작성합니다.
엑셀에서 미수금 자동 계산하기
미수금을 매번 계산기로 계산하면 시간이 오래 걸리고 입력 오류도 발생할 수 있습니다.
엑셀 수식을 사용하면 매출금액과 입금금액만 입력해도 미수금이 자동 계산됩니다.
E2 셀에 다음 수식을 입력합니다.
=C2-D2
C2는 매출금액, D2는 입금금액입니다.
매출금액이 1,500,000원이고 입금금액이 1,000,000원이면 E2에는 500,000원이 표시됩니다.
수식을 입력한 다음 E2 셀 오른쪽 아래의 채우기 핸들을 아래로 끌면 다른 거래에도 자동으로 적용됩니다.
매출금액이나 입금금액이 변경되면 미수금도 즉시 다시 계산됩니다.
IF 함수로 입금상태 자동 표시하기
미수금 금액만 확인하는 것보다 입금상태를 함께 표시하면 관리하기 편리합니다.
G2 셀에 다음 수식을 입력합니다.
=IF(E2<0,"초과입금",IF(E2=0,"입금완료",IF(F2="","기한미입력",IF(TODAY()>F2,"연체","미수금"))))
이 수식은 미수금 잔액과 입금기한을 비교하여 상태를 표시합니다.
| 조건 | 표시 결과 |
|---|---|
| 미수금이 0원 | 입금완료 |
| 미수금이 남아 있고 입금기한 전 또는 당일 | 미수금 |
| 미수금이 남아 있고 입금기한 경과 | 연체 |
| 입금금액이 매출금액보다 많음 | 초과입금 |
| 미수금이 남아 있고 입금기한 미입력 | 기한미입력 |
TODAY() 함수는 컴퓨터의 현재 날짜를 기준으로 계산합니다.
따라서 오늘이 10월 30일이고 입금기한이 10월 15일인 거래처에 미수금이 남아 있다면 자동으로 연체가 표시됩니다.
단, 입금기한은 반드시 엑셀이 인식하는 날짜 형식으로 입력해야 합니다.
SUMIFS 함수로 거래처별 미수금 합계 구하기
한 거래처와 여러 번 거래했다면 각 거래의 미수금을 따로 확인하는 것보다 거래처별 총 미수금을 계산하는 것이 효율적입니다.
예를 들어 대한상사와 세 번 거래한 경우를 살펴보겠습니다.
| 거래처명 | 매출금액 | 입금금액 | 미수금 |
|---|---|---|---|
| 대한상사 | 1,500,000 | 1,000,000 | 500,000 |
| 대한상사 | 700,000 | 400,000 | 300,000 |
| 대한상사 | 900,000 | 900,000 | 0 |
| 합계 | 3,100,000 | 2,300,000 | 800,000 |
대한상사의 전체 미수금은 800,000원입니다.
거래처별 합계를 자동으로 구하려면 SUMIFS 함수를 사용합니다.
앞에서 작성한 원본 관리표를 기준으로 A열부터 G열까지 거래 데이터를 입력했다고 가정하겠습니다.
별도 요약 영역의 I1 셀에는 거래처명, J1 셀에는 총 미수금을 입력합니다.
I2 셀에 대한상사를 입력하고 J2 셀에 다음 수식을 작성합니다.
=SUMIFS($E$2:$E$1000,$B$2:$B$1000,I2)
이 수식은 B열에서 대한상사와 일치하는 거래를 찾고, 해당 거래의 E열 미수금만 합산합니다.
I3 셀에 미래유통, I4 셀에 한빛물산처럼 거래처명을 입력한 다음 J열 수식을 아래로 복사하면 거래처별 미수금을 자동 집계할 수 있습니다.
거래처가 많을 경우에는 엑셀의 피벗 테이블을 활용해 거래처별 합계를 한 번에 확인하는 방법도 있습니다.
전체 미수금과 회수율 자동 계산하기
거래처별 관리뿐 아니라 전체 매출에서 얼마를 회수했는지도 확인해야 합니다.
예를 들어 전체 매출금액이 5,500,000원이고 입금금액이 3,300,000원이라면 미수금은 2,200,000원입니다.
다음과 같이 요약표를 작성합니다.
| 관리 항목 | 금액 또는 비율 |
|---|---|
| 전체 매출금액 | 5,500,000원 |
| 전체 입금금액 | 3,300,000원 |
| 전체 미수금 | 2,200,000원 |
| 대금 회수율 | 60% |
전체 매출금액 계산 수식
=SUM(C2:C1000)
전체 입금금액 계산 수식
=SUM(D2:D1000)
전체 미수금 계산 수식
=SUM(E2:E1000)
대금 회수율 계산 수식
=IFERROR(SUM(D2:D1000)/SUM(C2:C1000),0)
회수율 수식을 입력한 셀의 표시 형식을 백분율로 변경하면 60%로 표시됩니다.
회수율은 전체 매출금액 중 실제 입금된 금액의 비율입니다. 회수율이 낮다면 미수금 잔액과 입금기한을 함께 점검할 필요가 있습니다.
조건부 서식으로 연체 거래처 강조하기
거래처가 수십 개 이상이면 연체된 거래를 눈으로 찾기 어려워집니다.
엑셀의 조건부 서식을 활용하면 연체된 거래를 자동으로 강조할 수 있습니다.
- A2 범위를 선택합니다.
- 홈 메뉴에서 조건부 서식을 클릭합니다.
- 새 규칙을 선택합니다.
- 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
- 다음 수식을 입력합니다.
=$G2="연체"
- 채우기 색상을 연한 빨간색으로 지정하고 확인을 누릅니다.
이제 입금상태가 연체로 표시된 거래는 해당 행 전체가 빨간색으로 강조됩니다.
입금이 완료되면 자동으로 연체 표시가 사라지므로 관리자가 일일이 색상을 변경할 필요가 없습니다.
엑셀 조건부 서식 기능에 관한 자세한 내용은 마이크로소프트 공식 안내에서 확인할 수 있습니다.
챗GPT로 미수금 관리 업무 자동화하기
챗GPT는 단순한 계산 수식 작성뿐 아니라 미수금 관리표를 개선하는 데도 활용할 수 있습니다.
예를 들어 다음과 같이 요청할 수 있습니다.
챗GPT 입력 예시
“엑셀 거래처별 미수금 관리표에서 미수금이 50만 원 이상이고 입금기한이 지난 거래처를 자동으로 찾는 수식을 만들어 주세요. 거래처별 미수금 합계와 연체 거래 건수도 확인할 수 있게 해주세요.”
미수금이 50만 원 이상인 연체 거래 건수를 계산하려면 COUNTIFS 함수를 사용할 수 있습니다.
=COUNTIFS(E2:E1000,">=500000",G2:G1000,"연체")
이 수식은 미수금이 500,000원 이상이면서 입금상태가 연체인 거래 건수를 계산합니다.
단, 동일 거래처에서 여러 건이 연체되었다면 거래처 수가 아닌 거래 건수로 집계됩니다.
챗GPT에 실제 엑셀 파일을 첨부하여 분석을 요청할 수도 있습니다. 이때 거래처의 계좌번호, 담당자 연락처 등 민감한 정보는 제거하거나 가명으로 바꾼 후 사용하는 것이 안전합니다.
미수금 관리표를 사용할 때 주의할 점
미수금 관리표는 계산 수식이 정확해도 실제 입금 내역을 잘못 기록하면 잔액이 틀어질 수 있습니다.
특히 다음 사항을 확인해야 합니다.
첫째, 같은 입금액을 중복으로 입력하지 않아야 합니다.
둘째, 입금이 여러 차례 이루어졌다면 해당 거래에 대한 누적 입금액을 정확히 반영해야 합니다.
셋째, 거래처명이 서로 다르게 입력되면 SUMIFS 함수에서 별도 거래처로 인식할 수 있습니다. 예를 들어 대한상사와 (주)대한상사는 서로 다른 이름으로 처리됩니다.
넷째, 취소·반품·할인 등으로 매출금액이 변경되면 그 내역을 반영해야 합니다.
다섯째, 입금기한이 지난 거래와 실제 회수가 불가능한 채권은 구분해야 합니다. 단순 연체가 곧 회수 불가능을 의미하는 것은 아닙니다.
거래가 많아질 경우에는 매출 관리표와 입금 내역표를 별도로 작성하고 거래번호를 기준으로 연결하는 방식이 더 정확합니다. 입금 내역을 누적 기록하면 부분 입금이나 여러 차례 입금된 거래도 확인하기 쉽습니다.
거래처별 미수금 관리표 활용 정리
챗GPT와 엑셀을 함께 사용하면 복잡한 회계 프로그램을 설치하지 않아도 기본적인 거래처별 미수금 관리표를 만들 수 있습니다.
핵심은 매출금액과 입금금액을 정확하게 기록하고, 수식으로 미수금과 입금상태를 자동 계산하는 것입니다.
SUMIFS 함수로 거래처별 미수금을 집계하고, IF 함수로 입금상태를 구분하며, COUNTIFS 함수로 연체 거래 건수를 확인하면 실무에 필요한 기본적인 관리가 가능합니다.
처음에는 거래일, 거래처명, 매출금액, 입금금액, 미수금, 입금기한, 입금상태의 7개 항목으로 시작하는 것이 좋습니다.
이후 거래 규모가 커지면 거래번호, 담당자, 최종 입금일, 연체일수 등의 항목을 추가해 관리표를 확장할 수 있습니다.
무엇보다 중요한 것은 관리표를 만드는 것에서 끝내지 않고 실제 입금 내역과 정기적으로 대조하는 습관입니다. 이를 통해 거래처별 미수금을 빠르게 파악하고 대금 회수 업무를 체계적으로 관리할 수 있습니다.
답글 남기기