Supply Chain & Inventory Optimization
Eppen Inventory Risk Pooling & Square-Root Law Formula
Calculate centralized safety stock z * sigma * SQRT(N * (1 + (N - 1)*rho)) via Gary Eppen’s (1979) risk-pooling formula and quantify how demand correlation erodes Square-Root Law savings.
Why the famous Square-Root Law of warehouse consolidation fails when regional demand is correlated!
Supply-chain textbooks teach Maister’s (1976) and Gary Eppen’s (1979) Square-Root Law: consolidating N identical regional warehouses (each holding safety stock z * sigma for total decentralized safety stock N * z * sigma) into 1 central distribution center cuts safety stock by 1 / SQRT(N) — BUT ONLY WHEN REGIONAL DEMANDS ARE STATISTICALLY INDEPENDENT (rho = 0)! When regional demands share pairwise correlation rho in [-1 / (N - 1), 1] (driven by nationwide promotions, macro cycles, or weather), aggregate variance equals N * sigma^2 * (1 + (N - 1) * rho), so Centralized Pooled Safety Stock is SS_pool = z * sigma * SQRT(N * (1 + (N - 1) * rho))!
Build a 4-warehouse consolidation check FIRST: Warehouses_N = 4 (B2), Service_Z_Score_z = 2.0 (B3), LeadTime_Demand_SD_sigma = 50 units (B4), and Pairwise_Correlation_rho = 0.00 (B5). Decentralized safety stock is 4 * 2 * 50 = 400.00 units; at rho = 0.00, centralized safety stock drops 50% to 2 * 50 * SQRT(4) = 200.00 units (Saving = 200.00 units!). Now test correlated national promo demand rho = 0.75: SS_pool jumps to 100 * SQRT(4 * 3.25) = 100 * SQRT(13) = 360.5551 units (saving shrinks 80% from 200.00 down to 39.4449!), and at rho = 1.00 savings hit 0.0000!
| Demand Correlation Scenario (N = 4, z = 2.0, sigma = 50) | Pooled Safety Stock Formula | Centralized SS & Units Saved |
|---|---|---|
| Decentralized Baseline (4 Regional Depots) | N * z * sigma | 400.0000 units (Baseline) |
| Independent Regions (rho = 0.00, Sqrt Law) | z * sigma * SQRT(N) | 200.0000 units (200.00 Saved, -50%!) |
| Correlated Promo Demand (rho = 0.75) | z * sigma * SQRT(4 * (1 + 3*0.75)) | 360.5551 units (Only 39.4449 Saved!) |
| Perfectly Synchronized Shock (rho = 1.00) | N * z * sigma | 400.0000 units (0.0000 Saved!) |
Correlation-Adjusted Centralized Pooled Safety Stock Formula
Enforce positive-semi-definite equicorrelation bounds -1 / (N - 1) <= rho <= 1 so impossible negative portfolio variances are flagged cleanly.
=LET(n_wh, B2, z_sc, B3, sg_d, B4, rh_c, B5, IF(OR(n_wh < 2, INT(n_wh) <> n_wh, z_sc <= 0, sg_d <= 0), "Require Integer N >= 2 and Positive z, sigma", IF(OR(rh_c > 1, 1 + (n_wh - 1) * rh_c < 0), "Invalid Correlation Matrix (Not Positive Semi-Definite)", ROUND(z_sc * sg_d * SQRT(n_wh * (1 + (n_wh - 1) * rh_c)), 4))))Net Safety-Stock Units Saved by Centralization Formula
Compare N * z * sigma - SS_pool against outbound parcel-shipping surcharges before closing regional depots in a shared Arte Sheets network-design workbook.
=IF(ISTEXT(E3), E3, ROUND(E2 - E3, 4))Try the example
Use the original values or change them to test your understanding.