Template structure
A useful reconciliation workbook separates source statement totals, converted transaction rows, adjustments, and the final variance calculation.
- Statement summary: opening balance, closing balance, total credits, total debits.
- Converted rows: date, description, debit, credit, amount, balance.
- Review notes: missing rows, duplicate headers, sign corrections, manual adjustments.
Variance checks
The goal is not to make a spreadsheet look tidy. The goal is to prove that the converted rows reconcile back to the statement.
- Opening balance + credits - debits = closing balance.
- Transaction count matches the statement detail.
- Unreconciled difference is explained before import.
Reference table
| Field | Purpose | Check |
|---|---|---|
| Opening balance | Starting point | Matches statement summary |
| Credits | Money in | Total agrees to statement where shown |
| Debits | Money out | Total agrees to statement where shown |
| Closing balance | Ending point | Matches calculated row balance |
FAQ
Can this template be used for credit cards?
Yes, but credit cards use liability logic. Payments, refunds, purchases, fees, and interest should be reviewed with the card statement summary.
What should I do if balances do not match?
Check repeated headers, missing continuation lines, summary rows extracted as transactions, and incorrect debit/credit signs before importing.