ABC analysis comes from inventory management, and most guides explain it with warehouse parts. It works just as well for a retail assortment, with one condition: you have to know which products it suits. Below I show how I run ABC and XYZ analysis in Excel, how I read the combined matrix and where the method misleads fashion teams.
What ABC Analysis Shows in Retail
ABC analysis is a ranking of products by their contribution to a total, usually sales value, gross margin or units. You sort products from the highest contributor to the lowest, calculate the cumulative share and cut the list into three classes: A items bring about the first 80% of the total, B items the next 15% and C items the last 5%.
The 80/20 rule behind it is the Pareto principle: in most ranges a small share of products brings most of the result. In a real range with hundreds of options, the A class is often a fifth of the products or fewer. The exact split varies, and that is the point of the exercise: it shows where attention and stock investment matter most in your business, not in a textbook.
When should you use ABC analysis? I use it to decide where to spend planning time, which products must never be out of stock, which to count and check first, and which to question at the next range review. It is sometimes confused with EOQ, the economic order quantity. They answer different questions: ABC decides how much attention an item deserves, EOQ calculates how much to order each time for an item that is replenished.
Choose the measure deliberately. Sales value is the default, gross margin is better when margins differ a lot between categories, and units suit warehouse and replenishment questions. I often run two versions and look at the products that are A on sales but C on margin.
How to Do ABC Analysis in Excel
You need one row per product with its sales value for a meaningful period, such as the last 52 weeks for continuity lines. Then four steps:
- Sort the table by sales value, largest first.
- Add a column with each product’s share of the total.
- Add a running total of that share.
- Classify each product by its cumulative share: up to 80% is A, up to 95% is B, the rest is C.
' Columns: A = product, B = sales value (sorted largest first)
' C: share of total
=B2/SUM($B$2:$B$101)
' D: cumulative share
=SUM($C$2:C2)
' E: ABC class
=IF(D2<=80%,"A",IF(D2<=95%,"B","C"))
' Excel 365: a sorted copy that updates when new weeks are added
=SORTBY(A2:B101, B2:B101, -1)Here is a small worked example with ten continuity products and illustrative sales. A range this short is less skewed than a real one, but the mechanics are identical.
| Product | Sales | Share | Cumulative | Class |
|---|---|---|---|---|
| Slim jeans | 24,000 | 24% | 24% | A |
| Crew T-shirt | 18,000 | 18% | 42% | A |
| Knit jumper | 14,000 | 14% | 56% | A |
| Chino trousers | 11,000 | 11% | 67% | A |
| Oxford shirt | 9,000 | 9% | 76% | A |
| Hoodie | 7,000 | 7% | 83% | B |
| Denim jacket | 6,000 | 6% | 89% | B |
| Linen shirt | 5,000 | 5% | 94% | B |
| Leather belt | 3,500 | 3.5% | 97.5% | C |
| Socks 3-pack | 2,500 | 2.5% | 100% | C |
One detail to agree in advance: the product that crosses the 80% line. With the formula above, the hoodie at 83% becomes B. Some teams use the cumulative share of the previous row instead, which pulls that product into A. Either is fine, as long as the rule stays the same from season to season.
XYZ Analysis for Demand Stability
ABC analysis tells you how much a product contributes; XYZ analysis tells you how predictable its demand is. It uses the coefficient of variation (CV) of weekly or monthly sales: the standard deviation divided by the average. A low CV means steady demand, a high CV means sales jump around from week to week.
' Weekly units in columns C:N (12 weeks)
=STDEV.P(C2:N2)/AVERAGE(C2:N2)
' XYZ class with thresholds in cells Q1 (X limit) and Q2 (Y limit)
=IF(O2<=$Q$1,"X",IF(O2<=$Q$2,"Y","Z"))Take two products over six weeks. A crew T-shirt sells 120, 110, 130, 125, 115 and 120 units: the average is 120, the standard deviation about 6.5, so the CV is roughly 5%, clearly X. A knit jumper sells 40, 90, 20, 150, 60 and 100: the average is about 77 and the CV about 56%, clearly Z.
Thresholds differ by business. As a rule of thumb for weekly fashion sales, I start with X up to 25%, Y from 25% to 50% and Z above 50%, then adjust so that the classes make sense to the planners. Monthly data is smoother, so its thresholds can be lower.
ABC-XYZ Matrix for Assortment Decisions
Put the two classes together and every product lands in one of nine cells. Each cell suggests a different way to plan and buy it.
| Class | X · stable demand | Y · variable demand | Z · irregular demand |
|---|---|---|---|
| A · high contribution | Automatic replenishment, never out of stock | Replenish with safety stock, review weekly | Plan manually, buy in smaller drops |
| B · medium contribution | Automatic replenishment, lower safety stock | Review every few weeks | Buy cautiously, review each season |
| C · low contribution | Replenish with minimum stock | Question the number of options | Candidates to delist unless they have a clear role |
AX products are the backbone of the range: they deserve the tightest availability targets and the least manual effort, because their demand is easy to forecast. AZ products are the opposite: they matter a lot but are hard to predict, so they need a planner’s judgement, and they are where stock-outs and overstocks both cost the most. CZ products take effort and bring little, which makes them the first place to look when a range needs to be simplified.
ABC Analysis Mistakes in Fashion
Does ABC-XYZ analysis work for fashion? It works well for continuity lines, basics and replenished items with several seasons of history. For seasonal fashion it needs care, and these are the mistakes I see most often:
- Running it on individual seasonal styles that live only a few weeks. Use categories, price tiers or attribute groups instead, such as “midi dresses at the middle price tier”.
- Leaving in weeks with stock-outs. Zero sales because there was no stock push a product down the ABC ranking and make its demand look irregular.
- Leaving in markdown and promotion weeks, which create spikes that say more about the discount than about demand.
- Classifying at size level. Sizes split the volume, so every product looks smaller and more erratic than it is; classify the option, then use a size curve.
- Mixing markets with different seasons or price levels in one ranking.
- Treating C as “delete”. An entry-price item, a new launch or a product that completes an outfit can be C and still earn its place.
- Running the analysis once. Refresh it every season, or monthly for replenished lines, and watch which products change class.
Used this way, ABC-XYZ analysis is a quick first cut of an assortment, not a verdict. I pair it with sell-through and weeks of cover, described in my article on retail KPIs, before recommending that anything is added to or removed from a range.