Which model plans better? Weekly demand forecasting for 250 SKUs
A back-test of four statistical forecasting methods on two years of weekly sales – to replace the planners' 4-week moving average with a better, segment-specific approach.
Business question
Replenishment was driven by a 4-week moving average per SKU. It reacts slowly to seasonality and over-reacts to promotions. Before buying a forecasting tool, the team wanted to know: how much would better statistics actually improve accuracy, and for which products?
Data & method
Naive (last week), 4-week moving average, exponential smoothing (α = 0.25) and seasonal exponential smoothing with category seasonal indices from year 1.
One-week-ahead forecasts for the last 26 weeks, compared with actual sales – no information from the future is used.
WMAPE = Σ|actual − forecast| ÷ Σ actual (volume-weighted error). Bias = Σ(forecast − actual) ÷ Σ actual (over/under-forecasting).
Excel: exponential smoothing & WMAPE
' Column B = actual weekly sales, column C = forecast, cell $H$1 = alpha (0.25)
' Start value (first forecast) = average of the first 4 weeks
C2: =AVERAGE(B2:B5)
' Exponential smoothing: F(t) = alpha * A(t-1) + (1 - alpha) * F(t-1)
C3: =$H$1*B2 + (1-$H$1)*C2 ' copy down
' WMAPE over the 26-week back-test
=SUMPRODUCT(ABS(B80:B105 - C80:C105)) / SUM(B80:B105)
' Bias (+ = over-forecast, − = under-forecast)
=(SUM(C80:C105) - SUM(B80:B105)) / SUM(B80:B105)Interactive dashboard
Forecast error by model
Best model per SKU
WMAPE by category and model
Key findings
Exponential smoothing cuts the forecast error from 57.7% (naive) to 45.2%.
Compared with today's moving average the gain is modest – a new tool alone will not solve the planning issues.
Seasonal smoothing wins for Beverages, where summer peaks are strong; for stable categories simple smoothing is enough.
The naive method is almost never the best. Weekly SKU-level error stays high because of promotions and sporadic C items.
Recommendations
Use exponential smoothing as the default
Replace the 4-week moving average – same effort, lower error and no lag on trend changes.
Add seasonality for seasonal categories
Apply seasonal indices to Beverages and review them once a year.
Clean promotions from history
Flag promo weeks and plan them separately with Sales input – they are the main source of remaining error.
Track WMAPE and bias monthly
Report accuracy by ABC class: focus planner time on A items, where each percentage point is worth the most.
Data
Weekly sales for all 250 SKUs over 104 weeks: