ABC Analysis: Calculations, Excel Steps, and Classification Rules
Shusaku Yosa
ABC analysis ranks products, customers, or other items by a selected value and uses cumulative shares to assign priority classes. Define the population, period, measure, thresholds, and boundary-crossing rule before calculating.
A does not automatically mean profitable, and C does not mean unnecessary. Revenue, gross profit, and unit volume produce different rankings. The fictional ten-item example below separates calculation from business decisions.
Choose a measure that matches the decision
Oracle’s ABC analysis explanation describes relative item ranking against a selected criterion. Use a consistent population and reporting period.
Decision | Possible measure | Companion checks |
|---|---|---|
Revenue concentration | Net revenue for the period | Discounts, returns, and profitability |
Economic concentration | Consistently defined gross profit or contribution | Cost scope, negative values, allocations |
Volume concentration | Units shipped or sold | Price, returns, critical components |
Inventory priorities | Inventory value or annual usage value | Demand variability, lead time, stockout impact |
Inventory value or shipped units alone do not measure inventory efficiency. Review turnover, availability, and cash tied up separately. Combine ABC with customer analysis when recency, frequency, or lifetime value matters. This classification method also differs from activity-based costing, another use of “ABC.”
Calculate shares from ten fictional items
The example uses Japanese yen, excluding tax, for one period. Total net revenue is JPY 10,000,000 and all values are positive. Fix discount and return treatment using consistent revenue definitions.
Item share equals item revenue divided by total revenue. Cumulative share adds shares in descending order. Item 1 contributes 3,000,000 ÷ 10,000,000 = 30%; the first two items contribute 30% + 22% = 52%.
Item | Revenue | Share | Cumulative share | Class |
|---|---|---|---|---|
1 | JPY 3,000,000 | 30% | 30% | A |
2 | JPY 2,200,000 | 22% | 52% | A |
3 | JPY 1,600,000 | 16% | 68% | A |
4 | JPY 1,000,000 | 10% | 78% | A |
5 | JPY 700,000 | 7% | 85% | B |
6 | JPY 500,000 | 5% | 90% | B |
7 | JPY 400,000 | 4% | 94% | C |
8 | JPY 300,000 | 3% | 97% | C |
9 | JPY 200,000 | 2% | 99% | C |
10 | JPY 100,000 | 1% | 100% | C |
Total | JPY 10,000,000 | 100% | — | A 78% / B 12% / C 10% |
A complete positive-value dataset with a positive denominator totals 100% before rounding. Displayed percentages can sum differently. Zero totals, negative values, and missing data need investigation rather than automatic application of a conventional cumulative table.
Define what happens when an item crosses a threshold
With thresholds of 70% and 90%, item 4 takes cumulative revenue from 68% to 78%. Its classification needs an explicit rule. Microsoft’s ABC report documentation treats thresholds as configurable, not a universal percentage.
This example classifies using the cumulative amount before adding the item. Below 70% means A; from 70% to below 90% means B; 90% or above means C. The first crossing item stays in the higher-priority group. Therefore item 4 is A, item 6 is B, and item 7 is C.
- A: four items, JPY 7,800,000, or 78%.
- B: two items, JPY 1,200,000, or 12%.
- C: four items, JPY 1,000,000, or 10%.
A different rule using cumulative share after addition can place item 4 in B and leave A at 68%. That is a classification choice, not inherently an arithmetic error. Align formulas, labels, and notes. Define stable ordering or grouping for tied values too.
The 80/20 idea is a concentration heuristic, not a guarantee that 20% of items generate 80% of revenue. Thresholds such as 80/15/5 also require a management rationale.
Reproduce the calculation in Excel
- Import item IDs and revenue; check duplicates, period, returns, and missing values.
- Sort descending with a stable tie rule.
- Use B1 as a text heading and B2:B11 for revenue amounts in yen.
- Calculate shares in C, cumulative shares in D, and classes in E.
- Reconcile totals and inspect boundary rows manually.
Use C2=B2/SUM($B$2:$B$11) and D2=SUM($C$2:C2). Format C and D as percentages while retaining underlying values.
For classification, use E2=IF(SUM($B$1:B1)<SUM($B$2:$B$11)*70%,"A",IF(SUM($B$1:B1)<SUM($B$2:$B$11)*90%,"B","C")) and copy down. SUM ignores the text heading; the expanding range includes amounts before the current row.
This applies thresholds to original amounts rather than rounded display percentages or a subtraction of cumulative shares. Do not conceal a zero-denominator problem as 0%. An Excel Pareto chart can visualize concentration from categories and amounts, but retain the classification rule in the table notes.
Use classes with exceptions, not automatic actions
Class or exception | Check | Possible decision |
|---|---|---|
A: high impact | Margin, volatility, supply constraints | Review availability and economics more closely |
B: intermediate | Growth and testable improvement potential | Test a focused change; promotion is not success itself |
C: lower ranked | Critical parts, complementary items, new products | Retain exceptions rather than automatically removing |
Negative, zero-total, missing data | Returns, losses, source problems | Investigate separately from a normal cumulative table |
Ties and boundaries | Stable ordering and crossing rule | Apply the same method on every update |
Class A alone does not determine safety stock: demand variability, replenishment time, stockout impact, and holding costs matter. Removing a low-ranked component can disrupt a high-revenue product. New items can also rank low simply because their observation period is short.
Use cost classification and allocation for economics, and LTV and CAC for customer value. High-revenue accounts can require substantial service effort.
Review absolute outcomes as well as rank changes
An item can move into A because other items declined. Keep population, period, measure, thresholds, and classification method comparable; review revenue, gross profit, availability, inventory cash, and workload too. A before-and-after difference alone does not establish causality.
Maintain a record of scope, measure, period, thresholds, rule, exceptions, owner, action, and review date. Xtrategy’s verified CRM and initiative-cost features can help organize related management information. Check the feature overview without assuming automatic ABC classification or inventory optimization.