Calder Manufacturing: four gross margins and one board decision
Calder Manufacturing runs eight plants and reports to the board nine days after month-end. At the last board meeting the plant directors and group finance presented four different figures for gross margin, ranging from 29.8% to 34.2%. Investigation found that freight is absorbed at a standard rate in three plants and at actual cost in the rest, rebate accruals are calculated by hand in spreadsheets, and plant A translates its export sales at a budget rate. The group holds 4,300 duplicate customer records and 900 duplicate vendor records, and 12% of purchase orders are raised without a valid supplier code. A data warehouse programme was paused last year for budget reasons. Two quarters ago the board approved a dividend using a margin figure that was later restated by £2.1m. The CFO wants a single version of the truth before the next board cycle.
1. Diagnose why Calder produces four gross margins. Distinguish the underlying causes from the symptoms, using recognised data quality dimensions.
Read the worked answer
Symptoms versus causes
The four figures are the symptom. Buying a data warehouse would treat the symptom. The causes sit in definitions, ownership and input controls.
Cause 1 - no single definition (consistency). Freight absorbed at standard in three plants and actual elsewhere, and budget FX in plant A, mean the same word describes four calculations. This is a definition failure, not a systems failure. No warehouse can resolve it; only an agreed, signed-off definition can.
Cause 2 - manual calculation (accuracy). Hand-built rebate accruals in spreadsheets are unreviewable and version-prone. There is no audit trail showing who changed what.
Cause 3 - no master data ownership (uniqueness). 4,300 duplicate customers and 900 duplicate vendors mean consolidation, rebate calculation and spend analysis are all unreliable, and they raise fraud and duplicate-payment exposure.
Cause 4 - weak input controls (validity, completeness). 12% of purchase orders without a valid supplier code will land in the wrong cost category or the wrong plant, distorting plant margin directly.
Cause 5 - latency (timeliness). Nine days to report means the board is discussing a closed period with no time to act within it.
Cause 6 - incentives. If plant scorecards are built on plant-chosen definitions, plants are rational to choose the flattering one. Governance must address this, not just the data.
2. Recommend what the CFO should do in the next 90 days, and separately what should be done over 12 months. Be explicit about what the next board pack should contain.
Read the worked answer
First 90 days - governance before technology
- Publish a definitions catalogue for the ten board-level KPIs, starting with gross margin: freight basis, FX basis, rebate accrual method, and what is included in cost of sales. Group financial controller drafts, CFO signs, audit committee notes.
- Reconcile the £2.1m restatement and set out publicly, to the board, what caused it and what has changed. Unresolved prior errors destroy confidence in every subsequent number.
- Centralise master data creation. Freeze new customer and vendor creation to a small central team; run a cleanse-and-merge on the duplicates with a documented rule for which record survives.
- Add one input control - a validation rule blocking purchase orders without a valid supplier code - and report weekly on the 12% until it falls.
- Replace the manual rebate spreadsheets with a single reviewed calculation per plant, signed by the plant finance lead.
Next board pack
- One gross margin, on the agreed definition, with a reconciliation to the previous basis and a one-line explanation of the change.
- A short data quality statement: duplicate records cleared, PO compliance rate, open definitional issues.
- No range of figures presented as equally valid; where a basis is still disputed, say which figure is the board's figure and why.
12 months
Restart the data warehouse with narrow scope - one subject area, the board KPIs - sponsored by the CFO, with named data owners per domain and a monthly data quality scorecard. Add a data steward role. Align plant scorecards to group definitions so that no one benefits from a local variant.