How to Do an ABC Analysis: Where the Money Moves Through Stock and What Each Class Means for Ordering

What an ABC analysis answers

Somebody has to set a reorder point, a safety stock and an order quantity for every part in a store, and check them now and then. With a few hundred parts, nobody gives each one the same care. An ABC analysis tells you which parts to look at first.

It ranks parts by consumption value, the money that passes through each part in a year. The ranked list is then cut into three classes. A holds the parts with the highest consumption value, C the lowest, and B whatever sits between. Ten extra days of safety stock on the mechanical seal, an A part, tie up €2,958.90. On the O-ring set, a C part, the same ten days come to €98.63.

There are two things it won't tell you: whether a part may run out, and whether you hold too much of it. Mistakes 3 and 5 come back to both.

How to do an ABC analysis in four steps

Consumption value = units used per year × unit cost

Share = consumption value ÷ total consumption value of all parts

Work out consumption value. Use a full year of consumption and the same unit cost your stock list is valued at.

Sort, highest first. The part with the largest consumption value goes to the top.

Add share and cumulative share. Cumulative share is the running total of the shares from the top down. The last row reads 100%.

Draw the class limits. You decide up to which cumulative share a part is A and up to which it is B. Everything after that is C.

Steps one to three are arithmetic. Step four is a decision, and no formula makes it for you. Set the A limit low enough that you can check every A part by hand.

ABC analysis inventory example: fifteen spare parts

This is an invented example. The company, the parts and the figures are all made up. The store is the pump maker's spare parts store from how to calculate inventory turnover, how to calculate reorder point and how to calculate economic order quantity.

Six parts come from those articles with yearly consumption and unit cost unchanged: the mechanical seal, gasket set, electric motor, impeller, control board and the housing for the old series. Six parts are too few to split three ways, so nine more have been added for this article. All fifteen are in the table below.

The class limits are a choice made for this example. A runs to a cumulative share of 75%, B to 95%, and C is the rest. A part's class depends on the cumulative share including the part itself, so a part is A if that figure is 75% or less. Other limits work too, as long as you can say why you chose them and keep them from year to year.

Step 1: consumption value

The electric motor uses 1,200 pcs × €85.00 = €102,000 a year. The fifteen parts add up to €457,040.

Step 2: sort

The mechanical seal leads at €108,000 and the rating plate comes last at €800. The motor is second.

Step 3: share and cumulative share

€102,000 ÷ €457,040 = 22.3%

Only the mechanical seal, at 23.6%, is above it. The motor's cumulative share is 23.6% + 22.3% = 45.9%.

Step 4: class

45.9% is below 75%, so the motor is an A part.

All fifteen parts, worked the same way:

Shares are shown to one decimal. Cumulative shares are summed from the unrounded shares, which is why the pump shaft reads 94.8% while the rounded shares above it add to 94.7%. Unrounded it is 94.75%.

The result by class

Four parts out of fifteen carry 73.5% of consumption value. The seven C parts together move €23,990 a year, a little over a fifth of what goes through the mechanical seal alone. The class percentages are rounded one by one and add to 99.9%.

Look at two rows. The most expensive piece on the list, the €140.00 housing, ends up in C. The gasket set costs €6.00 and is in A, because 9,000 of them go out every year.

What each class means for reorder point and order quantity

Every day of stock costs a share of consumption value

A day of stock ties up 1/365 of a part's consumption value. Ten days of safety stock on the mechanical seal put €108,000 × 10 ÷ 365 = €2,958.90 on the shelf. On the O-ring set the same ten days come to €3,600 × 10 ÷ 365 = €98.63. Same rule, thirty times the money.

A parts: work each one out and check it often

On an A part every input to the reorder point deserves its own look. The control board has a lead time of 140 days, a safety stock of 150 and a reorder point of 381. If the system holds one week more lead time than the supplier really needs, the reorder point goes up by 600 × 7 ÷ 365 = 11.5 boards. Every delivery then arrives with 11.5 more boards on the shelf than anyone decided on, €1,380.82 of stock. How to calculate reorder point shows how to measure lead time on your last ten orders.

The EOQ formula ties order frequency to consumption value and nothing else.

Orders per year = √(consumption value × holding cost rate ÷ (2 × cost per order))

With the assumptions from how to calculate economic order quantity, €50 per order and a 20% holding cost rate, the mechanical seal gets √(108,000 × 0.2 ÷ 100) = √216 = 14.7 orders a year. One delivery lasts 365 ÷ 14.7 = 24.8 days. The rating plate gets √(800 × 0.2 ÷ 100) = √1.6 = 1.3 orders, and one delivery lasts 288.6 days. By the formula, A parts are ordered often and lean, C parts rarely and generously.

B parts: the class doesn't tell you where the error is

