Reconciliation
Vendor statement reconciliation: a free Excel template and the seven steps to run it
A free Excel template that matches a vendor statement to your AP ledger, and seven steps to clear the differences before month-end.
By Grid98··7 min read
A vendor emails you a statement showing $4,710.50 outstanding. Your AP ledger says you owe them $5,650.00. One of you is wrong by $939.50. Until you find out which, you risk paying an invoice twice or missing one that never reached you.
You close that gap with a vendor statement reconciliation. You match each line on the vendor's statement to the entry in your ledger, list the differences and clear them one at a time. Below you will find an Excel template that does the matching and the seven steps to run it. The last two sections cover the volume at which a spreadsheet stops coping, and the steps you can hand to software.
The template
The workbook opens with the example above loaded, so you can see a finished reconciliation before you paste your own data. You paste the vendor's lines into the Statement sheet and your own into the Ledger sheet. The Summary sheet does the arithmetic.
Three design choices separate it from a plain two-column comparison:
- It matches on a cleaned document number. The match key drops capitals, spaces, dashes and the # sign, so INV-1041 on the statement meets inv 1041 in your ledger.
- It compares totals per document number. An invoice you keyed twice shows up as a difference of one full invoice. A row-by-row comparison marks both copies as matched and hides the duplicate from you.
- It proves the result. The Summary sheet splits the difference into three causes and shows an Unexplained figure. You have finished once that figure reads 0.00.
The Summary sheet for the example reads like this:
| Balance per vendor statement | 4,710.50 |
|---|---|
| Balance per AP ledger | 5,650.00 |
| Difference to explain | (939.50) |
| On the statement, missing from the ledger | 300.00 |
| On both, amounts differ | (2,220.00) |
| In the ledger, missing from the statement | 980.50 |
| Unexplained | 0.00 |
Behind those three lines sit five findings: an invoice and a credit memo missing from the ledger, a $90.00 keying error, an invoice entered twice, and a payment the vendor has yet to apply.
The errors a statement reconciliation catches
Your ledger holds the documents that reached you. The vendor's statement holds the documents they sent. Comparing the two surfaces four kinds of error:
- Invoices you never received. You learn about them from a late fee or a credit hold. They also understate your payables at month-end, and your auditors test for that in their search for unrecorded liabilities.
- Invoices you entered twice. A second copy arrives by post after the emailed one, someone keys it with a slight change to the number, and your system's duplicate check lets it through.
- Credits you have not used. The vendor issued a credit memo for a return or a pricing error and you kept paying invoices in full.
- Payments the vendor misapplied. You paid invoice 1042 and they posted the cash to another customer's account, so their collections team chases you for money you sent.
Seven steps to reconcile one vendor
- Pick the vendors. Reconcile your largest vendors by spend each month, and add any vendor who chased you for payment in the last quarter. Rotate the rest across the year.
- Ask for a statement dated your cut-off. Request one as of the last day of the month. An open-item statement lists each unpaid document and suits this work. A balance-forward statement gives you one opening figure, so ask the vendor for the detail behind it.
- Pull your side as of the same date. Run the vendor's open items from your AP ledger and add the payments you made during the month. QuickBooks calls the report Vendor Balance Detail. NetSuite, Sage Intacct and Dynamics 365 each have an aged payables report you can filter to one vendor.
- Paste both sides into the template. Enter invoices as positive amounts and payments or credits as negative amounts, on both sheets. Type the two closing balances into the Summary sheet. If a check on that sheet reads No, you left out a line.
- Classify each red row. The template flags a row for one of three reasons. The table below lists the cause you will find most often behind each one.
- Act, and write down what you did. Record the cause and the action in the Notes column as you go. An auditor, or the colleague who covers your leave, can then follow your work without asking you.
- Get a second person to sign. The reviewer reads the notes, confirms Unexplained reads 0.00 and adds their name to the Summary sheet. File the workbook with the statement PDF in your close folder.
| The template shows | Likely cause | Your next move |
|---|---|---|
| Invoice missing from the ledger | The vendor sent it to an old address, or it sits in an approver's inbox | Request a copy, route it for approval, and accrue it if it belongs to the month you are closing |
| Credit memo missing from the ledger | The vendor issued it and your team did not record it | Request the memo and record it before the next payment run |
| Payment missing from the statement | The payment is in transit, or the vendor posted it to the wrong account | Confirm it cleared your bank, then send the vendor the remittance detail |
| Invoice missing from the statement | You posted another vendor's invoice to this account | Check the vendor name on the invoice PDF and move the entry |
| Amounts differ by a small figure | A keying error, or tax and freight the PO left out | Compare your entry to the invoice PDF, then correct it or dispute it |
| Amounts differ by one full invoice | You entered the invoice twice | Void the second entry. If you paid both, ask the vendor for a refund or a credit |
The point at which Excel stops coping
The template handles one vendor well. The work around it grows with each vendor you add, and you will feel it in four places:
- You have to ask. Few vendors send statements unprompted. Someone on your team emails each vendor on the list, then keeps a second spreadsheet of who replied.
- Statements arrive as PDFs. Each vendor uses their own layout, and you retype or copy each line before matching can start.
- Prefixes and leading zeros defeat the match key. One vendor prints 1041, your clerk keyed INV-001041, and you edit document numbers by hand for each vendor like that.
- The workbook forgets. Last month's open items and your notes on them stay in last month's file, so you investigate the same disputed invoice again.
With a fixed headcount you respond by reconciling fewer vendors. The errors then sit with the vendors you skipped.
The steps software can take
Go through the seven steps and ask of each one whether it needs a person's judgment. Most of them follow a rule you could write down, and a rule you can write down is one software can run.
| Software takes | Your team keeps |
|---|---|
| Requesting statements on a schedule and chasing the vendors who stay silent | Choosing the vendors to cover |
| Reading statement PDFs and portal exports into lines | Disputing a price or a quantity with the vendor |
| Pulling ledger detail from your accounting system as of the cut-off | Approving an adjustment or a void |
| Matching, with each vendor's prefixes and zero-padding learned once | Signing the reconciliation |
| Carrying open items and notes forward from month to month |
Grid98 builds that workflow around the accounting system you run today, then monitors it and fixes it when a vendor changes their statement layout. Your team opens a list of breaks, each with the statement line and the ledger entry side by side. Show us how you reconcile vendor statements now, and we will send back a map of the steps software can take.
Common questions
How often should you reconcile vendor statements?
Each month for your largest vendors, before you close payables. Once a quarter suits vendors who send you a handful of invoices. Reconcile any vendor ahead of a large payment run or a year-end audit.
Is this the same as an AP subledger reconciliation?
No, and you need both. The subledger reconciliation ties your AP aging total to the payables control account in your general ledger, with your own records on both sides. A vendor statement reconciliation tests those records against an outside source, one vendor at a time.
Do nonprofits need to reconcile vendor statements?
Yes. Auditors test a nonprofit's payables for completeness the same way they test a company's. If you charge vendor costs to a grant, a missed invoice also understates that grant's spending in your report to the funder.