Bank Reconciliation Statement Template in Excel, OpenOffice Calc & Google Sheet to easily perform bank reconciliation every month.
With the help of this template, you can easily identify and reconcile the difference between Bank Book Journal and Bank Statement. Moreover, it consists of a Bank Reconciliation Statement Printable Format.
Additionally, it also consists of a Bank Reconciliation working sheet when the volume of the bank transactions is high.
What is a Bank Reconciliation Statement?
As per Wiki Bank Reconciliation means:
“In bookkeeping, bank reconciliation is the process by which the bank account balance in an entity’s books of account is reconciled to the balance reported by the financial institution in the most recent bank statement. Any difference between the two figures needs to be examined and, if appropriate, rectified.”
Businesses prepare a bank reconciliation statement to reconcile entries that cause differences between the accounting records and the bank statement.
This reconciliation statement outlines the deposits in transit, unrecorded withdrawals, bank charges, interest, unrecorded payments, etc. for a specific period usually a month.
The Bank Book Ledger of ABC Ltd shows a bank balance of $15,252. Whereas, the bank statement shows $14,200. While reconciling the statement, the accountant finds that a check of $1000 was deposited on the last date of the month and the bank charges $52 as bank charges.
The reconciliation will be as follows:
$14,200 + $1,000 + $52 = $15,252.
Purpose of Bank Reconciliation
Generally, businesses perform bank reconciliation at the end of every month. This frequency depends on the volume of transactions, nature of business, or the size of the organization.
Moreover, based on the above factors some businesses perform monthly, fortnightly(15days), or weekly. In some cases, businesses reconcile bank statements every day.
According to the accounting experts, the best practice to perform reconciliation must be weekly. This allows the businesses to identify and correct entries on time.
Often, when the transactions are recent you can easily recall and rectify them. Moreover, as some businesses perform closing of accounts monthly, the bank reconciliation process is smooth.
The main purpose of a Bank Reconciliation Statement is to identify, resolve, and properly report the difference between bank records and our financial records.
Reconciliation assures that proper reporting of each account is done for every bank transaction. Failing to do so can result in unclear financial reports as well as fraud.
Useful Terms in Bank Reconciliation Process
To properly understand a bank statement, here are some useful terms that bank uses in the statement. Let us understand them in brief.
Direct Bank Transfer
It denotes the transfer of money from our account to the creditor’s/employee’s account.
Overdraft is a facility which a bank provides to its premium customers. It allows the business to pay more than what they deposit. It is an outstanding amount that the business using overdraft owes to the bank.
When a business has an agreement with its creditor to automatically draw out a variable amount of money from its bank account.
The nature of some payments is recurring. In such cases, we use a direct debit. For example, utility bills, contractual buying contracts, BG, and LC.
This is similar to direct debit. Under this, a creditor has an agreement with the firm to draw out a fixed amount of money at a fixed interval. For example, insurance premiums.
A person or business entity writing the cheque.
A person or business to whom the business issues a check.
A check was issued but not yet withdrawn or deposited in the bank.
A check was received but not cleared by the business.
Businesses issue and receive checks. Sometimes, due to insufficient balance, mismatch of signature, or on the special instruction of the account holder, the bank refuses to pay that amount. Such checks are known as dishonored checks.
Banks charge their customers for making different kinds of transactions. These are the service charges that a bank receives against the service they provide. All such charges are known as bank charges.
Bank charges interest on facilities taken by the business. These facilities include loans, overdrafts, letters of credit, bank guarantees, etc. Bank receives interest on these facilities. Bank credits such as interest charges are credited to our account.
Bank pays a stipulated amount of interest keeping money in their banks. Banks debit such interest payments to the business.
Download Bank Reconciliation Statement Template (Excel OpenOffice Calc & Google Sheet)
We have created a simple and easy Bank Reconciliation Statement Template with predefined formulas and functions. Just insert relevant entries to reconcile your bank transactions with the bank book.
Download your desired format and start using it.
Feel free to contact us for the customization of this template as per your requirement. We also design new templates based on your needs. You can hire us for our services on Fiverr or directly contact us at firstname.lastname@example.org.
Contents of Bank Reconciliation Statement Template
This template consists of 3 sheets: Bank Reconciliation Statement, Printable FOrmat for Bank Reconciliation Statement, and Bank Reconciliation Working Sheet.
Let us understand each in detail.
Bank Reconciliation Statement Template
This template consists of 7 sections.
The bank information section consists of the following:
Statement As on
Balance as Per Bank Statement
Insert the bank name and account number. Insert the date on which the statement was printed.
Status cell displays the status of the bank reconciliation. If both balances match, it displays “Reconciled” in green, and, if not, it displays “Mismatch” in red.
Insert the bank balance as per the statement.
Deposit in Transit
If you have multiple deposits, insert the date-wise information in the section as shown in the below image. If you have only one or already have a figure, then directly insert it against Total Deposit in Transit.
Insert date-wise all the outstanding cheques in this section. If it is one or you already have the totals insert directly against Total Outstanding Checks.
Unrecorded Inward Entries
In this section, record all the inward entries which are not recorded in the bank book. It can be interest income, direct payments received, etc.
Unrecorded Outward Entries
In this section, record all the outward entries which are not recorded in the bank book. It can be interest paid, direct payments to vendors, Bank charges, etc.
Usually, banks don’t make any mistakes, but sometimes there can be typographical errors. Often, such mistakes occur when similar entries with minor differences are presented to the bank.
In the summary section, enter the bank balance as per the bank book. The bank reconciliation is complete.
If still there are differences, there are chances of typographical errors in accounting entries in the Bank Book. Thus, enter such entries in this section and check the status of the reconciliation.
Each section has a plus button beside the row number to enter details of each section. It will open the detailed entry section.
Bank Reconciliation Working Sheet
Bank Reconciliation Working Sheet is for those businesses that have a higher volume of transactions. In such organizations, there are multiple entries to calculate under each head.
Insert the date-wise date in this sheet in the respective section. Now, copy the total and paste it into the Bank Reconciliation Statement.
Printable Bank Reconciliation Statement Format
Click on the image to enlarge and print the Printable Bank Reconciliation Statement Format:
Bank Reconciliation Process
- Subtract balance of bank book and balance on the bank statement. This will show the exact amount of difference in both.
- Usually, the balance in the bank statement shows less amount as it doesn’t contain many entries. Thus, write the bank balance on a sheet.
- The first and foremost step in bank reconciliation is to manually match the list of issued checks and deposits with the bank statements. This helps us to know the amount of unpresented checks and deposits in transit.
- Add deposits in transit to the bank balance as per the statement.
- Deduct any outstanding checks.
- Add inward entries not reported in Bank Book.
- Subtract Outward entries not reported in Bank Book.
- Subtract the difference of entries made with the wrong amount by the bank.
- Add the difference of entries made with the wrong amount in the bank book.
That’s it. It’s done.
Benefits of Bank Reconciliation
- Reconciliation makes us aware of the inward and outward spending of our business. It saves us from overdrawing.
- Helps in detecting fraudulent transactions.
- Moreover, helps to keep an eye on unauthorized transactions made by others.
- It also helps to identify typographical errors. Such typographical error by the bank or by the staff can cost us more. Thus, it helps us to eliminate such errors at bay.
- Reconciliation helps to identify and rectify transposition, transposition, transplacement accounting errors.
- Keep an eye on unnecessary charges levied by the bank. Failing to keep account of such unnecessary charges increases the cost of running a business. Therefore, reconciliation helps to highlight the bank expenses we pay every month.
- Apart from this, it also assists in the collection process of accounts receivable. Bank statement helps us to know the dishonored checks. This helps to take action against the client for timely collection.
Frequently Asked Questions
What will be the journal entry for Interest Income on the Bank Statement?
Interest Income Account - Debit
Bank Account - Credit
What will be the journal entry for Interest paid on the Bank Statement?
Bank Account - Debit
Interest Account - Credit
How to make adjustment for wrongly reported amount in the bank statement?
Such transaction has to be notified to the bank for the difference. The bank rectifies the entry in their system. If the reported amount is less than the actual then the bank has to pay. In case of excess entry bank either recovers from the client or has to bear the amount from their pocket.
If you like this article, kindly share it on different social media platforms. So that your friends and colleagues can also benefit from the same. Sharing is Caring.
Moreover, send us your queries or suggestions in the comment section below. We will be more than happy to assist you.