ABC and XYZ Analysis: How to Classify Inventory and Plan Stock
Practice4 minInnovialabs editorial team
In short
ABC ranks products by revenue contribution, XYZ by demand stability; together they show which items to always keep in stock, which to order on demand and which to drop.
The short answer
ABC analysis answers "what brings in money": products are sorted by revenue and split into groups A, B and C. XYZ analysis answers "how predictable is demand": products are grouped by how much sales fluctuate from period to period. Combining them gives a nine-cell matrix, and each cell gets its own purchasing policy.
Everything is done in an ordinary spreadsheet — Excel or Google Sheets — using a sales export from your accounting system.
Preparing the data
You need a table: SKU, unit sales and revenue by period (usually by month), covering at least several months. The more periods, the more reliable XYZ is.
- Exclude new products that have been on sale for less than the analyzed period: they have no history.
- Account for stockout days: zero sales caused by shortages distort the demand picture.
- Use periods of equal length.
ABC analysis by revenue
- Calculate total revenue for each SKU over the whole period.
- Sort SKUs by revenue, descending.
- Calculate each SKU's share of total revenue.
- Calculate the cumulative share — the running sum of shares from the top.
- Assign groups. A common convention: A — up to 80% of cumulative revenue, B — the next 15%, C — the rest. Adjust the boundaries to your business.
D2 = C2 / SUM($C$2:$C$500) share of revenue
E2 = SUM($D$2:D2) cumulative share
F2 = IF(E2<=0.8,"A",IF(E2<=0.95,"B","C"))
You can run ABC on profit instead of revenue to see what actually earns money. If margin data is available, do both.
XYZ analysis by demand stability
- For each SKU, take unit sales by month.
- Calculate the mean and standard deviation.
- Calculate the coefficient of variation: standard deviation divided by the mean.
- Assign groups. A frequent convention: X — up to 10%, Y — 10 to 25%, Z — above 25%. For seasonal retail, the boundaries are usually widened.
N2 = STDEV.P(B2:M2) / AVERAGE(B2:M2)
O2 = IF(N2<=0.1,"X",IF(N2<=0.25,"Y","Z"))
How to read the matrix
| X — stable | Y — fluctuating | Z — unpredictable | |
|---|---|---|---|
| A | Always in stock, auto-reorder | Stock with seasonality in mind | Manual control, order to demand |
| B | Regular reorder | Moderate safety stock | Small batches |
| C | Minimal stock | Order less often, larger batches | Candidates to drop or sell to order |
- AX, BX — the core of the range. Forecasts are accurate and reordering can be automated.
- AZ — important but unpredictable products. Mistakes here cost the most: investigate the causes of fluctuation (promotions, seasons, large one-off orders).
- CZ — little money and chaotic demand. Usually it is better to sell them to order or remove them.
From matrix to decisions
The reorder point is the stock level at which it is time to order: average daily demand times lead time, plus safety stock.
Safety stock covers demand fluctuations. The classic formula is the service level factor times the standard deviation of demand times the square root of lead time (in the same time units).
Safety stock = Z × σ × √L
Reorder point = d × L + Safety stock
Here Z is the factor for the desired service level (not the Z group), σ is the standard deviation of daily demand, L is lead time in days and d is average daily demand.
Set the service level by matrix cell: high for group A, lower for C. This concentrates money tied up in stock where shortages hurt the most.
Common mistakes
- Analyzing too short a period: XYZ over two or three months tells you nothing.
- Treating zeros caused by stockouts as lack of demand.
- Mixing new and discontinued items with the core range.
- Running the analysis once: groups change, so repeat it.