Compare an invoice with a price list
Última verificação · Esta página é disponibilizada em inglês.
To compare an invoice with a price list, join them by item code, not description, check that the lines add up to the printed total, then compare each billed price with the list in force on the invoice date, ignoring a cent of rounding. Price Double Check does this from a forwarded email and replies with the lines that differ.
The difficulty is not the arithmetic. It is that the two documents arrive as a PDF and a spreadsheet by email, that descriptions on the invoice do not match the words on the price list, and that the list which applies is not always the newest one.
The item code is the only safe key
In one real chain we validated against, a sports-nutrition manufacturer billing a Baltic distributor, 335 of 442 invoice lines carried a description that did not match the price-list wording line for line. The same product was "SUB9 SALTS ELEKTROLYTES - Duplo" on the invoice and "SUB-9 SALTS ELECTROLYTES DUPLO" on the list. Text matching fails on most lines, quietly.
The item code is the supplier's own key, printed on both documents by the same system. Three things still need care. Codes appear with undocumented suffixes (5116_1 next to 5116), so a suffix is stripped and the price confirms the match. One code can sit twice in a price list with two pack sizes; when the prices agree, the first row is safe, and when they do not, the line is a conflict to be reported rather than resolved. And some invoices print no code at all, in which case the description is mapped to a code from an earlier document of the same supplier that carried both. That last step only works when the whole chain of documents is available, which is why we ask for the invoices, the price lists and the purchase orders together rather than one file at a time.
The lines must add up before any line is compared
Before a single unit price is compared, the extracted lines are summed and the sum must equal the printed invoice total to the cent. This is the arithmetic gate. It exists because reading a PDF is not reliable by itself: a quantity of 1 next to a price of 15,00 can be read as 115.00, and no downstream comparison recovers from that. If the sum agrees with the total, the read was complete. If it does not, the invoice is held back and reported as unread, with the gap in euros, rather than compared partially.
In the validation chain, 11 of 11 invoices agreed with their printed total to the cent after parsing, and the 382 lines behind them were compared with zero misreads. The gate is also the sentence a purchaser can trust in the reply: the lines we read equal your total. The difference between a reading and an answer is the subject of a separate article.
Which price list applies to the invoice
A price list has an effective date, and the one in force on the invoice date is the one to compare against, not the newest one on file. The most expensive finding in the chain we validated was exactly that: a line billed at 13.23 against 11.235 on the list in force, 17.75 per cent over, 47.76 euros on one line, which ate 91 per cent of the margin of that delivery. The newer list that would have made the price legitimate had never been sent.
The price list a purchaser receives carries the price addressed to them. A manufacturer's sheet may hold a retail, a retailer and a distributor column; the distributor receives the whole sheet and strips their own purchase column before sending it on. With one price column, that column is the reference. With several, the column addressed to the recipient addressed to you is the one to use. Where the sheet makes that unambiguous, the reply names the column it read; where it does not, the reply says it could not tell which column applies, because a wrong column produces a page of invented differences.
A cent of rounding is not a finding
Price lists carry unit prices to four decimals (0.40425, 1.173375); invoices bill to two. Plus or minus one cent per unit is a tolerance, and the rounding rule is half-up. This is a modelling choice with consequences: with truncation instead of half-up, a list price of 2.205 becomes 2.20 against a billed 2.21, and that one line becomes a false overcharge on every invoice it appears on. A naive comparison sends three false alarms per invoice and is switched off within a month.
Five rules to compare an invoice with a price list
| Step | Rule | What it catches |
|---|---|---|
| Join | By item code within the supplier | Descriptions that differ in wording |
| Gate | Line sum equals printed total to the cent | A misread quantity or price |
| Reference | The list in force on the invoice date | A price from a list never sent |
| Tolerance | One cent per unit, half-up rounding | False alarms on four-decimal prices |
| Report | Show the difference, do not judge it | Freight, units of measure, pack size |
The last row matters as much as the first. Some differences have no reference price at all: freight has none, a quantity of 1 that means one box of 240 units is a form trap rather than a short delivery, and a 550 ml pack on the list against a 600 ml pack on the invoice at the same price is a question for the supplier, not a verdict. Those lines are listed with their amounts and left to the purchaser.
Other ways purchasers compare an invoice with a price list
Most purchasers at a distributor, an importer or a retailer do this one of four ways, and each is a reasonable choice for someone. The table states what each needs from you and what it does not do.
| Way | What you type in | What it misses |
|---|---|---|
| Spreadsheet with VLOOKUP | Invoice lines and the list, by hand | No arithmetic gate; rounding; the list in force |
| AP tool such as Lightyear | The agreed price list per supplier, kept current | A list you forgot to upload; plans from GBP 130 a month |
| ERP three-way match | Purchase order, goods receipt, invoice | Whether the PO price was the list price |
| Price Double Check | Nothing; forward the emails | Photos, delivery notes, approvals, payment |
The spreadsheet is what the Microsoft Excel community recommends when asked, and the formula is one line: subtract a VLOOKUP of the item reference against the list from the billed price, and multiply by quantity for the overcharge. The answer in that thread carries the same caveat as this page: descriptions vary, so the item reference must be the key. What the spreadsheet cannot do is check that the lines it received equal the invoice total, choose the list by date, or notice that 2.205 rounds to 2.21. It also needs the invoice retyped or converted, which is the step purchasers skip when the week is busy.
Lightyear, an accounts-payable tool, lists automatic price checking as a feature: it compares each line's product code with the supplier's agreed price list and flags overcharges. That is the right comparison. It presupposes that you load and maintain the price list for each supplier inside the tool, and its published plans start at GBP 130 a month for 125 document credits. For a company that already routes every bill through such a tool for approval and posting, it is the better choice, because the check sits where the payment is released.
An ERP with three-way matching checks the invoice against your own purchase order and goods receipt. It answers a different question, whether you were billed for what you ordered and received, and never asks whether the price keyed into the purchase order was the list price. A keying error passes through and matches perfectly. How that control works without an ERP is on the three-way match page; the purchase-order leg is on the purchase order page.
What the reply contains
When an invoice arrives and a price list for that supplier is on file, the reply comes back by email and states, in order:
- What was read: the supplier, the invoice number and date, the price list used and its effective date.
- The arithmetic gate: the sum of the lines and the printed total, and that they agree.
- The findings as a table: code, description, quantity, billed price, list price, the difference, and whether it is in your favour or against you.
- What could not be matched, with amounts: lines without a code, codes not on any list, freight.
- What to send to get more: a newer price list, the purchase order, the missing invoice of a chain.
The reply is immediate per invoice. Changes between two price lists, and a price that drifts across several invoices without a new list, arrive as a digest, because they are invisible on one document. A price that quietly becomes the new normal, or a pack that shrinks at the same price, is the subject of checking a supplier price increase.
Where to start
- Compare an invoice with a price list first, before any purchase order: the last invoice from your largest supplier and the list they sent, in PDF, Excel or CSV.
- Forward both, from the mailbox they arrived in, to your personal forwarding address. Purchase orders from Sent can follow later.
- Read the gate line first. If the lines equal the total, the findings below it are complete.
- Send the reply to the supplier as it is. It names codes, quantities and list prices, which is what their credit note needs.
How the same comparison plays out on a real chain of 30 documents, with the findings a purchaser had missed by hand, is told in the article on checking an invoice against a price list.
Em resumo
- The invoice descriptions do not match the price list wording.
- That is the normal case, not an exception: in one validated chain 335 of 442 lines differed in wording. The comparison joins by item code, so the descriptions never need to agree. Where an invoice prints no code, the description is mapped to a code from an earlier document of the same supplier.
- The price list is a PDF and the invoice is another PDF.
- Both are read as documents and the lines are extracted, then summed against the printed total before anything is compared. If the sum does not equal the total, the invoice is reported as unread with the gap, not compared. In the validation chain every invoice agreed to the cent.
- A one-cent difference on a line shows up on every invoice.
- A price list to four decimals billed at two decimals produces a cent of rounding on many lines, and that is a tolerance, not a finding. The rounding rule is half-up. Only differences beyond a cent per unit are reported.
- The supplier sent a new price list last month and the invoice uses the old prices.
- The list in force on the invoice date is the reference, so an invoice dated before the new list's effective date is compared with the old list. A line billed at the new price before that date shows as a difference against you. Price protection on orders placed earlier is a matter for the supplier, and the reply shows the amount.
- Freight and a substituted pack size appear as findings.
- They appear as lines that could not be matched, with amounts, and not as overcharges. Freight has no reference price on any list, and a 550 ml pack against a 600 ml pack at the same price is a question, not a verdict. The reply lists them so the purchaser can decide.
Forward one invoice and the price list to your forwarding address and the comparison comes back by email, with the gate line first and the differing lines under it.