Your Shopify catalogue has 40 SKUs. Last Tuesday you spent forty minutes deciding how many wick trimmers to reorder — a ₹120 item that moves about ₹1 lakh a year — and then approved the candle production order, a ₹30 lakh line, in ninety seconds because the supplier's email was already open. That is the problem ABC analysis of inventory solves. It ranks every SKU by the cash it actually consumes, so your attention lands where the money is instead of where the last email came from. Below: the Pareto logic, the exact steps on a Shopify export, a fully worked 10-SKU example, what you change per grade, the XYZ upgrade, and where the method quietly lies to you.
Key takeaways
- Annual usage value = units sold (12 months) × cost per unit. Not retail price.
- Sort descending, add a cumulative % column, cut at ~80% (A), ~95% (B), the rest (C).
- The grades are worthless until you change something: review frequency, service level, count cadence, PO approval, supplier attention.
- Layer XYZ (demand variability) on top — an A-item with erratic demand needs a different policy from an A-item that sells like a metronome.
- ABC is blind to margin, hero products and cheap-but-blocking components. Write down three overrides and cap them.
The Pareto logic, stated honestly
Vilfredo Pareto noticed that 80% of Italy's land sat with 20% of its people. The pattern repeats in inventory: a small share of SKUs consumes most of the money. ABC analysis just formalises it into three buckets so you can apply three different levels of effort instead of one flat level to everything.
The flat level is the real enemy. Most small brands manage every SKU identically — same reorder logic, same glance, same approval path — so the bestseller gets under-managed and the wick trimmer gets over-managed. Attention is your scarcest input. ABC is a rule for spending it.
One honesty note up front, because most articles skip it: the 80/20 split rarely lands at 20% of SKUs in a small catalogue. With 10 to 50 SKUs the curve is flatter — 35% to 45% of SKUs carrying 80% of value is completely normal. Do not force the ratio. Cut where the cumulative curve flattens and take whatever share of SKUs that gives you.
How to run ABC analysis on a Shopify inventory export
Six steps. Fifteen minutes for 10 SKUs, and roughly the same fifteen minutes for 300, because the spreadsheet does not care.
1. Pull twelve months of net units sold
Shopify Admin → Analytics → Reports → Sales by product variant SKU. Set the date range to the trailing 12 months and export. You want net units — gross units minus returns — because returned stock did not consume capital, it came back.
2. Pull cost per unit, not price
Products → Export, or the Inventory export, gives you the Cost per item field. If that column is empty for half your catalogue, stop and fill it in — this whole analysis is meaningless with retail prices in it. Retail price measures what customers paid you; ABC is about what you paid, because the point is to rank capital tied up. Use fully landed cost if you have it — manufacturing, freight, duty, inbound handling. Factory invoice cost is a workable stand-in for a first pass.
3. Multiply into annual usage value
Two SKUs can be identical on either input and land in different grades. A ₹22 matchbox that sells 2,600 units a year consumes ₹57,200. A ₹720 gift set that sells 1,850 units consumes ₹13.3 lakh. Volume alone would have ranked them the other way round.
4. Sort descending on that column
Highest usage value at the top. This is the entire ranking.
5. Add a running cumulative percentage
With annual usage value in column D and data starting in row 2, put this in E2 and fill down:
The anchored first reference makes the range grow as you drag, so each row shows the share of total inventory value consumed by everything down to and including that SKU.
6. Cut at 80% and 95%
Grade in column F, filled down:
Note that it reads the row above. That is deliberate: the SKU that straddles a boundary lands in the higher class, because you would rather over-manage one item than under-manage it. N() converts the header text to zero, which makes the first row always an A.
A worked ABC classification example: 10 SKUs
A home-fragrance brand, one financial year, costs landed into the Mumbai warehouse. Sorted descending, cumulative percentage computed, grades applied by the rule above.
| SKU | Units sold | Cost/unit | Annual usage value | Cumulative % | Grade |
|---|---|---|---|---|---|
| Soy candle 200g — Sandalwood | 9,600 | ₹310 | ₹29,76,000 | 33.3% | A |
| Reed diffuser 200ml | 6,200 | ₹265 | ₹16,43,000 | 51.6% | A |
| Gift set — 3 candles | 1,850 | ₹720 | ₹13,32,000 | 66.5% | A |
| Room spray 100ml | 6,800 | ₹185 | ₹12,58,000 | 80.6% | A |
| Car diffuser | 3,100 | ₹180 | ₹5,58,000 | 86.8% | B |
| Travel tin candle 60g | 4,100 | ₹95 | ₹3,89,500 | 91.2% | B |
| Diffuser refill oil 30ml | 4,200 | ₹85 | ₹3,57,000 | 95.2% | B |
| Ceramic candle holder | 1,400 | ₹190 | ₹2,66,000 | 98.2% | C |
| Wick trimmer | 900 | ₹120 | ₹1,08,000 | 99.4% | C |
| Matchbox set | 2,600 | ₹22 | ₹57,200 | 100.0% | C |
Total annual usage value: ₹89,44,700. Four SKUs — 40% of the catalogue — carry 80.6% of it. Three carry the next 14.6%. The bottom three carry 4.8% between them, about ₹4.3 lakh, which is less than the top SKU consumes in seven weeks.
Read the losers as carefully as the winners. The diffuser refill sells more units than the travel tin and still grades lower, because unit cost differs. And the gift set sells the fewest units of any A-item, which is exactly why volume-based intuition would have missed it.
The half most articles skip: what you actually do differently
A classification that changes no behaviour is decoration. Here is the policy grid — the operating rules that should differ by grade.
| Policy | A grade | B grade | C grade |
|---|---|---|---|
| Review frequency | Weekly, by a named person | Monthly | Quarterly, or when the trigger fires |
| Target service level | 97–98% | 92–95% | 85–90%, and accept the odd stockout |
| Safety stock method | Per-SKU z-score calculation, recomputed monthly | Max-minus-average rule of thumb | Flat buffer — order big, order rarely |
| Forecasting | Real demand forecast with seasonality | 3-month moving average | Last year's total, divided by four |
| Cycle counting | Monthly physical count | Quarterly | Annually, plus a spot check if numbers look odd |
| PO approval | Founder or ops lead signs, forecast attached | Ops lead, within a standing budget | Auto-reorder at a fixed quantity, no approval |
| Supplier attention | Quarterly scorecard, second source identified | Annual review | One supplier, no scorecard, do not spend the time |
| Order frequency | Small and often — protects cash | Balanced | Large and rare — the ordering cost exceeds the holding cost |
Two of those rows do the heavy lifting. Service level is where ABC pays for itself: pushing an A-item from 95% to 98% costs real buffer, and the only way to afford it is to stop paying for 98% on the wick trimmer. Our safety stock guide works through the z-score arithmetic if you want the exact multipliers. Order frequency is the counter-intuitive one: A-items want small, frequent orders so cash keeps cycling, while C-items want one big annual buy because the admin cost of a PO swamps the carrying cost of a ₹57,000 line.
The approval row matters more than it looks. Most brands have exactly one approval path, which means the founder rubber-stamps forty POs a month and reads none of them. Route C-items to an automatic trigger, and the four POs that actually deserve scrutiny get it. Velocity-driven reorder alerts handle the C tail without a human in the loop; the A-items still land on a desk.
The two-dimensional upgrade: ABC × XYZ
ABC tells you how much money a SKU consumes. It says nothing about how predictable that consumption is — and those are completely different problems. An A-item that sells 40 units every single week needs almost no buffer and can run close to just-in-time. An A-item that sells 5 units one week and 190 the next needs a fundamentally different policy, even though both sit in the same grade.
XYZ classification fixes that. Take monthly units sold for the last 12 months, compute the coefficient of variation, and bucket:
Compute both dimensions and every SKU gets a two-letter code. Nine cells, nine policies:
| X — steady | Y — seasonal/trending | Z — erratic | |
|---|---|---|---|
| A — high value | AX. The dream cell. Tight reorder point, thin buffer, near just-in-time. This is where you free up cash with no added risk. | AY. Do not run a flat reorder point. Plan by calendar: pre-build ahead of the peak, ramp the buffer down after it. | AZ. The hardest cell in the catalogue. Largest safety stock, weekly human review, and fix the lead time rather than the buffer — a second supplier beats another lakh of stock. |
| B — mid value | BX. Automate completely. Simple reorder point, monthly glance, minimal touch. | BY. Two planning cycles a year, seasonal reorder points, no weekly attention. | BZ. Modest buffer, monthly review. Strong candidate for make-to-order or a longer promised dispatch window. |
| C — low value | CX. Order a year's worth once. Buffer is cheap here; a stockout on a stationery item is embarrassing, not expensive. | CY. Buy the season's quantity in one go, before the season. | CZ. Either hold a generous cheap buffer or delete the SKU. Low value and unpredictable is the classic profile for a SKU that should be cut. |
Two cells deserve a standing calendar reminder. AZ is where stockouts on your biggest products come from, and no formula saves you — it needs a person looking weekly and a supplier conversation about lead time. CZ is where dead stock accumulates quietly, because nobody reviews C-items. Run the turnover ratio on that cell specifically and you will usually find something that has not moved in nine months.
Where ABC lies to you — and the override rule
ABC ranks by capital consumed. That is one useful lens, and it misses three things badly.
It ignores margin entirely
A ₹29 lakh usage-value SKU at 18% gross margin contributes less profit than an ₹8 lakh SKU at 62%. ABC will grade the first A and the second C, and your review calendar will follow the wrong one. Run a second sort on gross margin rupees before you finalise grades — it takes one extra column and reshuffles more of the list than you expect.
It ignores strategic and hero products
The product the press writes about. The entry SKU that acquires customers who then buy four more things. The new launch with three months of history, which the trailing-12-month maths grades C automatically. None of these show up correctly in usage value, and demoting them because a spreadsheet said so is how brands accidentally kill their own funnel.
It ignores cheap items that block everything
This is the expensive one for manufacturers. A ₹4 zip pull, a ₹2 care label, a ₹6 bottle cap — dead C by usage value, and if it is missing, a 2,000-unit production run stops. The cost of that stockout is not the ₹8,000 of missing caps; it is the idle line, the delayed dispatch, and the courier surcharge to catch up. Criticality has nothing to do with value.
The override rule. Grade by value first. Then apply exactly three overrides, in this order, and write each one down with a reason:
- Blocking override. If an item's absence stops production or dispatch of an A or B item, it inherits that item's grade. Your bill of materials tells you precisely which components those are — that is the whole point of having one.
- Margin override. If a B or C item sits in the top quartile of gross margin rupees, promote it one grade.
- Strategic override. Name them explicitly, cap the list at three SKUs, and treat them as A regardless of the maths. New launches sit here until they have twelve months of history.
Everything else stays where the arithmetic put it. The cap matters more than the rules: without it, every SKU becomes somebody's special case within two quarters and the classification is worthless again. Re-review the override list at each quarterly run and delete anything that no longer earns its place.
How often to re-run it
Quarterly is right for most brands, plus once immediately after a big season ends, because a Diwali or BFCM quarter reshuffles the top of the list. Grades drift for real reasons — a SKU that was B all year becomes A after one good campaign, and its safety stock and review cadence should follow within weeks, not next January.
Set a reminder, or let the numbers maintain themselves. Honey Shelf tracks real daily velocity per variant excluding stockout days, holds cost against every SKU, and its reports and XLSX export give you the ranking without rebuilding the spreadsheet each quarter. The maths above is not hard. Keeping it current by hand, four times a year, on a catalogue that keeps growing — that is the part people quietly stop doing.