In this guide, you will learn how to find outstanding checks in a bank reconciliation process.
By comparing your records with the bank statement, identifying outstanding checks, and reconciling them, you can maintain accurate financial records.
The aim is to calculate the adjusted balances of both depositor’s and the bank’s records.
So let’s dive in and understand the steps involved.
You might also like: Risks of Not Performing Bank Reconciliations
Download an Excel example below.
1. Comparing a Bank Statement to Your Records
To begin, take the bank statement and compare it to your records.
Look for any checks that have been issued and recorded in your records but have not yet cleared the bank or transit payments.
This comparison will help you identify discrepancies that need to be resolved through the bank reconciliation.
2. Identifying Outstanding Checks
Next, focus on identifying outstanding checks.
These are checks that you have issued but have not yet been presented to the bank for payment.
Outstanding checks represent the difference between the cash book balance and the bank’s records.
The cash balance per book is usually lower than the bank’s balance, in this case.
By identifying these checks, you can ensure that your records match the actual balance.
You might also like: What are outstanding checks in bank reconciliation?
3. Checking “Checks Issued” Records
Check the “Checks Issued” records or a similar section in your accounting system, ledger, or check register to determine the exact amount and date of each outstanding check.
This will help you verify and reconcile the outstanding checks against the bank statement.
4. Using Excel’s VLOOKUP Function to Find Cleared Checks
One efficient method to compare your outstanding checks with the cleared checks on the bank statement is by utilizing Excel’s VLOOKUP function.
With VLOOKUP, you can easily search for specific check numbers within the bank statement and determine if they have been cleared.
To use the VLOOKUP function, create a new column next to your list of outstanding checks in your Excel sheet.
In the first cell of the new column, enter the VLOOKUP formula: =VLOOKUP(check number, range of cleared checks on bank statement, column number, FALSE).
Replace “check number” with the cell reference of the check number in your list of outstanding checks.
The “range of cleared checks on bank statement” should be the area of the bank statement that contains the cleared checks, including the check numbers, dates, and amounts.
The “column number” is the column that has the cleared check information (e.g., 2 for dates or 3 for amounts). The “FALSE” argument indicates an exact match.
After entering the formula, drag it down to apply it to all the outstanding checks.
As a result, the VLOOKUP function will indicate whether each outstanding check has been cleared, helping you easily identify which checks require reconciliation.
In the next step, you’ll learn how to calculate outstanding checks in bank reconciliation.
5. Calculating the Total Value of Outstanding Checks
Once you have marked the cleared checks using the VLOOKUP function, calculate the total value of the remaining outstanding checks.
These checks have not been marked as cleared on the bank statement and are still considered outstanding.
Sum up the value of these checks to determine the total outstanding amount.
6. Reconciling Outstanding Checks
Once you have identified and marked the cleared checks using the VLOOKUP function, it’s time to reconcile the outstanding checks.
Compare the remaining outstanding checks from your records to the bank statement’s list of cleared checks.
Ensure that the checks that have been marked as cleared are matched correctly.
If any outstanding checks were not marked as cleared on the bank statement, investigate the reasons for the discrepancy.
It is possible that these checks were not presented to the bank, or there may be an error in recording or processing.
Make the necessary adjustments in your records to ensure accuracy.
By properly accounting for outstanding checks and reconciling them with the bank statement, you can maintain financial integrity and produce reliable financial reports.
Read more: What are Outstanding Checks in Bank Reconciliation?
- What is a Bank Reconciliation Accountant? - November 26, 2023
- 7 Steps to Make a Bank Reconciliation - November 26, 2023
- Analyzing Accounts Receivable in Excel (with example) - November 25, 2023