피크기간 의류 PLU별 판매예측 & 일일 입고 평준화(Excel+Copilot+Solver)
4주 히스토리·재고/입고 스케줄·피크 세일 계획을 활용해 PLU별 일예측을 만들고, 재고 제약을 반영한 실제 판매량을 계산한 뒤, 하루 총 팔레트 수를 동일하게 맞추는 평준화 계획을 Excel에서 완성합니다. Copilot 프롬프트와 Solver 설정까지 포함합니다.
Quick Fix
- 정상가 기준 28일 기초율 계산
- 세일/정상가 Uplift 계산
- 피크 플랜 Uplift 반영 후 Forecast 산출
- 재고/입고/안전재고로 ActualSales 제약
- ReqPallets 계산 → Plan에서 평준화
데이터 구조
- Sales_4w: Date, PLU, Qty, PriceType
- Inventory: PLU, OnHand_0, UnitsPerPallet, SafetyStock
- Restock: PLU, ArrivalDate, ArrivalQty
- PeakPlan: Date, PeakFlag, UpliftPlan%
예측 수식
BaseDaily_PLU = AVERAGEIFS(Sales_4w!C:C, Sales_4w!B:B, [@PLU], Sales_4w!D:D, "NORMAL")
SaleUplift_PLU = IFERROR(AVERAGEIFS(Sales_4w!C:C, Sales_4w!B:B, [@PLU], Sales_4w!D:D, "SALE")
/AVERAGEIFS(Sales_4w!C:C, Sales_4w!B:B, [@PLU], Sales_4w!D:D, "NORMAL"),1)
PlanMult = IF(XLOOKUP([@Date], PeakPlan!A:A, PeakPlan!B:B)="Y", 1+XLOOKUP([@Date], PeakPlan!A:A, PeakPlan!C:C), 1)
ForecastUnits = ROUND(BaseDaily_PLU * IF(PeakFlag="Y", SaleUplift_PLU, 1) * PlanMult, 0)
제약 반영
Receipts = SUMIFS(Restock!C:C, Restock!A:A, [@PLU], Restock!B:B, [@Date])
OnHandStart = (첫날) XLOOKUP([@PLU], Inventory!A:A, Inventory!B:B) / (이후) 전일 OnHandEnd
SellableCap = MAX(OnHandStart + Receipts - SafetyStock, 0)
ActualSales = MIN(ForecastUnits, SellableCap)
OnHandEnd = OnHandStart + Receipts - ActualSales
ReqUnits = MAX(ForecastUnits - SellableCap, 0)
ReqPallets = CEILING( ReqUnits / UnitsPerPallet, 1 )
입고 평준화(정수)
Plan 시트에서 Base 매트릭스와 TargetPallets를 계산하고, ExtraPallets 입력(정수)을 더해 최종 일일 합계가 Target과 같아지도록 맞춥니다.
- GapVsTarget > 0인 날 → OOS 위험이 빠른 PLU에
+1팔레트부터 채우기 - 자동화: Solver로
∑(일일합계 − Target)^2최소화 + 정수 제약
Copilot 프롬프트
1) Sales_4w에서 Base/SaleUplift 계산 → PeakPlan 반영 ForecastUnits 생성
2) Inventory/Restock으로 Receipts, SellableCap, ActualSales, OnHandEnd 계산
3) ReqUnits/ReqPallets, TargetPallets, Plan 매트릭스 생성
4) ExtraPallets 입력영역과 GapVsTarget, OOS 하이라이트 만들기
5) Solver: ExtraPallets(정수)로 일일합계의 분산 최소화
트러블슈팅
| 증상 | 원인 | 해결 |
|---|---|---|
| 예측 과대 | Uplift 중복 | 피크일 중복 Uplift 제거, Cap 적용 |
| 소진 미반영 | 안전재고 미적용 | SellableCap에 SafetyStock 반영 |
| 일일합계 변동 | 정수 팔레트 편차 | ExtraPallets 또는 Solver로 평준화 |
| 과잉 입고 | CEILING 누적 | 과잉 상한 제약 추가(허용 1팔레트) |
| 입고 반영 지연 | 컷오프 미고려 | 도착시각 기준 반영일 조정 |