Bank Reconciliation Example in Excel for Small Businesses

Bank Reconciliation Example in Excel for Small Businesses

A small business can record every sale, issue invoices on time, and still find that its bank statement does not match its bookkeeping records. The difference does not automatically mean money is missing. A customer deposit may be recorded in the books before the bank processes it, or a check may remain outstanding at the end of the month.

Other differences require actual accounting corrections. For example, a bank might deduct a service fee or process an automatic payment that the business has not entered into its ledger.

Bank reconciliation is the process of identifying these differences and explaining them until the adjusted bank balance equals the adjusted book balance.

This guide walks through a complete fictional example for a small US service business. You will learn which items belong on each side of the reconciliation, how to calculate the adjusted balances, and how to build a simple Excel worksheet.

All company details and financial amounts below are fictional educational examples.

What is bank reconciliation?

Bank reconciliation compares the cash balance reported by a financial institution with the cash balance recorded in a company's accounting system for the same date.

The objective is not to force two numbers to match through arbitrary adjustments. It is to explain timing differences, identify missing transactions, correct recording errors, and establish a supportable closing cash balance.

The Internal Revenue Service discusses this process in Publication 583, which recommends reconciling business checking accounts each month.

A typical reconciliation contains two calculations:

• Adjusted bank balance = statement balance + deposits in transit - outstanding checks, plus or minus verified bank errors.
• Adjusted book balance = ledger balance + unrecorded credits - unrecorded debits, plus or minus verified bookkeeping corrections.

When both sides agree, the reconciliation is mathematically complete. However, agreement alone does not prove that every transaction is legitimate or correctly classified. Supporting documents and independent review still matter.

Documents to collect before starting

For a basic monthly reconciliation, gather the bank statement covering the period, the cash account ledger, deposit records, payment confirmations, outstanding check details, and any relevant invoices or receipts.

Use the same closing date for both balances. Comparing a September 30 bank statement with an October 2 ledger balance will create unnecessary differences.

For a small business using a spreadsheet, it is helpful to organize records by transaction date, amount, reference number, payment method, and whether the transaction has cleared the bank.

Do not include real customer account numbers, banking credentials, or other sensitive information in public templates or demonstration files.

Worked example: Cedar Field Services

Imagine Cedar Field Services, a fictional US maintenance business preparing its September 30 bank reconciliation.

The owner finds the following balances:

| Record | September 30 balance | | --- | ---: | | Bank statement | $12,480.00 | | Accounting cash ledger | $12,315.00 |

The balances differ by $165.00. Rather than entering a single unexplained correction, the owner reviews the transactions and identifies five relevant items.

First, a customer deposit of $1,850.00 was recorded in the accounting ledger before September 30, but the bank did not credit it until the following month.

Second, previously issued checks totaling $2,125.00 were already deducted from the ledger but had not cleared the bank by September 30.

Third, the bank deducted a $35.00 service fee that had not been recorded in the ledger.

Fourth, the bank credited $20.00 in interest that was missing from the accounting records.

Finally, an automatic software subscription payment of $95.00 appeared on the bank statement but had not been entered in the ledger.

These five items explain the difference between the two original balances.

Step 1: Calculate the adjusted bank balance

Start with the bank statement closing balance of $12,480.00.

Add the deposit in transit because the business has already recorded that receipt, but the bank has not yet included it in its statement balance.

Subtract the outstanding checks because the business has already recorded those payments, but the bank has not yet deducted them.

| Bank reconciliation item | Amount | | --- | ---: | | Bank statement closing balance | $12,480.00 | | Add: Deposit in transit | $1,850.00 | | Less: Outstanding checks | ($2,125.00) | | Adjusted bank balance | $12,205.00 |

The calculation is:

`12,480 + 1,850 - 2,125 = 12,205`

The adjusted bank balance is therefore $12,205.00.

Important: Deposits in transit and outstanding checks are normally timing differences. Because they are already recorded in the business ledger, they do not require duplicate journal entries merely because they appear on the reconciliation.

The business should retain the deposit references and outstanding check numbers so these items can be verified when they clear.

Step 2: Calculate the adjusted book balance

