The Purpose of a Bank Reconciliation Schedule
A bank reconciliation is the fundamental accounting control that proves an organization’s general ledger cash account agrees with the bank’s official statement balance. In practice, the ending balance reported on your bank statement almost never equals the ending cash balance recorded in your internal books.
This difference does not necessarily mean errors were made; it typically reflects timing differences—checks written that have not yet cleared the banking system, deposits recorded after bank cut-off times, or automatic service fees not yet entered into your software. Creating a structured reconciliation schedule in Excel isolates these timing differences and proves financial integrity.
A bank statement reconciliation is an internal accounting control. To achieve a true audit-ready reconciliation, both the bank side and the company book side must be adjusted to reach an identical "Adjusted Cash Balance".
The Fundamental Bank Reconciliation Equation
Sign Convention: All inputs entered into this schedule are expressed as positive absolute numbers, where formulas add cash inflows and subtract cash outflows. On the bank track, deposits in transit add cash while uncleared checks deduct cash. On the book track, interest earned adds cash while service fees and NSF charges deduct cash. If your bank export contains negative numbers for withdrawals, enter their absolute values or adjust the formula operators accordingly to avoid accidental double-negatives.
A formal bank reconciliation operates on two parallel tracks:
Track 1: Bank Statement Balance Adjustments
• Start with: Ending Balance per Bank Statement
• Add: Deposits in Transit (funds received and recorded internally but not yet credited by the bank)
• Subtract: Outstanding Checks (checks issued that have not yet cleared the bank)
• Result: Adjusted Bank Balance
Track 2: General Ledger (Book) Balance Adjustments
• Start with: Ending Cash Balance per General Ledger
• Add: Bank Collections and Interest Earned (credits recorded by the bank not yet posted to books)
• Subtract: Bank Service Charges, Merchant Processing Fees, and NSF (Non-Sufficient Funds) checks
• Result: Adjusted Book Balance
Adjusted Bank Balance = Bank Statement Balance + Deposits in Transit - Outstanding Checks
Adjusted Book Balance = General Ledger Balance + Interest Earned - Bank Fees - NSF Checks
Proof of Balance: Adjusted Bank Balance - Adjusted Book Balance = 0.00Building the Excel Reconciliation Template
Set up a two-part reconciliation model in your worksheet:
Section A (Bank Side):
• Cell B3: Bank Statement Ending Balance ($14,850.25)
• Cell B4: Total Deposits in Transit ($2,400.00)
• Cell B5: Total Outstanding Checks ($1,850.50)
• Cell B6 (Formula): =B3 + B4 - B5 (Evaluates to $15,399.75)
Section B (Book Side):
• Cell B10: General Ledger Cash Balance ($15,424.75)
• Cell B11: Bank Interest Earned ($15.00)
• Cell B12: Monthly Account Service Fee ($40.00)
• Cell B13 (Formula): =B10 + B11 - B12 (Evaluates to $15,399.75)
Automating the Variance Proof and Discrepancy Checks
In cell B16, create a formal Proof of Balance test formula:
=IF(ABS(B6 - B13) < 0.005, "RECONCILED: Variance $0.00", "DISCREPANCY: " & TEXT(B6 - B13, "$#,##0.00"))If the variance formula returns anything other than zero, audit your schedule for common reconciliation mistakes:
• Transposition errors: Did someone record $54.00 as $45.00? (If the discrepancy is evenly divisible by 9, a transposed digit is almost always the cause).
• Omitted bank fees: Did an automated ACH withdrawal or foreign transaction fee post at the end of the month?
• Uncleared items: Were older outstanding checks from previous months mistakenly dropped from this month’s schedule?
Posting Necessary Adjusting Journal Entries
Items appearing on the Bank side (such as outstanding checks and deposits in transit) are timing differences that will clear on next month's statement; no journal entry is required for them.
However, all items on the Book side (interest earned, monthly bank fees, wire transfer charges) represent real cash movements that must be entered into your accounting ledger via journal entries to bring your general ledger cash account into permanent agreement with the bank.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
Bank reconciliations should be completed monthly upon receipt of each official bank statement, or weekly for high-volume commercial accounts to catch fraud and duplicate payments promptly.
A deposit in transit is cash or checks received and recorded on the company’s books on the last day of the month, but deposited too late in the day to appear on the bank’s monthly statement until the following day.
Primary Evidence & Official References
- AccountingTools: Bank Reconciliation Procedure — accountingtools.com (Verified 2026-10-19). Official source documentation →
- Corporate Finance Institute: Bank Reconciliation Guide — corporatefinanceinstitute.com (Verified 2026-10-19). Official source documentation →