Download Bank Reconciliation Statement Excel Template

Bank Reconciliation Statement is a ready-to-use template in Excel, Google Sheet, and OpenOffice Calc to identify the difference between checking account and Bank Book.

Update the Bank Book and enter the outstanding cheques, outstanding deposits, Bank Charges, etc. and the template will automatically display the difference.

What is A Bank Reconciliation Statement?

A Bank Reconciliation Statement is a document that compares the Bank Balance as per Bank statement and the balance as per Bank Book maintained by us.

This statement reflects the outstanding cheques, outstanding deposits, Bank Charges, etc. This document helps us to find discrepancies between our records and the bank statement.

For example, you deposit a cheque in the bank but it doesn’t reflect in receipts of the bank statement. At the time of bank reconciliation, you can easily find out.

The time frame of Bank Reconciliation depends on many factors. These factors include the size of the organization, number of bank transactions, nature of the business, etc.

You can do it weekly, fortnightly, or monthly. If you have too many transactions through the bank, then doing reconciliation weekly or fortnightly is the best option.

As an Accounting professional, I will suggest weekly or fortnightly. Long duration for reconciliation can be hectic and time-consuming.

Purpose of Bank Reconciliation Statement

Usually, the balances as per the statement and the company’s records are not the same. This is due to deposits in transit, outstanding checks, bank charges, interest earned or paid, etc.

The main purpose of a Bank Reconciliation Statement is to identify, resolve, and properly report the difference between checking account and Bank Book.

With the large volume of bank transactions, it becomes necessary to reconcile the bank with our records. This reconciliation will assure proper recording of all bank transactions.

It is an internal financial report of business that explains and documents any differences between the bank book and our accounting records.

Steps to Prepare A Bank Reconciliation Statement

The Bank reconciliation process consists of the following 8 steps:

1. Identify uncleared checks and deposits in transit.
2. Add back any deposits in transit.
3. Deduct any outstanding checks.
4. Add notes receivables and interest earned to the balance.
5. Subtract Bank Charges, interest paid, service fees, penalties, etc.
6. Match it with the company’s balance. If the difference remains it means that some entries are missing to report either in Bank book or not reported in the bank statement.
7. This provides you with an adjusted balance.
8. The bank balance should match the adjusted balance of the Bank Book.

Bank Reconciliation Statement Template

We have created a Bank Reconciliation Statement along with Bank Book for simple and fast reconciliations. You can customize it according to your needs as and when required.

Excel Google Sheets Open Office Calc

Click here to Download All Financial Statement Excel Templates for ₹299.

Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.

Apart from that, you can also download other financial statement templates from our websites like Profit and Loss Account Template, Cash Flow Statement Template, Trial Balance Template, and Balance Sheet Template.

Let’s discuss the contents of this template in detail.

Contents of Bank Reconciliation Statement Template

This workbook consists of two files: Bank Book and Bank Reconciliation Statement.

Bank Book

In the Bank book, you can record the transaction done through the bank daily. Cheque deposits, cheques issued, cash deposits, cash withdrawals, etc are recorded in this file.

A screenshot of Bank Book is as follows:

The upper right corner shows the available bank balance as per our records. This balance never matches with Bank statement due to many reasons.

Once you have completed entering data into Bank Book, comes the reconciliation part. For this purpose, we prepare the Bank reconciliation statement.

Bank Reconciliation Statement

In the reconciliation statement, your Bank Book entries are compared to the Bank Statement.

It isn’t going to be so hard for you to complete this task. If you maintain your bank book regularly then this task would not take more than 15-30 minutes.

Note: Enter data in Blue colored cells only.

Open the reconciliation template and follow 5 steps mentioned below:

1: You need to enter the date of the issue for the bank statement in the designated cell. Then you need to enter closing bank balance as per statement along with the closing balance of Bank Book in the designated cells.

See the image below:

2: Enter the outstanding cheques that have been issued but not presented in the bank.

Tip: To easily identify your outstanding cheques is to look cheque numbers in sequence on the bank statement. This way you can find the cheque numbers that have been skipped.

Normally, the non-presented cheques are those that are issued towards the end of the month. Start from the end of the statement to speed up the process.

3: Enter any outstanding deposits in the designated areas. Usually, we don’t record cash deposits in our accounting system unless they have been deposited in the bank.

4: Enter any other outstanding adjustments here. These adjustments include any entries that are recorded in your Bank Book but not in the Bank Statement.

5: Balancing is the final step. Once you enter all of the outstanding items, the variance should be 0.

If the variance in the cell (B16) is 0 then the cell in F18 will show “BALANCED”. If there is a variance, it will show “OUT OF BALANCE”.

Benefits of Preparing Bank Reconciliation Statement

• Regular reconciliation will make us aware of the amount that we can spend and save us from overdrawing the account.
• It prevents theft by unauthorized transactions.
• Humans are prone to mistakes. A small typographical error by the bank can cost us more. Hence, a regular reconciliation will help us eliminate these errors at bay.
• It not only prevents banking errors but also helps us to identify and rectify accounting errors made at our end.
• The statement displays fees charged by the banks. Mostly, businesses fail to keep an eye on these bank charges. The bank adds these charges based on the usage of the account. Excessive charges add to the cost of running the business. Thus, reconciliation highlights the amount of money you pay every month.
• It helps us in managing the collection of accounts receivable.  When the client’s check doesn’t clear it alerts management to take timely action for collection.

Reasons for Difference Between Bank Statement and Accounting Records

Discrepancies can be due to following reasons:

• Cheques of customers deposited but not credited by the bank.
• Cheques issued by us but not yet presented in the bank.
• Interest from Bank is not recorded in Bank Book.
• Bank Charges may vary and are not recorded in Bank Book.
• Other miscellaneous queries of transactions.

We thank our readers for liking, sharing, and following us on different social media platforms, especially Facebook.

If you have any queries or questions, share them in the comments below and I will be more than happy to help you.

Frequently Asked Questions