A hard-coded fix and an out-of-date FX table
You are a trainee in the group reporting team of a listed retailer with a 31 December year end. The consolidation is built in a cloud spreadsheet shared across four countries. Two days before the board pack is due, an automated check reports that group property, plant and equipment is $1.2m higher than the sum of the subsidiary schedules. A colleague tells you he 'fixed' a similar difference last month by overwriting the affected formulas with hard-coded values; the change was never documented or reviewed. You also notice the exchange rate table for the overseas subsidiary pulls from a public website and the closing rate shown was last refreshed on 27 December. The group financial controller asks you to 'just get the pack out - the audit committee won't look at the detail'. The overseas finance manager has emailed you her login credentials so you can inspect her ledger directly.
1. Identify the ethical and control issues in this situation and set out what you should do, in what order.
Read the worked answer
Issues, in order of severity
- Unexplained $1.2m difference and a hard-coded override. The formula has been replaced by a number, so the control that detects the error has been disabled and the trail is gone. The $1.2m may be an aggregation error, a duplicate schedule or a genuine misstatement; you cannot know until it is traced.
- Stale closing rate. A rate refreshed on 27 December is not the 31 December closing rate. Re-translating the subsidiary's assets and liabilities at the wrong rate misstates the consolidated balance sheet and the translation reserve.
- Pressure to release the pack unreviewed. 'The audit committee won't look at the detail' is not a basis for issuing figures you cannot support; the board pack is the source of the published numbers.
- Using a colleague's login credentials is not acceptable. It breaches access controls and, where personal data is involved, data protection obligations. Legitimate access must be granted to you in your own name.
What to do
- Do not use the credentials; ask the finance manager to grant you read-only access or to run the report for you.
- Rebuild the affected formulas from the source schedules, document the reconciliation of the $1.2m and quantify whether it is material to profit and net assets.
- Obtain the 31 December closing rate from an authoritative source (central bank, or the group's treasury-confirmed rate) and re-translate.
- Report the position, the amount and your proposed resolution to the group financial controller in writing; if you are instructed to release unsupported figures, escalate to the CFO and, if necessary, the audit committee chair. It is not a breach of confidentiality to escalate through the proper channel.
- Record the issue and your conclusions so the auditors can see them.
2. Recommend the technology and process controls that would prevent a recurrence, explaining the benefit of each.
Read the worked answer
| Control | What it prevents | Benefit |
|---|---|---|
| Lock/protect formula cells; allow input only in designated (colour-coded) input areas | Accidental or casual hard-coding of calculated totals | The $1.2m error would have surfaced immediately as an input rejection |
| Version control with change log (who, when, why) | Undocumented overrides | Every adjustment to the consolidation is traceable and reviewable |
| Reconciliation control: group total vs subsidiary schedules, with a named preparer and an independent reviewer | Unreconciled differences being carried forward | Two pairs of eyes; the preparer cannot sign off their own work |
| Automated validation rules (balance sheet balances, intercompany agrees, FX rates present for every entity and every period end) | Stale or missing rates, intercompany mismatches | Exceptions are queued to a person rather than silently absorbed |
| Rates interface from a single controlled treasury source, with a date-stamp displayed | Manual or web-scraped rates | Removes the 27 December problem; the date is visible on the face of the pack |
| Periodic data analytics over the full ledger (duplicate detection, unusual journals, Benford-type tests) | Errors that sampling would miss | Tests 100% of transactions, allowing the team to spend time on judgement rather than extraction |
Point to make: technology moves the risk, it does not remove it. Automated checks only work if the output is reviewed by someone with the skill and authority to challenge it, which is why the review control sits alongside every system control.
3. The controller asks for a one-paragraph note to the CFO explaining why the board pack should be delayed. Draft it.
Read the worked answer
"The group property, plant and equipment total in the draft pack is $1.2m higher than the sum of the subsidiary schedules. A previous difference of this type was addressed by overwriting formulas with hard-coded values, and the audit trail for that change no longer exists; I am therefore unable to confirm whether the $1.2m is a presentational error or a misstatement. Separately, the closing exchange rate applied to the overseas subsidiary was last refreshed on 27 December and will need to be re-run at the 31 December rate, which will change the translated assets, liabilities and translation reserve. I recommend we hold the pack by 48 hours: I can rebuild the affected formulas, complete the reconciliation and re-translate the subsidiary within that time, and the pack will then be supportable to the audit committee and the auditors. Releasing it earlier would mean presenting figures we cannot substantiate, which I am not able to sign off."
Why this framing works: it states the facts and the amounts, gives a specific remedy and timeline rather than simply refusing, and makes clear that the concern is accuracy and auditability, not obstruction. It also escalates in writing, which protects both the trainee and the CFO if the issue later proves material.