Six business units, six versions of the truth
A spend analysis engagement needs twelve months of accounts payable data from the client's six business units. The extracts arrive: two from SAP, one from Sage, three from spreadsheets maintained locally. Supplier names are spelled five ways. Cost centre codes differ between units. One extract is missing February. Another has totals in a merged row at the bottom of each page.
Two consultants spend a week cleaning in Excel. When the client sends a corrected extract, they do much of it again. When the partner asks whether the cleaned total reconciles with the client's reported spend, the answer takes another afternoon.
Why data cleaning eats engagements
- Client systems produce inconsistent extracts, and every unit is different.
- Cleaning steps are done by hand and not recorded.
- Corrections from the client mean repeating the work.
- Reconciliation to reported figures happens late, if at all.
- Mappings, such as supplier and category, are rebuilt on each engagement.
Clients rarely know how messy their data is. Their own reports come from systems that hide the inconsistencies, and they are surprised when an extract does not reconcile with the figures their finance team presents. The conversation about why takes time too.
What manual cleaning costs
| Issue | Effect |
|---|---|
| Days of consultant time | Budget spent before analysis starts |
| Undocumented changes | Findings hard to defend when challenged |
| Rework on corrected extracts | Deadlines slip |
| No reconciliation | Risk of presenting wrong totals |
When a client's CFO challenges a finding, the team needs to show exactly how their data became the number on the slide.
Undocumented cleaning also makes knowledge fragile. When the consultant who cleaned the data rolls off onto another engagement, nobody else knows which suppliers were merged or which rows were excluded, and any follow-up question from the client becomes an investigation.
How we build cleaning pipelines
- Each extract type is profiled on arrival: columns, formats, periods, gaps and obvious anomalies.
- Cleaning steps are defined once per source, in code or a tool such as Power Query, and documented in plain language.
- Codes, suppliers and categories are mapped to a common structure, with fuzzy matching suggesting duplicates for a consultant to confirm.
- Checks run automatically: row counts, missing periods, totals reconciled to figures the client has provided.
- Every change to the data is logged, so any figure can be traced back to the original extract.
- When a corrected extract arrives, the pipeline reruns in minutes.
- Mappings built on one engagement are kept, where confidentiality allows, as a starting point for the next.
We work alongside your analysts, and the pipelines are built so your team can run and adapt them without us.
We usually build the first pipeline on a live engagement's data, alongside the consultants, so it is useful immediately. The steps are then generalised for the next engagement of the same kind, which is where the time saving really shows.
What the team does with the time
Analysis starts on data that reconciles, with a record of how it got there. Corrected extracts are a minor event, not a lost week.
Consultants spend their time on what the data shows, which is what the client is paying for.
Is this your analysis phase?
- Client extracts take days to clean.
- Cleaning steps live in someone's head.
- Corrected extracts mean starting again.
- Totals are not reconciled to the client's reported figures.
- Mappings are rebuilt on every engagement.