This tutorial will teach you how to automate bank reconciliation in Excel.
It’s a concise guide designed to assist you in preparing a bank reconciliation statement.
You’ll learn how to use PivotTables to streamline your reconciliation process.
We will delve deep into this function to help you enhance your bank reconciliation skills.
I hope you’ll read through this post because I promise you’ll learn how to perform bank reconciliation in Excel quickly and automatically.
Written By Kevin Mil - Author/Founder
Here are some valuable tips to help streamline not only bank reconciliation but also other accounting tasks.
Before we dive in, let’s address the importance of proper data preparation.
It’s a crucial step that ensures your automated system functions smoothly and accurately, minimizing errors and maximizing efficiency.
While automation can save time and effort, a solid foundation is needed, which can only be achieved by correctly organizing and inputting your data.
Now that we’ve covered the importance of data preparation, let’s focus on how to effectively use a pivot table for bank reconciliation.
To get started, you need to prepare your working paper, ensure consistency by using a common reference, and then sort your data using the sorting function.
With these three key steps in mind, I’ll guide you through each one step by step, providing clarity and making it easier to understand.
What is an automated bank reconciliation in Excel?
In Excel, automated bank reconciliation is facilitated through the use of a formula that compares transactions and balances between bank statements and company records, thereby automating the identification of discrepancies, such as missing or duplicate transactions, and enabling quick and accurate updates to financial records.
Here are the steps for automated bank reconciliation in Excel:
1. Working Paper Preparation
The first step in bank reconciliation is to organize your data.
This involves reconciling receipts and disbursements between your company’s cash in bank subsidiary ledger and the related bank statement.
To do this effectively, you should separate the receipts and disbursements.
For example, if you are reconciling receipts, you can differentiate between the receipts recorded in the cash book and those reflected in the bank statement.
To illustrate, you can create two sheets in Excel.
In the first sheet, prepare a table that lists all the receipts recorded in the cash book for a particular month.
In the second sheet, create another table that includes all the receipts found in the bank statement for the same period.
To make the working paper almost complete, start by adding the reconciling items from the previous bank reconciliation.
First, include the deposits in transit from the previous month in the current receipts and all other receipts from the book that should appear in the bank’s records.
Next, add any other reconciling items identified in the previous reconciliation to the receipts recorded by the bank, ensuring these receipts are also reflected in the depositor’s records.
There are other reconciling items you need to consider, such as receipts recorded in the depositor’s records, and those recorded in the bank statement.
I won’t discuss each one in detail, as this post is getting lengthy, and it’s important for you to think through these aspects on your own.
For example, unrecorded deposits/interest should be added to the bank statement receipts, and deposits in transit should be included in the book’s recorded receipts for the month.
The final working paper should look like this:
2. Common Reference
After completing 90% of your working paper to automate bank reconciliation in Excel, you need at least two columns for each sheet.
Remember, we are still reconciling receipts (deposits) at this point.
The first column must include a specific reference you use to record your deposits in your cash book, such as a batch no.
or another common reference.
This reference will be manually encoded to match the receipts found in the bank statement.
As you can observe, fully automating bank reconciliation is not entirely feasible, even with advanced commercial bank reconciliation software.
This limitation is even more pronounced when using Excel.
Despite the sophisticated capabilities of such software, you still need to input data manually for the automation process to work effectively.
Thus, the necessity for manual data entry remains a significant aspect of the reconciliation process.
In this case, you need to add the batch numbers (Common References) in the first column of the receipts table on the bank statement receipts sheet.
This requirement explains why we need at least two columns.
The second column will contain the amount listed in the bank statement.
3. Use the PIVOT table
Now it’s time to use PIVOT to automate our bank reconciliation.
Before diving into that, however, we need to discuss how a pivot table works.
Essentially, whatever field you place in the row of the pivot table will be summarized, and if you drag the amount field into the values area, it will aggregate all the balances accordingly.
Understanding this functionality allows us to leverage pivot tables effectively for our reconciliation process.
To accomplish this, we combine both the book and bank receipts into a single sheet.
Next, we convert the amounts in either the book or bank receipts sheet into negative values.
In this case, we will first convert the receipts from the bank into negative values.
Next, we will create a separate sheet to combine both the book receipts and the bank statement receipts.
By consolidating these receipts into a single sheet, we can then apply a pivot table to expedite the reconciliation process.
Now we apply the PIVOT table and here is the result:
4. Interpret the results
Now it’s time to interpret the results provided by the PIVOT table.
If you examine the PIVOT table, you’ll notice that the “Sum of Amount” column contains both zeroes and other amounts.
The zeroes indicate that those items are fully reconciled.
In contrast, the row labels with balances in the “Sum of Amount” column signify that these items are still unreconciled.
Since we are reconciling receipts, the remaining balances likely represent deposits in transit.
5. Prepare your Automated bank reconciliation in Excel
I know you are happy to have read this post.
However, what you have learned represents just one side of the coin.
As you continue your reconciliation, you will also need to apply these skills when reconciling disbursements, including check issuances, fund transfers, charges, and others.
This task might be easier since banks usually provide check numbers in their statements.
The real challenge, however, lies in reconciling other items that banks do not provide, such as bank charges and fund transfers.
To streamline my bank reconciliation process, I utilized a specific template.
By simply replacing the data within the template and hitting the refresh button, I was able to expedite the process significantly.
Now, it’s your turn to create an automated bank reconciliation in Excel using a similar approach.