Vendor statement reconciliation template
By the Analistable team · Updated · 2 min read
The workbook has a Statement sheet for the supplier's lines, a Ledger sheet for your payables lines and a Summary. Paste both, and formulas match by invoice or credit note number, show amount differences, flag documents missing on either side and prove that those items explain the whole balance difference.
Part of our guide: How to reconcile data in Excel
What the template does
A supplier says you owe one figure; your payables ledger says another. The gap is made up of invoices you have not booked, credit notes you have not received, documents booked at a different amount and items the supplier has already cleared. Matching the two lists by document number exposes each one. The approach is the same as any two-sided match in the reconciliation guide; duplicate supplier bills are covered in find vendors billed twice.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Statement | Date, Doc no, Amount (from the supplier) | Our amount, Difference, Status |
| Ledger | Doc no, Date, Amount (your open items) | Status: On / Not on statement |
| Summary | Nothing | Both balances and the explained difference |
How to fill it in
- Type or paste the supplier statement's open items into Statement: date, the supplier's document number and amount. Credit notes are negative.
- Export your open payables for that supplier and paste into Ledger with document number first.
- Make sure document numbers use the same format on both sides; strip prefixes your system adds.
- Fill formulas down, then work through every line that is not Agreed.
How the formulas work
Each statement line pulls your booked amount for the same document and classifies the result:
=SUMIFS(Ledger!$C$2:$C$100,Ledger!$A$2:$A$100,B2)
=IF(COUNTIFS(Ledger!$A$2:$A$100,B2)=0,"Missing in ledger",IF(E2=0,"Agreed","Amount differs"))The Summary adds up the differences by status and subtracts ledger items the supplier does not show. The unexplained figure should be zero:
=ROUND(B4-B8,2)Worked example
The statement shows 4,945.00 and the ledger 4,805.00. The 140.00 gap breaks down into four items, and together they explain it exactly:
| Line | Value |
|---|---|
| Statement balance | 4945 |
| Ledger balance | 4805 |
| Difference | 140 |
| Missing in ledger (INV-505, CN-12) | 490 |
| Amount differs (INV-503) | -50 |
| In ledger, not on statement (INV-499) | -300 |
| Unexplained | 0 |
Limits and when to move on
- Payments are not modelled separately. If the statement shows payments, list them as negative lines with the payment reference on both sides.
- One supplier per copy. For a supplier-wide review, export all open items with a supplier column and match on two columns at once.
- When document numbers are formatted differently, fix mismatched IDs before joining, or load both files into Analistable and ask it to match them.
Frequently asked questions
- Should I use the supplier's or my own document number?
- The supplier's. Their invoice number is printed on both the statement and the invoice you booked; your internal voucher number appears only on your side.
- What does Amount differs usually mean?
- A typo, a VAT difference, a partial credit or a price dispute. Pull the invoice copy and correct whichever side is wrong.
- Does it work in Google Sheets?
- Yes. Open the file in Google Sheets; it uses SUMIFS, COUNTIFS, IF, SUM and ROUND only.