bank-reconciliation-helper

Bank Reconciliation Helper

This skill reconciles a bank statement against the entity's cash book (general ledger cash account) and drafts the supporting workpaper. It is for accountants, bookkeepers, controllers, and auditors performing month-end close or substantive cash testing. It matches line items with sensible tolerance, classifies the residual differences (deposits in transit, outstanding payments, bank charges not yet booked, errors), proves the reconciliation arithmetically, and ages the open reconciling items so stale items get investigated. It operates only on the files you provide in your session.

When to use this

• Month-end or period-end close: tying the cash book balance to the bank statement closing balance.
• Bookkeeping clean-ups where the cash account has drifted and needs to be re-tied.
• Audit/review cash work: testing the year-end bank reconciliation and following up on long-outstanding items.
• Investigating a known difference between the ledger cash balance and the bank balance.
• Building a repeatable reconciliation workpaper before handing off to a reviewer.

Inputs

Provide two datasets (CSV preferred; an Excel workbook with two tabs also works):

1. Bank statement extract with columns: date, description, reference (cheque no, payment ref, or transaction ID), amount (signed: credits positive, debits negative, OR separate debit/credit columns), and ideally a running_balance.
2. Cash book / GL cash extract with the same conceptual columns: date the entity recorded the entry, description, reference, amount (signed from the entity's perspective: receipts positive, payments negative).

Also tell me:

• The reconciliation date (period end).
• The opening reconciled balance (prior period, if available) and the bank closing balance and cash book closing balance at the reconciliation date.
• The currency and whether amounts are in the same currency on both sides.

A bundled synthetic example is provided: samples/bank_recon_sample.csv. It contains both sides in one file with a source column (BANK or LEDGER) so you can see the expected shape. Split it on source to get the two datasets.

How to do it

Reconciliation is a controls discipline, not just arithmetic. Follow these steps.

1. Normalise both datasets

• Coerce dates to ISO YYYY-MM-DD. Coerce amounts to a single signed convention (entity perspective: money in positive, money out negative). If the bank uses separate debit/credit columns, compute amount = credit - debit.
• Trim and upper-case references; strip noise tokens from descriptions (e.g. REF, PAYMENT, INV, trailing transaction IDs) into a normalised key for fuzzy matching, but keep the original text for the workpaper.
• Confirm sign orientation: from the entity's books, a customer receipt increases cash (positive); on the bank statement the same receipt is a credit. Align both to the entity perspective so equal economic events carry equal signs. State your chosen convention explicitly.

2. Sanity-check the control totals first

• Bank closing balance minus bank opening balance should equal the sum of bank movements. Cash book closing minus opening should equal the sum of ledger movements. If a side does not foot, stop and flag the data quality issue before matching - a non-footing extract produces a meaningless reconciliation.

3. Match in passes, tightest first

Apply rules in order; once a line is matched, remove it from both pools so it cannot match twice.

1. Exact match: same normalised reference AND amount equal to the cent.
2. Amount + near date: amount equal to the cent AND dates within a tolerance window (default +/- 5 calendar days, configurable). This catches timing differences where reference formatting differs.
3. Reference + amount tolerance: same normalised reference AND amounts within the smaller of 0.5% or a fixed absolute tolerance (default 0.01, raise only with a stated reason such as FX rounding). Treat tolerance hits as matched-with-difference and list the difference separately - do not silently absorb it.
4. Many-to-one / one-to-many: look for a bank line equal to the sum of several ledger lines on the same day (batched receipts/payments), and vice versa. Only group when the grouped total ties to the cent and the items share a plausible link (same counterparty/date).

Never force a match across different signs or across a material amount gap to make the reconciliation balance. An unexplained item is a finding, not an error to be hidden.

4. Classify the unmatched residual

Every remaining item must be classified:

• Deposits in transit: receipts recorded in the cash book, not yet on the bank statement (in the cash book pool, money-in). Add back to the bank balance.
• Outstanding payments / unpresented cheques: payments recorded in the cash book, not yet cleared by the bank. Deduct from the bank balance.
• Bank items not in the books: bank charges, interest, direct debits, standing orders, FX fees, returned/bounced items on the bank statement but not yet in the cash book. These require a ledger adjustment (journal), not a reconciling timing item - flag for posting.
• Errors / duplicates: transposed amounts, double-postings, wrong-account entries on either side. Flag for correction.

5. Prove the reconciliation

Use the standard bank-to-book proof:

Bank statement closing balance
  + Deposits in transit (in books, not yet at bank)
  - Outstanding payments (in books, not yet cleared)
  +/- Bank errors (if the bank is wrong)
= Adjusted bank balance

Cash book closing balance
  + Items on bank not yet booked (interest, etc.)
  - Items on bank not yet booked (charges, DDs, returns)
  +/- Book errors
= Adjusted book balance

Adjusted bank balance MUST equal Adjusted book balance.

If the two adjusted balances do not agree, the difference equals an unexplained item still in a pool - do not sign off. The residual difference must be 0.00 or fully explained.

6. Age the open reconciling items

For each unmatched item still open at the reconciliation date, compute age in days from the item's own date to the reconciliation date and bucket: 0-30, 31-60, 61-90, 90+. Items older than one statement cycle (typically 30 days for cheques, sooner for electronic payments) warrant follow-up: an uncashed cheque past its validity window may need to be voided and reinstated as a liability; a long-outstanding deposit in transit may indicate a misposting or fraud.

Output

Produce a Markdown bank reconciliation workpaper (and, on request, a companion CSV of the line-level matching detail). The workpaper contains:

1. Header: entity name, bank account, currency, reconciliation date, preparer (leave blank for sign-off), reviewer (blank).
2. Reconciliation summary (the proof in step 5) with both columns and the final tie-out, ending in a bold "Difference: 0.00" line or the unexplained amount.
3. Reconciling items schedule - a table with columns: Item ID, Type (deposit in transit / outstanding payment / bank-only item / error), Date, Reference, Description, Amount, Age (days), Age bucket, Action.
4. Matched items detail (CSV or appendix): match_rule (exact / amount+date / ref+tolerance / grouped), bank_date, ledger_date, reference, bank_amount, ledger_amount, difference.
5. Journal entries to post - a short list of adjustments needed to bring the cash book current (bank charges, interest, returned items), each with a suggested Dr/Cr.
6. Notes / exceptions - data quality issues, tolerance hits, and items needing investigation.

Quality checks

• Confirm both extracts foot (closing - opening = sum of movements) before reporting any result.
• Confirm no line item appears in more than one match or in both a match and an unmatched pool.
• Confirm the sign convention is consistent across both datasets and stated in the workpaper.
• Confirm the proof ties to 0.00; if not, the difference must equal the sum of still-open items - show the arithmetic.
• Common errors to avoid: absorbing a tolerance difference without disclosing it; classifying a bank charge as a timing item (it needs a journal); netting unrelated items to force a balance; matching across opposite signs; ignoring duplicate ledger postings that happen to net out.
• Do not invent transactions to plug a gap. An unexplained difference is reported as such.

Worked example

Using samples/bank_recon_sample.csv for Aurora Components Ltd at 2026-05-31 (GBP):

• Split on source. The exact-match pass ties the customer receipt from Northwind Trading BV (ref INV-2041, 12,500.00) and the supplier payment to Borealis Supplies (ref CHQ-1087, -3,200.00).
• The amount+near-date pass matches a card settlement recorded 2026-05-29 in the ledger to its 2026-06-01 bank credit (within the 5-day window).
• Residual: a cheque CHQ-1091 for -1,450.00 is in the cash book but not on the bank statement -> outstanding payment (deduct from bank). A 2026-05-31 receipt of 2,000.00 is in the books but not yet at the bank -> deposit in transit (add to bank). Bank shows a -45.00 service charge and +18.50 interest not in the books -> bank-only items needing journals.
• Proof: Bank statement closing balance at 2026-05-31 is 41,830.00 (the last May running balance in the sample; the 2026-06-01 card settlement batch of 2,046.50 is excluded as it posts after the cutoff). Adjusted bank = 41,830.00 + deposit in transit 2,000.00 - outstanding cheque 1,450.00 = 42,380.00. Cash book closing 42,406.50 + interest 18.50 - charges 45.00 = 42,380.00. Difference: 0.00. The CHQ-1091 outstanding cheque is aged 0-30 days; no stale items this period.

Disclaimer

Disclaimer: This skill is a drafting and analysis aid, not professional advice. It does not provide accounting, audit, tax, investment, or legal advice. All output must be reviewed and approved by a qualified professional before use or reliance.

---

Part of LedgerSkills - security-vetted Claude Code skills for finance teams. New skills scanned and tested weekly; subscribe on the site.