Supply Chain Analytics & Inventory Theory
AR(1) & Moving-Average Order-Up-To Bullwhip Formula
Calculate Lee, Padmanabhan, and Whang’s (1997) exact AR(1) MMSE order-up-to variance amplification ratio and Chen et al.’s (2000) p-period moving-average bullwhip lower bound.
Prove why rational order-up-to replenishment amplifies upstream variance!
Hau Lee, V. Padmanabhan, and Seungjin Whang (1997) proved that the Bullwhip Effect is not merely irrational panic buying — even a completely rational retailer using Minimum Mean-Squared Error (MMSE) forecasting under a base-stock Order-Up-To policy amplifies order variance Var(O_t) / Var(D_t) whenever stationary AR(1) demand autocorrelation rho in (0, 1) interacts with replenishment lead time L >= 1: Var(O_t) / Var(D_t) = 1 + (2 * rho * (1 - rho^L) * (1 - rho^(L + 1))) / (1 - rho)! Worse still, Frank Chen, Zvi Drezner, Jennifer Ryan, and David Simchi-Levi (2000) showed that if planners re-estimate mean demand each period with a p-period Simple Moving Average, order variance amplifies by AT LEAST 1 + (2 * L) / p + (2 * L^2) / p^2 even when demand is completely i.i.d. (rho = 0)!
Verify on AR1_Autocorrelation rho = 0.60 (B2), Replenishment_Lead_Time L = 2 periods (B3), and Moving_Average_Span p = 4 periods (B4): Lee-Padmanabhan-Whang Exact AR(1) MMSE Bullwhip Ratio is 1 + (2 * 0.60 * (1 - 0.36) * (1 - 0.216)) / (1 - 0.60) = 1 + 1.50528 = 2.5053x; and Moving-Average i.i.d. Bullwhip Bound is 1 + (2 * 2) / 4 + (2 * 4) / 16 = 1 + 1.00 + 0.50 = 2.5000x! Set rho = 0 in cell B2: optimal MMSE bullwhip collapses to 1.0000x (zero amplification because optimal forecast stops chasing noise!), whereas the 4-period moving average STILL amplifies variance 2.5000x! Set rho = -0.50 (mean-reverting promotional demand): MMSE ratio drops to 0.4375x (order smoothing < 1.0)!
| Theoretical Bullwhip Model (rho=0.60, L=2, p=4) | Closed-Form Variance Ratio | Verified Output |
|---|---|---|
| Lee-Padmanabhan-Whang AR(1) MMSE | 1 + 2*rho*(1 - rho^L)*(1 - rho^(L+1))/(1 - rho) | 2.5053x |
| Chen-Simchi-Levi Moving-Avg Bound (rho=0) | 1 + (2*L)/p + (2*L^2)/p^2 | 2.5000x |
| i.i.d. Demand Under MMSE (rho = 0) | 1 + 0 | 1.0000x (Zero Bullwhip) |
| Negatively Correlated AR(1) (rho = -0.50) | Order Smoothing (< 1.00) | 0.4375x |
Lee-Padmanabhan-Whang (1997) Exact AR(1) MMSE Bullwhip Formula
Enforce stationary AR(1) bounds ABS(rho) < 1 and integer lead time L >= 1!
=LET(rh_ar, B2, ld_tm, B3, p_ma, B4, IF(OR(ABS(rh_ar) >= 1, ld_tm < 1, p_ma < 1), "Invalid Bullwhip Input", ROUND(1 + (2 * rh_ar * (1 - (rh_ar ^ ld_tm)) * (1 - (rh_ar ^ (ld_tm + 1)))) / (1 - rh_ar), 4)))Chen-Drezner-Ryan-Simchi-Levi (2000) Moving-Average Bullwhip Formula
Lengthening moving-average window p from 4 to 12 periods cuts forecast-updating amplification from 2.5000x down to 1 + 4/12 + 8/144 = 1.3889x!
=IF(ISTEXT(E2), E2, ROUND(1 + (2 * B3) / B4 + (2 * (B3 ^ 2)) / (B4 ^ 2), 4))Try the example
Use the original values or change them to test your understanding.