Download Bank Reconciliation Statement Excel Template - ExcelDataPro (2022)

Download Bank Reconciliation Statement Excel Template - ExcelDataPro (1)

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.

Table of Contents

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.

(Video) Cashbook in Excel

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.

Download Bank Reconciliation Statement Excel Template - ExcelDataPro (2)

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.

(Video) How To Make Automatic student fee collection and status system in excel

A screenshot of Bank Book is as follows:

Download Bank Reconciliation Statement Excel Template - ExcelDataPro (3)

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 thedate 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:

Download Bank Reconciliation Statement Excel Template - ExcelDataPro (4)

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.

(Video) Accounts and golden rules of accounting | Golden rules of accounting | Golden rules of accounting |

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

What does BRS mean in accounting?

BRS stands for Bank Reconciliation Statement.

How frequently Bank Reconciliation must be done?

The frequency of Bank Reconciliation depends upon the volume of transactions. For a small business, it is feasible monthly or bimonthly. But for bigger companies, it is feasible to do it weekly or bi-monthly to avoid chaos at the end of every month.

What are the reasons for the difference in Bank Book and the Bank Statement?

Usually, the reason for the difference is due to non-reporting of the following in Bank Book:

  • Outstanding checks.
  • Deposit in transit.
  • Credits for interest earned.
  • Service charges.

Which Items On A Bank Reconciliation Will Require A Journal Entry?

The items appearing on the bank statement but not reported in books of accounts will require journal entries.

When can it be said that the Bank Reconciliation is complete?

The Bank Reconciliation completes when the balances of the Bank statements and cash account/Bank book matches after passing the adjustment entries.

FAQs

Does Excel have a bank reconciliation 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.

How do I prepare a bank reconciliation statement in Excel? ›

We will be seeing how to prepare a bank reconciliation statement in Excel. So here we are given bank

How do you complete a bank reconciliation statement? ›

Bank reconciliation steps
  1. Get bank records. You need a list of transactions from the bank. ...
  2. Get business records. Open your ledger of income and outgoings. ...
  3. Find your starting point. ...
  4. Run through bank deposits. ...
  5. Check the income on your books. ...
  6. Run through bank withdrawals. ...
  7. Check the expenses on your books. ...
  8. End balance.

How do you prepare a bank reconcile manually? ›

Steps in Preparation of Bank Reconciliation Statement
  1. Check for Uncleared Dues. ...
  2. Compare Debit and Credit Sides. ...
  3. Check for Missed Entries. ...
  4. Correct them. ...
  5. Revise the Entries. ...
  6. Make BRS Accordingly. ...
  7. Add Un-presented Cheques and Deduct Un-credited Cheques. ...
  8. Make Final Changes.

What formula is used in bank reconciliation procedures? ›

A bank reconciliation can be thought of as a formula. The formula is (Cash account balance per your records) plus or minus (reconciling items) = (Bank statement balance). When you have this formula in balance, your bank reconciliation is complete.

What is bank reconciliation PDF? ›

A bank reconciliation is the process of matching the balances in an entity's accounting records for a cash account to the corresponding information on a bank statement .

What is bank reconciliation statement with example? ›

Bank Reconciliation Example – 1

A cheque of $300 was deposited, but not collected by the bank. Bank charges of $50 were recorded in Passbook, but not in Cash Book. Cheques worth $200 were issued, but not presented for payment. Bank interest of $100 was recorded in Passbook, but not in Cash Book.

What are the three methods of a bank reconciliation? ›

There are three steps: comparing your statements, adjusting your balances, and recording the reconciliation.
  • Step one: Comparing your statements. ...
  • Step two: Adjusting your balances. ...
  • Step three: Recording the reconciliation.
10 Aug 2021

What are 4 types of bank reconciliation? ›

Types of Account Reconciliation. Account reconciliations come in various forms and can be for personal or professional use. There are five primary types of account reconciliation: bank reconciliation, vendor reconciliation, business-specific reconciliation, intercompany reconciliation, and customer reconciliation.

How long should a bank reconciliation take? ›

How long does it take to prepare the bank reconciliation? It depends on the number of transactions, but generally, you should be able to do your reconciliation in up to 30 minutes.

Why is my bank reconciliation not balancing? ›

If you are not out of balance for the previous reconciliation the problem is with the CURRENT reconciliation. Check for bank fees, direct debits, un-entered (forgotten) transactions, duplicate entries, or transactions that may have been incorrectly entered. You should also check for any errors on the bank statement.

How do you prepare a reconciliation statement for cost accounting? ›

Step 1. At first, base profit figure, say, profits as per cost account is placed on the credit side of the account and if costing books reveal a loss, it is to be put on the debit side of the memorandum reconciliation account. (d) Under valuation of closing stock in cost accounts. Step 3.

What is bank reconciliation statement and how it is prepared? ›

A bank reconciliation statement summarizes banking and business activity, reconciling an entity's bank account with its financial records. Bank reconciliation statements confirm that payments have been processed and cash collections have been deposited into a bank account.

Is bank reconciliation statement prepared by bank? ›

