Statistical
ABC–XYZ inventory example: cumulative spend share and demand variation
Combine a chosen cumulative-spend classification with population CV of four demand observations. Thresholds are example policy choices, not universal stocking rules.
Separate value share from observed demand variation
Sort complete SKU records by descending annual spend before computing cumulative spend share. This example assigns the class from the cumulative share including the current item: A through 80%, B through 95%, then C. An item crossing a boundary moves to the next class under this convention; other businesses use different boundary policies.
XYZ here uses population standard deviation divided by mean demand across four equally spaced quarters. X is CV <=0.20, Y is CV <=0.50, and Z is above 0.50. These are chosen example thresholds. Four observations are a limited description, not proof of forecastability or seasonality, and CV is undefined at zero mean.
The label combines two descriptive measures. It does not establish an automatic just-in-time, safety-stock or make-to-order decision. Lead times, stockout costs and demand structure need separate analysis.
| SKU (A) | Annual Spend (B) & Cum % | Quarterly Demand (C:F) | CV & 9-Box ABC-XYZ Tag |
|---|---|---|---|
| SKU-101 | $75,000 (75% -> A) | 100, 105, 95, 100 | CV = 0.0354 (<=0.20 -> X) = "AX" |
| SKU-204 | $17,000 (92% -> B) | 80, 130, 60, 130 | CV = 0.3082 (<=0.50 -> Y) = "BY" |
| SKU-309 | $8,000 (100% -> C) | 10, 110, 0, 80 | CV = 0.9274 (>0.50 -> Z) = "CZ" |
Combined 9-box ABC-XYZ classification formula
With sorted annual spend in B2:B4, SUM($B$2:B2)/SUM($B$2:$B$4) computes running cumulative spend share while STDEV.P(C2:F2)/AVERAGE(C2:F2) computes demand CV.
=IF(OR(COUNT($B$2:$B$4)<>3,MIN($B$2:$B$4)<0,SUM($B$2:$B$4)<=0,$B$2<$B$3,$B$3<$B$4,COUNT(C2:F2)<>4,MIN(C2:F2)<0),"Invalid inputs",IF(AVERAGE(C2:F2)=0,"Undefined CV",LET(cum, SUM($B$2:B2) / SUM($B$2:$B$4), cv, STDEV.P(C2:F2) / AVERAGE(C2:F2), IFS(cum <= 0.80, "A", cum <= 0.95, "B", TRUE, "C") & IFS(cv <= 0.20, "X", cv <= 0.50, "Y", TRUE, "Z"))))Demand Coefficient of Variation CV = STDEV.P / AVERAGE
STDEV.P treats the four supplied quarters as the complete descriptive window. STDEV.S estimates sample dispersion and gives a different CV. Low means can make CV large; all-zero demand is reported as undefined rather than X.
=IF(OR(COUNT(C3:F3)<>4,MIN(C3:F3)<0),"Invalid inputs",IF(AVERAGE(C3:F3)=0,"Undefined CV",ROUND(STDEV.P(C3:F3) / AVERAGE(C3:F3), 4)))Try the example
Use the original values or change them to test your understanding.