Reporting
Production reporting consolidation
A major Australian rail operator, resurfacing operations
The situation
Every resurfacing machine logged its production on an activity sheet. The sheets worked, in the sense that the data existed. But looking up what happened across a season meant opening production reports one at a time, and the data inside them varied enough between operators that comparing it was unreliable.
Two problems, and the second could not be fixed without the first: you cannot consolidate data that was not captured consistently.
What we built
A replacement template, then the consolidation layer it made possible.
- Data validation lists, conditional formatting and inline error checking, so the sheet resists being filled in wrongly rather than merely recording that it was.
- A structure designed from the outset to be machine-readable, not just human-readable.
- A Power Query model that loads every activity sheet into a single consolidated production history.
The result
Historic production is now a query rather than an excavation. Because the consolidated data is clean, the team can report on it directly, including the measures that were previously too laborious to track: average production rates, and lost time attributable to breakdowns.