Next, review transactions recorded by the bank but not yet entered in the company's accounting ledger.

The original book balance is $12,315.00.

The missing bank service fee reduces cash by $35.00. The interest credit increases cash by $20.00. The automatic software payment reduces cash by $95.00.

| Book reconciliation item | Amount | | --- | ---: | | Accounting ledger closing balance | $12,315.00 | | Less: Bank service fee | ($35.00) | | Add: Interest earned | $20.00 | | Less: Software subscription payment | ($95.00) | | Adjusted book balance | $12,205.00 |

The calculation is:

`12,315 - 35 + 20 - 95 = 12,205`

Both adjusted balances now equal $12,205.00.

The reconciliation difference is:

`12,205 - 12,205 = 0`

This means the identified adjustments explain the original discrepancy in the fictional example.

Step 3: Record the missing journal entries

A reconciliation worksheet identifies adjustments, but it does not automatically update the accounting ledger.

The business must record verified transactions that were missing from its books.

For a conventional double-entry accounting system, the three missing transactions could be recorded as follows, assuming the stated account classifications are appropriate.

| Transaction | Debit | Credit | | --- | --- | --- | | Bank service fee | Bank Fees Expense $35 | Cash $35 | | Interest earned | Cash $20 | Interest Income $20 | | Software subscription | Software Expense $95 | Cash $95 |

Each entry has equal debit and credit amounts.

The specific expense and income account names depend on the business's chart of accounts and accounting policies. A qualified bookkeeper or accountant should review classifications when necessary.

Do not record the $1,850 deposit in transit again if it is already in the ledger. Similarly, do not record the $2,125 in outstanding checks a second time.

Duplicate entries would create new errors rather than solve the reconciliation.

Step 4: Build the reconciliation in Excel

You do not need a complicated workbook to reproduce this example.

Create a worksheet named Bank Reconciliation. Enter the following labels in column A and amounts in column B.

| Cell | Description | Value or formula | | --- | --- | --- | | B2 | Bank statement balance | 12480 | | B3 | Deposits in transit | 1850 | | B4 | Outstanding checks | 2125 | | B5 | Adjusted bank balance | =B2+B3-B4 | | B7 | Book balance | 12315 | | B8 | Bank fees | 35 | | B9 | Interest income | 20 | | B10 | Automatic payments | 95 | | B11 | Adjusted book balance | =B7-B8+B9-B10 | | B13 | Reconciliation difference | =ROUND(B5-B11,2) |

Format the amount cells as US dollars with two decimal places.

The expected results are:

• B5: $12,205.00
• B11: $12,205.00
• B13: $0.00

These formulas are suitable for the fictional example. A production workbook should include separate transaction-level schedules for multiple outstanding checks, deposits, corrections, and bank-originated items rather than relying only on manually entered totals.

For a larger transaction list, use unique reference numbers and preserve the original source documents. A spreadsheet total without supporting transactions is difficult to audit.

Step 5: What if the balances still do not match?

A nonzero reconciliation difference means more investigation is required.

Start by confirming that the bank statement and ledger use the same cutoff date and the same bank account.

Then check for duplicate payments, missing deposits, transposed digits, incorrect signs, and transactions entered into the wrong accounting period.

A common mistake is subtracting an outstanding check from both the bank and book sides. Another is adding a deposit in transit to the books even though the receipt was already recorded.

Also review electronic payments that may have been processed automatically, including subscriptions, merchant fees, loan payments, and bank charges.

If a transaction appears incorrect on the bank statement, investigate it with the bank instead of inventing a balancing journal entry.

Never use an unexplained adjustment simply to make the difference cell display zero.

For a more detailed explanation of timing differences, see AccountingCoach's bank reconciliation guide.

A practical monthly reconciliation checklist

A small business owner or designated bookkeeper can follow this sequence:

1. Download the complete bank statement for the period. 2. Export the cash ledger for the same closing date. 3. Match bank deposits to recorded receipts. 4. Match cleared payments to ledger transactions. 5. List deposits in transit and outstanding checks individually. 6. Identify bank fees, interest, automatic payments, and other unrecorded items. 7. Investigate errors and obtain supporting evidence. 8. Calculate both adjusted balances. 9. Post approved book adjustments and confirm the updated ledger balance. 10. Save the reconciliation, supporting documents, and reviewer approval in a secure location.

