Want to learn identifying discrepancies in your bank reconciliation?
Read on.
Written By Kevin Mil - Author/Founder
How do you find discrepancy in bank reconciliation?
Identifying bank reconciliation discrepancies in Excel is easy if you already know what you are doing.
One powerful tool that can help streamline this process is the use of PIVOT tables.
By leveraging PIVOT tables, you can quickly analyze and compare data from your bank statements and accounting records.
Later in this article, we will dive deeper into how to effectively utilize PIVOT tables for bank reconciliation, providing step-by-step instructions to make the process more efficient and accurate.
While a full bank reconciliation involves several processes, this discussion will focus specifically on identifying discrepancies.
Rather than providing a comprehensive tutorial, we will concentrate on key steps such as verifying the starting balances in the checkbook and bank statement.
Following these steps, you can effectively pinpoint any differences between your company’s records and the bank statement:
1. Review Reconciling Items from Prior Periods and Combine it to Current Transactions
When reconciling disbursements from book to bank to identify outstanding checks and outstanding disbursements, it is essential to incorporate all reconciling items from the previous bank reconciliation.
This step involves listing down all outstanding checks and outstanding disbursements from the prior month and integrating them into the current month’s disbursements.
Typically, the current month’s disbursements are reflected as credits in the Cash in Bank account for the given month.
Book Disbursements = Outstanding Checks/Disbursements(Bank Reconciling Items) + Current month’s disbursement.
Bank Disbursements=Unrecorded Transactions(Book reconciling Items) + Current month’s Disbursements
2. Compare Bank Statement to Company Records (Prior/Current Period)
After preparing your working paper, you can now use the PIVOT table in Excel to reconcile both records.
To do this, you first need to combine both records into a single table.
Once you have merged the records into a unified table, you can proceed with using the PIVOT table to perform the reconciliation process.
Take a look at the example below to see how this is done in practice.
Notice that transactions from bank statement is in negative amounts.
Now that the data is prepared, it’s time to create a Pivot Table in Excel to analyze it effectively.
To begin, select the data range and insert a new Pivot Table.
Next, arrange the fields in the Pivot Table to match the desired layout shown below.
This structure will allow for a clear and meaningful analysis of the data.
Once the Pivot Table is set up correctly, you can explore the insights it reveals and draw conclusions from the organized information.
It is crucial not to omit the reference column when creating a bank reconciliation in Excel using a PIVOT table.
This column serves as the key identifier that the PIVOT table uses to locate and highlight any discrepancies between your records and the bank statement.
You enable the PIVOT table to effectively pinpoint and bring attention to any mismatches or errors in your bank reconciliation process.
Ensuring the reference column is present allows for a more efficient and accurate reconciliation, making it easier to identify and resolve any issues.
3. Examine Canceled Checks and Deposit Slips
- Verifying that payees and amounts on canceled checks match your records.
- Confirming that deposits were processed correctly by the bank and recorded accurately in your books.
In the examples discussed earlier, we focused on disbursement transactions.
However, it’s important to note that you can also apply the PIVOT method to other types of transactions.
When doing so, it’s crucial to ensure that the references used are unique, as these will serve as the basis for reconciling cancelled checks and deposits.
Maintaining distinct references, you can streamline the reconciliation process and avoid any potential confusion or discrepancies.
4. Scrutinize Bank Fees, Interest, and Adjustments
- Validating that any bank charges, such as monthly fees or wire transfer costs, are legitimate and expected
- Recording any missing transactions, such as interest earned or adjustments made by the bank
Thoroughly examining these key areas, you can identify discrepancies in your bank reconciliation and take the necessary steps to resolve them.
Regularly performing these steps will help you maintain accurate financial records, identify errors or fraudulent activity, and ensure that your company’s books align with the bank’s records.
Staying vigilant and addressing discrepancies promptly, you can avoid potential financial issues and maintain the integrity of your accounting processes.