엑셀 재고관리: 입출고 장부와 실사 수량이 다를 때 추적하는 방법
AI Editter
재고 실사에서 장부 25개, 실제 22개가 나왔다고 장부 숫자를 22로 덮어쓰면 당장은 맞습니다. 대신 출고 누락인지, 창고 이동 중인지, 단위가 다른지 확인할 단서가 사라집니다. 다음 달에도 같은 차이가 생길 수 있습니다.
엑셀 재고관리의 목표를 “현재 수량 표시”에만 두지 말고, 어떤 입출고가 그 수량을 만들었는지 되짚을 수 있는 장부로 잡아야 합니다. 여기서는 금액 평가보다 품목·창고·수량의 일치에 집중합니다.
장부와 실사가 같은 것을 세고 있는지부터 확인
실사 기준은 품목코드 + 창고 + 기준시각 + 단위입니다. 필요하면 로트와 보관 상태도 추가합니다. 불량품을 실사에는 포함하고 장부에서는 제외했다면 같은 숫자가 나올 수 없습니다.
다음은 가상 품목 A-10의 본창고 기록입니다. 모든 수량은 EA 기준이며, 기초재고는 10월 1일 00:00 직전의 확정 값입니다.
| 시각 | 전표 | 구분 | 수량 변화 | 계산 후 수량 |
|---|---|---|---|---|
| 기초 | OPEN-A10 | 이월 | 20 | 20 |
| 10-01 09:00 | IN-001 | 입고 | 12 | 32 |
| 10-01 11:00 | OUT-001 | 출고 | -7 | 25 |
| 10-01 14:00 | MOVE-001-OUT | 보조창고 이동 | -3 | 22 |
10월 1일 18:00 기준 본창고 실사 22개라면 일치합니다. 이동 전표가 누락된 장부는 25개로 보입니다. 전사 전체 재고에서는 보조창고에 들어온 +3도 있어야 하므로, 창고 이동은 총재고를 줄이지 않습니다.
입력 표는 거래 기록, 현황 표는 계산 결과
거래 표 수불에는 최소한 전표번호, 시각, 품목, 창고, 구분, 수량변화, 근거를 둡니다. 수량변화는 입고·반품입고가 양수, 출고·폐기가 음수인 방식으로 통일합니다. 입력 수량을 모두 양수로 받는다면 구분별 부호를 별도 열에서 계산하고 허용 구분을 목록으로 제한합니다.
현황 표의 현재고는 직접 수정하지 않습니다. 실사 차이는 승인된 조정 전표를 추가해 반영합니다. 조정 사유가 차이 맞춤뿐이라면 다음 점검 때 같은 문제가 반복돼도 원인을 알 수 없습니다.
전표번호 중복을 검사할 때는 한 전표에 여러 품목이 가능한지도 확인합니다. 그 구조라면 전표번호 + 행번호가 거래 식별자입니다. 창고 이동의 출고와 입고는 공통 이동번호를 공유하고 각 행의 식별자는 달라야 합니다.
기준시각까지 합산하는 SUMIFS
현황 시트에서 A2가 품목, B2가 창고, C2가 확정 기초수량, F1이 기초 기준시각, G1이 실사 기준시각이라고 하겠습니다. 기초는 F1 직전까지 반영한 수량이고, 이후 거래를 F1 이상 G1 미만으로 집계합니다.
=C2+SUMIFS(수불[수량변화],
수불[품목],A2,
수불[창고],B2,
수불[시각],">="&$F$1,
수불[시각],"<"&$G$1)날짜만 표시돼도 실제 셀에 시각이 들어 있으면 비교 결과가 달라집니다. 하루 전체를 집계할 때는 종료일 23:59:59보다 다음 날 00:00 미만을 조건으로 두면 경계가 명확합니다. 범위 크기와 조건 인수는 SUMIFS 공식 문서에서 확인할 수 있습니다.
SUMIFS가 0을 반환했다고 품목이 정상 등록됐다는 뜻은 아닙니다. 품목 마스터에 존재하는 코드인지, 창고 코드가 허용 목록에 있는지, 수량과 시각이 실제 숫자인지 따로 검사합니다.
차이가 났을 때 확인할 순서
① 마감시각을 맞춥니다. 실사 중 입출고가 계속됐다면 실사 시작·종료 사이의 거래를 따로 모읍니다. 18:00에 센 수량과 18:30까지 입력된 장부를 비교하면 정상 거래도 차이로 나타납니다.
② 창고 이동을 양쪽에서 확인합니다. 본창고 -3이 있는데 보조창고 +3이 없다면 이동 중 재고인지 입력 누락인지 확인합니다. 두 창고 모두 같은 시점에 확정해야 전사 합계가 맞습니다.
③ 단위를 확인합니다. BOX 2개가 EA 2개로 들어갔는지 봅니다. 1BOX=12EA라면 24EA지만, 포장 수량이 바뀌는 품목은 현재 환산율을 과거 거래 전체에 적용하면 안 됩니다. 거래 시점의 환산율을 보관합니다.
④ 취소·반품·불량품을 확인합니다. 출고 취소를 원본 행 삭제로 처리했는지, 반품을 입고와 반품입고에 두 번 넣었는지 점검합니다. 보류·불량 재고는 판매 가능 수량과 분리해 봅니다.
⑤ 근거를 확인한 뒤 조정합니다. 분실이나 파손으로 확정된 경우 조정 사유, 승인자, 증빙 위치를 남깁니다. 조사 중인 차이를 자동으로 폐기 처리하지 않습니다.
도입 전에 틀린 데이터를 넣어 보는 검사
| 시험 | 기대 결과 |
|---|---|
| 같은 거래 식별자를 두 번 입력 | 중복 경고, 확정 대상에서 확인 필요 |
| 등록되지 않은 품목 입력 | 미등록 품목 목록에 표시 |
| 출고 후 수량이 음수 | 음수 발생 시각과 전표를 추적 |
| 이동 출고만 입력 | 짝이 없는 이동번호 표시 |
| 실사 직후 거래 추가 | 해당 실사 기준 수량은 유지 |
| 포장 단위를 잘못 입력 | 기본 단위 환산 차이로 발견 |
음수 재고는 무조건 수량을 0으로 고칠 문제가 아닙니다. 입고 입력 지연, 소급 전표, 실제 재고 부족을 구분해야 합니다. 월말 현재고만 보는 것보다 시간순 잔량에서 처음 음수가 된 전표를 찾는 편이 빠릅니다.
엑셀로 유지할 수 있는 범위 판단
한 담당자가 제한된 창고와 품목을 관리하고, 입력과 마감을 통제할 수 있다면 엑셀로도 추적 가능한 장부를 만들 수 있습니다. 동시에 여러 사람이 수정하거나, 로트·유통기한·바코드·예약 재고까지 관리해야 한다면 파일 잠금과 이력 관리가 병목이 됩니다.
프로그램 도입 여부를 판단할 때는 현재고 화면보다 “잘못된 전표를 누가 언제 고쳤는지”, “이동 중 재고를 어떻게 처리하는지”, “마감 후 소급 입력이 가능한지”를 시연해 달라고 요청합니다. 엑셀에서 정리한 예외 사례가 그대로 도입 검증 자료가 됩니다.