9. ABC/XYZ Analysis of a Product Range

ABC analysis splits items by their contribution to revenue (the Pareto principle: a small share of items brings in most of the money):

1. calculate each item's revenue and sort in descending order (already done in our table);
2. calculate each item's share of total revenue: =F2/$F$10;
3. calculate the cumulative share: this item's share plus the shares of all items above it;
4. assign a class: cumulative share up to 80% is A, up to 95% is B, the rest is C.

XYZ analysis rates how stable demand is, using the coefficient of variation: =STDEV.S(B2:E2)/AVERAGE(B2:E2), the spread of quarterly sales relative to the average. Up to 10% is X (stable demand), up to 25% is Y (fluctuating, seasonal), above that is Z (unpredictable).

The 80/95 and 10/25 thresholds are common but not the only valid ones. That is why they sit in cells B12:B15, and the IF formulas refer to them instead of containing numbers.