Supplier True Cost Tracker Spreadsheet
This spreadsheet prices what each supplier’s lateness and mistakes cost you, and adds that to what they charge.
A purchase ledger records the invoice and nothing else. It does not record the hours a line stood still, the express freight you paid to catch up, or the goods you gave a customer to keep them quiet — those go to labour, to carriage and to sales, never to the supplier who caused them. This file puts all three back where they belong.
What is in it
- Deliveries — one row per delivery: the supplier, the date you ordered it, the date they promised it, the date it arrived, what it was worth, how it arrived and the three things it cost you.
- By Supplier — the on-time record next to what each one really costs, measured against the day they promised rather than the day you ordered.
- How Late — the cost banded by days late, because it does not rise in a straight line, plus one number for what a single day of lateness is worth to you.
- What Went Wrong — short, wrong, damaged, out of temperature.
- The Real Price — what each supplier charges on paper against what they charge once lateness and faults have been added in.
- Setup and Overview — one rate, two lists you name yourself, and the whole year on one screen.
Who it is for
Anywhere a late delivery stops somebody working: a workshop, a kitchen, a site, a garage, a printer, a salon, a shop, a factory. You do not need a production line — use whatever an hour of waiting costs you, whether that is an engineer standing about, a van held back or a fitter sent home.
The worked example
Across 520 deliveries and $1,907,064 of spend, lateness and faults cost $61,106 in lost hours, express freight and credits — 3.2% on top of everything spent.
The cheapest supplier on paper charged 93 where the cheapest is 100, and really charged 105.9. Their deliveries arrived on the promised day 47.6% of the time, against 95.8% for the supplier they replaced, and a single day of lateness was worth $136.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
A look inside the file
Questions people actually ask
How do I work out what a supplier's lateness actually costs me?
You price the three things a purchase ledger never records: the hours the line stood still, the express freight you paid to catch up, and the credits you gave customers to keep them quiet. In the worked year those came to $61,106 across 520 deliveries, which is 3.2% on top of everything spent, and one day of lateness was worth $136. The file then adds that to each supplier's price, so the cheapest supplier on paper at 93 turns out to really charge 105.9.
We do not have a production line. Is this only for manufacturers?
No, it works anywhere a late delivery stops somebody working: a workshop, a kitchen, a site, a salon, a garage, a printer, a shop. Use whatever an hour of waiting costs you, whether that is an engineer standing about, a van held back or a fitter sent home. The arithmetic does not care what the hour was for.
We do not know what our suppliers charge against each other.
A rough comparison of the same basket is enough: you set your cheapest supplier at 100 and score the others against it. If two suppliers do not sell the same things, put them both at 100 and read the knock-on column instead. Lateness is measured against the day they promised it rather than the day you ordered, so each delivery needs three dates.
Which spreadsheet programs does it run in?
Excel, Google Sheets and LibreOffice. It is built as .xlsx for Excel 2016 or newer on Windows or Mac, and loads into Google Sheets through File then Import then Upload. No macros, no add-ons, no account to create, nothing to install and no subscription.
Does it raise or chase orders, and does it hold personal data?
No, it is not a purchasing system and it talks to nothing; it measures what happened after the order was placed. It is not a stock system or an EDI connection either, and it does not know your lead times, your contracts or your service-level agreements. It records suppliers, deliveries and amounts. It will not make anybody turn up on time; it tells you what not turning up cost you, per supplier, in points of price.
Need it built around your business?
This file assumes goods arriving against a promised date, and an hour of waiting you can put a rate against. If your supply works differently — a kitchen where a late delivery means a dish comes off the menu rather than a line stopping, a site where the sequence matters more than the day, an importer counting weeks at a port rather than days on a van — I can build the same arithmetic around how your deliveries actually land.
All other templates are listed on the Excel & Google Sheets Templates page.