Bank reconciliation statement is generally prepared by the customer of the bank or the bookkeeper of a company with the purpose to compare the bank's records with the company's records.

How do I convert a PDF file to Excel? ›

Open a PDF file in Acrobat.
  1. Open a PDF file in Acrobat.
  2. Click on the “Export PDF” tool in the right pane.
  3. Choose “spreadsheet” as your export format, and then select “Microsoft Excel Workbook.”
  4. Click “Export.” If your PDF documents contain scanned text, Acrobat will run text recognition automatically.

How do I download a bank statement to a CSV file? ›

Downloading a CSV file:
  1. Log into your online banking.
  2. Select the account you want to download a CSV for.
  3. Find a “view statements” or “view transaction history” or similar tab and click on it.
  4. Insert the date range requested.
  5. Select the CSV option as the "Output" or "Format"
  6. Click "Export", "Save" or "Download"

How can I edit bank statement in PDF? ›

How to edit PDF files:
  1. Open a file in Acrobat.
  2. Click on the “Edit PDF” tool in the right pane.
  3. Use Acrobat editing tools: Add new text, edit text, or update fonts using selections from the Format list. ...
  4. Save your edited PDF: Name your file and click the “Save” button.

How reconciliation is done in Excel? ›

#1 – Match Data Using VLOOKUP Function

The VLOOKUP function is not only used to get the required information from the data table. It can also be used as a reconciliation tool. When reconciling or matching the data, the VLOOKUP formula leads the table. For example, look at the below table.

What are the bank reconciling items examples? ›

Items in a Bank Reconciliation
  • Deposits in transit. Deposits you made after the bank statement was issued have already been recorded in your books.
  • Outstanding checks. ...
  • Non-sufficient funds (NSF). ...
  • Bank fees. ...
  • Interest income. ...
  • Errors.
4 May 2022

What is the difference between bank statement and bank reconciliation? ›

The information on the bank statement is the bank's record of all transactions impacting the entity's bank account during the past month. A bank reconciliation should be completed at regular intervals for all bank accounts, to ensure that a company's cash records are correct.

Why bank reconciliation statement is prepared PDF? ›

BRS is prepared on a periodical basis for checking that bank related transactions are recorded properly in the cash book's bank column and also by the bank in their books. BRS helps to detect errors in recording transactions and determining the exact bank balance as on a specified date.

Is cash a debit or credit account? ›

Whenever cash is received, the Cash account is debited (and another account is credited). Whenever cash is paid out, the Cash account is credited (and another account is debited).

What is balance sheet reconciliation? ›

Balance sheet reconciliation verifies the accuracy of the balance sheet by comparing the numbers on the general ledger to other forms of documentation, to explain any discrepancies. Essentially, reconciliation is done to verify that accounting for a certain period has been accurately portrayed on a company's books.

What is an example of reconciliation? ›

Reconciliation definition

Reconciliation is the act of bringing people together to be friendly again or coming to an agreement. An example of reconciliation is two siblings who mend their relationship after a period of fighting.

What is bank reconciliation in simple words? ›

Bank Reconciliation is an important process in accounting in which organizations match their bank statements with the transactions that are recorded in their general ledger. Preparing a bank reconciliation statement helps businesses to eliminate possible errors in transactions or bookkeeping.

What is BRS in simple words? ›

Bank Reconciliation Statement is a record book of the transactions of a bank account. This statement helps the account holders to check and keep track of their funds and update the transaction record that they have made.

What are the rules of bank reconciliation statement? ›

Bank Reconciliation Statement Rules

Any debit balance in the cash book is referred to as the deposits of the business entity. Debit in cash book is equal to credit in passbook. Credit balance in cash book means unfavorable balance. Debit balance in cash book means favorable balance.

What is the journal entry for bank reconciliation? ›

The journal entries for the bank fees would debit Bank Service Charges and credit Cash. The journal entry for a customer's check that was returned due to insufficient funds will debit Accounts Receivable and will credit Cash.

What two items do you need to reconcile your checking account? ›

Contact the bank and work together to correct the account. When should you reconcile your account?
...
  • compare check record register with the bank statement.
  • compare deposits and withdrawals.
  • enter missing transactions.
  • add missing credits.
  • subtract missing debits.

Who prepares bank reconciliation statement? ›

It is process of matching the balances in a n entity's accounting records for a cash account to the corresponding information on a bank statement. The bank reconciliation statement is prepared by the business enterprises.

How is reconciliation done? ›

There is not a specific method that reconciliation needs to be done in, but in general, it involves comparing your internal accounts to your external accounts, such as reviewing payments and deposits, reviewing bank statements for all outflows and inflows of cash, noting charges that you have no receipts for, and ...

What are the risks of not performing bank reconciliations? ›

Companies that do not perform regular bank reconciliations run the risk of falling victim to fraud, unauthorized withdrawals, or bank errors. If left unchecked, these issues can lead to cash flow leaks that can hamper business operations and growth.

How many days after a month ends should the bank reconciliation be done? ›

At a minimum, the bank reconciliation should be done within a few days after the end of each month. However, with the bank's electronic records readily accessible, the bank reconciliation should be done more frequently.

