ABC analysis step by step

A warehouse holds hundreds, sometimes thousands, of positions, but they are not all equally important. A handful of items bring in most of the revenue, while many sit on the shelf for years. Controlling everything with the same rigour is the easiest way to waste time and money. ABC analysis divides items into three groups by importance and shows where to focus attention.

Five steps

1. Choose the period and the criterion. The period is 6-12 months, 12 for seasonal goods. The criterion is usually sales value, or gross profit for a more precise result.
2. Gather the data. Export the period total for each item from 1C or Excel. Merge duplicate records and bring units of measure to one form.
3. Sort the totals in descending order: the best seller first.
4. Calculate each item's share of the total and the cumulative share, that is, the sum of the shares up to and including that item.
5. Split into groups. The usual cut-offs are 80/15/5: cumulative share up to 80 percent is A, 80 to 95 is B, the rest is C. Agree in advance which group an item sitting exactly on a boundary goes to, and write it down.

An example

A wholesale depot's quarterly sales total 100 million so'm (illustrative figures), and the list is limited to ten items:

• Sunflower oil — 45 million, cumulative 45.
• Flour — 20 million, cumulative 65.
• Sugar — 15 million, cumulative 80.
• Rice — 6 million, cumulative 86.
• Tea — 5 million, cumulative 91.
• Pasta — 4 million, cumulative 95.
• Salt — 2 million, cumulative 97.
• Vinegar — 1.5 million, cumulative 98.5.
• Yeast — 1 million, cumulative 99.5.
• Vanillin — 0.5 million, cumulative 100.

Result: group A holds oil, flour and sugar — three of the ten items but 80 percent of sales. Group B holds rice, tea and pasta — 15 percent. Group C holds the remaining four items — just 5 percent. A real range has hundreds of positions and group A is usually a small share of them, though the exact proportion depends on your data.

Excel formulas: share — C2 / SUM($C$2:$C$11). Cumulative share — ROUND(SUM($D$2:D2), 4). Group — IF(E2<=0.8, "A", IF(E2<=0.95, "B", "C")). The separator (comma or semicolon) depends on your Excel settings.

If you change the criterion, the order changes too. Say one item has 20 million so'm of revenue at a 3 percent margin: profit 0.6 million. Another has 5 million of revenue at a 25 percent margin: profit 1.25 million. By revenue the first matters more, by profit the second. So it is worth working out group A both by revenue and by profit.

The cut-offs are not a law either: with a small number of items 70/20/10 may suit better. What matters is applying the chosen rule the same way every time.

What to do with each group

• Group A: the tightest control. Run cycle counts often (for example monthly), watch the balance daily, calculate safety stock and agree a separate arrangement with the supplier.
• Group B: moderate control. A count once a quarter and a standard reorder point.
• Group C: simplified handling. A count once or twice a year, infrequent orders in large batches, and some items can be dropped from the range.

Common mistakes

• Sorting by number of units instead of value: a cheap but fast-moving item wrongly lands in group A.
• Using too short a period: a single month's data turns random spikes into "important" items.
• Putting new and dormant items into the main list. New ones have no history and dormant ones have zero sales, so treat them separately.
• Looking only at revenue. An item with large revenue but low profit may sit in group A, so check the margin too.
• Treating group C as "unnecessary". Salt or vanillin earn little, but customers often buy them along with the main goods. Check joint purchases before removing anything from the range.

Conclusion

ABC analysis is not complicated: sort, calculate shares and split by thresholds. Its strength lies in the decisions that follow: what to count more often, where to put more stock, what to cut. CountLab runs ABC/XYZ analysis together with stock-count results and helps build a cycle count plan by group.