How to use the three-way match checker
- Paste the purchase order lines as item, quantity and unit price.
- Paste the goods received as item and quantity.
- Paste the invoice lines as item, quantity and unit price, and set a quantity and a price allowance if your policy has one.
- Read the items that do not match and the reason for each, and check the amount billed but not received.
Formula
items are matched by name, ignoring case and extra spaces; repeated lines for one item are added
price differs when |invoice price - order price| is above the allowed percent of the order price
invoiced more than received when invoiced quantity - received quantity is above the allowed units
billed but not received = sum of (invoiced - received) x invoice price for items billed for more than arrived
Quantities may have up to four decimals, and unit prices up to four decimals. Money is held in whole cents when it is totaled.
Four items ordered, three received, four billed
The order has Bolt M8, Nut M8, Washer M8 and Cable tie. Goods received are 100 bolts, 90 nuts and 200 washers. The invoice bills 100 bolts, 100 nuts, 200 washers at $0.11 and a $15.00 pallet fee. Bolts match. Nuts are invoiced for 10 more than were received. Washers cost $0.11 against an order price of $0.10, so the invoice price is higher.
The cable ties are on the order but not on the invoice and nothing came, and the pallet fee is on the invoice but not on the order and nothing was received. That is 4 of 5 items that do not match. The amount billed but not received is 10 x $0.08 + $15.00 = $15.80. A 10% price allowance clears the washers, and a 10 unit allowance clears the nuts.
Why three papers
The order says what was agreed, the receipt says what arrived, and the invoice says what is being charged. Paying only when all three agree stops payment for goods that were never delivered or were priced differently. The tolerances let a small difference through when your own policy allows it.
Check the arithmetic on the invoice with the Invoice Arithmetic Checker, start an order with the Purchase Order Generator, make your own invoices with the Invoice Generator, and reconcile what you paid with the Bank Reconciliation Template.
Naming items consistently
The match depends on the item names. A bolt called Bolt M8 on one paper and M8 bolt on another looks like two items, one missing from each of the other papers. Use the same names, or the same part numbers, in all three boxes. Use the same unit too, so a box of ten is not compared with ten single items.
Assumptions and limits
- One delivery is compared with one order and one invoice.
- Item names are the same on all three papers, and quantities use the same unit.
- Allowances are your own policy and start at zero.
- Freight, tax, currency and partial deliveries on other dates are not modeled. Up to 1,000 lines in each box are read, and nothing is uploaded.
Frequently asked questions
What does Received more than ordered mean?
More arrived than the order asked for. It is listed even when the invoice agrees with the order, since an extra delivery may be a surprise you need to resolve.
Can I use item codes instead of names?
Yes. Any text before the quantity is used as the item name, so codes work as long as they are the same on all three papers.
Why is the price comparison skipped for some items?
It needs both an order price and an invoice price. If an item is missing from either paper, the result names the missing paper instead.
How are tolerances applied?
The quantity allowance is in units and applies to each comparison between quantities. The price allowance is a percent of the order price. A difference exactly equal to the allowance passes.