XYZ analysis: how stable demand is

ABC analysis says how much income an item brings, but it does not show how predictable demand is. Two items may each sell 100 pieces a month: one around 100 every month, the other 50 one month and 150 the next. The first can be managed with a simple rule, while the second always carries a risk of surplus or shortage in stock. XYZ analysis measures exactly that difference.

The coefficient of variation

The core measure is the coefficient of variation:

V = standard deviation ÷ average demand × 100.

The standard deviation shows how far demand typically strays from its average. Dividing by the average makes that spread relative, so cheap and expensive, fast and slow items can be compared on one scale. Widely used cut-offs:

• X: V up to 10 percent — demand is even.
• Y: V from 10 to 25 percent — demand fluctuates.
• Z: V above 25 percent — demand swings sharply.

The cut-offs are not absolute; you can adjust them to your data, as long as the rule stays the same.

An example with three items

Take sales for six months. All three items have the same average — 100 pieces a month.

First item: 100, 104, 98, 102, 96, 100. Deviations from the average are 0, 4, −2, 2, −4, 0; the sum of their squares is 40. 40 ÷ 6 ≈ 6.67, and the square root is ≈ 2.58. V = 2.58 ÷ 100 × 100 ≈ 2.6 percent. This is group X.

Second item: 85, 120, 95, 110, 80, 110. Deviations −15, 20, −5, 10, −20, 10; sum of squares 1,250. 1,250 ÷ 6 ≈ 208.3, square root ≈ 14.4. V ≈ 14.4 percent. This is group Y.

Third item: 60, 140, 80, 120, 50, 150. Deviations −40, 40, −20, 20, −50, 50; sum of squares 9,000. 9,000 ÷ 6 = 1,500, square root ≈ 38.7. V ≈ 38.7 percent. This is group Z.

The average is the same, yet they must be managed in entirely different ways. That is why setting stock from average sales alone leads to mistakes.

The difference shows up in money too. Safety stock is directly proportional to the standard deviation (a separate article covers it), so all else being equal the third item needs roughly 15 times more stock than the first: 38.7 ÷ 2.6 ≈ 15.

Calculating it in Excel

If each item's months sit in one row, the formula is simple: STDEV.P(B2:G2) ÷ AVERAGE(B2:G2). STDEV.P calculates for the whole population and STDEV.S for a sample; with six values they differ slightly, so choose one and always use it. Take at least 6-12 months of data, preferably a full year. For fast-selling items you can use weeks instead of months. Note that the coefficient depends on the length of the period: on weekly data it is usually higher than on monthly data, so compare all items over the same period.

What to do with each group

• X: a simple automatic rule is enough — a reorder point and a small safety stock.
• Y: make the stock somewhat larger and review the forecast monthly or weekly.
• Z: hard to predict. Safety stock will be larger, so for expensive goods consider working to order, small batches and a quick arrangement with the supplier. Group Z does not mean a "bad" item, but it can tie up the most cash.

Common mistakes

• Too short a period: with three or four values the coefficient comes out random.
• Ignoring seasonality. Drinks in summer or heaters in winter naturally swing sharply, which turns them into Z. Assess them within the season or after removing the seasonal factor.
• The effect of promotions. A jump in a discount week does not mean the item itself is unstable.
• Assessing new items. For an item with no history XYZ is unreliable; keep it on a separate "new" list.

Conclusion

XYZ analysis shows the predictability of demand through one simple calculation and reveals the difference that an average hides. Together with ABC it gives each item a suitable stocking policy. CountLab combines ABC/XYZ analysis with stock-count results and shows how often a given item should be counted and controlled.