Challenge Scenario
During the monthly accounting close, auditors compare general ledger balances recorded internally against external bank statements. Any mismatch indicates a delayed deposit, duplicate charge, or clerical error that requires investigation.
As a Senior Auditor, you are reconciling five account balance records:
- Variance (Column D): Calculate the dollar difference between GL Ledger and Bank Statement:
=B2-C2.
- Reconciliation Status (Column E): If the absolute variance is zero (or less than 0.01), mark as
"MATCHED"; otherwise flag as "DISCREPANCY" using =IF(ABS(D2)<0.01, "MATCHED", "DISCREPANCY").
- Net Total Variance (Cell D8): Sum the total variance across all records using
SUM(D2:D6).
- Total Discrepancies (Cell E8): Count how many records have a discrepancy using
COUNTIF(E2:E6, "DISCREPANCY").
Your Sheet Layout
Understanding the reconciliation worksheet layout:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C6 |
Ref Code, GL Balance, Bank Balance |
Transaction Source |
Internal ledger vs external bank statement |
Read-only account statements. |
D2:D6 |
Variance ($) |
Audit Difference |
=B2-C2 |
Calculates dollar difference between books. |
E2:E6 |
Status |
Audit Classification |
=IF(ABS(D2)<0.01, "MATCHED", "DISCREPANCY") |
Assigns MATCHED vs DISCREPANCY flag. |
D8 |
Net Variance |
Summary |
=SUM(D2:D6) |
Combined net variance (-$300.00). |
E8 |
Total Discrepancies |
Summary |
=COUNTIF(E2:E6, "DISCREPANCY") |
Counts accounts requiring manual investigation (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D6, calculate the dollar variance (= GL Ledger - Bank Statement).
2
In cells E2:E6, assign "MATCHED" if variance is 0, otherwise "DISCREPANCY" using IF and ABS.
3
In cell D8, sum the net variance across all records.
4
In cell E8, count the total number of records with "DISCREPANCY".
Solution & Formula Breakdown
Let's review the audit reconciliation logic:
1. Calculating Dollar Variance (D2:D6)
=B2-C2
- TX-901: $14,500 - $14,500 = $0.00
- TX-902: $8,200 - $8,000 = +$200.00 (GL has more than Bank)
- TX-903: $23,100 - $23,100 = $0.00
- TX-904: $5,400 - $5,900 = -$500.00 (Bank has more than GL)
- TX-905: $11,000 - $11,000 = $0.00
2. Classification with IF and ABS (E2:E6)
=IF(ABS(D2)<0.01, "MATCHED", "DISCREPANCY")
Using ABS(D2) ensures that both positive differences (+200) and negative differences (-500) are treated as discrepancies without needing complex nested conditions.
3. Audit Summary Metrics (D8 & E8)
- Cell D8 (Net Variance):
=SUM(D2:D6) → -$300.00.
- Cell E8 (Discrepancy Count):
=COUNTIF(E2:E6, "DISCREPANCY") → 2 discrepancies.
Using ABS(D2)<0.01 instead of D2=0 protects your formula against floating-point rounding precision errors in financial sheets!