How do I incorporate data that mixes sale and regular-price sales into a forecast?

Calculate Uplift by dividing the average on sale days by the average on regular-price days, then multiply it on peak days. Remove duplicate uplift and optionally apply day-of-week weighting.

How are inventory and delivery constraints calculated?

Calculate SellableCap=MAX(OnHandStart+Receipts−SafetyStock,0) and ActualSales=MIN(Forecast,SellableCap) to prevent stockouts (OOS).

Can I make the daily pallet count the same?

You can level manually with the ExtraPallets (integer) helper table on the Plan sheet, or automatically level with a Solver model that minimizes the deviation of daily totals (sum of squares).

What can I automate with Copilot?

You can automate base rate and Uplift calculations, the constraint flow (Receipts/SellableCap/ActualSales), ReqPallets and Target calculations, and creation of the Plan helper table and conditional formatting through prompts.

How do I reduce excessive deliveries?

Limit excess caused by integer rounding with an upper constraint, such as an allowance of one pallet, and add a penalty to the Solver objective function if needed.

CONTENTS

Peak-Period Apparel PLU Sales Forecasting & Daily Delivery Leveling (Excel + Copilot + Solver)

Use 4 weeks of history, inventory/delivery schedules, and peak sale plans to create daily forecasts by PLU, calculate actual sales with inventory constraints, and complete a plan that levels total daily pallet counts in Excel. Includes Copilot prompts and Solver settings.

Quick Fix

Calculate the 28-day base rate for regular-price sales
Calculate Uplift for sale and regular-price sales
Apply the peak plan uplift and calculate the Forecast
Constrain ActualSales using inventory, deliveries, and safety stock
Calculate ReqPallets → level them in the Plan

Data Structure

Sales_4w: Date, PLU, Qty, PriceType
Inventory: PLU, OnHand_0, UnitsPerPallet, SafetyStock
Restock: PLU, ArrivalDate, ArrivalQty
PeakPlan: Date, PeakFlag, UpliftPlan%

Forecast Formulas

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)

Applying Constraints

Receipts     = SUMIFS(Restock!C:C, Restock!A:A, [@PLU], Restock!B:B, [@Date])
OnHandStart  = (first day) XLOOKUP([@PLU], Inventory!A:A, Inventory!B:B) / (thereafter) prior-day 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 )

Delivery Leveling (Integers)

On the Plan sheet, calculate the Base matrix and TargetPallets, then add integer ExtraPallets inputs so that the final daily total equals the target.

For days where GapVsTarget > 0 → start by adding +1 pallet to PLUs with the earliest OOS risk
Automation: Use Solver to minimize ∑(daily total − Target)^2 with integer constraints

Copilot Prompt

1) Calculate Base/SaleUplift from Sales_4w → create ForecastUnits using PeakPlan
2) Calculate Receipts, SellableCap, ActualSales, and OnHandEnd using Inventory/Restock
3) Create the ReqUnits/ReqPallets, TargetPallets, and Plan matrix
4) Create an ExtraPallets input area plus GapVsTarget and OOS highlights
5) Solver: Minimize the variance of daily totals using ExtraPallets (integers)

Troubleshooting

Issue	Cause	Solution
Forecast too high	Duplicate uplift	Remove duplicate uplift on peak days and apply a cap
Stockout not reflected	Safety stock not applied	Include SafetyStock in SellableCap
Daily total varies	Integer pallet variance	Level using ExtraPallets or Solver
Excessive deliveries	Accumulated CEILING rounding	Add an upper constraint for excess deliveries (allow 1 pallet)
Delayed delivery reflection	Cutoff time not considered	Adjust the applicable date based on arrival time

아웃룩 첨부 파일 용량 한도 총정리 — 25MB/20MB/150MB와 해결책

Peak-Period Apparel PLU Sales Forecasting & Daily Delivery Leveling (Excel + Copilot + Solver) Practical Guide