Spend Analysis: Supplier ABC, Small Purchases and Category Summary
A free Excel workbook that totals your purchase export by supplier and category and calculates the ABC class and the share of small purchases; you enter the thresholds, the file suggests none.
What is a spend analysis?
A spend analysis totals the purchases of a period by supplier and by category and shows in numbers where spend is concentrated and where it is scattered. The input is an accounting or ERP export or an invoice list; amounts are taken excluding VAT. The Excel workbook below calculates each supplier’s total, share, cumulative share and ABC class, the count and amount share of small purchases, and the number of suppliers per category. You enter the thresholds; the file suggests none.
Free spend analysis template (Excel)
First enter your supplier and category names on the Lists sheet, then enter each purchase document on the Purchases sheet. Supplier total, share, rank, cumulative share, ABC class, the count and amount of documents below your threshold, category totals and the number of suppliers per category are calculated for you; a name not in the list and a blank or text amount are flagged on the row. Prints on A4; no sign-up.
The workbook is an analysis tool; it suggests no ABC threshold, small-purchase threshold, number of suppliers or savings. Rows starting “EXAMPLE —” are examples and their figures are not real: clear them and enter your own purchases. Both ABC thresholds and the small-purchase threshold come blank.
What data does a spend analysis need?
The raw material is the purchase record: a supplier-level transaction export from your accounting or ERP system, or an invoice list. Each row is one document and carries five pieces of information. The Purchases sheet asks for the same five columns.
| Field | What to enter | Watch out for |
|---|---|---|
| Date | Document date | Enter the purchases of a single period; if periods are mixed, the shares lose their meaning |
| Supplier | The name picked from the Lists sheet | Two spellings of the same company count as two suppliers; a name that is not in the list keeps the row out of the analysis |
| Category | From your own category list | A blank category, or one not in the list, keeps the row out of the analysis |
| Document no. | Number of the invoice, receipt or return document | For tracking only; the file does not catch the same document entered twice |
| Amount excl. VAT | The document’s amount without VAT; returns and discounts with a minus sign | Do not mix amounts including and excluding VAT; use a single currency |
The file assumes each row is one document: if you split one invoice across categories into several rows, each row counts as a separate document.
Why excluding VAT? The VAT rate can differ from item to item; if you add up two purchases with different rates including VAT, the amounts do not sit on the same base. Returns and discounts go in the same column as a separate row with a negative amount: they reduce the net total and do not count as documents. The period you choose changes the result: a period that contains a seasonal or one-off purchase (a capital item, for example) gives different shares from one that does not; note the unusual purchase or move it to a separate category.
The same logic works at item level: if you put item (or item group) names on Lists and enter the item name in the Supplier column on Purchases, the table ranks items instead; up to 60 names can be entered. The column headings still say supplier, the calculation does not change; you also need to fill the Category column (a single “General” category, for example).
Supplier ABC analysis, step by step
- Find each supplier’s net total. Take the total of its purchase documents and subtract returns and discounts.
- Calculate the share. Divide each supplier’s net total by the grand total.
- Rank from largest to smallest and write the cumulative share. The cumulative share is the sum of the shares up to and including that supplier. In the workbook you do not need to sort the list; the formula adds the larger totals, the equal totals listed before it, and itself.
- Choose two thresholds. Your organisation sets the A and B cumulative share thresholds; there is no single standard threshold and the file suggests none. While the thresholds are blank, the ABC column shows “Enter threshold”.
- Assign the class. If the supplier’s cumulative share before itself is below the A threshold, A; otherwise, if below the B threshold, B; otherwise C.
The consequence of the rule in step five: the supplier that takes the total past a threshold is also in that class, and the A group covers at least the A threshold of spend. Two readings are possible for the boundary supplier: “the one that crosses the threshold is also A” (the file’s rule) or “only those up to the threshold are A”. Under the second, if a single supplier’s share is above the threshold, there may be no A at all. Whichever you choose, keep the same rule from period to period.
| Supplier | Net total | Share | Cumulative share | Cumulative share before itself (in the file: cumulative share − share) |
|---|---|---|---|---|
| EXAMPLE — Supplier A | 19,500.00 | 65.1% | 65.1% | 0.0% |
| EXAMPLE — Supplier B | 8,500.00 | 28.4% | 93.5% | 65.1% |
| EXAMPLE — Supplier C | 1,550.00 | 5.2% | 98.7% | 93.5% |
| EXAMPLE — Supplier D | 400.00 | 1.3% | 100.0% | 98.7% |
The figures are EXAMPLE rows placed in the file only to show how the calculation works; they are not real spend or a threshold recommendation. Supplier A’s total is 12,000.00 + 8,500.00 − 1,000.00 (return) = 19,500.00.
To read the table with your own thresholds, look at the last column. Supplier B’s cumulative share before itself is 65.1%: if your A threshold is above 65.1%, B is also A; if it is below, B’s class is B or C depending on your B threshold. Of two suppliers with equal totals, the one listed first is ranked first; if they fall on a class boundary, that order decides the class. A supplier whose net total is zero or negative (returns exceed purchases) gets no class and is not in the share denominator.
How to read small purchases (tail spend)
Small purchases are the sum of many documents that are each low in value; it is the document-level counterpart of tail spend. How tail spend shows up on the MRO side is explained in What is MRO?; this workbook measures it with your own data.
- Choose one per-document threshold (excluding VAT). The threshold belongs to your organisation; the file suggests none. One way to choose is an amount limit your organisation already uses for order approval; another is a limit you pick after looking at how document amounts are distributed. It is not a legal or regulatory limit.
- Divide the count and amount of documents below the threshold by the totals of all purchase documents: the count share and the amount share are read separately.
- Look at the number of small documents per supplier. It shows which suppliers the small documents pile up with.
| Measure | Value |
|---|---|
| Purchase documents (amount above zero) | 7 |
| Documents below the threshold (900, 650 and 400) | 3 |
| Count share | 42.9% |
| Total amount of purchase documents | 30,950.00 |
| Amount of documents below the threshold | 1,950.00 |
| Amount share | 6.3% |
| Suppliers with documents below the threshold | 2 (Supplier C: 2 documents, Supplier D: 1 document) |
1,000 was chosen only to calculate the example; it is not a recommendation. Shares are calculated over purchase documents only: the return row (−1,000.00) is not in the denominator, which is why the total differs from the net total in the supplier table (29,950.00).
A high count share with a low amount share means many documents are carrying little spend. Every document brings its own approval, goods receipt, invoice entry and reconciliation; measure the time and cost of that work from your own process, the file does not (for the method, see the supplier consolidation calculator). A document whose amount equals the threshold does not count as below it.
Number of suppliers per category
The category summary gives, for each category, the net total, the number of documents and the number of suppliers with at least one purchase document in that category; a supplier is counted once. The number is a measure, not a verdict.
| Category | Net total | Documents | Suppliers |
|---|---|---|---|
| EXAMPLE — Category 1 | 22,000.00 | 3 | 2 |
| EXAMPLE — Category 2 | 6,400.00 | 2 | 2 |
| EXAMPLE — Category 3 | 1,550.00 | 2 | 1 |
The three categories add up to 29,950.00, the same as the net total in the supplier table (apart from rounding to two decimals in the file): the file includes or excludes every row by a single rule.
Many suppliers in one category can point to fragmentation, but it is not a problem in itself: the variety of the items, the delivery location or the need for a second source on a critical item can justify the number. Few suppliers, on the other hand, can mean dependency. How to weigh the number against the gain and the risk of reducing suppliers is covered in supplier consolidation; the effect on delivery time in supplier count and delivery time.
Spend analysis interpretation traps
| Trap | Why it misleads | What to do |
|---|---|---|
| Treating ABC as enough on its own | ABC looks only at the amount. A low-value item can be a part whose absence stops the line, and it may be bought from a class C supplier | Look at criticality and availability separately: classify by criticality and availability |
| Reading a high share as a price advantage | Share shows volume, not that the price is right; a high share can point to dependency on that supplier | Compare quotes line by line; read the share as a dependency risk too |
| Leaving out returns and discounts | Leaves the total gross and shifts the shares | Enter them as rows with a negative amount |
| Choosing the period at random | Seasonal or one-off purchases shift the shares | Choose a single, consistent period; note unusual purchases |
| Two spellings of the same company | They count as two suppliers; both look smaller than they are | Keep one spelling of the name on the Lists sheet |
| Treating fewer suppliers as a goal | Fewer suppliers can reduce competitive pressure and delivery flexibility | Look at criticality item by item first; keep a second source for critical items |
The file catches none of these by itself: it only calculates the numbers; the interpretation is yours.
Pulling scattered small purchases into one list
If the Small Purchases sheet shows many small documents, you can collect those items into a single requirement list and ask for a quote. AKSCO is a corporate supplier based in Türkiye that works without a catalogue and does not hold stock; we take your list, source the items and give a written quote line by line. On standard items we aim to quote the same business day. Custom production, imports or 50+ line lists can take longer; when they do, we tell you the timeline in writing on the day we receive the request. We do not promise that pulling items together will lower the price: quotes are compared line by line, you set up the comparison, and you can send the same list to other suppliers too.
For small items you use regularly, the consumables stock card shows when to order. We deliver across Türkiye’s 81 provinces and ship abroad under the Incoterm agreed for the order. A quote request creates no obligation to order. Scope: Office & Stationery Supplies, Cleaning & Hygiene Products, Safety Equipment & PPE, Industrial MRO & Spare Parts.
Using the Excel workbook
- Lists. Enter up to 60 supplier and 30 category names once; names cannot contain * ? ~ or leading, trailing or double spaces.
- Purchases. Enter each document on one row; pick the supplier and category from the list and enter the amount excluding VAT. If the Check column says anything other than “OK”, the row goes into no summary.
- Read the Status line. The line at the top of Purchases and the three summary sheets says whether the EXAMPLE rows have been cleared, how many rows have a name not in the list or a blank or text amount, and how many rows are in the analysis; a blank threshold is shown as information, not as a warning.
- Supplier Summary. Optionally enter the A and B cumulative share thresholds; if blank, the ABC column shows “Enter threshold”.
- Small Purchases. Optionally enter the per-document threshold; if blank, the sheet shows “Enter threshold”.
- Category Summary. Nothing to enter; it is calculated.
A name typed by hand may not match the list (for example because of letter case under some Excel language settings); the file then says “not in list” and leaves the row out of the summaries. Picking the name from the list avoids this.
Scope and limits
This page and workbook are an analysis tool that turns spend into numbers by supplier and category. They suggest no ABC threshold, small-purchase threshold, target number of suppliers, savings or price, and do not decide which supplier to keep or drop. The workbook does not convert currencies, does not calculate VAT and does not catch a document entered twice. Up to 500 purchase rows, 60 suppliers and 30 categories can be entered. It is not an accounting record or an audit document. Public bodies buy under their own procurement rules; this page is for internal analysis in the private sector.
Frequently Asked Questions
What is a purchasing spend analysis?
Totalling the purchases of a period by supplier and by category to show in numbers where spend is concentrated and where it is scattered. The input is an accounting or ERP export or an invoice list, with amounts excluding VAT. The output is each supplier’s total, share and ABC class, the share of small purchases and the number of suppliers per category.
What data do I need for a spend analysis?
For each purchase document: date, supplier, category, document number and the amount excluding VAT. Returns and discounts go in as separate rows with a negative amount. Take the purchases of a single period; if periods are mixed, the shares lose their meaning.
How do I do a supplier ABC analysis?
Find each supplier’s net total, rank from largest to smallest, and calculate share and cumulative share; then assign classes A, B and C against two cumulative share thresholds your organisation chooses. The rule in the file: if the supplier’s cumulative share before itself is below the A threshold, A; otherwise, if below the B threshold, B; otherwise C.
What percentage should the ABC thresholds be?
There is no single standard threshold; each organisation chooses it for its own purpose. That is why the file leaves both thresholds blank, and while they are blank the ABC column shows “Enter threshold”. Whatever thresholds you choose, use the same thresholds and the same boundary rule from period to period.
How is tail spend calculated?
Tail spend is not defined by a single calculation: at supplier level it is also read as the C class of an ABC (Pareto) analysis. This file also measures it at document level: you choose a per-document threshold (excluding VAT), and the count and amount of purchase documents below it are divided by the count and amount of all purchase documents. The count share and amount share are read separately. The threshold is your organisation’s; the file suggests none and it is not a legal or regulatory limit.
How do returns and discounts enter the analysis?
As separate rows with a negative amount in the same amount column. They reduce the supplier’s net total but do not count as documents, and they are not in the denominator of the small-purchase shares. A supplier whose net total is zero or negative gets no ABC class.
Is reducing the number of suppliers always right?
No. Fewer suppliers can reduce transaction work, but can also reduce competitive pressure and delivery flexibility; dependence on a single source for a critical item is a risk. The decision should rest not only on the ABC table but on the item’s criticality, delivery reliability and a line-by-line comparison of quotes.
Related guides
Consumables Stock Card Template
Balance and reorder point warning for regularly used items.
Read moreWhat Is MRO? Industrial Consumables Explained
Tail spend on the MRO side, and a two-axis classification by criticality and availability.
Read moreSupplier Consolidation Transaction Cost Calculator
What merging orders onto fewer suppliers can change in transaction cost, with a break-even test.
Read moreSingle-Supplier Consolidation: 8 Advantages
What consolidating suppliers gains, what it risks, and where to start.
Read moreSupplier Evaluation Scorecard
Score suppliers against criteria rather than by spend.
Read moreCorporate Purchasing Process
Seven steps from request to invoice, and the document at each step.
Read moreSend your list — your quote is ready within 1 business day.
A list, a photo or an Excel file — whatever is easiest. On standard items we aim to quote the same business day. Custom production, imports or 50+ line lists can take longer; when they do, we tell you the timeline in writing on the day we receive the request.