Industry Use Cases

How to Reconcile a Vendor Statement in Excel With a Free Workbook

The supplier statement shows $2,820 due. Your AP ledger shows $1,750. Before you ask the supplier to resend everything, put both lists on the same cutoff and reconcile them by reference. In this sample, four items explain the entire $1,070 difference.

Try the vendor statement reconciliation workbook

Download the practice workbook (.xlsx)

This example covers one fictional vendor, one currency, and a $0 opening balance. Bills increase the amount payable. Credits and payments reduce it. Both lists include a few records posted after October 31 so you can see what the cutoff changes.

October 31 reconciliation in USD
ExceptionStatement minus ledgerSource and next check
Missing bill INV-1048+$800Vendor Statement!B3:D3. Ask for the invoice and check the receipt or approval before entry.
Unrecorded credit CM-033−$180Vendor Statement!B5:D5. Confirm the credit memo and where it should be applied.
Payment PMT-882 crossed the cutoff+$500AP Ledger!B5:D5 records October 30. Vendor Statement!B7:D7 records November 2.
Bill INV-1055 amount mismatch−$50Vendor Statement!D6 shows $400. AP Ledger!D4 shows $450.
Total explained+$1,070$2,820 statement balance less $1,750 ledger balance.

Try this question. At the Review Scope cutoff, reconcile Vendor Statement to AP Ledger by type and reference. Show both balances, every item that explains the difference, and the Excel cells behind each item. Distinguish payments posted after cutoff from missing entries.

Open Jolene.pro to run the example · Browse more Jolene.pro Workbook Studies

1. Match the report scope

Use the same supplier, currency, and cutoff on both sides. Confirm whether the statement starts with an opening balance. For QuickBooks Online, export the relevant vendor activity to Excel and make sure it includes the payments and credits you need. Open bills alone can omit the history that explains a difference.

2. Calculate each balance before matching records

Put each source in its own Excel table. Sum signed amounts with a posting date on or before the cutoff. If your tables contain multiple suppliers, add the supplier as a second filter. Keep later rows visible so you can check timing differences.

3. Match transactions by reference and type

Join by vendor, transaction type, and reference where the IDs are stable. Compare signed amounts for confirmed matches. Check for repeated keys before joining. When a payment covers several bills, reconcile the allocation or remittance detail before treating a repeated reference as an error.

4. Give the difference an owner

Separate missing entries, date differences, and amount differences. In this case the November 2 payment explains $500 at month end. Check its receipt date before making a correction. Keep the $800 bill, $180 credit, and $50 mismatch on the review list until you have their documents.

If the invoice is present but the delivered quantity is in doubt, use the PO, receipt and invoice quantity walkthrough. Its separate synthetic example traces 12 units billed ahead of receipt confirmation even though total billed and accepted values both equal $1,560.

Intuit’s guide to exporting reports from QuickBooks Online.

All records are synthetic. Confirm dates, balances, and supporting documents in your accounting system before posting or paying.

Follow Jolene.pro on LinkedIn for more spreadsheet guides.

Checking customer receipts as well? See how to match several customer payments to one bank deposit in Excel, including what to leave open when two slips have the same total.

3 comments

Leave a comment