Free vendor statement reconciliation template

What is inside

• Reconcile tab The statement balance and your balance at the top, then up to thirty lines with the amount on the statement, the amount in your books, the difference and a result for each line.
• Summary tab The difference to explain, how much your listed lines explain, what is left over, and counts and dollars by result.
• How to use tab The steps, the sign convention for payments and credits, and the usual reasons a statement and your books disagree.

How to use it

1. Fill the yellow cells at the top with the vendor, the statement date, the statement balance and your own balance for that vendor on the same date.
2. List each statement line in the statement column, then enter your amount for the same invoice. Leave your amount empty if the invoice is not in your books.
3. Add a row for anything in your books that is not on the statement, such as a payment that is still on its way.
4. Read the Result column, pick an action for each line that does not match, and check that the unexplained difference on the Summary tab is zero.
5. Fix errors in your books with a dated entry, ask the vendor for copies of invoices you do not recognize, and keep the filled copy with the month's records.

Using it in Google Sheets

1. Open Google Sheets and choose File, then Import, then Upload, and pick the downloaded file.
2. Choose "Replace spreadsheet" or "Insert new sheet(s)".
3. Check the formulas and the dropdown lists after the import. Most carry over, but Google Sheets handles a few functions and formats differently.

Things to watch

• Signs matter. Enter what you owe as positive numbers and payments, credits and returns as negative numbers on both sides, or the differences will not add up.
• Use the same date on both sides. A bill or payment dated just before or after the statement date is the most common reason for a gap. Compare balances as of the statement date.
• A match is not proof the invoice is right. The sheet compares two sets of numbers. It cannot tell whether you actually received the goods or whether the price was the one you agreed. Check the invoice and the purchase order.
• Disputes are your call. Decide with the vendor how to treat a disputed line, and ask your accountant how to record a credit, a write-off or a prior-period correction.

Questions

Want a fuller version with worked examples? See SOP Library in the store. This free template stays free either way.

Want more than a template? See Month-End Close Workbook.

Want every toolkit? The toolkit membership includes all of them, with a lower rate if you pay yearly.

Related: Guide: vendor statement reconciliation, Guide: accounts payable process, Monthly close checklist, Days payable outstanding calculator, Bank reconciliation difference finder, Are my books ready for taxes?, or all free templates.

These templates track your own numbers. They are not tax, legal or accounting advice, and MCC Solutions is not a CPA firm. Ask your CPA how rules apply to you.