Free template
Account reconciliation template
Reconcile a balance sheet account to its support: an accrual schedule, a subledger, a loan statement. Explain each difference as a reconciling item, and the template shows whether anything is left unexplained.
Download the template
Download the account reconciliation template .xlsx
What’s in the workbook
- Account, period end, ledger balance, the supporting schedule used, preparer and reviewer
- A supporting balance built from up to 15 items
- Up to 10 reconciling items with type, amount, the action to take and the date cleared
- The unexplained difference, flagged red until it reaches zero
Blue cells are inputs. Formulas recalculate in Excel, Google Sheets and LibreOffice. The sample data below is already filled in so you can see how it works; replace it with your own.
What the sample looks like
| Item | Amount |
|---|---|
| October utilities, not yet billed | 1,840.00 |
| Audit fee, work performed through October | 6,500.00 |
| Wages earned Oct 26-31 | 9,000.00 |
| Sales commissions earned in October | 1,110.00 |
| Total per support | 18,450.00 |
| Amount | |
|---|---|
| Balance per general ledger | 19,650.00 |
| Less: total per supporting schedule | -18,450.00 |
| Difference to explain | 1,200.00 |
| Reconciling item (error): September legal accrual of $1,200 left in place after the invoice was posted to accounts payable | -1,200.00 |
| Unexplained difference | – |
What a good reconciliation shows
- The ledger balance
- Taken from the general ledger or trial balance at the period end.
- Independent support
- A schedule, statement or subledger that proves the balance, not a copy of the ledger itself.
- Explained differences
- Each reconciling item says what it is, whether it's timing or an error, and what will clear it.
- Nothing left over
- The unexplained difference is zero, or small enough that your policy allows it to be left.
- Two signatures
- Prepared by one person, reviewed by another, both dated.
In the sample, accrued liabilities are $19,650 in the ledger but the accrual schedule supports $18,450. The $1,200 gap is a September legal accrual that was never reversed after the invoice was posted to payables. Reversing it clears the item.
Cash accounts have their own format: use the bank reconciliation template. Reconciliations are a core step of the month-end close, and reconciliation software automates much of the matching.
Account reconciliation template: questions
- What is an account reconciliation?
- A comparison of a general ledger account's balance with independent evidence of what it should be, with any difference explained. It proves the balance sheet figure is right.
- Which accounts should be reconciled each month?
- Cash, receivables, payables and accruals usually monthly; others, such as fixed assets or equity, at least quarterly or when they change. Higher-risk and higher-balance accounts get reconciled more often.
- What's the difference between a timing item and an error?
- A timing item will clear on its own, such as a payment recorded in the ledger before the bank processes it. An error needs a correcting entry, like the unreversed accrual in the sample.