Outstanding items should be carried forward and checked during the next reconciliation. An old outstanding check should not disappear from the schedule simply because a new month has started.

The IRS explains the importance of retaining records that support business transactions in Publication 583.

Can an offline bookkeeping app help?

A spreadsheet is often sufficient for a small business with limited transactions and one responsible reviewer.

As transaction volume grows, a structured bookkeeping application may make it easier to organize income, expenses, invoices, and financial summaries.

VectoraWork's Small Business Bookkeeping OS is an offline HTML application intended to help small businesses organize financial records.

However, buyers should verify the current product specifications before assuming that a particular version includes automated bank reconciliation, direct bank connections, or accounting integrations.

For a broader recordkeeping routine, see our Small Business Bookkeeping Workflow.

An offline application does not automatically synchronize data between devices. Before using important business records, verify the application's export and backup options and test recovery with fictional data.

Frequently asked questions

How often should a small business reconcile its bank account?

Monthly reconciliation is a practical starting point and is recommended in IRS Publication 583. Businesses with substantial transaction volume or higher operational risk may benefit from more frequent reviews.

Does a deposit in transit require a journal entry?

Not merely because it is in transit. If the deposit has already been recorded correctly in the ledger, it is normally a bank-side timing adjustment. An actual missing or incorrect ledger entry is a separate issue.

Should outstanding checks be recorded again?

No. An outstanding check that has already been recorded in the accounting system should not be entered again simply because it has not cleared the bank.

Is a zero reconciliation difference enough?

No. A zero difference confirms mathematical agreement between adjusted balances. It does not independently verify transaction legitimacy, correct classification, or the completeness of supporting records.

Practical takeaway

A reliable bank reconciliation explains differences instead of hiding them.

Start with the statement balance and ledger balance for the same date. Identify timing differences on the bank side, record verified missing transactions on the book side, and calculate both adjusted balances independently.

For the fictional Cedar Field Services example, both sides reconcile to $12,205.00, with a final difference of $0.00.

The most useful reconciliation is one that another person can reproduce using the original records.

Free Bank Reconciliation Excel Template and Calculator

Want to try the example with your own numbers? Use our two free companion resources. Both start with the fictional Cedar Field Services example used in this guide, so you can verify the results before replacing the sample amounts.

Download the editable Excel workbook

Download the Free Bank Reconciliation Excel Template (.xlsx)

The workbook includes five organized worksheets: a quick-start guide, a monthly reconciliation dashboard, a transaction-level bank timing schedule, a book-adjustments and journal-entry log, and an audit checklist. It uses Excel formulas, type dropdowns, distinct input and calculated fields, and built-in balance checks. No macros or paid add-ins are required.

The sample reconciliation should display:

• Adjusted bank balance: $12,205.00
• Adjusted book balance: $12,205.00
• Difference: $0.00
• Book adjustments: $35 bank fee, $20 interest, and $95 software payment

Change a sample amount to confirm the difference updates. Then save a separate copy before you enter actual business records. The starting book balance is the amount before the listed book-side corrections; recording those corrections again would create duplicates.

Use the free browser calculator

Open the Interactive Bank Reconciliation Calculator

The calculator works in a browser, including on mobile. Add deposits in transit, outstanding checks, and verified missing book transactions. It calculates both adjusted balances and highlights any remaining difference. It can export your current figures to a CSV file or print the result, without registration or third-party libraries. It does not send entered values to a remote calculation service or save them automatically. Download your report before closing the page.

> Important: All default figures are fictional. A zero difference confirms mathematical agreement, not the accuracy, completeness, authorization, or legal treatment of individual transactions. Both resources are educational tools and do not replace a qualified bookkeeper or accountant.

Editorial disclosure: Cedar Field Services and every transaction in this article are fictional. This guide provides general educational information and is not individualized accounting, tax, or financial advice. Review accounting treatment with an appropriately qualified professional when needed.