거래처별 매출 보고서, 엑셀로 월마감 정리하는 법

거래처별 매출 보고서는 매월 마감 뒤 거래처마다 이번 달 매출과 반품, 전월과 전년 대비 증감, 목표 달성률, 미수 잔액을 한 표로 정리한 문서입니다. 이 보고서가 있어야 영업 회의에서 "전체 매출이 왜 줄었나"를 거래처 단위로 설명할 수 있고, 매출은 늘었는데 돈이 들어오지 않는 거래처를 일찍 찾을 수 있습니다. 거래처가 수십 곳이 넘으면 손으로 숫자를 옮기는 데만 월초 하루 이틀이 들고, 한 곳만 잘못 옮겨도 회의 내내 그 숫자를 다시 맞추게 됩니다. 엑셀로 원데이터를 모으고 집계하는 순서부터 보고서 양식까지 유통/제조 회사 기준으로 정리합니다.

거래처별 매출 보고서에 들어가는 숫자

보고서는 거래처 한 곳을 한 줄로 두고, 아래 숫자를 열로 둡니다. 거래처가 많으면 매출 상위 20곳과 증감이 큰 곳만 본문에 두고 나머지는 부록으로 보냅니다.

매출 기준은 회사 안에서 하나로 정합니다. 출고일 기준, 세금계산서 작성일 기준, 입금일 기준이 섞이면 같은 거래처라도 부서마다 다른 숫자가 나옵니다. 영업 보고서는 보통 출고(인도)일 기준 순매출을 쓰고, 미수금은 세금계산서와 입금 기록으로 맞춥니다. 월합계 세금계산서를 발행하는 거래처는 출고와 세금계산서 날짜가 다르므로, 보고서 하단에 기준을 적어 둡니다.

원데이터를 한 시트로 모으기

집계를 시작하기 전에 원데이터를 한 시트에 모읍니다. 판매 시스템에서 출고 내역, 반품 내역, 매출 할인 내역을 각각 내려받아 같은 열 구조(일자, 거래처 코드, 거래처명, 품목 코드, 수량, 금액, 구분)로 붙입니다. 반품과 할인은 금액을 음수로 넣으면 합계를 낼 때 따로 빼지 않아도 됩니다.

거래처는 이름이 아니라 코드로 맞춥니다. "한일리테일", "(주)한일리테일", "한일리테일 물류센터"처럼 이름이 흔들리면 피벗 테이블이 세 거래처로 나눠 버립니다. 본사와 지점을 한 거래처로 볼지 따로 볼지도 정해 두고, 거래처 코드 표에 상위 거래처 열을 하나 더 둡니다.

원데이터 범위는 엑셀 표로 만들어 둡니다. 다음 달 데이터를 아래에 붙이면 표 범위가 자동으로 늘어나 피벗 테이블과 수식이 새 데이터를 그대로 읽습니다. 붙이기 전에는 이번 달 출고 건수와 금액 합계를 판매 시스템 화면의 합계와 맞춰 봅니다. 내려받는 과정에서 페이지가 나뉘어 일부 행이 빠지는 일이 생각보다 자주 있고, 여기서 맞춰 두지 않으면 나중에 보고서 합계가 회계 숫자와 다를 때 원인을 찾기 어렵습니다.

피벗 테이블과 SUMIFS로 집계하기

집계는 두 가지 방법이 있습니다. 거래처별, 월별 합계를 빠르게 보려면 피벗 테이블이 편합니다. Microsoft 지원 문서에 나온 대로 원데이터 표 안의 셀을 선택하고 삽입 탭에서 피벗 테이블을 고른 뒤, 행에 거래처, 열에 월, 값에 금액을 넣으면 거래처와 월이 교차하는 집계표가 만들어집니다. 일자 필드를 열에 넣으면 월 단위로 묶을 수 있습니다.

보고서 양식이 정해져 있어 칸 위치가 고정되어야 한다면 SUMIFS 함수가 낫습니다. SUMIFS 함수는 SUMIFS(합계 범위, 조건 범위1, 조건1, 조건 범위2, 조건2, ...) 형식으로 여러 조건을 동시에 만족하는 값만 더합니다. 예를 들어 원데이터 표의 이름이 출고이고 보고서 A열에 거래처 코드, 1행에 월 시작일이 있다면 이번 달 순매출은 아래처럼 구합니다.

=SUMIFS(출고[금액], 출고[거래처코드], $A5, 출고[일자], ">="&C$1, 출고[일자], "<"&EDATE(C$1,1))

