Jubilee Specialist Hospital: a 47-sheet payroll nobody owns
Jubilee Specialist Hospital in Kumasi employs 640 staff and runs a monthly payroll of GH₵4.9 million. Payroll is produced in a 47-worksheet Excel workbook built by a management accountant who resigned eight months ago. The workbook hard-codes the 2019 PAYE bands and SSNIT contribution rates; overtime hours are pasted in from ward registers; night-shift allowance is driven by a VLOOKUP on staff ID, and there are 30 duplicate IDs. There is no version control: supervisors email copies to the finance office, and the file lives on a shared drive that IT does not back up. A recent internal review found 12 staff overpaid and 9 underpaid, a net error of GH₵38,000, and could not establish who had changed the allowance rate or when. The HR Director wants the errors fixed quietly before the auditor arrives; the Finance Director wants to buy the payroll module of the hospital's existing accounting system.
1. Assess and rank the risks in the current arrangement, including fraud risk.
Read the worked answer
Rank by combination of likelihood and impact.
- Statutory non-compliance (near-certain, high impact). Tax bands and SSNIT rates frozen at 2019 mean every pay run since the rates changed is wrong. That creates exposure to penalties and interest, and a restatement of amounts due, not just a staff relations problem.
- Undetected manipulation (likely, high impact). The workbook is unprotected on an unbacked shared drive with no version history. Anyone with access can change a rate, an allowance or a bank account number and there is no record. The finding that nobody can say who changed the allowance rate is the same weakness an employee would exploit.
- Systematic mispayment (already occurring, moderate impact). Duplicate staff IDs break the VLOOKUP, so allowances land on the wrong person or nowhere. GH₵38,000 is large enough to be material to staff morale and small enough to have gone unnoticed for months.
- Key-person and continuity risk (certain, high impact). One departed author, no documentation, no backup. A corrupted file means payroll cannot run.
- Integrity of the financial statements (moderate). Payroll is a major cost line; if the computation is unreliable, accruals and provisions are unreliable.
- Audit and conduct risk (rising). The proposal to fix errors quietly is itself a risk. Correcting errors is proper; concealing them from the auditor is not, and looks like a cover-up if discovered later.
The correct characterisation to give management is that this is not a spreadsheet problem, it is an access and accountability problem that happens to live in a spreadsheet.
2. Recommend interim controls to get next month's payroll out safely.
Read the worked answer
Objective: one controlled pay run, not a redesign.
- Freeze the file. Take a hash or checksum of the current workbook, store it read-only with a copy held by internal audit, and make the master file read-only apart from a controlled working copy.
- Strip the calculation out of the variable inputs. Move the PAYE bands and SSNIT rates to a single clearly labelled input sheet, update them from the current statute, and have the Finance Director sign a printed copy of that sheet.
- Two-person rule. No change to rates, bank details or staff master data without a second authorised person; protect the input sheet with a password held by two people, one of whom is not in payroll.
- Fix the master data before the run. Clean the duplicate IDs and run a duplicate bank-account and duplicate-ID report; every employee's bank detail change should be verified against a document, not an email.
- Independent recomputation. Recalculate a random sample of 30 employees by hand from source records, including one night-shift worker and one leaver, and reconcile the total gross-to-net control total to the general ledger.
- Backup and version naming. Nightly backup of the shared drive outside the payroll function, and one retained version per pay run with a clear naming convention.
- Correct GH₵38,000 openly. Post the correction, document it, and disclose it to the audit committee with the remediation plan. Tell the HR Director plainly that correcting quietly is not available.
These controls are deliberately manual because they must work next month. They are not a permanent answer.
3. Advise the Finance Director on the longer-term options: payroll module, standalone payroll package, outsourced payroll, or a rebuilt controlled spreadsheet.
Read the worked answer
Evaluate against four criteria: control, statutory accuracy, integration, and implementation risk.
Payroll module of the existing accounting system. Strongest on integration - the payroll journal posts directly to the ledger with no re-keying, which removes the reconciliation weakness. Usually carries a maintained tax table that the vendor updates when rates change, which addresses the highest-ranked risk. Weakness: implementation effort, the vendor's local support quality is unknown, and hospitals often need shift, overtime and multiple allowance types that generic modules handle clumsily. Ask for a reference site of comparable size, and test a full parallel run on real data before commitment.
Standalone payroll package. Often better at complex shift and allowance rules and quicker to implement, but integration is manual or paid-for, so the reconciliation risk returns in a smaller form. Best if integration can be automated cheaply.
Outsourced payroll. Transfers calculation risk to a specialist with contractual accuracy and confidentiality obligations, and is attractive at 640 staff with no in-house payroll specialist. But the hospital remains responsible for the accuracy of input data, personal data leaves the premises (raising Data Protection Act obligations), and errors are harder to correct quickly. Suitable as a bridge for one to two years while the module is implemented.
Rebuilt controlled spreadsheet. Cheapest and fastest, and acceptable only for analysis and modelling, not for the system of record. Do not choose it.
Recommendation. Go to the payroll module if the vendor demonstrates a comparable hospital reference and a successful parallel run; keep outsourced payroll as a fallback if the parallel run fails. Either way, hire or designate a payroll specialist who owns the process. The root cause was not Excel; it was that nobody owned payroll after the author left.