Operations
MAE, WAPE & Forecast Bias for Intermittent Zero-Demand SKUs
Measure supply-chain forecast accuracy without #DIV/0! zero-actual explosions using Mean Absolute Error MAE = AVERAGE(|A - F|), Weighted Absolute Percentage Error WAPE = SUM(|A - F|) / SUM(A), and Signed Bias.
Why supply-chain planners replace classic MAPE with WAPE on intermittent SKUs!
Classic Mean Absolute Percentage Error (MAPE = AVERAGE(|Actual - Forecast| / Actual)) crashes with #DIV/0! the moment a slow-moving SKU has a single zero-unit week—and massively overweights tiny 1-unit weeks over 150-unit peak weeks! Retail S&OP and demand-planning teams solve both flaws with Weighted Absolute Percentage Error (WAPE, also called MAD/Mean ratio): WAPE = SUM(|Actual - Forecast|) / SUM(Actual)!
Pairing WAPE with Mean Absolute Error MAE = AVERAGE(|Actual - Forecast|) (in physical units) and Signed Forecast Bias = (SUM(Forecast) - SUM(Actual)) / SUM(Actual) reveals both total volume-weighted error magnitude AND whether forecasts systematically over-predict (+Bias) or under-predict (-Bias)!
| Week | Actual Units (A) | Forecast Units (B) | Abs Error |A - F| (C) |
|---|---|---|---|
| W1 | 100 | 90 | 10 |
| W2 (Stockout / Zero Actual!) | 0 | 20 | 20 (Classic MAPE = #DIV/0!!) |
| W3 | 150 | 130 | 20 |
| W4 | 50 | 60 | 10 -> MAE = 15.0 | WAPE = 60/300 = 20% |
Weighted Absolute Percentage Error WAPE = SUM(|A - F|) / SUM(A)
Divides total absolute unit error SUM(C2:C5) by total actual demand SUM(A2:A5), handling individual zero-demand weeks cleanly as long as total period demand > 0.
=IF(SUM(A2:A5) <= 0, "Zero Total Period Demand", ROUND(SUM(C2:C5) / SUM(A2:A5), 4))Mean Absolute Error (MAE) & Signed Forecast Bias (%)
AVERAGE(C2:C5) computes unit MAE, while (SUM(B2:B5) - SUM(A2:A5)) / SUM(A2:A5) measures net over/under-forecasting bias.
=AVERAGE(C2:C5)Try the example
Use the original values or change them to test your understanding.