Bank reconciliation workpaper

Purpose

Reconcile a bank statement to the cash ledger for one account and period, showing which transactions agree, which are timing items, and which require investigation. Produce a reproducible workpaper rather than a journal entry.

Scope

The work covers statement transaction ID, value date, description, debit, credit, currency, ledger entry ID, posting date, amount, and prior open-item status. It handles one-to-one, one-to-many, and many-to-one matches, bank fees, interest, outstanding payments, deposits in transit, and duplicate lines.

Excluded: posting adjustments, approving reconciliations, releasing payments, and jurisdiction-specific accounting conclusions.

Data basis

• Bank statement lines with account, statement date, value date, debit, credit, amount, currency, and bank reference.
• Cash ledger entries with journal ID, posting date, amount, currency, account, counterparty, and description.
• Prior-period reconciliation, open-item status, matching rules, and tolerance policy.

Result

A reconciliation sheet with match status, bank reference, ledger ID, match type, signed bank amount, signed ledger amount, difference, explanation, age, and citation, plus a memo with the closing balance bridge and unresolved items.

Quality criteria

• Beginning balance plus signed statement activity equals the statement ending balance.
• Ledger activity and all bank lines are counted once; no source row disappears in matching.
• A proposed match records its rule, score or arithmetic, and both source identifiers.
• Every unmatched item has a reason, amount, age, and source citation.

Instructions

Use value date for bank timing and posting date for ledger timing, retaining both. Apply exact account, currency, and amount matches before reference-based or grouped matching. Use the tolerance from the matching policy; never invent one. Treat a difference as timing only when the evidence identifies a pending payment, deposit, fee, or interest item. Preserve sign conventions and show the formula for every bridge total.

Adapt before use

• Map bank and ledger column names, account identifiers, and sign conventions.
• Define the reconciliation period, permitted amount tolerance, and maximum age for carried items.
• Add the matching policy, bank-fee treatment, and prior open-item schedule to scope.