002
Work Reporting consolidation
Fourteen spreadsheets, four definitions of revenue
- Source spreadsheets retired
- from 14to 0
- Days to close the month
- from 9to 2
- Metric definitions, agreed & documented
- 31
The situation
A specialty healthcare provider with six clinics. Board reporting was assembled by hand each month from fourteen spreadsheets, each maintained by a different department, each with its own copy of the rules.
The brief we were given was “build us a dashboard.” That was not the problem.
What we found
Revenue differed across departments by as much as 11% in a given month. We traced every difference and found that none of them were arithmetic errors.
Billing counted revenue when a claim was submitted. Finance counted it when cash cleared. Operations counted the contracted rate at time of service, ignoring adjustments. Clinical reporting counted procedures and applied an average.
Each definition was correct for the question that department was asking. What was missing was any acknowledgment that they were four different measures. A dashboard built on top of this would have picked one silently and made the other three departments distrust it within a month.
We stopped building and spent three weeks on definitions instead.
What we built
The deliverable that mattered was not the warehouse. It was a metric register: a document, agreed line by line with each department head, naming thirty-one measures precisely — including four separate revenue measures, each with its own name, owner, and stated purpose.
Only then did we build. Fivetran for extraction, BigQuery as the warehouse, dbt for the modelling with every metric in the register implemented as a tested model. Where a metric had a legally-required definition, the test asserts it and the build fails if it drifts.
Reporting is Looker Studio — deliberately unambitious tooling, chosen because the client’s analyst already knew it and would still be there after we left.
Where it landed
Month-end close went from nine days to two. All fourteen spreadsheets are retired; the last one took longest because its owner was, reasonably, waiting to be convinced.
The register is now maintained by the client’s own analyst, and has grown by nine metrics since handover. Two of those went through a definition dispute before being added, which is exactly the process working.