Blog · Procurement and supply chain · Procurement
ABC supplier analysis groups suppliers by their share of annual purchasing spend. Build a ranked table from accounts payable records, apply explicit cutoffs, and reconcile every tier to the source total.
ABC supplier analysis ranks suppliers by annual spend and groups them according to their cumulative share of the total. Start with reconciled accounts payable records, aggregate by a stable supplier ID, and apply a written boundary rule. The result guides review effort; it does not decide whether a supplier is safe to replace.
Use a complete calendar or fiscal year of posted purchasing records. For this example, a US industrial distributor analyzes calendar-year 2025 invoices and credit memos in USD. The amounts below are illustrative business records, not industry benchmarks.
The export needs a unique invoice-line key, posting date, supplier ID, category, currency, and signed purchasing amount. Keep invoices positive and credit memos negative. State whether the amount includes freight and nonrecoverable taxes. Exclude employee reimbursements and other non-supplier entries only through a documented scope rule.
Use supplier IDs rather than names to combine branches and spelling variants. The supplier name normalization guide covers that preparation. Decide whether the analysis treats separate legal entities as distinct suppliers or combines them under a parent, and retain the underlying IDs either way.
Do not combine different currencies without a documented conversion basis. Also investigate suppliers with net credits for the year before calculating cumulative shares: negative supplier totals can make the running percentage fall instead of rise.
Suppose the source sheet is named Purchases. Column B contains posting dates, C contains canonical supplier IDs, and E contains signed USD amounts. Put each supplier ID in column A of a separate ranking sheet. Calculate annual spend with:
=SUMIFS(Purchases!$E$2:$E$10000,Purchases!$C$2:$C$10000,A2,Purchases!$B$2:$B$10000,">="&DATE(2025,1,1),Purchases!$B$2:$B$10000,"<"&DATE(2026,1,1))
The formula uses several conditions on the same population, as described in Microsoft's SUMIFS documentation. Expand all ranges together when the export contains more rows.
Place the result in column C and the supplier's category in B. The example assigns each supplier to one category for clarity. In a real multi-category business, retain supplier-category detail separately and sum it once into the supplier ranking.
Sort the entire table by annual spend, largest first. Use supplier ID as a consistent tie breaker. Sorting only the amount column would detach each supplier from its spend.
Use this illustrative policy: A covers rows whose cumulative spend share is at most 80%; B covers subsequent rows through 95%; C contains the rest. A supplier whose row crosses a boundary goes into the next tier. Other boundary policies are possible, but changing policy changes the result.
Supplier spend share = Supplier annual spend / Total annual spend
Cumulative share = Spend from the first ranked supplier through the current row / Total annual spend
With eight suppliers in rows 2 through 9, enter these formulas and fill down:
D2 = C2/SUM($C$2:$C$9)
E2 = SUM($C$2:C2)/SUM($C$2:$C$9)
F2 = IF(E2<=80%,"A",IF(E2<=95%,"B","C"))
Here, D2 = identifies the destination cell; enter the expression beginning with = in that cell. Microsoft documents IF as a logical comparison that returns different results for true and false conditions. The nested comparison implements the stated policy.
| Supplier | Category | Annual spend (USD) | Spend share | Cumulative share | Tier |
|---|---|---|---|---|---|
| Aster | Components | 400,000 | 40.0% | 40.0% | A |
| Beacon | Components | 250,000 | 25.0% | 65.0% | A |
| Cedar | Packaging | 150,000 | 15.0% | 80.0% | A |
| Delta | Components | 80,000 | 8.0% | 88.0% | B |
| Elm | Services | 50,000 | 5.0% | 93.0% | B |
| Flint | Packaging | 30,000 | 3.0% | 96.0% | C |
| Grove | Services | 25,000 | 2.5% | 98.5% | C |
| Harbor | Packaging | 15,000 | 1.5% | 100.0% | C |
| Total | 1,000,000 | 100.0% |
Percentages are displayed to one decimal place; classification uses the unrounded cumulative share. Cedar ends exactly at 80%. Flint takes the cumulative share from 93% to 96%, so this policy assigns Flint to C. Do not split its invoices merely to force B to end at 95%.
The ranking method resembles ABC customer analysis, but supplier spend measures purchasing exposure rather than customer revenue or service entitlement.
Reconcile the supplier ranking to the scoped AP export before using it in a review. Then summarize the assigned tiers:
| Tier | Suppliers | Annual spend (USD) | Share of annual spend |
|---|---|---|---|
| A | 3 | 800,000 | 80.0% |
| B | 2 | 130,000 | 13.0% |
| C | 3 | 70,000 | 7.0% |
| Total | 8 | 1,000,000 | 100.0% |
The spend check is 800,000 + 130,000 + 70,000 = 1,000,000. The supplier check is 3 + 2 + 3 = 8. Both must match the ranking, and the ranking must match the source population. A missing supplier can leave the final cumulative share at 100% while the underlying population is incomplete.
The same source rows also give this category view:
| Category | Suppliers | Annual spend (USD) | Company spend share |
|---|---|---|---|
| Components | 3 | 730,000 | 73.0% |
| Packaging | 3 | 195,000 | 19.5% |
| Services | 2 | 75,000 | 7.5% |
| Total | 8 | 1,000,000 | 100.0% |
Components account for $730,000, but this table does not show whether its suppliers can substitute for one another. Use suppliers per category to examine the category structure before proposing consolidation. If you rerun ABC within each category, label it separately: a category A supplier need not be a company A supplier.
Assign an owner to the largest purchasing relationships and check whether their agreements cover the categories actually bought. For the tail, examine repeated small orders, duplicate suppliers, and purchasing routes before negotiating a reduction in supplier count.
Keep the spend tier beside a separate criticality assessment. A purchasing manager can use the tier to allocate commercial review time while an operations owner identifies suppliers whose interruption would stop production. Neither assessment should silently overwrite the other.
For a visual explanation of ranking and cumulative shares, see Pareto analysis. Keep supplier decisions tied to the underlying purchasing records rather than assuming that a familiar distribution must appear in every business.
Covirage is not affiliated with Microsoft.
Bring an AP export with supplier IDs, categories, dates, and signed amounts, plus your tier policy. Covirage maps the columns, and its tools compute and check the supplier rankings and category comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for procurement teams for the purchasing decisions those comparisons support.
Choose cutoffs that support your review capacity and document how boundary suppliers are treated. There is no requirement that a tier end exactly at its cutoff because a supplier is usually kept whole. Keep the same rule when comparing periods.
Either can answer a business question, but they measure different things. Invoice spend describes purchasing recorded during the period; payments describe cash outflows. Select one basis, include its corresponding credits, and reconcile to that source without mixing the two.
A C classification means a small share of measured spend, not low operational importance. A supplier providing a critical spare part can remain essential. Combine spend tiers with availability, substitution, and service evidence before changing review frequency or removing suppliers.