How To Create A Reconciliation Report In Excel

Bank Reconciliation Formula

Bank Reconciliation Formula (Table of Contents)

Bank Reconciliation Procedure
Reconciliation Procedure Format
Examples

What is Bank Reconciliation Formula?

Bank Reconciliation is one of the most important tasks of every Accounting and Finance Department in the Company. What is Bank Reconciliation? Bank reconciliation processes in which the balance of Bank account in the Books of Company is matched with the balance of our company account in the Books of Bank. In simple words, it is the process of reconciling the balance of bank account in books of the Company with the balance as reflected in the Books of Bank. This process is performed in every company. This helps to determine if there any accounting changes to be made in books. The bank reconciliation process is performed on a periodical basis according to the volume of transactions. If the volume of the transaction is high, then Bank Reconciliation is to be performed on a monthly basis or fortnightly or on a daily basis. If the volume of transactions is less then it can be performed on a quarterly or half-yearly or yearly basis.

What are the reasons for the mismatch of the Balance of Bank account in Books of Company with Balance in the books of Bank?

Below are entries due to which reconciliation process is performed:

1. Unpresented Cheques: Unpresented cheques mean cheques issued by the company but not presented in Bank. In this case, the company will pass the entry in Books as and when a cheque is issued but the bank will pass entry once it receives that cheque. Hence, this is one of the activities which causes a difference in balances.

2. Deposit in Transit: Deposit in Transit is also one of the reasons for differences in the balances on reconciliation date. In this, the company has deposited cheques or cash and passed entry in Books, but the bank has not credited due to the banking clearing procedure. This is caused due to time lag.

3. Errors in Bank: Amount which is wrongly entered by Bank in Company's ledger.

4. Errors by Company: Amount which is wrongly entered by Company in Bank's ledger.

Bank Reconciliation Procedure

Below is step by step procedure for Bank Reconciliation:

Match the Opening balance as per Bank statement with the books.
Check and tick all the debit entries as reflected in bank ledger with the credit entries in Bank Statement, identify which are missed.
Check and tick all the credit entries as reflected in Bank Ledger with the debit side in the bank statement, identify which are missed.
Correct the entries which are erroneously passed in Bank Ledger.
Take the adjusted closing balance of the Bank in Bank Reconciliation Statement.
Add back all the Unpresented Cheques and other credits that are not reflected in Bank Statement but reflected in Bank Ledger.
Less all the deposit in transit and another debit which is not reflected in Bank Statement but the entry for the same is passed in Books.
After adjustment of the above amount, you will arrive at Balance as per Bank Statement.

Format for Bank Reconciliation Statement

Below is a brief format of the Bank Reconciliation Statement.

Bank Reconciliation Statement Format

Examples of Bank Reconciliation Formula (With Excel Template)

Let's take an example to understand the calculation of Bank Reconciliation Formula in a better manner.

You can download this Bank Reconciliation Formula Excel Template here – Bank Reconciliation Formula Excel Template

Bank Reconciliation Formula – Example #1

From the following particulars, prepare Bank Reconciliation statement for M/s XYZ and company as at 31^stDecember 2018

Balance as per Bank Book is 8,000
Cheques issued of Rs. 20,000 and 25,000 but presentedon 5^th January 2019.
A customer has deposited cash directly into the bank amounting to Rs. 50,000
Bank charges debited by bank amounting to Rs. 1,050
Cheque received dishonored by the Bank amounting to Rs. 20,000
Cheque of Rs. 10,000 deposited in the bank on 30^th December 2018, cleared by the bank on 3^rd January 2019.
Closing balance as Bank Statement Rs. 51,950/-

Solution:

Below is the Bank Reconciliation statement for the date 31^st December 2018.

Bank Reconciliation Formula-1.2

Bank Reconciliation Formula – Example #2

Below is Bank Statement of ABC International Limited:

Bank Statement of ABC International Limited

Below is Bank Account Ledger in the Books of ABC International Limited:

Bank Account Ledger in the Books of ABC International Limited

Prepared the Bank Reconciliation Statement for 31^st January 2019.

Solution:

Step 1: First of we should match all the debit entries and credit entries in Bank Ledger and Bank Statement of ABC International. From the checking following can be noted:

Cheques issued to DFP Limited and XYZ Limited issued by the company but not presented in Bank
Cheque from Customer C deposited into Bank on 31^st January 2019 but not credited in Bank Statement.
Cheque received from customer DF dishonored by the bank and same is not recorded in Books of ABC International Limited
Bank Charges debited by Bank but not recorded in Books of Accounts

Below is the Bank Reconciliation Statement from the above Information:

Bank Reconciliation Formula-2.3

Relevance and Uses

Bank Reconciliation statement is the most important activity which is performed by every company. It helps to detect fraud and money laundering by the company and bank. It helps to detect that all transactions in books of accounts are recorded. It gives a clear picture of what Bank is reflecting balance and what books of accounts are reflecting the bank balance.