Cash Reconciliation

The Problem

Mid-market controllers running 4–12 bank accounts close each month with a manual bank reconciliation statement built in Excel. The accounts executive ticks bank statement lines against the Tally or SAP cash book ledger, lists outstanding cheques and deposits in transit on the BRS, and chases unrecorded bank charges, NEFT fees, and interest credits that were never posted. For an account with 800–1,500 monthly transactions, this consumes 2–3 working days per ledger and is repeated 4–12 times every period close.

The control failure is well-documented. SA 505 (External Confirmations) and the ICAI Guidance Note on Audit of Banks both treat the BRS as a primary working paper, and Schedule III of the Companies Act 2013 requires the cash and bank balance disclosed in the financial statements to be supported by a reconciled position. When a stale cheque older than 90 days under the RBI Master Direction on Negotiable Instruments sits unaddressed on the BRS, or when a bounced cheque (R-return) is not linked to its original credit, the closing cash balance carries an unsupported figure straight into the trial balance.

Manual reconciliation breaks down on three specific patterns: two payments of the same value clearing on the same date to different vendors, a single statement narration that bundles principal and TDS, and a deposit in transit that crosses period end and gets double-posted in the next month. Each of these is found by the statutory auditor at the year-end, not by the controller in time.

Why It Matters: Context

A bank reconciliation has four reconciling-item classes recognised under accepted Indian accounting practice: cheques issued but not yet presented, deposits credited in books but not yet on the statement, bank-side debits and credits not in the books (charges, interest, direct credits), and errors on either side. The controller is expected to identify each item, supply the cheque number or UTR, and either carry it forward as a timing difference or pass a journal entry to bring the books in line with the bank.

In a mid-market environment with 50–500 active vendors and no dedicated treasury team, the senior accountant typically owns reconciliation across Acme Bank, and Acme operating accounts. Statements arrive in mixed PDF and Excel formats with varying column orders, narrations are abbreviated (NEFT/ACMEN12345678/ABC ENTERPRISES), and the cash book exported from Tally uses voucher numbers rather than instrument numbers.

The downstream consequence is direct: an unreconciled bank balance qualifies the statutory audit report under SA 705, and in regulated entities triggers an observation in the Long Form Audit Report submitted to the bank.

What This Workflow Automates

1. Classifies each uploaded file as Bank Statement, Cash Book, or Payment Voucher using header signals (bank logo, account number, ledger name) and rejects mis-paired files before extraction begins.
2. Normalises account identity by matching the trailing digits of the bank statement account number against the ledger name on the cash book; halts with an Account Identity Failure exception if the digits do not align.
3. Extracts every line item from both sources — date, narration, debit, credit, balance, cheque number, UTR, voucher number — and standardises date formats and amount signs across the two ledgers.
4. Matches transactions in priority order: instrument reference (cheque number / UTR / NEFT reference) first, then voucher number, then amount-plus-date as a last resort, preventing false matches on identical-amount payments to different counterparties.
5. Classifies every unmatched line into one of six reconciling-item buckets: outstanding cheque, deposit in transit, unrecorded bank charge, unrecorded interest, reversed/bounced transaction, or genuine discrepancy.
6. Flags outstanding cheques older than 90 days as stale under the RBI Master Direction and drafts a write-back journal reversing the bank credit to the issuer ledger.
7. Generates the BRS in the format expected by statutory auditors — opening balance per books, additions and deductions by reconciling-item class, closing balance per bank — together with proposed journal vouchers for unrecorded charges and reversals.

All of this happens in roughly 90 seconds per account with deterministic outputs every controller can audit line by line.

Edge Cases We Simulate

The workflow ships with a battery of synthetic test scenarios that exercise every failure mode we have seen in real-world data. Each scenario produces a deterministic outcome that an auditor or controller can verify in seconds.

Sample Documents

Download or inspect the seeded sample files used to demonstrate this workflow:

Sample Results

On a representative Acme Bank current account with 1,142 bank statement lines and 1,098 cash book entries for a single month, the workflow matched 1,061 transactions on instrument reference, isolated 34 outstanding cheques totalling ₹18.4 lakh, 6 deposits in transit of ₹4.2 lakh, 9 unrecorded bank charges aggregating ₹3,847 (SMS charges, NEFT fees, quarterly maintenance), and 2 reversal pairs linked as bounced cheques. The closing balance per books reconciled to the bank closing balance to the rupee.

The exception class that consistently surfaces in the demo data is Same Amount, Different Counterparty — two payments of ₹50,000 clearing on the same date to different vendors. Amount-plus-date matching would have crossed the legs; routing on UTR kept each payment posted to its correct vendor ledger and prevented a downstream vendor reconciliation break.

Why Automation Wins Here

A reconciliation that takes a senior accountant 2–3 days per account in Excel is completed in under two minutes per account, with each of the four reconciling-item classes auto-classified and the stale cheque and unrecorded charge journals pre-drafted. Across a 10-account close, this compresses 20–30 person-days of monthly effort to under an hour, while eliminating the false-positive matches that manual amount-and-date ticking produces.

The output drops directly into the audit working paper file: a BRS in the SA 505 format, the matched-transaction register, the exception list with classification reasons, and the proposed journal vouchers — all referenced to the source bank statement page and cash book voucher number, ready for the statutory auditor's bank balance verification procedure.

Frequently Asked Questions