What this Excel supplier price tracker does
The spreadsheet gives you one place to enter invoice lines and compare a current unit cost with an approved baseline. It calculates the dollar change, percentage change, estimated monthly impact, and a suggested review priority. The useful output is not a colourful dashboard; it is a short list of invoice-backed questions you can resolve.
Free supplier price change tracker
Seven sheets, sample rows, formulas, unit normalizer, product and supplier masters, and a Read Me workflow.
If you prefer a system-neutral starting point, download the starter CSV.
Set up the workbook correctly
1. Create stable product IDs
Supplier descriptions are unreliable keys. “Sunflower oil 5L” and “Cooking oil—sunflower 5 litre” may be the same item. Assign both to one internal Product ID and keep the original description as evidence. Excel Tables are useful because formulas and filters expand as rows are added; see Microsoft’s Excel Table guidance.
2. Choose one comparison unit
Compare each to each, kg to kg, and litre to litre. If one invoice is for 12 cases of four 5 L jugs, the comparable quantity is 240 L—not 12. A pack-size change can look like a price change when it is really a unit mismatch.
3. Decide the comparable cost basis
Use the same treatment for discounts, freight, fees, tax, and currency on every row. Keep the original invoice because tax and audit support requires adequate records; Canadian businesses can review the CRA’s documentary requirements.
Worked invoice example
| Field | Earlier invoice | Later invoice |
|---|---|---|
| Product | Sunflower oil 5 L | Sunflower cooking oil 5L |
| Comparable quantity | 60 L | 60 L |
| Net comparable cost | $270 | $288 |
| Unit cost | $4.50/L | $4.80/L |
Change % = ($4.80 − $4.50) ÷ $4.50 = 6.67%
At an expected 180 L per month, the estimated monthly impact is $0.30 × 180 = $54. That tells you more than 6.67% alone: the percentage identifies movement, while impact helps set the review order.
Excel formulas to use
Unit cost
=IFERROR(Net_Cost / Comparable_Quantity, "")
Previous unit cost
Use XLOOKUP when available: =XLOOKUP(Product_ID,Product_Master[Product ID],Product_Master[Baseline Unit Cost],""). Microsoft documents the inputs and match behaviour in its XLOOKUP reference.
Change and impact
Change % = (New Unit Cost − Old Unit Cost) ÷ Old Unit Cost
Estimated impact = (New Unit Cost − Old Unit Cost) × Expected Volume
Conditional formatting can make exceptions scannable, but it should never replace the evidence check. Follow Microsoft’s conditional-formatting guidance and keep the rule explainable.
Run a 15-minute weekly review
- Paste the latest invoice lines.
- Resolve unmapped Product IDs and unit mismatches first.
- Sort by estimated impact, then by change percentage.
- Check invoice, purchase order, contract, pack, freight, discount, tax, and currency.
- Assign an owner and status; update the baseline only after confirmation.
Frequently asked questions
What columns should a supplier price tracker include?
At minimum: invoice date and number, supplier, Product ID, supplier SKU and description, comparison unit, comparable quantity, comparable net cost, current and previous unit cost, change percentage, expected volume, impact, status, owner, and note.
Should I compare invoice totals or line items?
Compare normalized line-item unit costs. Invoice totals change with quantity, product mix, credits, freight, and tax, so they cannot tell you which product moved.
When should I stop using Excel?
Excel works for a manageable number of suppliers and invoices with a disciplined owner. Consider automation when entry is delayed, product mapping is inconsistent, several people edit the file, or important changes are found too late.