The summary sheet that is short by one day's sales
Each of twelve monthly sales sheets has the same layout: headers in row 1, daily entries in rows 2 to 32, amounts in column B. A Summary sheet totals them with formulas such as =SUM(Jan!B2:B31) running down column B. The summary shows 148,320 while the regional manager's own total is 152,880, and the gap equals the 4,560 entry recorded for 31 March. Last week a staff member inserted rows on the April sheet and one summary cell now displays #REF!. Another summary cell was replaced with the fixed number 12,500 so it would stop changing. The workbook travels by email as sales_2024_final.xlsx, and three people edit it.
1. Explain the 4,560 discrepancy and the #REF! error, and describe how you would confirm each cause in under a minute per sheet.
Read the worked answer
The gap. =SUM(Jan!B2:B31) covers rows 2 to 31, which is days 1 to 30. Day 31 lives in row 32, so every 31-day month silently drops its last day. March had 4,560 on the 31st; the other 31-day months evidently had no entry that day, which is why only one month's value is missing and the error stayed hidden. Confirmation: select the summary cell and look at the coloured range border on the source sheet, or press Ctrl and bracket to jump to the referenced cells, then compare the last highlighted row with the last data row. The range does not grow when data is appended below row 31, which is why inserting rows on April did not fix April either.
The #REF!. It means the formula referenced cells or a sheet that no longer exists, which fits rows having been inserted and then deleted. Confirmation: Formulas > Trace Precedents or Evaluate Formula shows the broken reference immediately. The hardcoded 12,500 is the most dangerous item, because it looks like a formula result and no audit trail will reveal it.
2. Propose a structure that would have prevented all three faults, and give the formula you would use for a monthly total in the new layout.
Read the worked answer
Collapse the twelve sheets into one flat transaction table on a single sheet, with one row per sale and columns: Date, Month, Region, Rep, Amount. Load it as an Excel Table named Sales, so ranges expand and shrink automatically and no formula ever names a fixed row range.
Monthly total formula: =SUMIFS(Sales[Amount],Sales[Month],$A2)
Region-by-month, filled across and down: =SUMIFS(Sales[Amount],Sales[Month],$A2,Sales[Region],B$1)
Alternatively, build a PivotTable on the table with Month in Rows and Region in Columns; it refreshes from the data and cannot point at a stale range.
Why this fixes the faults: appending a row extends every total automatically (no missing day 31), inserting rows cannot break a structured reference (no #REF!), there is exactly one place to enter data so hardcoded overrides become visible, and reconciliation reduces to comparing the pivot with =SUM(Sales[Amount]), which must agree to the cent.
3. The workbook is emailed between three editors and figures are published monthly. Name three controls you would add and say what each one protects against.
Read the worked answer
- Lock the reporting layer. Protect the summary sheet, leaving only input cells unlocked. This prevents the next person from typing 12,500 over a formula, which is the fault a reviewer can never spot by eye.
- Validate at entry. Data validation on Amount (decimal, greater than or equal to zero) and on Date (within the reporting period), plus a reject rule for blank or duplicate invoice numbers. This stops bad data entering rather than detecting it later.
- A visible reconciliation check. A cell such as =IF(ABS(SummaryTotal-SUM(Sales[Amount]))<0.005,"OK","CHECK") with conditional formatting, reviewed before publication. This catches exactly the class of fault that caused the 4,560 gap.
Two supporting changes: retire email attachments in favour of a shared OneDrive or SharePoint file with version history, and apply a filename and sign-off convention so sales_2024_final_v07 is identifiable as the published one. If a second person signs off the monthly pack against the reconciliation cell, the control is a review, not just a formula.