Is bank reconciliation necessary? ›

Bank reconciliations are an essential internal control tool and are necessary in preventing and detecting fraud. They also help identify accounting and bank errors by providing explanations of the differences between the accounting record's cash balances and the bank balance position per the bank statement.

What are some of the most common errors that can be caught by reconciling a bank statement and check register? ›

Here are some common reconciling errors: You mistakenly cleared transactions that weren't on your statement. You didn't mark all the transactions shown on your statement. You didn't notice an incorrect dollar amount on one or more items.

What are the two most common causes for people not being able to reconcile their bank statements accurately? ›

There are times when a bank statement will fail to accurately reflect a company's transactions. Common causes could be outstanding checks, bank errors or deposits that are still in transit.

What are the errors in bank reconciliation statement? ›

Bank Reconciliation Errors: Examples

Check-printing charges. Customer checks that were returned for insufficient funds. Bank fees for returned checks. Other bank fees, such as safe deposit box fees.

How do I create a bank book in Excel? ›

How to create a Cash Book in Excel - Step by Step Guide [2021] - YouTube

How do you do a Vlookup on a bank reconciliation? ›

How to Use VLookup for Bank Reconciliation - YouTube

How do you reconcile two sets of data in Excel? ›

When comparing two lists of data, select both columns of data, press F5 key on the keyboard, select the “Go to special” dialog box. Then select “Row difference” from the options. Matching cells of data across the rows in the columns are in white color and unmatched cells appear in grey color.

What are the three methods of a bank reconciliation? ›

There are three steps: comparing your statements, adjusting your balances, and recording the reconciliation.
  • Step one: Comparing your statements. ...
  • Step two: Adjusting your balances. ...
  • Step three: Recording the reconciliation.
10 Aug 2021

What is bank reconciliation PDF? ›

A bank reconciliation is the process of matching the balances in an entity's accounting records for a cash account to the corresponding information on a bank statement .

What is reconciliation in Excel? ›

At the end of any accounting period, reconciliation involves matching balances and ensuring that debits (credits) from one account for one transaction is same as the credit (debits) to another account for the same transaction. read more before applying the formula.

How do you find exact match in Excel using VLOOKUP? ›

Exact match - 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP("Smith",A1:B100,2,FALSE).
...
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
  1. =VLOOKUP(A2,A10:C20,2,TRUE)
  2. =VLOOKUP("Fontana",B2:E7,2,FALSE)
  3. =VLOOKUP(A2,'Client Details'!A:F,3,FALSE)

Does VLOOKUP look for an exact match? ›

In most scenarios, exact match is employed when using VLOOKUP, especially when a unique value is the lookup value. Given that the lookup value is available in the leftmost column of the table provided, an exact match is employed in this VLOOKUP example.

How do I match the same data in Excel? ›

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

How do you reconcile supplier balance? ›

Supplier statement reconciliation is an important measure for the accounts payable department to ensure that the general ledger is correct.
...
The process of supplier statement reconciliation
  1. Step 1: Agree on the opening balance. ...
  2. Step 2: Agree to these period entries. ...
  3. Step 3: Assign credits and payments. ...
  4. Step 4: Difference.

How do you prepare a vendor reconciliation? ›

Gather Your Invoices
  1. Gather Your Invoices.
  2. Organize your invoices for the vendor by date. ...
  3. Inspect the Line Items.
  4. Match the line items on your vendor statement to your invoices. ...
  5. Place Check Marks on Matched Items.
  6. Place check marks on your statement when the invoice matches the line item. ...
  7. Highlight any Inconsistencies.

What is vendor balance reconciliation? ›

It is the process of checking the entity's payables to vendor account balance and vendor outstanding balance. Reconciling vendor statements enables a business to ensure no inaccuracy or mistake between what the vendor has charged and the supplies, inventory, or services received by the company.

How do I compare 4 sets of data in Excel? ›

When to use a column chart for comparing data
  1. If you want to compare two to four data series, then use a clustered column chart:
  2. Step-2: Click on 'Select Data' from the drop-down menu:
  3. Step-3: Click on the 'Switch/Row Column' button:
  4. Step-4: Click on the 'OK' button. The column chart will now look like the one below:
27 May 2022

How do I match two lists in Excel? ›

You can use the IF Function to compare two lists in Excel for matches in the same row. If Function will return the value TRUE if the values match and FALSE if they don't.

Top Articles

You might also like

Latest Posts

Article information

Author: Msgr. Benton Quitzon

Last Updated: 10/15/2022

Views: 5351

Rating: 4.2 / 5 (63 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Msgr. Benton Quitzon

Birthday: 2001-08-13

Address: 96487 Kris Cliff, Teresiafurt, WI 95201

Phone: +9418513585781

Job: Senior Designer

Hobby: Calligraphy, Rowing, Vacation, Geocaching, Web surfing, Electronics, Electronics

Introduction: My name is Msgr. Benton Quitzon, I am a comfortable, charming, thankful, happy, adventurous, handsome, precious person who loves writing and wants to share my knowledge and understanding with you.