Forecast vs actual

Ship the SQL every S&OP review demands

A demand planning star schema: 30 SKUs (4 intermittent, 2 NPI with no history), 5 locations, 4 forecast versions (STATISTICAL/CONSENSUS/OVERRIDE/NAÏVE), 480 forecast rows, 120 actuals. Planted data-quality issues: STATISTICAL version over-forecasts Category C by ~18% (bias pattern); CONSENSUS beats STATISTICAL on only 60% of periods; 2 promos with negative lift (pull-forward effect); 4 SKUs with zero-actual periods that destroy MAPE.

WMAPE calculation, forecast bias detection, tracking signal computation, consensus vs statistical forecast value-add, promo lift attribution, and ABC×XYZ segmentation — the full demand planning analyst SQL curriculum across 30 missions.

Learn why WMAPE (Σ|A−F|/ΣA) is the industry default — it weights by volume and avoids divide-by-zero on intermittent SKUs.

WMAPE vs MAPE

Spot the systematic over-forecast on Category C that hides inside an acceptable WMAPE — the pattern demand planners escalate to leadership.

Forecast Bias Detection

Compute rolling tracking signals and flag SKUs where |TS| > 4 — the statistical trigger for model recalibration.

Tracking Signal

Measure whether the human consensus override actually beats the statistical baseline — it only does 60% of the time in this dataset.

Consensus Value-Add

WMAPE, forecast bias, and tracking signals.

SKUs by lifecycle

Location count

Forecast versions

Recent actuals

Promo calendar

MAPE per SKU

Bias by category

Forecast attainment

Best/worst forecast accuracy

WMAPE

Intermittent SKU detection

Promo lift attribution

Forecast coverage

Accuracy by version

Rolling WMAPE

Tracking signal

Forecast value-add

Lag analysis

ABC x XYZ segmentation

Persistent bias by SKU

Naive benchmark

Coefficient of variation

Promo uplift attribution

SKU segmentation

FVA by version

ROI ranking

Demand-supply gap

S&OP prep

Demand planning capstone

sku_id

sku_code

sku_name

lifecycle

abc_class

xyz_class

dim_skus

location_id

location_name

region

dc_type

dim_locations

version_id

version_name

lock_date

horizon_weeks

dim_forecast_versions

forecast_id

forecast_period_date

forecast_generated_date

forecast_qty

fact_forecasts

actual_id

period_date

units_sold

revenue

fact_actuals

promo_id

promo_name

promo_type

start_date

end_date

lift_pct_actual

fact_promotions

Column	Type	Key
actual_id	INT	PK
sku_id	INT	FK → dim_skus
location_id	INT	FK → dim_locations
period_date	TEXT
units_sold	INT
revenue	REAL

Column	Type	Key
forecast_id	INT	PK
sku_id	INT	FK → dim_skus
location_id	INT	FK → dim_locations
version_id	INT	FK → dim_forecast_versions
forecast_period_date	TEXT
forecast_generated_date	TEXT
forecast_qty	INT

Forecast vs actual

The Brief

You'll practice

Tables & columns available

Hints (3)