How to Make Bank Reconciliation Statement in Excel Format

Banking reconciliation statements summarize bank and business activity. Using financial records and bank accounts, they reconcile a company’s finances. If you are looking for some special tricks to create a bank reconciliation statement in Excel format, you’ve come to the right place. There is one way to create a bank reconciliation statement in Excel format. This article will discuss every step of this method to create a bank reconciliation statement in Excel format. Let’s follow the complete guide to learn all of this.

Bank Reconciliation Statement: Overview

Banking reconciliation statements summarize bank and business activity using financial records and bank accounts.

Example: For instance, XYZ Company recorded a balance of $33506 based on its record. But, the bank shows a balance of $96,179 based on its statement. Through investigation, the company identified that the bank has not added deposits in transit and has not deducted outstanding cheques. So, XYZ adjust its statement and referred to the cheques as outstanding cheques and the deposit as deposit in transit.

Procedures in a Bank Reconciliation

In order to reconcile a bank statement, you need to evaluate financial records with bank statements. In the case of bank errors, undeducted for deposits, and unused checks, adjust the balance on the bank statement. Some personal or business accounts often overlooked interest and maintenance fees. They should adjust these differences in the cash account records. Compare the balances after you have made corrections and adjustments. In case the account reconciliation fails, repeat the process.

Why Reconciliation Process Is Carried Out?

The bank carried out the reconciliation process because the depositor’s bank balance and the depositor’s bank statement are not the same. The reason for the errors must be determined and corrected. The following topics are those for which the reconciliation process is carried out.

Deposit Understate: Cheques that have not been presented in a bank are referred to as deposit understated. In this scenario, the business will make the entry in the checkbooks as soon as a check is written, while the bank will make the entry after receiving the check. Consequently, this action alters the balance.
Deposit in Transit: Deposits in transit are another factor that affects the balances on the date of reconciliation. In this case, the business deposited checks or cash and completed the bookkeeping process, but the bank did not credit the account as a result of the banking clearing process. The cause of this is a time lag.
Error in Bank: The amount that the bank incorrectly recorded in the company’s statement.

Advantages of a Bank Reconciliation

Statements of bank reconciliation are useful instruments for spotting fraud. The dishonest behavior can be stopped, for instance, if a check is changed and the resulting payment is more than expected.
Rectifying discrepancies that influence tax reporting is made easier by reconciling bank statements. Without balancing, businesses risk paying taxes that are either too high or too low.
Statements of bank reconciliation also aid in finding mistakes that might harm financial reporting.

In the following section, we will use one effective and tricky method to create a bank statement in Excel. To create a more understandable stock balance sheet, it is necessary to input particulars for a bank statement, prepare a primary outline, adjust the balance as per the depositor, and finally adjust the balance as per the bank statement. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Entering Particulars for Bank Reconciliation Statement in Excel Format

To create a bank reconciliation statement, first of all, we need to know the particulars of the bank statement. Then we have to compare the bank balance as per the depositor with the bank balance of the bank statement. In the following image, we illustrate the particulars which we will use to create a bank reconciliation statement. Based on the following picture, it is evident that the depositor’s bank balance and the depositor’s bank statement are not the same, so we need to identify the reason for errors and adjust them.

Step 2: Preparing the Primary Outline of the Bank Reconciliation Statement

To create a bank reconciliation statement we need to follow some specific rules. At first, we want to make a dataset. To do this we have to follow the following rules.

Firstly, write ‘ABC Bank Ltd.’ in some merged cells at a larger font size, That will make the heading more attractive. Then, enter the date of this statement and input the account number of the person or business organization. Click here to see a screenshot that illustrates what the fields look like.

Step 3: Adjusting Bank Balance as per Depositor

Considering the above details, it is evident that the depositor’s bank balance and the depositor’s bank statement are not the same, so we must determine the reason for the errors and correct them. In this step, we are going to adjust the bank balance as per the depositor. To do this you have to follow the following process.

At first, we need to add the error on the depositor books because the cheque written to pay the creditor was recorded at $300 but written for only $30. So that’s why we need to add $270.
Next, we need to deduct the NSF cheque debited by the bank which is almost $2300.
Then, we have to deduct the bank service charge of $75.

Finally, to calculate the adjusted balance as per depositor, we have to type the following formula.

=C9+C11-C13-C14

Then, press Enter.
Therefore, you will be able to adjust the balance and get the adjusted balance as per the depositor as shown below.

Step 4: Adjust Bank Balance as per Bank Reconciliation Statement in Excel

As the depositor’s bank balance and the depositor’s bank statement are not the same, so we must determine the reason for the errors and adjust them. In this step, we are going to adjust the bank balance as per the bank statement. To do this you have to follow the following process.

First of all, we need to add deposits in transit of $54,700 because deposits in transit are another factor that affects the balances on the date of reconciliation. In this case, the business deposited checks or cash and completed the bookkeeping process, but the bank did not credit the account as a result of the banking clearing process. The cause of this is a time lag.
Next, we have to add a deposit understate of $10000 because the bank has not presented cheques as they have not received them yet.
Then, we need to deduct outstanding cheques of $129,478 because the business company has written these cheques and the bank has to clear these checks.

Finally, to calculate the adjusted balance as per the bank statement, we have to type the following formula.

=C17+C19+C20-C22

Then, press Enter.
Therefore, you will be able to adjust the balance and get the adjusted balance as per the bank statement as shown below.

By following the above-mentioned procedures, finally, we will be able to create a template of bank reconciliation statements as shown below.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.

Bank Reconciliation Statement.xlsx

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create a bank reconciliation statement in Excel. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!

<< Go Back to Excel Reconciliation Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!