Free guide: spend analysis
Spend analysis in Excel: the structure, the formulas and the traps
A spend analysis is only as good as the table under it. This guide sets out the structure the Starter Kit uses, the formulas with their expected values on the practice dataset, and the checks to run before a number leaves your desk.
Start with one clean table
Import the extract with Power Query rather than pasting it, so the clean-up steps repeat on next month's file. Load it as an Excel table called Spend, one row per invoice. The kit's practice dataset uses these columns:
| Column | What it holds |
|---|---|
| InvoiceDate, InvoiceNumber | When, and a unique key for duplicate checks |
| Supplier | Normalised supplier name |
| CategoryL1, CategoryL2, Description | Two-level category and the free-text line |
| Quantity, UnitPrice_NGN, Amount_NGN, Currency | Value fields, with currency stated |
| PONumber, POCompliant | The PO reference and a Y/N flag |
| ContractedSupplier | Y/N: does a contract cover this supplier |
| Department, Location, PaymentTermsDays | Who bought, where, and on what terms |
The core formulas, with expected values
Each formula below runs on the fictional sample (150 invoices, 20 invented suppliers). The expected value lets you test your build before you trust it.
| KPI | Formula | Expected |
|---|---|---|
| Total spend | =SUM(Spend[Amount_NGN]) | 552,660,500 |
| PO compliance, spend | =SUMIF(Spend[POCompliant],"Y",Spend[Amount_NGN])/SUM(Spend[Amount_NGN]) | 82.03% |
| PO compliance, count | =COUNTIF(Spend[POCompliant],"Y")/COUNTA(Spend[InvoiceNumber]) | 70.00% |
| Contracted share | =SUMIF(Spend[ContractedSupplier],"Y",Spend[Amount_NGN])/SUM(Spend[Amount_NGN]) | 62.06% |
| Off-contract share | =SUMIF(Spend[ContractedSupplier],"N",Spend[Amount_NGN])/SUM(Spend[Amount_NGN]) | 37.94% |
| Weighted payment terms | =SUMPRODUCT(Spend[PaymentTermsDays],Spend[Amount_NGN])/SUM(Spend[Amount_NGN]) | 28.99 days |
| Median invoice | =MEDIAN(Spend[Amount_NGN]) | 312,500 |
| Small invoices (count) | =COUNTIF(Spend[Amount_NGN],"<1000000") | 96 |
Some regional settings use semicolons instead of commas as separators. In a production workbook, wrap ratios in IF(denominator=0,NA(),...) so an empty filter shows an error rather than a fake 0%.
Three views that answer real questions
- Where the money goes. Category share: Professional Services is 44.09% of spend on the sample. Share shows where to look, not what you can save.
- Who you depend on. The top 10 suppliers hold 87.99%. Concentration is a prompt to check alternatives and contract terms, not proof of risk on its own.
- How controlled it is. The PO by contract matrix, covered in the controls guide.
Five traps that make a spend cube wrong
- Using the mean as "typical". The mean invoice is NGN 3,684,403; the median is NGN 312,500. A few large invoices drag the mean up.
- Off-contract as 1 minus contracted. Blank flags become off-contract by accident. Count the N flag directly.
- Not reconciling. Tie the total to the ledger extract before you present anything. A figure that does not reconcile is not shown.
- Duplicate invoices. Add
=COUNTIF(Spend[InvoiceNumber],[@InvoiceNumber])as a helper column and review anything above 1. - Flags that disagree with the data. A Y in POCompliant with a blank PONumber is a data problem, not a compliant purchase.
Real extracts are messier. Supplier names need normalising, categories need a governed mapping, and credits must keep their sign. The kit's build guide covers each step, with an acceptance table so you know when the build is right.
Go further
The Starter Kit teaches this method end to end on the practice dataset: a 54-page guide, a click-by-click Excel and Power BI build guide, 29 exercises and 3 case studies with worked answers, 28 AI prompts, a self-checking workbook and a 29-KPI dictionary.
CA$15 once (about US$11), plus local sales tax where it applies, shown at checkout. Card payment by Stripe. Instant download. All sales final.
Free one-page KPI cheat sheet: download the PDF, no email needed.