Bank statement reconciliation
AdvancedBank reconciliation is a critical month-end accounting task. You compare the bank statement against internal records to identify discrepancies and ensure accurate financial reporting.
You have two data sets:
- Bank Statement (columns A-D): Transactions recorded by the bank
- Internal Records (columns F-I): Transactions recorded in your accounting system
Some transactions appear in one system but not the other. Your job is to identify these discrepancies and calculate the reconciliation totals.
Your tasks:
Status column (K3:K10): For each bank transaction, determine if the reference exists in the internal records. Write a formula that returns "Match" if the bank reference is found in the internal records, or "Missing" if not found.
Bank balance (C13): Calculate the total of all bank statement amounts.
Book balance (C14): Calculate the total of all internal record amounts.
Difference (C15): Calculate the difference between bank and book balances (bank minus book).
Missing items total (C16): Calculate the sum of bank amounts where the status is "Missing" - these are items recorded by the bank but not yet in your books.
Use absolute references where appropriate so formulas can be copied down.
Need some help?
Hint 1
For the status formula, use VLOOKUP to search for the bank reference (column B) within the internal references (column G). Wrap it with ISNA to check if it's not found.
Hint 2
The SUMIF function can sum amounts based on a condition: =SUMIF(criteria_range, criteria, sum_range). Use this to sum amounts where status equals "Missing".
Answer
Exercise
Bank statement reconciliation
AdvancedBank reconciliation is a critical month-end accounting task. You compare the bank statement against internal records to identify discrepancies and ensure accurate financial reporting.
You have two data sets:
- Bank Statement (columns A-D): Transactions recorded by the bank
- Internal Records (columns F-I): Transactions recorded in your accounting system
Some transactions appear in one system but not the other. Your job is to identify these discrepancies and calculate the reconciliation totals.
Your tasks:
Status column (K3:K10): For each bank transaction, determine if the reference exists in the internal records. Write a formula that returns "Match" if the bank reference is found in the internal records, or "Missing" if not found.
Bank balance (C13): Calculate the total of all bank statement amounts.
Book balance (C14): Calculate the total of all internal record amounts.
Difference (C15): Calculate the difference between bank and book balances (bank minus book).
Missing items total (C16): Calculate the sum of bank amounts where the status is "Missing" - these are items recorded by the bank but not yet in your books.
Use absolute references where appropriate so formulas can be copied down.
Need some help?
Hint 1
For the status formula, use VLOOKUP to search for the bank reference (column B) within the internal references (column G). Wrap it with ISNA to check if it's not found.
Hint 2
The SUMIF function can sum amounts based on a condition: =SUMIF(criteria_range, criteria, sum_range). Use this to sum amounts where status equals "Missing".