Supplier invoice register in Excel: PO, receipt, and invoice
A supplier invoice register is one row per invoice you received. You type the invoice number, the vendor, the invoice amount, the currency, the due date, and a status. On the same row you type the purchase order amount and the goods receipt amount. The PO flag compares the order amount with the invoice. The receipt flag compares the receipt amount with the invoice. The match is matched only when both flags match. The first screen is a sheet named Invoices. Row 1 is the header. Row 2 is one invoice you can check by hand: number, vendor, 1200, EUR, a due date, status open, a PO number, PO amount 1200, receipt amount 1200. Both flags say match and the match column says matched. Nothing else is on that screen. There is no aging bucket, no customer bill, and no software list. This is not the accountant page for invoices you issue to customers. It is not a payment schedule, which says when cash leaves. It is not collections, which chase accounts receivable. It is not the aging how-to, which sorts open payables into 30, 60, and 90 day buckets. Here you only ask whether the three amounts agree and whether the invoice is still open. A blank PO amount or a blank receipt amount stays incomplete. Do not type a number to make the flag look finished. Paid and disputed rows stay on the sheet. The open-break count ignores them. Elyx can write the three flags. You still type the invoice. The trial lasts 7 days, with no card.
The Problem
The register fails when it lives in an inbox. A PDF says a vendor billed 1200. Nobody can see, on one row, the order amount and the receipt amount next to it. A sentence in an email is not a three-way match. A second failure is mixing this list with customer invoicing. An invoice you send has a customer and lines you billed. An invoice you received has a vendor and amounts you are checking. If you paste customer bills here, the morning count no longer means open supplier invoices that do not match. A third failure is turning the sheet into a payment calendar or an aging report. A due date belongs on the row so you can see it. The flags do not read the due date. Buckets of 30, 60, and 90 days are a different page. Deleting a paid invoice removes the only record of the match you already checked.
Benefits
Each supplier invoice is one row with the amount, the due date, and the status you typed.
The PO flag compares the order amount with the invoice amount.
The receipt flag compares the receipt amount with the invoice amount.
Matched means both flags match. Incomplete means an amount is missing. Break means the amounts differ.
Paid and disputed rows stay on the sheet and drop out of the open-break count.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Invoices. In row 1 type: Invoice, Vendor, Amount, Currency, Due, Status, PO, PoAmount, ReceiptAmt, PoFlag, RcptFlag, Match. Invoice is the number on the bill you received. Vendor is the supplier. Amount is the invoice total you are checking. Currency is a code you type, such as EUR. Due is a real date. Status is a word from a list. PO is the order number. PoAmount and ReceiptAmt are amounts you typed from the order and the receipt. PoFlag, RcptFlag, and Match are formulas. Freeze row 1. Do not merge cells. This header row is the first screen.
One row per invoice number. A credit note is its own row with its own number. Do not net it into the invoice amount.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open, scheduled, paid, and disputed. Open means you have not paid and you have not set the invoice aside. Scheduled means a payment date exists somewhere else, on a payment schedule, and this invoice is still unpaid. Paid means the cash has left. Disputed means you are not paying until the amount is corrected. Only open rows stay in the open-break count. People must pick a word. A synonym such as done or hold will not match.
Scheduled is not a date. The due date stays in Due. The payment calendar is a different sheet.
Enter one invoice you can check by hand
On row 2 type invoice INV-1042, vendor North Mill, amount 1200, currency EUR, a due date, status open, PO PO-88, PoAmount 1200, ReceiptAmt 1200. You will predict both flags as match and Match as matched. On row 3 use the same invoice amount 1200, PoAmount 1200, and ReceiptAmt 1180. You will predict PoFlag match, RcptFlag mismatch, and Match break. On row 4 leave PoAmount blank. You will predict PoFlag missing and Match incomplete. Use amounts you can subtract before you trust the formulas.
If the receipt has not arrived, leave ReceiptAmt blank. Do not copy the invoice amount into it.
Write the PO flag
PoFlag is blank when the invoice number is blank. It is missing when the PO amount is blank. It is match when the PO amount and the invoice amount differ by less than half a cent. Otherwise it is mismatch. With Invoice in A2, Amount in C2, and PoAmount in H2, use =IF(A2="","",IF(H2="","missing",IF(ABS(H2-C2)<0.005,"match","mismatch"))). The half cent ignores a rounding dust difference. It does not forgive 1180 against 1200.
=IF(A2="","",IF(H2="","missing",IF(ABS(H2-C2)<0.005,"match","mismatch")))Currency is not in this formula. If two currencies share a row, the amounts are not comparable. Do not mix them.
Write the receipt flag and the match
RcptFlag uses the receipt amount in I2 the same way: =IF(A2="","",IF(I2="","missing",IF(ABS(I2-C2)<0.005,"match","mismatch"))). Match is blank when the invoice number is blank. It is matched when both flags are match. It is incomplete when either flag is missing. Otherwise it is break. Use =IF(A2="","",IF(AND(J2="match",K2="match"),"matched",IF(OR(J2="missing",K2="missing"),"incomplete","break"))). Break means both amounts were typed and at least one does not equal the invoice.
=IF(A2="","",IF(AND(J2="match",K2="match"),"matched",IF(OR(J2="missing",K2="missing"),"incomplete","break")))A matched row can still be open. Match is about the three amounts. Status is about payment.
Count the open rows that break
On a sheet named Morning, the open breaks are =COUNTIFS(Invoices!F:F,"open",Invoices!L:L,"break"). Open rows that are incomplete are =COUNTIFS(Invoices!F:F,"open",Invoices!L:L,"incomplete"). Paid and disputed rows are out of those counts because the status test fails. A blank match is not a break. The header says Match, not break. Write the rule next to the count. Sort the invoice sheet by Due, oldest first, when you want the nearest dates at the top. The count does not require that sort. This count is not an aging bucket.
=COUNTIFS(Invoices!F:F,"open",Invoices!L:L,"break")Do not count visible rows by hand after a filter and type over the formula.
Read a break as a row to open, not as a row to delete
If Match is break and status is open, the amounts disagree and the invoice is still unpaid. Open the order and the receipt. Fix the amount that is wrong, or set status to disputed and leave the row. If Match is incomplete, type the missing amount when you have it. Do not delete the row because the receipt is late. A paid row with a break stays visible and leaves the open-break count. If a paid row still shows a number you typed over the formula, the flag was overwritten.
Filter Match to break for the exceptions. Filter Status to paid when you need history.
Check three rows before you share the count
Amount 1200, PoAmount 1200, ReceiptAmt 1200, status open: both flags match, Match matched, and the open-break count ignores the row. Amount 1200, PoAmount 1200, ReceiptAmt 1180, status open: Match break, and the open-break count includes the row. The same 1180 receipt with status paid: Match is still break, and the open-break count ignores the row. A blank PoAmount: Match incomplete, not break. If a check fails, fix the status word or the amounts before you send the count. The count is only this sheet.
Write the three checks on Morning if more than one person updates invoices.
Template Features
PO flag against the invoice
Match, mismatch, or missing. Blank when the invoice number is blank.
=IF(A2="","",IF(H2="","missing",IF(ABS(H2-C2)<0.005,"match","mismatch")))Receipt flag against the invoice
The same test on the receipt amount.
=IF(A2="","",IF(I2="","missing",IF(ABS(I2-C2)<0.005,"match","mismatch")))Three-way match
Matched only when both flags match. Incomplete if either amount is missing. Break otherwise.
=IF(A2="","",IF(AND(J2="match",K2="match"),"matched",IF(OR(J2="missing",K2="missing"),"incomplete","break")))Open breaks
COUNTIFS reads status open and match break.
=COUNTIFS(Invoices!F:F,"open",Invoices!L:L,"break")History stays on the sheet
Paid and disputed rows remain. They leave the open-break count because status is not open.
Concrete Examples
Order, receipt, and invoice agree
You want to know if a 1200 invoice matches the order and the receipt you typed.
Amount 1200. PoAmount 1200. ReceiptAmt 1200. Status open.
Result: PoFlag is match. RcptFlag is match. Match is matched. The open-break count ignores the row.
The receipt is short
The order was 1200 and the invoice is 1200, but the receipt you typed is 1180. You do not want this called a match.
Amount 1200. PoAmount 1200. ReceiptAmt 1180. Status open.
Result: PoFlag is match. RcptFlag is mismatch. Match is break. The open-break count includes the row.
The receipt has not been typed
The goods receipt amount is unknown. You want the row visible and not counted as a break.
ReceiptAmt blank. PoAmount 1200. Amount 1200. Status open.
Result: RcptFlag is missing. Match is incomplete. The open-break count ignores the row. The incomplete count includes it.
Pro Tips
Half a cent is dust, not a tolerance you negotiate
The 0.005 test only absorbs a rounding difference. If your vendor bills in whole cents, 1200.00 against 1199.99 can match. 1180 against 1200 is a break. Do not widen the test to hide a short receipt.
Due date is not the match
Due sits on the row so you can sort. The flags do not read it. Aging buckets are the how-to linked below. A payment schedule is where you put the date cash leaves.
Customer invoices do not belong here
Bills you issue are the accountant invoicing list. Collections on those bills are the credit controller list. Paste neither into Vendor.
Text amounts do not subtract
If Amount was pasted as text, ABS will not compare it. Re-enter the number. Then read the flags again.