A spend analysis template is a spreadsheet that gathers every purchase into one table and slices it by supplier, category, department and month. It shows where money goes, who you buy from most and where you can save.
It is the working file you use to answer one question: what did we buy, from whom, and who asked for it? Payables and card exports go into a single data tab, each line gets a clean supplier name and a category, and summary tabs total the result.
Procurement and finance teams run it quarterly, before budget season or ahead of a big renewal. A procurement spend analysis template adds the next step: turning the totals into a list of savings opportunities with an owner and a target date.
Most mid-sized companies can run spend analysis in a spreadsheet for years. The work is in the cleansing, not the maths: once supplier names and categories are consistent, every chart and pivot follows.
Invoice-level lines from AP, cards and expenses, with date, supplier, amount and cost centre.
A mapping table that turns "Brightline Software Inc" and "BRIGHTLINE" into one supplier.
A short, fixed list of categories so every line lands in exactly one bucket.
The cleaned table you can cut by supplier, category, department and month.
Savings ideas from the analysis, sized, owned and followed to a result.
Ready to use in Excel and Google Sheets. Fill it in, save it, reuse it.
Export 12 months of invoices, card transactions and expenses. Keep invoice-level lines, not monthly totals.
Merge supplier name variants, remove duplicates and convert everything to one currency.
Give every line one category from your taxonomy, and tag the department that asked for it.
Total by supplier, category, department and month, then rank suppliers to find concentration and tail spend.
List the opportunities, size them, assign owners and recheck the numbers next quarter.
Start with five columns: Date, Supplier, Category, Department, Amount. One pivot table on those answers most first-round questions.
Paste raw exports into the Raw tab and let the formulas build the cube. Supplier names are cleaned through a mapping table, categories are looked up from the supplier, and the month is worked out from the invoice date. Lines nobody can classify show as Unclassified, which is your first to-do list.
| Date | Supplier | Category | Department | Month | PO | Amount |
|---|---|---|---|---|---|---|
| 03 Jul 2026 | Brightline Software | Software | Engineering | 2026-07 | PO-2201 | 18,000.00 |
| 05 Jul 2026 | Northwind Logistics | Logistics | Operations | 2026-07 | PO-2187 | 7,450.00 |
| 12 Jul 2026 | Orbit Analytics | Software | Marketing | 2026-07 | No PO | 4,200.00 |
| 18 Jul 2026 | Acme Office Supply | Office supplies | Facilities | 2026-07 | PO-2210 | 860.40 |
| 02 Aug 2026 | Harbour Facilities | Facilities services | Facilities | 2026-08 | PO-2174 | 9,750.00 |
| 09 Aug 2026 | Kestrel Data | Software | Finance | 2026-08 | PO-2219 | 6,300.00 |
| Total | 46,560.40 |
Illustrative data, not real suppliers.
Works in Excel and Google Sheets. Headers in row 1, data from row 2, formulas copied down. Map is a two-column tab of raw name and clean name; Suppliers lists each clean supplier and its category.
| Col | Header | Entry or formula | What it does |
|---|---|---|---|
| A | Invoice date | Date | From the AP or card export |
| B | Supplier (raw) | Text, pasted as exported | Keep it untouched for audit |
| C | Supplier | =IFERROR(VLOOKUP(B2, | Clean name from the mapping table |
| D | Category | =IFERROR(VLOOKUP(C2, | One category per supplier by default |
| E | Department | Drop-down from your cost centre list | Who asked for the purchase |
| F | Month | =TEXT(A2, | Sorts correctly and feeds the month views |
| G | PO no. | Text | Blank means bought without a PO |
| H | PO status | =IF(G2='', | Flags off-process spend |
| I | Amount | Currency, net of tax | Converted to your reporting currency |
| J | Duplicate check | =IF(COUNTIFS(C:C, | Same supplier, amount and date twice |
A pivot table is fastest for exploring, and SUMIFS is better for a fixed report that refreshes when new rows land. The formulas assume the cube sits on a tab called Cube. Category totals below show a typical spread for a software-heavy business.
Illustrative 12-month spend of 700,000.00 by category.
Put the list of suppliers, categories or departments in column A and months across row 1.
| View | Formula |
|---|---|
| Spend by supplier | =SUMIFS(Cube!$I:$I, |
| Spend by category | =SUMIFS(Cube!$I:$I, |
| Category by month (matrix) | =SUMIFS(Cube!$I:$I, |
| Department by category (matrix) | =SUMIFS(Cube!$I:$I, |
| Share of total spend | =B2/SUM(B:B) |
| Suppliers in a category | =COUNTA(UNIQUE(FILTER(Cube!C:C, |
| Spend without a PO | =SUMIFS(Cube!I:I, |
Select the cube, then Insert, PivotTable (Excel) or Insert, Pivot table (Google Sheets).
| Pivot area | Field | Why |
|---|---|---|
| Rows | Category, then Supplier | Drill from category to the suppliers inside it |
| Columns | Month | Shows trends and one-off spikes |
| Values | Sum of Amount | The spend total for each cell |
| Filter | Department, PO status | Answer a budget owner's question in two clicks |
The UNIQUE and FILTER formula needs Excel 365 or Google Sheets.
In most companies a small group of suppliers takes most of the spend, while hundreds of small suppliers make up the tail. The tail rarely saves much on price, but cutting it reduces invoices, onboarding work and risk. Sort the supplier view descending before you add the cumulative column.
| Rank | Supplier | 12-month spend | Share | Cumulative | Segment |
|---|---|---|---|---|---|
| 1 | Brightline Software | 216,000.00 | 30.9% | 30.9% | Strategic |
| 2 | Harbour Facilities | 117,000.00 | 16.7% | 47.6% | Strategic |
| 3 | Northwind Logistics | 89,400.00 | 12.8% | 60.3% | Strategic |
| 4 | Kestrel Data | 75,600.00 | 10.8% | 71.1% | Review |
| 5 | Orbit Analytics | 50,400.00 | 7.2% | 78.3% | Review |
| 6 | Acme Office Supply | 10,325.00 | 1.5% | 79.8% | Review |
| 7-220 | 214 other suppliers | 141,275.00 | 20.2% | 100.0% | Tail |
| Total | 700,000.00 | 100.0% |
Illustrative data. Six suppliers take about 80% of spend; 214 share the rest.
Supplier names in A, spend in B, sorted largest first.
| Column | Formula |
|---|---|
| Rank | =RANK(B2, |
| Share of spend | =B2/SUM($B$2:$B$300) |
| Cumulative share | =SUM($B$2:B2)/SUM($B$2:$B$300) |
| Segment | =IF(E2<=0.6, |
| Tail suppliers | =COUNTIF(F2:F300, |
Look for six signals in the cube, each with a test you can run in a formula. Size every opportunity conservatively and only count savings once a contract or price change is signed. For a deeper walk-through, read the spend analysis guide.
| Signal | Test in the cube | Typical action |
|---|---|---|
| Several suppliers for one need | Suppliers in a category above 3 | Consolidate to one or two preferred suppliers |
| Overlapping software | Two tools with the same purpose in Software | Retire one and move users across |
| Large renewal coming | Top 10 supplier with a renewal inside 120 days | Benchmark price and renegotiate before notice |
| Spend without a PO | PO status is No PO | Route the supplier through intake and approvals; see maverick spend |
| Price creep | Same supplier, rising monthly spend, flat headcount | Ask for the usage report and right-size licences |
| Long tail | Segment is Tail | Move small buys to a catalogue or a preferred supplier |
One row per opportunity. Status moves from Idea to In progress to Signed.
| Opportunity | Supplier | Owner | Baseline | Target | Saving | Status |
|---|---|---|---|---|---|---|
| Renegotiate before renewal | Brightline Software | IT | 216,000.00 | 189,000.00 | 27,000.00 | In progress |
| Retire duplicate analytics tool | Orbit Analytics | Marketing | 50,400.00 | 0.00 | 50,400.00 | Signed |
| Consolidate courier spend | Northwind Logistics | Operations | 89,400.00 | 82,000.00 | 7,400.00 | Idea |
| Total | 355,800.00 | 271,000.00 | 84,800.00 |
Illustrative figures. Saving = Baseline minus Target.
| Tracker field | Formula |
|---|---|
| Saving | =D2-E2 |
| Saving % | =IF(D2=0, |
| Signed savings only | =SUMIFS(F:F, |
Keep the same six lines every month so people learn to read them quickly. Comment only on movements that need a decision. The formulas read from the cube and the savings tracker.
| Line | How it is worked out | What to look for |
|---|---|---|
| Total spend this month | =SUMIFS(Cube!I:I, | A jump against the 12-month average |
| Top category | Largest row in the category view | Changes in ranking since last month |
| Spend without a PO | No PO total divided by total spend | Should fall every quarter |
| New suppliers | Suppliers whose first invoice is this month | Each one should have gone through onboarding |
| Unclassified spend | Category is Unclassified | Keep it small so totals stay trustworthy |
| Signed savings to date | Signed rows in the savings tracker | Progress against the annual target |
Paste the month's export into Raw and check the duplicate column.
Clear the Unclassified list before you report anything.
Share the page with budget owners and the CFO within five working days of month-end.
Off-PO spend shows up after the fact. Spendflo routes every purchase through intake and approvals first.
See how it worksThe cleaned data set you can slice three ways at once: supplier, category and department, with time as a fourth cut.
Your fixed list of spend categories. Ten to twenty top-level categories is enough for most companies.
The many small suppliers that together make up the last slice of spend, usually bought without a contract.
Spend procurement can realistically influence, as opposed to taxes, payroll or rent. See addressable spend.
The share of spend that runs through an agreed supplier, contract and approval route.
Purchases made without a purchase order, so nobody approved the price before the invoice arrived.
| Right | Question for the cube | Where to look |
|---|---|---|
| Price | Are we paying the same supplier different prices? | Supplier view, unit price by month |
| Quality | Are we paying twice for rework or replacements? | Credit notes and repeat lines |
| Quantity | Are we buying more licences or stock than we use? | Software category against headcount |
| Place | Is each site buying from its own local supplier? | Department by supplier matrix |
| Time | Are urgent buys bypassing the PO process? | No PO lines and order dates |
Monthly totals hide duplicates, price changes and one-off purchases.
A lot of software and tail spend never touches AP, so leaving it out understates both.
Every supplier name you clean once stays clean in next quarter's refresh.
Changing categories mid-year makes month-on-month comparisons meaningless.
Ideas and quotes are not savings until the new price is in a contract or PO.
Tax inflates spend unevenly across regions, so work net of tax in one currency.
A 90-line taxonomy means half the lines get filed in the wrong place.
The top suppliers hold most of the money, so start negotiations there.
Spend drifts back within months unless the cube is refreshed and reported monthly.
Pull invoice lines from your accounting system plus card and expense exports into the Raw tab.
Sort raw names alphabetically and fill the mapping table. Start with the largest 50 suppliers.
Give each clean supplier one category and override the few that sell across categories.
Read the Pareto, run the six savings tests and log every idea in the tracker with an owner.
Lumen Retail exports 700,000.00 of 12-month spend across 220 suppliers. The cube shows Software at about 49% of spend, with Orbit Analytics and Kestrel Data both serving the marketing team. Lumen retires Orbit, saving 50,400.00, and renegotiates the Brightline renewal from 216,000.00 to 189,000.00. Total signed savings: 77,400.00. All figures are illustrative.
Every part on this page, in Excel and Google Sheets, with the examples filled in.
One cube and a supplier pivot answer most questions. Review twice a year, before budgets and before the largest renewal.
Add the mapping table, the Pareto and the savings tracker, and refresh monthly. Software usually becomes the biggest controllable category at this size.
Add Entity and Currency columns and convert at a fixed monthly rate. Compare the same supplier across entities to find price differences.
Best for large data sets and pivot tables.
Best for shared, live analysis.
Headers, mapping tab and formulas, no sample data.
Check what you pay for software against Spendflo's live pricing benchmarks before you renew.
See pricing benchmarksA good spend analysis template tells you who you pay, for what and which purchases skipped the process. The savings come from what happens next: renegotiated renewals, fewer suppliers and every new purchase approved before the invoice arrives.
Every purchase in one cube, by supplier, category, department and month.
Open the spend cube →2A Pareto shows the few suppliers worth negotiating and the tail worth cutting.
Open the Pareto →3Six tests and a tracker convert totals into signed savings.
Open the savings finder →Quick answers to what people ask most about the spend analysis template.
Export 12 months of invoice-level spend, clean the supplier names, give every supplier a category and total the result by supplier, category, department and month. Then rank suppliers and list savings opportunities with owners. Download the template above to get the cube, formulas and tracker already built.
For purchase spend, a spend cube with SUMIFS views and a supplier Pareto is the most useful starting point. For a single buying decision, use a cost-benefit or total cost of ownership model instead. You can download this spend analysis template free in Excel or Google Sheets.
They usually refer to the five rights of purchasing: the right price, quality, quantity, place and time. A spend analysis checks price directly and raises questions about the other four. Download the template to see a question for each right in the glossary tab.
A retailer with 700,000.00 of annual spend finds software is about half the total, with two tools doing the same job and a major renewal due soon. Retiring one tool and renegotiating the renewal saves 77,400.00 in this illustrative case. Download the template to run the same analysis on your data.
You can download one free on this page, in Excel or Google Sheets. It includes the spend cube, SUMIFS and pivot views, a supplier Pareto, a savings tracker and a monthly report.
Procurement
Purchase orders
Contracts
Vendor management
Sourcing and RFx
Budgets and business cases
Accounts payable
Purchasing
Software buying
Supply chain
Spendflo runs intake, approvals and contracts before money is committed, so every purchase arrives tagged and approved. Reporting is coming soon.
Enter your work email and we'll unlock every format.
Didn't start, or need another format? Pick one below.
Google Sheets: upload the file to Google Drive, then open it with Google Sheets.