날짜 조건을 "이상"과 "미만"으로 거는 이유는 월말 날짜에 시간이 붙어 있거나 31일이 없는 달이 있어도 한 달을 정확히 잡기 위해서입니다. 반품을 음수로 넣었다면 이 수식 하나로 순매출이 나옵니다.

두 방법을 함께 쓰는 것도 좋습니다. 피벗 테이블로 전체 합계를 보고, SUMIFS로 만든 보고서의 합계와 같은지 맞춰 보면 수식 실수를 바로 찾을 수 있습니다.

증감 기여도와 미수금 연령 계산

전체 매출이 전월보다 4,000만 원 줄었다면, 그 4,000만 원이 어느 거래처에서 왔는지를 보여 주는 것이 기여도입니다. 거래처별 증감액을 전체 증감액으로 나누면 됩니다. 한 거래처가 -3,200만 원이었다면 기여도는 80%로, 전체 감소의 대부분이 그 한 곳에서 나왔다는 뜻입니다. 기여도 순으로 정렬하면 회의에서 다룰 거래처가 자연스럽게 정해집니다.

미수금은 잔액만 보지 말고 경과 일수 구간으로 나눕니다. 세금계산서 작성일부터 기준일까지 지난 일수를 구해 30일 이내, 31~60일, 61~90일, 90일 초과로 나누는 방식이 일반적입니다. 경과 일수는 =기준일-작성일로 구하고, 구간은 IFS 함수나 구간표와 XLOOKUP으로 붙입니다. 거래처별 결제 조건(예: 월말 마감 익월 말 현금)을 넘긴 금액은 따로 열을 두어 빨간색으로 표시하면, 매출 순위와 함께 회수 위험이 한눈에 보입니다.

영업일 수가 다른 달을 비교할 때는 일평균 매출로 한 번 더 봅니다. 9월 영업일이 22일이고 10월이 추석 연휴로 19일이었다면, 10월 매출이 9월보다 8% 줄었어도 일평균으로는 6% 늘었을 수 있습니다. 보고서에 영업일 수와 일평균 매출 열을 하나씩 더해 두면 회의에서 "연휴 때문인가"를 두고 따로 계산할 일이 없습니다.

보고서 양식: 요약, 거래처 표, 설명 세 부분

보고서는 세 부분으로 나누면 읽는 사람마다 필요한 곳만 볼 수 있습니다. 첫 쪽 요약에는 전체 순매출, 전월/전년 대비, 목표 달성률, 결제 조건을 넘긴 미수 총액을 숫자 네 개로 두고, 그 아래에 이번 달 변화를 만든 거래처 세 곳을 한 줄씩 적습니다. 대표나 영업 임원은 이 한 쪽만 읽고 회의에 들어옵니다.

둘째 부분은 거래처 표입니다. 앞에서 정한 열을 그대로 두고, 담당 영업사원 열을 더해 담당자별로 필터링할 수 있게 합니다. 셋째 부분은 설명입니다. 기여도 상위 거래처와 미수 연령이 나빠진 거래처마다 원인과 다음 달 조치를 두세 줄로 적습니다. 숫자는 표에 있으니 설명에는 숫자를 반복하지 않고 이유와 할 일만 씁니다. 이 형식을 매달 유지하면 지난달 설명에 적은 조치가 이번 달 숫자로 어떻게 이어졌는지 바로 확인할 수 있습니다.

👉 채권관리 방법, 입금 대사부터 대손 처리까지

예시: 식품 유통사의 10월 마감 보고서

가공식품을 할인점, 2차 도매상, 급식업체에 유통하는 회사의 영업관리 담당자가 11월 3일에 10월 거래처별 매출 보고서를 만든다고 해 보겠습니다. 거래처는 46곳, 10월 전체 순매출은 18억 2,000만 원으로 9월보다 4,000만 원 줄었습니다.

담당자는 판매 시스템에서 10월 출고 2,814건, 반품 63건, 매출 할인 12건을 내려받아 원데이터 표 아래에 붙였습니다. 거래처 코드 표를 확인하니 9월에 새로 거래를 시작한 급식업체 한 곳이 이름만 있고 코드가 없어, 코드를 만들어 넣은 뒤 집계했습니다. 피벗 테이블의 10월 합계와 SUMIFS 보고서의 합계가 둘 다 18억 2,000만 원으로 맞는 것을 확인했습니다.

