Download I: Bank Reconciliation Statement (.xlsx)
What it does
Matches the cash book with the bank statement by reference and amount, and prepares the BRS automatically (unpresented cheques, uncredited deposits, bank-only items).
Who it is for
Accountants at every month end.
How to use it
- Go to 'Inputs'. Type the bank and account name, the reconciliation date, the opening balance per books and per bank statement (normally the same if last month was fully reconciled) and the closing balance shown on the bank statement.
- Go to 'Books'. Paste your cash book / bank ledger for the month: date, voucher number, particulars, cheque or UTR reference, receipt amount and payment amount. Put last month's unreconciled items at the top too.
- Go to 'Bank'. Paste the bank statement: date, narration, reference (cheque / UTR), withdrawal and deposit.
- Each line now shows 'Matched' or why it is not matched. Use Excel's filter on the 'Status' column to list unmatched items.
- Go to 'BRS'. The reconciliation statement is ready. 'Difference' should be zero. If it is not, look at the unmatched items, especially 'Reference found - amount differs'.
- Pass journal entries in the books for bank charges, interest and direct receipts shown under 'In bank, not in books'.
Tabs in the file
| Tab | What it holds |
|---|---|
| Inputs | Account details, opening balances and closing balance per bank statement. |
| Books | Cash book entries with match status. |
| Bank | Bank statement entries with match status. |
| BRS | The reconciliation statement and checks. |
Learn the topic
For computation and reconciliation assistance only; not a substitute for formal statutory audit or legal tax advice. All sample data is illustrative and made up. Rates, limits and rules must be checked against current law and your own agreements before use. No tax saving, audit outcome or compliance result is promised.