Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
A junior accountant prepares reconciliations in a different layout each month, without a check cell or sign-off, so reviewers cannot tell whether the work is complete.
Every reconciliation working paper uses the same blocks: header, two balances, reconciling items with type and age, adjusted balances, an unexplained difference, a check cell that must read zero, and preparer and reviewer sign-off.
One spreadsheet template with fixed columns (date, reference, narration, amount, in books, in statement, difference type, action, owner, age in days), SUM formulas for adjusted balances, a check cell and a locked sign-off block.
Each monthly reconciliation has the same layout, shows a zero check cell or a clearly flagged unexplained difference, and carries dated preparer and reviewer sign-off.
A reconciliation working paper needs the same blocks for any account: the account and period, the balance per your books, the balance per the independent statement, a list of reconciling items with their type and age, the adjusted balances, any unexplained difference, and a sign-off block. If your template has all seven, a reviewer can check any reconciliation in minutes.
What are the blocks of the template?
- Header. Company, account name, period, cut-off date.
- Balance per books. From the ledger on the cut-off date.
- Balance per statement. From the bank, supplier, customer or lender statement on the same date.
- Reconciling items. Every difference, listed one per row.
- Adjusted balances. Each balance after adding or deducting its reconciling items.
- Check cell and unexplained difference. The two adjusted balances compared.
- Sign-off. Preparer and reviewer, each with a name and date.
What columns does the reconciling items list need?
| Date | Reference | Narration | Amount | In books? | In statement? | Difference type | Action | Owner | Age (days) |
|---|---|---|---|---|---|---|---|---|---|
| 27-09-2026 | Inv 4471 | Packaging supplier invoice | ₹36,500 | No | Yes | Error: not booked | Book purchase in September | Accounts | 3 |
“In books” and “In statement” tell you which side is missing the entry. “Difference type” is either timing or error. “Age” is the number of days from the item’s date to the cut-off date; in Excel, it is the cut-off date cell minus the item’s date cell.
A worked example: the vendor reconciliation filled in
Using the same packaging supplier from the six-step process article: at 30 September the ledger shows ₹3,28,400 payable and the supplier’s statement shows ₹3,64,900.
| Line | Item | Amount |
|---|---|---|
| A | Balance per books (payable) | ₹3,28,400 |
| B | Add: invoice 4471 on statement, not in books | ₹36,500 |
| C | Adjusted balance per books (A + B) | ₹3,64,900 |
| D | Balance per supplier statement | ₹3,64,900 |
| E | Less: items in books, not on statement | ₹0 |
| F | Adjusted balance per statement (D − E) | ₹3,64,900 |
| G | Check cell (C − F) | ₹0 |
The check cell reads zero, so the reconciliation is complete once the ₹36,500 invoice is booked and the reviewer signs.
The same layout works for a bank account. Say the books show ₹5,12,300 and the bank statement shows ₹5,23,064. Reconciling items: a cheque of ₹18,000 issued but not presented, a deposit of ₹7,500 not yet credited, and bank charges of ₹236 on the statement only. Adjusted books: ₹5,12,300 less ₹236 gives ₹5,12,064. Adjusted statement: ₹5,23,064 less ₹18,000 plus ₹7,500 gives ₹5,12,564. The check cell shows ₹500, so one item is still missing, and the sheet is not ready for sign-off until it is found.
Which formulas does the sheet need?
Keep the formulas simple so anyone can follow them:
- Items to add to books: a SUMIFS on the amount column where “In books?” is “No” and “In statement?” is “Yes”.
- Items to deduct from the statement side: a SUMIFS where “In books?” is “Yes” and “In statement?” is “No”.
- Adjusted balance per books: balance per books plus items to add to books.
- Adjusted balance per statement: balance per statement less items in books only (or plus, depending on whether the account is a payable or a bank balance; set the sign once in the template and label it).
- Check cell: adjusted books minus adjusted statement. Format it to turn red if it is not zero.
- Age: cut-off date minus item date, so items older than 30 or 60 days stand out.
Test the template once with a made-up set of figures before you rely on it.
How do you lock the sheet and show who reviewed it?
- One file per account per month, named the same way each time, such as “Packaging supplier – Sep 2026”.
- Protect the formula cells so only the input cells can be changed.
- Sign-off block with preparer name and date, reviewer name and date, and a short comment box.
- Lock the whole sheet after review, or save a PDF copy, so the reviewed version cannot be changed later without trace.
- Store it with the evidence: the ledger extract and the statement for the same date.
Do not hide this template behind a form or email request; share it freely within your team so everyone uses the same layout.
What comes after the working paper?
The working paper proves one account for one month. The next level is a control plan that lists every reconciliation the business runs, its owner and frequency, and an ageing workbook that tracks long-running items such as TDS receivable.
What to read next
- Next in this series: Reconciliation control plan template
- TDS receivable ageing workbook in Excel
- When the number of accounts grows, see reconciliation software for Indian finance teams
Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →