Data Quality, IT Controls and Reporting Integrity at Larkfield Retail
Larkfield Retail Ltd migrated to a new ERP system in April 20X5. The monthly sales dashboard that feeds the management accounts and the sales bonus scheme is built from a data warehouse loaded overnight. At the September close the general ledger sales balance is £4,286,000 but the warehouse totals £4,200,000. The analyst found that 3% of transactions carried product codes created after the migration and were dropped by a mapping table that was never updated; two supplier master records are duplicated; and the same analyst, who built the mapping, has write access to the production database to 'fix things quickly'. The change log shows no entries for the mapping table since go-live. The CFO wants the accounts closed on the warehouse figure because the difference is 'only 2%', and wants the bonus calculation run before the issue is resolved. The audit committee meets next week.
1. Explain the £86,000 difference and identify the control failures that allowed it to arise.
Read the worked answer
The difference. £4,286,000 - £4,200,000 = £86,000, which is 2.0% of the ledger balance. The stated cause is that new product codes created after go-live were not added to the mapping table, so their transactions were silently dropped during the overnight load. This is a completeness failure in the warehouse, not a ledger error - but the direction has not been proven and the two figures must be reconciled transaction by transaction before either is relied on.
Data quality dimensions affected. Completeness (dropped records); validity and accuracy (unmapped codes); uniqueness (duplicated supplier master records); timeliness (mapping not updated when new codes were created); and consistency between the GL and the warehouse.
Control failures.
- Change management: no link between master-data creation and the ETL mapping, so a new product code can go live without the warehouse being updated; no testing of new codes end to end.
- Access control and segregation of duties: the analyst both builds the mapping and can write to production, which allows unlogged changes and removes independent review.
- Logging and monitoring: the change log shows no entries, so either changes are not logged or logging is not reviewed; neither supports assurance.
- Master data governance: duplicate supplier records indicate no de-duplication or approval control on master data.
- Reconciliation: no automated record-count and value reconciliation between the GL and the warehouse, or the gap would have been detected earlier.
- Key-person dependency: a single analyst holds the knowledge and the access, with no documentation or review.
2. Recommend the remediation and advise the CFO on closing the accounts and running the bonus calculation.
Read the worked answer
Do not close on the warehouse figure and do not run the bonus yet. The warehouse is the less reliable source: it has a known completeness defect. Closing on £4,200,000 would embed an unquantified error in the management accounts and in any bonus based on them.
Remediation sequence.
- Reconcile: extract the GL sales transactions, match to the warehouse at transaction level, and identify every record dropped because of unmapped codes. Quantify the value and confirm whether the £86,000 is fully explained.
- Correct through change control: update the mapping table, test in a non-production environment, obtain sign-off, and migrate the change with a logged, approved release.
- Restrict access: remove the analyst's production write access, give read-only access for analysis, and separate the developer role from production release.
- Backfill: re-run the affected period (April to September) and assess whether earlier management accounts and any interim bonus payments were based on incomplete data.
- Master data: introduce a duplicate check, a single owner, and an approval workflow for new product and supplier codes.
- Monitoring: implement daily record-count and value reconciliations, an exception report for unmapped codes, and a review of the change log.
Bonus. Bonuses calculated on uncertified data create an incentive to leave the defect unresolved and expose directors to clawback and restatement risk. The calculation should be run only after the data is certified, or on the GL figure with a documented adjustment.
Reporting. Record the issue in the management letter, disclose it to the audit committee, and state the remediation owner and timetable. If prior-period figures were affected, consider whether a correction and disclosure are required.
3. Draft three questions the audit committee should ask, with a note on why each matters.
Read the worked answer
Question 1 - Completeness of the data. 'Which reconciliations between the general ledger and the warehouse are performed, by whom, how often, and what exceptions were raised in the last six months?' Why it matters: the £86,000 was found by accident, not by a control. The answer shows whether a detective control exists or whether the committee is relying on individuals noticing problems.
Question 2 - Access and change. 'Who currently has write access to the production database, when was that access last reviewed, and can every change to the mapping table be traced to an approved ticket?' Why it matters: the analyst holds incompatible access and the change log is empty. If changes cannot be traced, the integrity of all reported data, not just sales, is in question.
Question 3 - Scope and consequence. 'Is 3% the complete population of post-migration product codes, and were any bonus payments or management decisions made on data affected by this defect?' Why it matters: the £86,000 is only the visible symptom. The committee needs to know whether the error is bounded, whether prior reporting was wrong, and whether clawback or restatement is required.
A useful supplementary question is: 'Who owns the remediation plan, what is the deadline, and what will the committee see as evidence of completion?' This converts the discussion from explanation to accountability.