선입선출(FIFO) 재고 평가·로트 관리 엑셀 템플릿
창고에서 선입선출은 두 가지를 뜻해요. 오래된 재고부터 꺼내고, 남은 재고는 가장 최근 입고 원가로 평가하는 것. 둘 다 로트 단위 기록이 필요해요. 이 통합 문서는 입고마다 원가와 유통기한이 있는 로트로, 출고는 날짜순으로 기록해요. 수식이 로트마다 얼마나 쓰였는지 계산하고, 출고마다 실제로 꺼낸 로트의 원가로 매기고, FIFO 재고 금액, 출고 원가, 다음 피킹 로트, 만료·임박·장기 재고를 알려줘요.
- 시트:
- 7
- 지원 버전:
- Microsoft 365용 Excel, Excel 2016 이후 버전, Mac용 Excel, 웹용 Excel
- 업데이트:
이 템플릿으로 할 수 있는 일
- 로트 대장: 입고일, 수량, 단가, 유통기한과 FIFO 기준 사용·잔여 수량
- 서로 다른 원가의 로트 두세 개에 걸친 출고도 정확한 FIFO 원가
- 품목별 현재고, FIFO 금액, 평균 단가, 출고 원가, 다음 피킹 로트, 가장 빠른 유통기한
- 만료·임박·장기 재고 표시와 보관 일수·남은 유통기한별 재고 연령 보고서
구성
통합 문서에는 시트가 7개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.
| 시트 | 용도 | 입력하는 것 | 자동 계산되는 것 |
|---|---|---|---|
| Start Here | 설정과 평가 | 평가 기준일, 유통기한 경고 일수, 장기 재고 일수 | FIFO 금액, 출고 원가, 재고 로트 수, 만료·임박·장기 금액, 가장 오래된 로트, 재고 초과 출고 |
| Items | 품목별 한 행(30) | 품목 코드, 설명, 단위 | 입고, 출고, 현재고, FIFO 금액, 평균 단가, 출고 원가, 다음 피킹 로트, 가장 빠른 유통기한, 상태 |
| Lots | 날짜순 입고(300) | 로트, 품목, 입고일, 수량, 단가, 유통기한 | 로트 금액, 이전 로트 수량, 사용 수량, 잔여, 잔여 금액, 보관 일수, 남은 유통기한, 상태, 순서 확인 |
| Issues | 날짜순 출고(600) | 날짜, 품목, 수량, 참조 | 이전 출고 수량, FIFO 원가, 단가, 재고 확인 |
| Aging | 보관 기간별·유통기한별 금액 | 구간 경계 | 구간별 로트 수와 금액 |
| Lists | 단위 | 단위 | 없음 |
| Checks | 데이터 검사 6개 | 없음 | OK 또는 Review |
시작하기 전에
- 입고마다 로트 번호를 주세요(발주 번호와 행, 또는 공급업체 배치 번호).
- 로트와 출고를 날짜순으로 입력하세요. 순서가 어긋나면 검사가 잡아요.
- 재고를 착지 원가(단가 + 운임)로 평가한다면 단위당 착지 원가를 쓰세요.
단계별 사용법
평가 기준일 설정
Start Here B6: 보통 월말. 그 이후 입고와 출고는 평가에서 빠져요.
품목 목록
Items 탭: 코드, 설명, 단위. 행의 나머지는 계산돼요.
입고를 로트로 기록
Lots 탭: 로트 번호, 품목, 입고일, 수량, 단가, 유통기한. 오래된 것부터.
출고 기록
Issues 탭: 날짜, 품목, 수량, 참조(주문이나 작업 지시). 옆에 FIFO 원가가 나와요.
피킹과 회전
Items J열이 재고가 남은 가장 오래된 로트예요. 거기서 꺼내세요. L열과 Lots N열이 만료·임박 재고를 표시해요.
월 마감
Start Here에 FIFO 금액과 출고 원가가 나오고, Checks 탭이 입고 금액 − 출고 원가 = 재고 금액임을 증명해요.
수식 지도
통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.
| 시트 | 셀 | 함수 | 계산 내용 | 읽는 값 | 쓰이는 곳 |
|---|---|---|---|---|---|
| Lots | H5 304행까지 아래로 복사됨 | IF, SUMIF | 이 로트 이전 수량 | B5 | 사용 수량과 출고 FIFO 원가가 모두 이 값을 기준으로 해요. |
| Lots | I5 304행까지 아래로 복사됨 | IF, OR, MAX, MIN, SUMIFS | 이 로트에서 사용한 수량 | D5, H5 | 잔여 = 수량 − 사용, 잔여 금액 = 잔여 × 이 로트 단가. |
| Lots | N5 304행까지 아래로 복사됨 | IF, AND | 로트 상태 | J5, M5, L5 | Start Here가 상태별 잔여 금액을 더해요. |
| Issues | E5 604행까지 아래로 복사됨 | IF, SUMIF | 이전 출고 수량 | B5 | 로트의 시작·끝 위치와 함께 출고 원가를 매겨요. |
| Issues | F5 604행까지 아래로 복사됨 | IF, SUMPRODUCT, ABS | 출고의 FIFO 원가 | E5, C5 | 품목별로 더하면 출고 원가예요. 10개 출고가 $32.00짜리 4개와 $33.10짜리 6개를 가져가면 $326.60. |
| Issues | H5 604행까지 아래로 복사됨 | IF, SUMIFS | 재고 확인 | E5, C5 | Start Here B19와 검사에서 셉니다. |
| Items | J5 34행까지 아래로 복사됨 | IF, IFERROR, INDEX, MATCH | 다음 피킹 로트 | A5 | FIFO 회전을 위한 피킹 지시예요. |
| Items | K5 34행까지 아래로 복사됨 | IF, OR, MINIFS | 재고 중 가장 빠른 유통기한 | A5 | 품목 상태를 정해요. |
| Aging | E5 9행까지 아래로 복사됨 | SUMIFS | 보관 기간 구간별 금액 | — | Start Here 차트. 합계는 FIFO 금액과 같아야 해요. |
모든 수식 설명
아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.
=IF(A5="", 0, SUMIF($B$4:B4, B5, $D$4:D4))같은 품목의 앞선 로트 수량을 모두 더합니다. 품목의 FIFO 대기열에서 이 로트가 시작하는 위치예요.
부분별 설명
$B$4:B4- 늘어나는 범위: 이 행 위의 모든 행.
SUMIF(…, B5, $D$4:D4)- 이 품목 앞선 로트들의 수량.
IF(A5="", 0, …)- 빈 행은 0이라 다른 수식이 계산할 수 있어요.
예시 데이터로 보면: B5 = BEAN-B → H5 결과 0
어떻게 쓰이나: 사용 수량과 출고 FIFO 원가가 모두 이 값을 기준으로 해요.
바꾸는 방법: 바꿀 것 없음. 로트를 날짜순으로 유지하세요.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(OR(A5="", C5>'Start Here'!$B$6),
0, MAX(0, MIN(D5, SUMIFS(Issues!$C$5:$C$604, Issues!$B$5:$B$604, B5, Issues!$A$5:$A$604, "<="&'Start Here'!$B$6)-H5)))기준일까지 품목 총 출고량에서 앞선 로트가 감당한 양을 빼고, 0과 로트 수량 사이로 제한합니다.
부분별 설명
SUMIFS(Issues!$C$5:$C$604, Issues!$B$5:$B$604, B5, Issues!$A$5:$A$604, "<="&'Start Here'!$B$6)- 기준일까지 이 품목 출고량.
-H5- 앞선 로트가 공급한 양을 뺌.
MIN(D5, …)- 로트 수량보다 많을 수 없음.
MAX(0, …)- 음수 없음.
C5>'Start Here'!$B$6- 기준일 이후 입고된 로트는 손대지 않음.
예시 데이터로 보면: D5 = 50, H5 = 0 → I5 결과 50
어떻게 쓰이나: 잔여 = 수량 − 사용, 잔여 금액 = 잔여 × 이 로트 단가.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 MIN, MAX 함수 사용법 →=IF(A5="", "", IF(C5>'Start Here'!$B$6,
"Not yet received", IF(J5=0, "Used up", IF(AND(M5<>"", M5<0), "Expired", IF(AND(M5<>"", M5<='Start Here'!$B$7), "Expiring soon", IF(L5>'Start Here'!$B$8, "Aged", "In stock"))))))차례로 확인합니다: 미입고, 소진, 만료, 경고 기간 내 만료 예정, 장기 기준 초과, 그 외 재고 있음.
부분별 설명
C5>'Start Here'!$B$6- 기준일 이후 입고.
J5=0- 소진.
M5<0- 만료.
M5<='Start Here'!$B$7- 만료 임박.
L5>'Start Here'!$B$8- 장기 재고.
예시 데이터로 보면: J5 = 0, M5 = —, L5 = — → N5 결과 Used up
어떻게 쓰이나: Start Here가 상태별 잔여 금액을 더해요.
바꾸는 방법: 기간은 Start Here에서 바꾸세요.
배우기: 엑셀 IF 함수 사용법 →=IF(A5="", "", SUMIF($B$4:B4, B5, $C$4:C4))같은 품목 앞선 출고의 누계: FIFO 대기열에서 이 출고가 시작하는 위치.
부분별 설명
SUMIF($B$4:B4, B5, $C$4:C4)- 이 품목의 앞선 출고.
예시 데이터로 보면: B5 = FLR-50 → E5 결과 0
어떻게 쓰이나: 로트의 시작·끝 위치와 함께 출고 원가를 매겨요.
바꾸는 방법: 바꿀 것 없음. 출고를 날짜순으로 유지하세요.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(A5="", "", SUMPRODUCT((Lots!$B$5:$B$304=B5)*(((((Lots!$H$5:$H$304+Lots!$D$5:$D$304)+(E5+C5)-ABS((Lots!$H$5:$H$304+Lots!$D$5:$D$304)-(E5+C5)))/2)-((Lots!$H$5:$H$304+E5+ABS(Lots!$H$5:$H$304-E5))/2))+ABS(((((Lots!$H$5:$H$304+Lots!$D$5:$D$304)+(E5+C5)-ABS((Lots!$H$5:$H$304+Lots!$D$5:$D$304)-(E5+C5)))/2)-((Lots!$H$5:$H$304+E5+ABS(Lots!$H$5:$H$304-E5))/2))))/2*Lots!$E$5:$E$304))각 로트를 시작(이전 수량)부터 끝(시작 + 수량)까지의 구간으로, 이 출고를 E5부터 E5 + C5까지의 구간으로 봅니다. 로트마다 겹치는 양에 그 로트 단가를 곱해 더해요.
부분별 설명
(Lots!$B$5:$B$304=B5)- 이 품목 로트만.
(a+b−ABS(a−b))/2- a와 b 중 작은 값을 열 전체에 쓸 수 있게 쓴 식: min(로트 끝, 출고 끝).
(a+b+ABS(a−b))/2- 큰 값: max(로트 시작, 출고 시작).
(x+ABS(x))/2- 겹치는 양, 안 겹치면 0.
*Lots!$E$5:$E$304- 로트별 단가를 곱함.
SUMPRODUCT(…)- 모든 로트에 걸쳐 더함.
예시 데이터로 보면: E5 = 0, C5 = 4 → F5 결과 73.96
어떻게 쓰이나: 품목별로 더하면 출고 원가예요. 10개 출고가 $32.00짜리 4개와 $33.10짜리 6개를 가져가면 $326.60.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 필수 함수 SUM, AVERAGE, COUNT, MIN, MAX 사용법 →=IF(A5="", "", IF(E5+C5>SUMIFS(Lots!$D$5:$D$304, Lots!$B$5:$B$304, B5, Lots!$C$5:$C$304, "<="&A5),
"Short", "OK"))이 출고로 그날까지 입고된 수량을 넘으면 Short.
부분별 설명
E5+C5- 이 출고 후 누적 출고.
SUMIFS(Lots!$D$5:$D$304, Lots!$B$5:$B$304, B5, Lots!$C$5:$C$304, "<="&A5)- 출고일까지 입고량.
예시 데이터로 보면: E5 = 0, C5 = 4 → H5 결과 OK
어떻게 쓰이나: Start Here B19와 검사에서 셉니다.
바꾸는 방법: 날짜나 수량을 고치거나 빠진 입고를 기록하세요.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →=IF(A5="", "", IFERROR(INDEX(Lots!$A$5:$A$304, MATCH(1, INDEX((Lots!$B$5:$B$304=A5)*(Lots!$J$5:$J$304>0), 0), 0)),
"None"))목록에서 처음(가장 오래된) 나오는, 수량이 남은 이 품목 로트.
부분별 설명
(Lots!$B$5:$B$304=A5)*(Lots!$J$5:$J$304>0)- 재고 있는 이 품목 로트는 1.
INDEX(…, 0)- Ctrl+Shift+Enter 없이 MATCH가 배열을 찾게 해요.
MATCH(1, …, 0)- 그런 첫 로트.
INDEX(Lots!$A$5:$A$304, …)- 로트 번호.
예시 데이터로 보면: A5 = OIL-1L → J5 결과 L26073
어떻게 쓰이나: FIFO 회전을 위한 피킹 지시예요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 INDEX와 MATCH 함수 사용법 →=IF(OR(A5="", F5=0), "", MINIFS(Lots!$F$5:$F$304,
Lots!$B$5:$B$304, A5, Lots!$J$5:$J$304,
">0"))재고가 남은 이 품목 로트들 중 가장 이른 유통기한.
부분별 설명
MINIFS(Lots!$F$5:$F$304, Lots!$B$5:$B$304, A5, Lots!$J$5:$J$304, ">0")- 품목 일치, 잔여 있음 조건의 최소 유통기한.
예시 데이터로 보면: A5 = OIL-1L → K5 결과 2028년 3월 25일
어떻게 쓰이나: 품목 상태를 정해요.
바꾸는 방법: 바꿀 것 없음.
배우기: 엑셀 MIN, MAX 함수 사용법 →=SUMIFS(Lots!$K$5:$K$304,
Lots!$L$5:$L$304, ">="&B5,
Lots!$L$5:$L$304, "<="&C5)보관 일수가 구간 경계 사이인 로트의 잔여 금액을 더합니다.
부분별 설명
Lots!$K$5:$K$304- 잔여 금액.
Lots!$L$5:$L$304, ">="&B5- 보관 일수가 From 이상.
…, "<="&C5- To 이하.
예시 데이터로 보면: E5 결과 33,346.45
어떻게 쓰이나: Start Here 차트. 합계는 FIFO 금액과 같아야 해요.
바꾸는 방법: B, C열 경계를 고치세요.
배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →팁과 한계
- 용량: 품목 30개, 로트 300개, 출고 600건. 이력이 길어지면 해마다 새 파일을 만들고 기초 로트를 잔여 수량과 원가로 넣으세요.
- 로트와 출고는 날짜순으로. FIFO는 순서에 달려 있고, Lots O열과 Issues H열 검사가 어긋난 행을 알려줘요.
- 반품 재입고: 출고 때의 원가로 새 로트로 입력하세요.
- 재고 조정(파손, 실사 차이): 부족분은 ADJ 같은 참조로 출고에, 초과분은 새 로트로 입력하세요.
- FIFO는 평가와 회전용이에요. 유통기한 순(FEFO)으로 꺼내야 한다면 Items K열과 Lots 상태를 쓰세요.
이 템플릿에 쓰인 기능 배우기
도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com