A class tells you where a lot of money flows. It does not tell you where the order quantity is wrong. The impeller is a B part. It is ordered 720 at a time today, and its EOQ is 126. The gap costs €1,864 a year. Of the five parts in the EOQ article, only the control board costs more. The mechanical seal, first on the ABC list, costs €399.

So run the formula for every A and B part and sort the result by the gap in euros. The ABC class decides how much attention a part gets. The gap decides where you start.

C parts: one rule for all of them

C parts can live with a fixed rule that someone reviews once a year, for example a generous safety stock in days and a few large orders. Ten extra days of O-ring sets cost €19.73 a year in holding cost at 20%. That hardly justifies working the figure out to the day.

The housing for the old series is the exception. It is a C part that still needs a closer look, as mistake 5 shows.

Doing the ABC analysis in a spreadsheet

A single sheet with seven columns is enough. The formulas below are written for row 2. Copy each one down to row 16, the last of the fifteen parts.

1. Column A is the item, column B units used per year, column C unit cost. Export these three from your system.
2. Column D is consumption value: =B2*C2
3. Sort rows 2 to 16 by column D, largest first. Cumulative share depends on the order, so sort before you fill column F.
4. Column E is share: =D2/SUM($D$2:$D$16), formatted as a percentage. The dollar signs keep the range fixed as you copy down.
5. Column F is cumulative share: =SUM($E$2:E2). The start stays put and the end moves with each row. F16 has to read 100%.
6. Column G is the class: =IF(F2<=0.75,"A",IF(F2<=0.95,"B","C"))

Name the sheet ABC. In the sheet where you keep reorder points and order quantities, =XLOOKUP(A2,ABC!$A$2:$A$16,ABC!$G$2:$G$16) brings in each part's class. If your version has no XLOOKUP, =VLOOKUP(A2,ABC!$A$2:$G$16,7,FALSE) does the same job. Look up by part number if you have one. Two lists rarely spell a description the same way, and one stray space returns #N/A.

To change the limits later, edit the two numbers in G2 and copy the formula down again.

Five counting mistakes in an ABC analysis

Each mistake comes with figures from the fifteen-part list.

1. Sorting by unit cost

Sort by unit cost and the €140.00 housing comes first. By consumption value it is ninth, with 1.2%. The four most expensive pieces are the housing, control board, pump shaft and electric motor, and together they carry €193,850, or 42.4% of consumption value. The mechanical seal, which has the largest consumption value of all, is eighth by unit cost.

Unit cost tells you what one piece costs. The money moving through a part is unit cost times quantity.

2. Sorting by units used

By quantity the gasket set, mechanical seal, screw set and O-ring set lead. Those four carry €167,850, or 36.7% of consumption value. The screw set goes out 5,000 times a year and has a share of 0.5%. The control board is tenth by quantity and carries 15.8%.

Pieces are no measure across parts. In a piece count, a screw weighs as much as a circuit board.

3. Using stock value in place of consumption value

The turnover article gives stock for the original six parts. It gives none for the nine added here. Ranked by average stock value, the control board leads with €36,000 and the housing follows with €28,000. The mechanical seal shares last place with the gasket set at €9,000, yet its consumption value of €108,000 is the largest on the list.

Stock value answers where money sits. An ABC analysis asks where money flows. Keep the two lists apart. How to calculate inventory turnover sets each part's stock against its consumption.

4. Leaving the boundary part undecided

Before the impeller, cumulative share stands at 73.5%. With it, 83.5%. This example counts the share including the part, so the impeller is B. Suppose someone counts the share before the part. The impeller is then A, since only 73.5% comes before it, and class A carries 83.5% instead of 73.5%. Under that rule the housing moves from C to B as well, with 94.75% before it.

Nothing about consumption or limits has changed, and two parts switch class. Write the boundary rule down next to the limits. If it changes between two years, parts will change class while their consumption stays exactly where it was.

5. Treating C as unimportant

The housing for the old series is a C part with 1.2% of consumption value. In the turnover article it ties up €28,000, which is 25.5% of the six parts' stock value, and it lasts 1,825 days. An ABC analysis on consumption can't see that stock. Put days on hand next to each part's class and look at every C part that sits for a long time.

A C part may not run out either. Without a €1.20 O-ring set a pump stays in the workshop just as it would without a motor. The class says how much effort a part is worth. How bad it is when the part is missing is a separate question, and the people who do the repairs are the ones to ask.

The next step

Export last twelve months' consumption and the unit cost for every part. Work out consumption value, share and cumulative share as above, and set your two limits. For the A parts, check lead time, safety stock and order quantity first, and note who last changed each of them. How evenly each part goes out across the year is what an XYZ analysis measures, worked on the same fifteen parts with twelve months each.

If you want to know where in your process reorder points and order quantities get set, and who checks them, that is the scope of our process analysis. Fixed scope, fixed duration, fixed price.