ABC inventory analysis: rank purchased parts without overlooking critical C items
Calculate ABC inventory classes from annual usage value with a worked table and Excel formulas. Keep critical C items visible and choose practical controls for each class.
ABC inventory analysis groups parts by their share of a chosen value measure. For purchased components, annual usage value is a useful starting point: units consumed during the year multiplied by unit cost. Rank the parts, set A, B and C boundaries, and use the groups to decide where buyers and inventory teams spend their time.
A low-value seal can still stop a build. Keep a separate criticality flag beside the ABC class so a C item does not disappear from the shortage list. Start with one site's consumption records and a list of parts with no approved substitute.
What does ABC analysis tell a buyer?
ABC shows which parts account for most of the value in your chosen scope. An A item merits closer cost and stock review; a routine C item may suit simpler replenishment. The class alone does not tell you whether a part is available, has a substitute or will stop production.
Oracle's overview of ABC analysis makes the valuation criterion a choice. This article uses annual consumption at cost. Ranking today's on-hand value or forecast demand answers a different question; label the basis on the report.
The Kraljic matrix adds supply risk and profit impact when you need to choose a sourcing approach. Use it alongside the ABC value class, especially for parts with no approved alternative.
Which data do you need from your ERP?
Export the part number, quantity consumed over the same twelve months, unit of measure and unit cost for one site or another clearly defined scope. Use production issues or consumption records rather than purchase receipts: buying a large lot does not mean you used it. Reconcile returns and reversed transactions.
Convert quantities and costs to the same unit before multiplying. A cost per box and usage in individual pieces will distort the ranking. Use one currency and a documented cost basis. The example below holds unit cost constant; for historical transaction values, use the costs attached to those transactions consistently. Oracle's compilation guide distinguishes historical usage value from on-hand and forecast measures.
Flag new parts, missing costs, negative net usage and items with no consumption for separate review. Do not silently turn missing costs into zero or assume last year's low usage describes next month's production. Retain a list of excluded items and the reason for each exclusion.
How do you calculate annual usage value and the ABC classes?
Calculate annual usage value = annual quantity consumed × unit cost. Sort largest to smallest, divide each value by the total, then calculate the running share. Decide the class boundaries before assigning labels. In this illustrative policy, A covers the first 80% of value, B the next 15%, and C the remainder.
Keep the item that crosses a boundary in the class it started in. The first item is A. Each later item is A if the cumulative share before it is below 80%, B if that share is at least 80% but below 95%, and C otherwise. A large part can make a class exceed its target share. These are policy choices, not fixed percentages of the number of parts. With one dominant item, a class can be empty.
| Part | Annual units | Unit cost | Annual usage value | Share | Cumulative share | Class |
|---|---|---|---|---|---|---|
| Machined housing | 1,200 | $50 | $60,000 | 60% | 60% | A |
| Motor | 200 | $100 | $20,000 | 20% | 80% | A |
| Sensor | 500 | $20 | $10,000 | 10% | 90% | B |
| Bearing | 1,000 | $5 | $5,000 | 5% | 95% | B |
| Coated seal | 4,000 | $1 | $4,000 | 4% | 99% | C |
| Label | 10,000 | $0.10 | $1,000 | 1% | 100% | C |
The total is $100,000. Two A parts account for 80% of annual usage value, two B parts for 15%, and two C parts for 5%. Those are consumption values, not cash tied up in stock today. Use average inventory and the carrying-cost rate to estimate the cost of holding stock.
How do you do ABC inventory analysis in Excel?
Put the six parts in rows 2–7. Use columns A for part, B for annual units, C for unit cost and D for annual usage value. Enter =B2*C2 in D2 and fill down. Sort the whole table by D, largest first, so quantities and costs stay with their part numbers.
Use part number as a second sort key when values tie, and record that rule. If your policy keeps equal-value items in one class, group them before assigning classes rather than applying the row formulas unchanged.
In E2, enter =D2/SUM($D$2:$D$7) for the share. In F2, enter =SUM($D$2:D2)/SUM($D$2:$D$7) for the cumulative share. Fill both down and format E and F as percentages. Check that D totals 100,000 and F7 is 100%.
Set G2 to A. In G3 enter =IF(F2<80%,"A",IF(F2<95%,"B","C")) and fill through G7. It reads the previous row's cumulative share, so the boundary-crossing item stays in the earlier class. Compare the output with the table before using your full export. Extend every total range when you add rows; keep a zero-total or empty export out of this calculation.
What should change for A, B and C items?
Assign a stock-review and counting routine to each class. A items often justify closer review of stock, open orders and count discrepancies because errors affect more value. Routine C items can use simpler ordering where demand and supply allow it. Oracle's ABC class guidance also describes using classes to vary cycle-count frequency.
| Value class | Starting control routine | Check before applying it |
|---|---|---|
| A | Review stock and open orders closely; investigate count differences promptly | Do not reduce cover without checking demand and confirmed supply |
| B | Review exceptions and replenishment settings on a regular schedule | Escalate a change in demand, lead time or supplier performance |
| C | Simplify repeat ordering and counting where the risk permits | Retain criticality, shelf-life and traceability controls |
Turn those priorities into a cycle-count plan with a defined count record, workload and first-pass measure. Keep exact matches separate from tolerance acceptance and approved stock corrections.
Choose review and count frequencies for your own workload and risk; the letters do not prescribe a universal calendar. ABC does not calculate an order quantity. Use the EOQ calculator to compare lot sizes and the reorder-point guide to work out when to order.
What if a C item stops production?
The coated seal stays C because its annual usage value is $4,000. Suppose one approved coater supplies it, there is no approved substitute, and its absence stops a scheduled build. Mark it C, critical, give it an owner, and review its confirmed deliveries against production needs. The critical flag changes the attention it gets.
Check available stock, open order quantities, actual lead-time variation and the time needed to approve an alternative. Use the safety-stock calculator when its demand assumptions fit the part. A low ABC class is no reason to cut its service target or set its buffer to zero.
Decide whether to expedite, hold a buffer or qualify another source from the shortage consequences and the cost of each option. The working-capital guide covers stock reductions without trading the cash target for a missed build.
When should you refresh the analysis?
Re-run it after a material change in demand, cost, product mix or sourcing, and give the report a regular review date. Compare movements by part before changing controls. A move from B to A can reflect a price increase rather than more usage. Save the period, cost basis, boundaries, exclusions and approver with each version.
Review new products against dated requirements because a short history understates their coming demand. Separate obsolete stock for disposition. For intermittent parts, inspect the timing and size of demand rather than relying on annual totals alone. The ABC report should end with owners and actions, including the exceptions, so the next buyer can use it.
Questions teams ask
Are the 80/15/5 boundaries mandatory?
No. They are the illustrative value shares used here. Choose boundaries that produce a useful workload, document how boundary-crossing items and ties are handled, and keep the method consistent between reviews. Do not force 20% of the part numbers into A just to match a familiar rule.
Does a C item always need less safety stock?
No. Its value class does not measure the effect of a shortage. Demand, lead-time uncertainty, service requirements and available alternatives determine the buffer. A cheap seal with no substitute can need closer delivery tracking than an expensive standard part.
Is ABC analysis the same as XYZ analysis?
No. In this guide ABC ranks annual usage value. XYZ adds a separate view of demand variability. Keep the two labels separate and define the variability measure before using an ABC/XYZ combination to change replenishment settings.
Can you classify parts with no usage history?
Historical usage cannot rank their future demand reliably. Flag new parts for review against the production plan, expected consumption and approved cost. Keep them visible until there is enough usable history; a missing history is not evidence that the part is unimportant.