130-plus ambulances.
One dashboard.
Every number audited.
A medical-transportation fleet ran its operations on gut feel and month-old spreadsheets, because nobody trusted the numbers. Here is the full story of building the analytics that leadership now argues from, and of auditing every figure on the way in.
A fleet flying on instruments nobody believed
The operation moved patients across a wide region with 130+ units and six-plus divisions, and managed it all on gut feel and spreadsheets that were a month old by the time anyone read them. The dispatch platform produced its own reports, but its core measure of how hard each ambulance was working came from a hidden calculation nobody could explain or verify. When two numbers disagreed, there was no way to say which one was right, so leadership trusted neither.
The mandate: one set of numbers, current, defined in plain language, and provably tied to the dispatch system underneath.
Modeled from the source, then audited to the digit
- Leadership now works from one live Power BI dashboard built on a protected copy of the dispatch system's data: utilization measured three clearly defined ways, cost per call, labor cost broken down by day and by cost center, and the division-by-division view the dispatch platform never offered.
- The vendor's mystery utilization number is a mystery no longer. I worked out their hidden formula and matched it to four decimal places, so both systems now provably produce the same answer.
- An entire regional program (6 units and 1,300+ trips, silently missing from the numbers for 22 months because of a filter that had been set once and never revisited) was found, restored, and the filter rebuilt so anything new shows up in the numbers by default. A data change can never silently drop a region again.
- Payroll got cleaner too: reconciling 615k+ scheduled hours against 641k+ punched hours surfaced ~24 hours of phantom paid time from a single broken time punch.
- 14 of the fleet's graded units, including the fleet's two largest, had been judged against the wrong performance targets; the grading rules were corrected so every unit is measured against its actual standard.
- All 23 single-crew units had their utilization understated by half because of a flaw in how crew size was counted. The fix changed how a sixth of the fleet is evaluated.
- Every calculation change shipped as a validated before/after comparison reviewed with operations and finance, and every report and definition is written down with a recorded history of every change, so nothing lives only in one person's head.
The discipline is the point: a dashboard nobody trusts is decoration, so every number here was checked against the dispatch system itself before anyone was asked to run the business on it.
In plain terms
Real cost per call
Labor cost by day and by cost center, and cost per call by division: the numbers that decide staffing and pricing, computed the same way every day instead of rebuilt by hand every month.
Decisions on current data
The month-old spreadsheet cycle is gone. The dashboard refreshes from a live copy of the dispatch data, so operations meetings argue about this week, not about whose spreadsheet is fresher.
Views the vendor never built
Division-by-division performance, three ways of measuring utilization side by side, and scheduled hours checked against hours actually worked: questions the dispatch platform's own reports simply couldn't answer.
Read-only by design
All reporting runs against a separate, read-only copy of the data, so no report or mistake can ever touch the live dispatch system that crews depend on mid-shift.
New units, same scorecard
Add a division, a program, or a unit and it lands in the dashboard automatically. The reporting is built so new operations show up in the numbers by default instead of silently falling out.
Definitions that outlive people
Every number on the dashboard has a written, plain-language definition and a running history of every change since it was defined. The numbers survive turnover, including mine.
This client, like every client here, is not named: confidentiality is part of what they pay for. Every figure comes from the engagement's own records, and a reference who'll take your call is available on request.