엑셀 재고 보유 수량(SOH) 관리 — ROP·안전재고·EOQ 자동 계산 템플릿
환경: Excel 365. 글 구성은 AI 보조를 받았지만 모든 수식은 직접 재현했습니다.
연계 가이드(내부 링크 7+)
1) 표 설계와 기본 규칙
- ItemsTbl : Item, UOM, LeadDays, Z(서비스레벨), AvgDailyDemand, SdDailyDemand, D(연수요), S(주문고정비), H(연보유비율)
- MovTbl : Date, Item, Warehouse, Type(IN/OUT/ADJ), Qty, UnitCost
- 모든 계산은 구조적 참조로 — 열이 이동해도 안전.
2) 보유 수량(SOH) 계산 — SUMIFS vs SCAN
방법 A) 품목·창고별 합계(SUMIFS)
=SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "IN")
-SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "OUT")
+SUMIFS(MovTbl[Qty], MovTbl[Item], [@Item], MovTbl[Warehouse], E2, MovTbl[Type], "ADJ")방법 B) 날짜순 누계(SCAN) — 일자별 잔액표
/* 특정 품목 i, 창고 w 의 일자별 누계 */
=LET(
t, SORT(FILTER(MovTbl, (MovTbl[Item]=i)*(MovTbl[Warehouse]=w)), 1, 1),
q, MAP(t[Type], t[Qty], LAMBDA(tp,qty, IF(tp="IN", qty, IF(tp="OUT",-qty, qty)))),
SCAN(0, q, LAMBDA(a,v, a+v)) )TIP: 음수 재고 방지
누계가 0 미만이면 강조: 조건부서식 셀 값 < 0 → 빨간 채우기.
3) 재주문점(ROP)·안전재고·EOQ — 공식 & 엑셀 수식
안전재고(일수요 변동 사용)
=ROUND( [@Z] * [@SdDailyDemand] * SQRT([@LeadDays]) , 0 )재주문점(ROP)
=ROUND( [@AvgDailyDemand] * [@LeadDays] + [@SafetyStock] , 0 )경제적 주문량(EOQ)
=ROUND( SQRT( 2*[@D]*[@S] / ([@H]*[@UnitCost]) ) , 0 )발주 알림(현재 SOH가 ROP 이하)
=IF( SOH <= [@ROP], "Reorder: "&TEXT([@EOQ],"#,##0"), "" )4) 회전율·재고일수(DOH)·재고가치
/* 기간 매출원가(COGS)가 없다면 출고×단가 근사 */
재고가치(현재) = SOH * 최근평균단가
회전율 = 총출고수량 / 평균재고수량
재고일수(DOH) = IFERROR( 평균재고수량 / 일평균출고수량 , 0 )/* 최근평균단가 — 이동가중평균 예시 */
=LET(t, FILTER(MovTbl, MovTbl[Item]=[@Item]),
inQty, FILTER(t[Qty], t[Type]="IN"),
inCost, FILTER(t[UnitCost], t[Type]="IN"),
SUM(inQty*inCost)/SUM(inQty))5) 피벗 없이 만드는 재고 대시보드
/* 품목별 핵심 지표 테이블 (정렬 Top N) */
=LET(
u, UNIQUE(MovTbl[Item]),
soh, MAP(u, LAMBDA(x, SUMIFS(SOH, Item, x))),
rop, XLOOKUP(u, ItemsTbl[Item], ItemsTbl[ROP]),
eoq, XLOOKUP(u, ItemsTbl[Item], ItemsTbl[EOQ]),
t, HSTACK(u, soh, rop, eoq),
SORT(t, 2, -1)) // SOH 기준 내림차순/* 재고 부족 리스트 */
=FILTER( 대시보드표, 대시보드표[SOH] <= 대시보드표[ROP] )슬라이서 또는 유효성 드롭다운으로 창고/카테고리 필터를 얹으면 운영이 훨씬 편해집니다.
6) 오류·성능·운영 체크리스트
- 표(Table) + 구조적 참조를 기본으로 — 수식 안정성↑
- 날짜 형식 통일(텍스트 날짜는
DATEVALUE변환) - 입고/출고 부호 일관(
Type으로 +/− 판정) - 월별 로그는 시트별로 관리하고
VSTACK으로 통합 - 대량 데이터는
LET으로 중간 스필 캐시 → 속도 개선 - 조건부서식: SOH ≤ ROP 빨간 배경, 0 미만 굵은 테두리