GPF Excel register that does not reconcile
You maintain the GPF subscription register for a J&K department in an Excel workbook. Columns: A Account No, B Name, C Opening Balance, D Subscription, E Withdrawal, F Closing Balance. F2 contains =C2+D2-E2. The total of column F is ₹48,72,350, but the GPF ledger total is ₹48,90,800, a difference of ₹18,450. You notice: some account numbers in column A have a green triangle and are left-aligned; a VLOOKUP from the salary sheet returns #N/A for 23 employees; two employees appear twice because rows were copied from two different sheets; row 47 has a closing balance stored as text with a leading apostrophe; and the formula in F52 refers to C51 instead of C52. The Section Officer wants to overwrite the total with the ledger figure and submit the statement.
1. Diagnose the likely causes of the ₹18,450 difference and rank them by risk.
Read the worked answer
Likely causes and risk ranking:
- Duplicate rows (high risk): two employees entered twice will double-count opening, subscription, withdrawal and closing. This both inflates totals and misstates individual balances.
- Formula error in F52 (high risk): referring to C51 instead of C52 gives a wrong closing balance for that row and a wrong total.
- Text-formatted account numbers or closing balances (medium risk): values stored as text are ignored by SUM or do not match in VLOOKUP, causing #N/A and understated totals.
- #N/A in VLOOKUP (medium risk): 23 employees may be omitted from the salary-based update, leaving stale or missing subscriptions.
- Row 47 apostrophe (low to medium risk): the closing balance is excluded from the total.
Duplicates and formula errors change amounts and should be investigated first; text and lookup failures drop records next.
2. What steps would you take to clean the data and correct the statement without overwriting the total?
Read the worked answer
Work on a copy and keep an audit trail.
- Convert account numbers to a consistent format; use TRIM to remove spaces.
- Convert text amounts with VALUE or Paste Special → Add; remove leading apostrophes.
- Remove duplicates using Account No as the key, after checking which row is correct.
- Fix the F52 formula and fill down; check the full range.
- Replace hard-coded totals with SUM over the exact range or an Excel Table.
- Reconcile each account to the GPF ledger using SUMIF and COUNTIF; investigate the ₹18,450 difference line by line.
- Do not overwrite the total with the ledger figure; make adjusting entries with reasons and keep a signed reconciliation.
3. Recommend three controls for future maintenance of this register.
Read the worked answer
- Data validation and unique keys: validate account numbers and amounts, and use a unique employee or account ID to prevent duplicate rows.
- Protected formulas and structured references: lock formula cells and use an Excel Table with structured references so new rows are included automatically.
- Monthly three-way reconciliation: reconcile the salary sheet, GPF register and ledger every month, with a signed checklist, backup and version control.