기여도 순으로 정렬하니 한일리테일이 -3,200만 원으로 감소의 80%를 차지했습니다. 품목별로 열어 보니 한일리테일의 10월 행사 품목이 9월과 달라져 즉석밥 매출이 크게 빠진 것이 원인이었습니다. 전년 10월과 비교하면 한일리테일 매출은 오히려 6% 늘어 있어, 담당자는 일시적인 행사 효과로 정리했습니다. 반대로 성우유통은 매출이 1,500만 원 늘었지만 미수금 중 61~90일 구간이 2,300만 원으로, 결제 조건(익월 말)을 한 달 넘긴 금액이 처음 생겼습니다.

보고서 본문에는 상위 20개 거래처 표와 기여도 상위 5곳의 설명 한 줄씩, 결제 조건을 넘긴 미수 거래처 목록을 넣었습니다. 회의에서는 한일리테일 11월 행사 품목 확인과 성우유통 결제 일정 확인을 담당자별 할 일로 정했고, 두 건의 결과는 11월 보고서의 설명 부분에 이어서 적기로 했습니다.

거래처별 매출 보고서에서 자주 하는 실수

첫째, 반품을 빼지 않은 출고 매출로 보고하는 것입니다. 반품이 많은 거래처는 실제보다 좋아 보이고, 월말 회계 숫자와 맞지 않아 보고서 전체를 의심받게 됩니다.

둘째, 영업일 수 차이를 무시하는 것입니다. 추석이나 설이 낀 달은 영업일이 3~4일 적습니다. 전월 대비가 크게 줄었다면 일평균 매출을 함께 보여 주어야 실제 변화가 보입니다.

셋째, 거래처 이름으로 집계하는 것입니다. 이름 표기가 한 글자만 달라도 다른 거래처로 집계됩니다. 거래처 코드 표를 한 곳에서 관리하고, 새 거래처는 첫 출고 전에 코드부터 만듭니다.

넷째, 매출과 미수를 따로 보고하는 것입니다. 매출 상위 거래처가 미수도 상위인 경우가 많습니다. 한 줄에 두 숫자를 같이 두어야 매출을 늘릴 거래처와 회수를 먼저 할 거래처가 구분됩니다.

거래처별 매출 보고서 자주 묻는 질문 (FAQ)

Q. 피벗 테이블과 SUMIFS 중 무엇을 써야 하나요?

자유롭게 나눠 보는 분석에는 피벗 테이블, 매달 같은 양식에 숫자를 채우는 보고서에는 SUMIFS가 맞습니다. 두 결과의 합계를 맞춰 보는 것을 마감 점검 항목으로 두면 실수를 줄일 수 있습니다.

Q. 거래처별 이익도 보고서에 넣어야 하나요?

가능하면 넣습니다. 매출 상위 거래처가 할인과 물류비 때문에 이익은 낮은 경우가 많습니다. 원가 마감 전이라면 표준원가 기준 매출총이익을 잠정치로 넣고, 마감 후 확정치로 바꿉니다.

Q. 영업사원마다 자기 거래처만 보게 하려면 어떻게 하나요?

원데이터와 거래처 표에 담당자 열을 두고, 피벗 테이블에 슬라이서를 붙여 담당자를 고르게 하는 방법이 간단합니다. 담당자별로 파일을 따로 나눠 보내면 원데이터가 여러 벌로 갈라져 다음 달에 다시 합쳐야 하므로, 한 파일에서 걸러 보는 방식을 권합니다. 담당자가 바뀐 거래처는 바뀐 날짜를 거래처 표에 남겨 두어야 지난 실적을 누구 것으로 볼지 다투지 않습니다.

Q. 보고서는 언제까지 만들어야 하나요?

출고와 반품 입력이 끝나는 월초 2~3영업일 안에 만드는 회사가 많습니다. 늦어질수록 회의에서 정한 조치가 그달 영업에 반영될 시간이 줄어듭니다. 세금계산서 발행이 끝난 뒤 미수금 숫자까지 한 번에 확정하는 일정을 잡습니다.

inline AI로 거래처별 매출 보고서 만들기

inline AI는 ARP의 출고, 반품, 세금계산서, 입금 기록을 거래처 코드 기준으로 모아 순매출, 전월/전년 대비, 기여도, 미수 연령을 계산하고, 회사 보고서 양식에 그대로 채웁니다. 기여도가 큰 거래처는 품목별 원인까지 찾아 한 줄 설명을 붙이기 때문에, 마감 숫자가 확정되는 날 바로 회의 자료가 준비됩니다.