Three-Way Match

Accounts Payable
Finance
Reconciliation
Check vendor invoices against purchase orders and goods receipts in Excel, route only the exceptions to a review queue, and record Approve, Reject, or Hold decisions in the workbook.

Stop paying invoices that do not agree with what was ordered and what arrived. Purchase orders, goods receipts, and invoices stay in Excel; Python checks each invoice line, leaves clean invoices out of the way, and puts only the exceptions in a queue with a suggested action. Reviewers record Approve, Reject, or Hold decisions in a table that stays in the workbook.

Result preview

With the sample data, 43 invoices are checked. 32 match cleanly, 3 are within tolerance, and 8 are exceptions holding $13,920.00: a price above the PO, two bills before the goods receipt, a short-shipped delivery, a partial delivery billed in full, two invoices with no PO, and a duplicate invoice number.

What this template does

  • Reads purchase order lines, goods receipts, and vendor invoices from three worksheets.
  • Applies a visible price tolerance (percent with a dollar floor) and quantity tolerance from the Dashboard.
  • Classifies each invoice as clean, within tolerance, or one exception type, in a fixed order of checks.
  • Publishes a summary, the exception queue with suggested actions, and exposure by vendor.
  • Stores reviewer decisions and notes in a workbook table so they survive closing and reopening the file.

Why Python

The checks depend on several sources at once: a receipt must cover the PO line, the invoice date must follow the receipt date, a duplicate must be found across the whole invoice list, and a tolerance must be applied per line. Python expresses those rules in one readable place, and the decision log gives the queue a durable record in its own worksheet table, separate from the source data.

Operating workflow

Author: a finance-systems or accounts-payable analyst maintains the matching rules, the suggested actions, and the workbook contract.

Workbook user: an accounts-payable clerk pastes the PO, receipt, and invoice exports into their tables, adjusts tolerances when policy allows, works the queue, and records each decision with a note.

Download the Excel template

Inputs and assumptions

Purchase Orders!A5:F45 holds 40 PO lines. Receipts!A5:E44 holds 39 receipts, one for each PO line that has been received. Invoices!A5:H48 holds 43 invoices from six vendors. Dashboard!H5:I8 holds the price tolerance (2%), the price tolerance floor ($1.00), and the quantity tolerance (1 unit).

Each invoice line matches to a PO line by PO number and line. Receipts for the same PO line are summed.

Notebook implementation

The notebook binds the three source tables and the settings once:

inputs = bf.inputs(
    orders=bf.ref("Purchase Orders!A5:F45", headers=True),
    receipts=bf.ref("Receipts!A5:E44", headers=True),
    invoices=bf.ref("Invoices!A5:H48", headers=True),
    settings=bf.ref("Dashboard!H5:I8", headers=True),
)

Reviewer decisions live in a bf.table named decisions, shown in the pane. The suggested action for each exception type sits in the marked # --- Edit these rules --- section.

How the calculation/model works

Checks run in this order, and the first failing check sets the exception type:

  1. Duplicate invoice: a later row repeats an invoice number already seen.
  2. No PO: the invoice’s PO line is not in the purchase orders.
  3. Billed before receipt: no goods receipt is recorded for the PO line, or the invoice date is earlier than the first receipt.
  4. Short-shipped or Partial delivery billed in full: the invoiced quantity is more than the received quantity plus the quantity tolerance. It is a partial delivery billed in full when the invoice covers the ordered quantity less the tolerance.
  5. Price above PO: the unit price is higher than the PO price by more than the larger of the percent tolerance and the dollar floor.

Invoices with no exception are either matched cleanly (no quantity or price variance) or within tolerance (a variance that the settings allow). Dollars held are the amounts of exceptions with an Open or Hold decision.

Architecture

  • Managed table backing sheet: bf.table("decisions", ...) binds to an Excel table bf_decisions on sheet decisions. Reviewer decisions (Approve, Reject, Hold) recorded in the task pane mutate this backing sheet directly, persisting decisions across work sessions and decoupling review status from raw transaction records.
  • Fixed spill boundaries: Published outputs target defined worksheet blocks: Dashboard!A5:B12 (summary), Dashboard!A14:I40 (exceptions with autoHeight), and By Vendor!A5:C11 (by_vendor). Expanding PO line capacity beyond row 45 or adding vendor categories requires updating both the bf.ref range references in notebook.py and the workbook layout definitions in workbook.py.

Validation / expected results

The baseline checks 43 invoices: 32 clean, 3 within tolerance, and 8 exceptions holding $13,920.00. Scenarios raise the price tolerance to 3%, lower the quantity tolerance to zero, record an Approve decision, and correct the duplicate invoice number.

Limitations

This starter workflow matches exact PO and line keys only, with no fuzzy vendor or item matching. It does not handle multi-currency, tax or freight allocation, partial-quantity receipts across several receipt dates, posting to an ERP, or user authentication. Decisions are a log in the workbook, not an approval workflow with sign-off.

When to use this approach

Use it when accounts payable checks each invoice against a PO and a receipt, and most invoices pass under explainable tolerances while a smaller queue needs a person to decide.