Forecasting & Demand Planning
Theil MSE Bias-Variance-Covariance and Mincer-Zarnowitz Formula
Decompose Mean Squared Forecast Error into exact additive Bias Share U_M, Variance Share U_S, and Unsystematic Covariance Share U_C = 1 - U_M - U_S alongside Mincer-Zarnowitz slope beta_MZ.
Diagnose WHY a forecast missed: systematic level bias, amplitude scaling, or pure noise?
A high Mean Squared Error MSE = (1/T) * SUM((f_t - y_t)^2) does not tell a demand planner whether to recalibrate the intercept, rescale forecast volatility, or accept irreducible market noise! Henri Theil (1966) proved the exact algebraic identity MSE = (f_bar - y_bar)^2 + (sig_f - sig_y)^2 + 2*(1 - r_fy)*sig_f*sig_y (using population SDs STDEVP), partitioning MSE into Bias Proportion U_M = (f_bar - y_bar)^2 / MSE, Variance Proportion U_S = (sig_f - sig_y)^2 / MSE, and Unsystematic Covariance Proportion U_C = 2*(1 - r_fy)*sig_f*sig_y / MSE with U_M + U_S + U_C = 1.0000 identically!
In parallel, Mincer and Zarnowitz (1969) regress realizations y_t on forecasts f_t: an optimal rational forecast satisfies intercept alpha_MZ = 0 and slope beta_MZ = SLOPE(y, f) = 1.0000! Test 5 quarters of actual sales y = {100, 110, 120, 130, 140} (B2:B6, mean = 120, sig_y = SQRT(200)) against biased over-volatile forecasts f = {104, 124, 144, 164, 184} (C2:C6, mean = 144, sig_f = 2*SQRT(200), r_fy = 1.0000!). Errors are {4, 14, 24, 34, 44} -> MSE = 776.00: Bias share U_M = 24^2 / 776 = 576 / 776 = 0.7423 (74.23%), Variance share U_S = 200 / 776 = 0.2577 (25.77%), Noise share U_C = 0.0000 (100% fixable linear calibration error, confirmed by beta_MZ = 0.5000 != 1)!
| Theil / Mincer-Zarnowitz Metric | Exact Formula (Population Moments) | Sample Output |
|---|---|---|
| Total Mean Squared Error MSE | SUMXMY2 / T = AVERAGE((f_t - y_t)^2) | 776.0000 |
| Theil Bias Proportion U_M | (AVERAGE(f) - AVERAGE(y))^2 / MSE | 0.7423 (576 / 776) |
| Theil Variance Proportion U_S | (STDEVP(f) - STDEVP(y))^2 / MSE | 0.2577 (200 / 776) |
| Theil Noise Share U_C & Slope beta_MZ | 2*(1 - CORREL(f,y))*sig_f*sig_y / MSE; SLOPE(y,f) | U_C = 0.0000; beta_MZ = 0.5000 |
Theil Bias Proportion U_M & Variance Proportion U_S Formula
Always use population standard deviations STDEVP (not sample N - 1 STDEV) so U_M + U_S + U_C sums to 1.0000 without finite-sample N/(N-1) leakage!
=LET(mse_v, SUMPRODUCT((C2:C6 - B2:B6)^2) / ROWS(B2:B6), IF(mse_v <= 0, "Perfect Zero-MSE Forecast", ROUND(((AVERAGE(C2:C6) - AVERAGE(B2:B6))^2) / mse_v, 4)))Mincer-Zarnowitz Efficiency Slope beta_MZ & Recalibration Check
Recalibrate the forecast using alpha_MZ = INTERCEPT(y, f) = 48 and beta_MZ = SLOPE(y, f) = 0.50, or test an unbiased noisy forecast (set C2:C6 = {104, 106, 120, 134, 136}, which has mean 120): U_M drops immediately to 0.0000!
=IF(STDEVP(C2:C6) <= 0, "Constant Forecast", ROUND(SLOPE(B2:B6, C2:C6), 4))Try the example
Use the original values or change them to test your understanding.