The Excel sheet that checks an invoice, and where it stops working
· Šis puslapis pateikiamas anglų kalba.
Put the price list on its own tab, key it on the supplier's item code rather than the description, look the code up with an exact match, round both prices to the cent, and allow one cent per unit. It stops at the second price list.
Price Double Check was built after that lookup had been written by hand too often: an invoice reaches it by email, along with the price list it should have been billed against, and the answer names the lines where the billed price and the listed price disagree. The recipe above is the whole of a spreadsheet that reaches the same answer for one supplier, and it is worth knowing exactly which supplier that is. What follows is a longer account of its limits, because the formula is the part nobody gets wrong.
The five settings that decide whether it tells the truth
Everything in the table below is a decision rather than a formula, and each row was bought by getting it wrong on real documents: one supply chain, thirty files, and every invoice line in them compared against the price lists that buyer had actually been sent.
| Setting | What to set it to | What happens when it is wrong |
|---|---|---|
| Match type | Exact. The fourth argument of a lookup defaults to an approximate match when it is omitted | A neighbouring item code returns a confident wrong price and no error at all |
| Join key | The supplier's item code, trimmed and forced to text | 335 of the 442 lines read carry a description that does not match its own price-list row word for word |
| Rounding on input | Round list prices as they are read, then both sides to the cent, half-up | One pair of files produced 43 differences that were not there, because a stored 1.176 reads back as 1.1760000000000002 |
| Tolerance | One cent per unit, and no percentage | Truncation instead of half-up sent three false alarms per invoice on a single item |
| Completeness gate | The extracted lines must sum to the printed total before any difference is read | A quantity and a price printed as 1 15,00 read as 115.00, and nothing says so |
The gate in the last row is the one most sheets do without, and it is the only check that catches a misreading rather than a mispricing. It is also what a supplier accepts as an argument: the lines add up to the total you printed, and here is the one that differs.
Building it, in the order that works
- Put the price list on a tab of its own, one row per item code. If the list carries more than one price column, take the column addressed to you and delete the others from your copy. A manufacturer's sheet often holds a recommended retail price, a retailer price and a distributor price, and the ratio between two of them took at least six distinct values across 177 rows of one real list, so no single discount can be derived from the others.
- Normalise the code before anything else. Trim it, force it to text, and strip the undocumented suffixes: 5116_1, 5126_1 and 5099_1 are 5116, 5126 and 5099, and the price confirms it. One cell in a real list held a tab character followed by four digits, and a lookup against it never matched anything.
- Round the list price as you read it. Four decimals in the list, two on the invoice, and a stored value that is a hair off the number you can see.
- Write the lookup with an exact match. An approximate match on a sorted column of item codes will not error; it will return the price of the code below the one you asked for.
- Compare after rounding both sides to the cent, half-up, and mark a difference only when it exceeds one cent per unit.
- Add the completeness gate as a cell of its own: the sum of your extracted line amounts against the invoice's printed total. Read nothing until those two agree.
- Leave a column for lines you will not judge. Freight has no reference price anywhere. An item that appears in no price list is an absence of basis rather than an overcharge, and in one real set six item codes were in that position permanently.
That is a morning's work and it holds. The method it encodes is the same one in checking an invoice against a price list by hand, and for a single supplier with a stable file the sheet is a reasonable place for it to live.
The second price list is where it stops
One supplier, one list, one layout: the sheet is fine. Each of the following arrived in a single real set of thirty documents, and each of them needs a rule rather than a formula.
- Two lists and no valid-from date on either. Price lists carry no version number and no period of validity, so the sheet has no way of knowing which one governed a given delivery. Which list applies is a question about the date the goods moved, and a lookup has no date in it.
- A code that appears twice. Two codes in one list occupied two rows each at the same price but different packaging and a different number of units to the case. First-match-wins is safe while the prices agree and silently wrong the moment they diverge.
- Lines that print no code at all. Three files in the set were affected, one of them only in its first thirty-two lines, and 101 lines were keyed by building a description-to-code crosswalk out of the supplier's own invoices from further up the chain and out of the price list itself. A lookup cannot construct that, and the crosswalk has to refuse the one description that matched two codes rather than pick the first.
- A header that is not in row one. In the real list the column titles sit in row 7 and the data runs from row 8 to row 255, of which 177 rows carry a code. Any sheet built on a fixed range breaks when the supplier adds a line above the table.
- A file that is two documents. One workbook in the set was an order sheet and, at the same time, a newer price list than any file with the word price in its name. Neither the filename nor the tab name can be trusted to say what a workbook is.
- A format nothing reads. An Apple Numbers file with no spreadsheet export beside it, or a contract scanned without a text layer, is where the sheet stops being the problem and the document starts being it.
What the sheet costs to keep
A lookup sheet is a small program, and it is maintained by whoever built it. A review of field audits of operational spreadsheets found errors in the large majority of the workbooks examined, with the share of erroneous formula cells running from under one per cent to about seven. Those were spreadsheets in daily use, built by people who knew the subject.
The failure mode that matters here is quiet. A sheet that breaks with an error is a sheet somebody fixes on Monday. A sheet that returns the neighbouring code's price, or compares against last year's list because the tab was never replaced, keeps producing clean green cells and is believed. In the acceptance run behind this service, 382 lines were compared and 41 of them carried a genuine price difference, one at 17.75% above the list in force, on a delivery where that single line took 91% of the margin.
Trumpai
- When a lookup sheet is the right answer
- One supplier, one price column, a file whose layout has not moved in a year, and somebody in the building who enjoys this kind of work. Under those conditions the sheet is honest and costs nothing to run. It stops being the right answer at the second supplier with a different layout.
- What the sheet cannot see at all
- Anything that is only visible across documents. A pack size that shrank at the same price does not appear on any invoice, because invoices do not print packaging, and a price that quietly became the new normal looks correct on every single invoice that carries it.
Forward one invoice and the price list it should have been billed against, and the comparison comes back by email with the lines that differ and what each one costs. The price and a worked example are on the home page.