Case study 02 · Demand planning

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.

Tools: Excel · SQL · DashboardData: 250 SKUs · 104 weeksTest: last 26 weeks · WMAPE · Bias
ⓘ Dataset is simulated to mirror a typical FMCG distribution network (250 SKUs, 4 warehouses, 14 suppliers). Methods, analysis and recommendations reflect how I work as a planner.
45.2%
forecast error (WMAPE) of the best model: Exp. smoothing
−22%
error vs. naive forecast (last week = next week)
−3.8%
error vs. the current 4-week moving average
0.0%
bias – forecast is practically unbiased
01

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?

02

Data & method

Models

Naive (last week), 4-week moving average, exponential smoothing (α = 0.25) and seasonal exponential smoothing with category seasonal indices from year 1.

Back-test

One-week-ahead forecasts for the last 26 weeks, compared with actual sales – no information from the future is used.

Metrics

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)
03

Interactive dashboard

Forecast back-test · last 26 weeks
Model

Actual sales vs. forecast, last 52 weeks. Shaded = test period

Forecast error by model

WMAPE across all SKUs – lower is better

Best model per SKU

Number of SKUs where the model has the lowest error

WMAPE by category and model

Best value per row highlighted
04

Key findings

−22%

Exponential smoothing cuts the forecast error from 57.7% (naive) to 45.2%.

−3.8%

Compared with today's moving average the gain is modest – a new tool alone will not solve the planning issues.

53.7% vs. 55.4%

Seasonal smoothing wins for Beverages, where summer peaks are strong; for stable categories simple smoothing is enough.

7 / 250

The naive method is almost never the best. Weekly SKU-level error stays high because of promotions and sporadic C items.

05

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.

06

Data

Weekly sales for all 250 SKUs over 104 weeks: