Supply Chain Bullwhip Effect Ratio: order variance amplification & lead-time bound

Compare observed order variance with demand variance

Compare the same product, units, aggregation interval and observation window. A sample variance ratio above one indicates greater order variance in this window; below one indicates lower order variance, not proof of a particular production policy or its cause. Five observations illustrate arithmetic and are not sufficient to establish stable long-run behavior.

The expression 1 + 2L/p + 2L^2/p^2 is a model-specific benchmark for the uncorrelated-demand case of the moving-average, order-up-to replenishment analysis. It assumes fixed lead time and the model’s forecasting and ordering rules. It is not a universal lower bound for arbitrary demand autocorrelation, order batching, capacity constraints or empirical five-week samples.

Empirical Bullwhip Variance Ratio VAR.S(Orders) / VAR.S(POS_Demand)

CV divides standard deviation by the mean. The CV ratio is not the variance ratio or its normalized equivalent: the squared CV ratio compares normalized variances. With equal positive means here, CV ratio5 corresponds to variance ratio25. For pack-size differences first convert quantities to consistent units.

=IF(OR(COUNT(A2:B6)<>10,MIN(A2:B6)<0),"Invalid inputs",IF(VAR.S(A2:A6)=0,"Undefined ratio",ROUND(VAR.S(B2:B6) / VAR.S(A2:A6), 2)))

Model-specific uncorrelated-demand benchmark

Use whole periods: lead time L must be nonnegative and window p a positive integer in the same time unit. L2 and p4 give2.5; L0 gives1 for this expression, without asserting every real system has that ratio.

=IF(COUNT(D2:D3)<>2,"Invalid inputs",IF(OR(D2<0,D3<1,D2<>INT(D2),D3<>INT(D3)),"Invalid inputs",ROUND(1 + (2 * D2) / D3 + (2 * (D2 ^ 2)) / (D3 ^ 2), 4)))

Further reading

• Microsoft Support — VAR.S function
• Chen et al. — Quantifying the Bullwhip Effect in a Simple Supply Chain