Statistical
Pareto 80/20 cumulative percentage and ABC inventory classification
Calculate running cumulative share on descending revenue SKUs and classify items into A (top 80%), B (next 15%), and C (bottom 5%) tiers.
Cumulative percentage requires descending order + anchored SUM
Pareto (80/20) and ABC inventory analysis sort items from highest annual dollar usage to lowest, then divide the expanding running sum SUM($B$2:B2) by the locked grand total SUM($B$2:$B$5).
This example classifies each item by its current cumulative share: at most 80% is A, at most 95% is B, and the rest C.
| SKU (A) | AnnualSpend (B) | Cumulative % | ABC Class |
|---|---|---|---|
| SKU-1 | 50000 | 50.0% (50k / 100k) | A (<=80%) |
| SKU-2 | 30000 | 80.0% (80k / 100k) | A (<=80%) |
| SKU-3 | 15000 | 95.0% (95k / 100k) | B (80–95%) |
| SKU-4 | 5000 | 100.0% (100k / 100k) | C (>95%) |
Cumulative share and ABC tier formulas
Sort Spend descending BEFORE applying the expanding SUM($B$2:B3) / SUM($B$2:$B$5) formula.
=ROUND(SUM($B$2:B3) / SUM($B$2:$B$5), 4)Assign A / B / C Pareto tier
IFS maps <= 80% to Class A, <= 95% to Class B, and the remaining tail to Class C.
=LET(cum, SUM($B$2:B4) / SUM($B$2:$B$5), IFS(cum<=0.8, "A", cum<=0.95, "B", TRUE, "C"))Inputs and interpretation
Sort nonnegative amounts descending and require a positive total. Define a stable tie order. Current-share and prior-share classification are different policies: a first item representing 90% is B under this example, so a tier can be empty. Thresholds are illustrative; the 80/20 heuristic is not a guaranteed distribution.
Try the example
Use the original values or change them to test your understanding.