Today, you’ll learn how to use Excel to do bank reconciliation.
Here are the ways to do bank reconciliation:
1. Prepare the Bank Reconciliation Report
Preparation of the Bank Reconciliation Report is a straightforward process which can be facilitated by Excel.
The use of formulas ensures that accurate amounts are reflected in the report. For instance, the SUM function can be employed to verify or correct totals.
2. Identify Deposits in Transit
To identify deposits in transit, the SUMIF function in Excel proves useful for matching book deposits with corresponding entries in the bank statements.
Utilizing batch numbers for book deposits and bank statement credits expedites the reconciliation process.
After matching, any remaining unmatched deposits are considered deposits in transit.
3. Determine Outstanding Checks
Determining outstanding checks involves comparing checks recorded in the books with those in the bank statement.
The VLOOKUP function in Excel can be employed for this purpose.
After matching, any checks that remain unmatched are identified as outstanding checks.
4. Recognize Bank Errors
Excel can be utilized to recognize bank errors, such as inaccuracies in the recording of cleared checks.
The VLOOKUP function is employed to retrieve values from the bank statement using check numbers, and any differences detected prompt further investigation to identify and rectify bank errors.
5. Detect Recording Errors in the Books
Similar to recognizing bank errors, Excel can be leveraged to detect recording errors in the books.
Using VLOOKUP, discrepancies in the recording of checks, whether overstated or understated, can be identified.
Additionally, the COUNTIF function can be utilized to detect instances of multiple accounting entries for a single check issuance.
6. Record Unrecorded Transactions
Occasionally, an accountant or bookkeeper may overlook recording certain financial transactions, such as unrecorded deposits, interest income, bank charges, fund transfers, and unrecorded checks.
By employing a combination of VLOOKUP, COUNTIF, and SUMIF functions, these unrecorded transactions can be identified and subsequently recorded in the books.
How to make bank reconciliation?
Click here for more in-depth guide of how to make a 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