The Spreadsheet That Runs Payroll
A charity with 1,900 employees processes payroll through a commercial payroll engine, but the monthly input file is assembled in a spreadsheet maintained by the payroll officer. The spreadsheet contains 14 linked tabs, several hard-coded rates, and a macro that formats and validates the output. The payroll officer is the only person who understands it; she built it eight years ago and has never documented it. The finance director reviews a summary tab and signs a printout before the file is submitted. In September a tax rate change was applied to one tab but not to a second tab that recalculated the same allowance for a subset of 120 staff, who were overpaid for two months before an employee queried it. The charity's auditors have historically tested payroll by sampling payslips against contracts.
1. Explain why the finance director's review did not operate as a control, and what would make it one.
Read the worked answer
The review is of a summary tab, which is an output of the spreadsheet's own logic. If the logic is wrong, the summary is internally consistent and wrong in the same way. The director is checking that the numbers look plausible, not that they were computed correctly, and he has no independent basis on which to judge - he cannot see the 14 tabs or the hard-coded rates.
To be a control, the review needs an independent expectation. Practical forms: a reconciliation of total gross pay to the prior month plus known changes (joiners, leavers, pay awards, rate changes), with every variance above a threshold explained and evidenced; a change log that the director reads before signing, so a tax rate change appears as a line item and prompts the question 'was it applied everywhere?'; and a second checker who recomputes a sample from source data rather than reading the summary.
The deeper point is that review controls depend on the reviewer knowing what a correct answer looks like. Where the only person who knows is the preparer, review degrades into authorisation.
2. The auditors' payslip-to-contract sample did not detect the error. Assess the adequacy of that test and propose a better one.
Read the worked answer
The sample tests whether individuals are paid according to their contract, which is an accuracy test at the individual level. The September error affected a computed allowance for a subset of 120 staff, so a random sample of, say, 25 payslips would include perhaps one or two of them, and even then the payslip would agree to the contract if the contract rate was correct and the calculation was not.
The test is therefore aimed at the wrong risk. It answers 'are people paid what they should be' but not 'is the population being calculated consistently'.
A better test: obtain the payroll input file and recalculate the full population independently for one month, comparing to output line by line. That is now feasible with 1,900 employees and would have found the second tab immediately, because the allowance would differ for exactly 120 records. Supplement with a test that every rate change in the period was applied to every location where that rate appears - a search of the spreadsheet for the old value would have revealed the missed tab. Auditors should also test the spreadsheet itself: hard-coded constants inside formulas, and whether the macro's validation actually runs.
3. Recommend a remediation path that the charity can afford, and rank the steps.
Read the worked answer
Ranked by risk reduction per unit of effort:
- Remove the hard-coded rates into a single, version-controlled rate table with an effective date, so a rate exists in one place. This is cheap and eliminates the exact failure that occurred.
- Introduce a documented change log and a pre-submission reconciliation to prior month plus known movements, with variances explained. This gives the finance director something real to review within a month.
- Cross-train a second person and require them to run the process in parallel for one cycle, then alternate. This addresses the key-person risk, which is currently the largest unquantified exposure - if the payroll officer leaves, payroll stops.
- Only then consider replacing the spreadsheet with a controlled input tool. This is the most expensive step and the least urgent, because steps 1 to 3 remove most of the risk at a fraction of the cost.
The sequencing matters: the charity's instinct will be to buy a system, which takes a year and leaves the current risks live throughout. The cheap controls can be in place before the next pay run.