Free Excel template
Supplier Statement Reconciliation Template (Free Excel)
This supplier statement reconciliation template is a free Excel file for comparing a supplier's statement with your own records line by line, so you can find and explain every difference before you pay or agree a balance. It has an Arabic sheet and an English sheet with the same layout.
Below: what each column means, how to fill it in, the most common causes of differences, and how Zawid Accounts makes next month's check shorter.
Zawid Accounts · Content updated:
What's inside the supplier reconciliation template
The workbook has two sheets, one in Arabic and one in English. Each sheet has one row per transaction and these columns:
- Date: the date of the invoice, payment, return or note.
- Reference: the invoice number, payment receipt or credit note number.
- Description: a short note such as ‘Invoice, 20 cartons’.
- Per supplier statement (Debit, Credit): the amounts exactly as the supplier's statement shows them.
- Per our records (Debit, Credit): the amounts exactly as your own books show them.
- Difference: the gap left on that line.
- Status: for example matched, timing or to investigate.
- Notes: what you found and what you'll do.
| Cause | What you see | How to fix it |
|---|---|---|
| Payment timing | Your payment is in your books but not on the supplier's statement. | Mark it as timing, check that it appears on the next statement, and send the payment receipt if it doesn't. |
| Invoice missing from your records | An invoice on the supplier's statement isn't in your books. | Ask for a copy, confirm the goods were received, then record it. |
| Invoice not yet on their statement | You received and recorded the goods, but the supplier hasn't invoiced yet. | Mark it as timing and confirm the invoice number with the supplier. |
| Duplicate entry | The same invoice or payment appears twice on one side. | Compare reference numbers and correct the duplicate on the side where it happened. |
| Returns or credit notes | You returned goods, but the credit shows on only one side. | Ask the supplier for the credit note, or record the return you agreed on. |
| Discounts | An early-payment or volume discount appears on one side only. | Confirm the agreed discount in writing, then record it on the side that's missing it. |
| Opening balance | The difference is already there on the first line, before any transaction. | Reconcile the previous period first, or agree the opening balance with the supplier. |
| Currency | Amounts don't match because they're in different currencies or were converted at a rate. | Compare in the invoice currency and keep each currency's balance separate. |
| Typing error | Same reference, different amount, such as 450 versus 540. | Check the original document and correct the wrong entry. |
How to fill in the supplier statement reconciliation template
Below the lines, a summary block compares the closing balance per the supplier with the balance per your books, shows the difference and splits it into explained and unexplained. To fill it in:
- Get the supplier's statement for a clear period, say March 1 to 31, and your own record of that supplier for the same dates.
- Compare opening balances first. If they differ, note it on the first line.
- Enter the supplier's lines in the supplier columns and yours in your columns. The sides are mirrored: an invoice is a debit on their statement and a credit to the supplier in your books, and your payment is the reverse.
- Match lines with the same reference and amount and mark them matched.
- For each line left over, enter the difference, a status and a note.
- Fill in the summary: both closing balances, the difference, and how much is explained.
- Chase the unexplained part: ask for a copy of the invoice or credit note, or correct your own record.
Common causes of supplier statement differences
Most differences come from a short list of causes. The table on this page shows how each looks and how to fix it. Clear timing differences first; they are the most common.
A worked example
Your books say you owe a supplier 4,600 at month end. The supplier's statement says 5,400. After matching every invoice and payment, two lines are left: an invoice of 600 the supplier recorded on the 30th that hasn't reached you yet, and your payment of 200 on the 31st that isn't on their statement.
The summary shows a difference of 800, all explained. When the invoice arrives and you've checked the goods, record it; the payment should appear on next month's statement. Only an unexplained amount should hold up agreeing the balance.
How Zawid's supplier account helps next month
Zawid Accounts keeps suppliers in separate accounts from customers. You record each purchase on credit as an amount you owe and each payment you make, with a date, a note and a currency. Put the invoice or receipt number in the note and next month's Reference column is ready.
At period end, open the supplier's account, choose the same period as their statement and export a PDF account statement: your side of the reconciliation in one document you can share, for example on WhatsApp. If you deal with a supplier in two currencies, each balance stays in its own currency, so reconcile each separately; Zawid does not convert between them.
Zawid records debts and payments. It is not a double-entry bookkeeping system and does not reconcile statements for you. On the Free plan, new users get up to 50 active customers and suppliers in total, and Free may show ads.
Who it suits, and its limits
It suits shops, wholesalers and small businesses that buy on credit from regular suppliers. The template is a working sheet, not an accounting or tax document. If a dispute involves large amounts, contracts or tax invoices, bring in a qualified accountant.
Frequently asked questions
What is supplier statement reconciliation?
Comparing a supplier's statement with your own record of purchases, returns and payments, then explaining every difference until the closing balances agree or the gap is fully explained.
Why are debits and credits reversed on the supplier's statement?
The supplier records you as a receivable, so invoices are debits on their side. In your books the supplier is a payable, so the same invoice is a credit. That's why the template has separate columns for each side.
How often should I reconcile supplier statements?
Monthly for regular suppliers, plus before any large payment or when terms change.
Is the template really free?
Yes. It's a free Excel file available from this page, and you can adapt it to your business.
Can Zawid Accounts reconcile the statement automatically?
No. Zawid keeps the supplier's account, balances and PDF statements on your phone; you compare them with the supplier's statement yourself.
What should I do with an unexplained difference?
Don't pay or sign off on it until you understand it. Ask the supplier for the documents behind it and check your records for a missing or duplicate entry.