The Dashboard That Nobody Trusts
A regional clinic network publishes a monthly operations dashboard built from three sources: an appointment system (exported as CSV), a billing database (queried directly), and a hand-maintained spreadsheet of staff headcount. Last month the dashboard reported an average wait time of 18 minutes. The operations director says the true figure is closer to 30. Investigation shows the appointment CSV records wait time only for patients who completed a visit, and roughly 22% of appointments are cancelled or marked 'did not attend' with blank wait fields. The billing query returns one row per invoice, so patients with multiple invoices in a month appear more than once. The headcount spreadsheet was last edited eight weeks ago.
1. Diagnose why the reported average wait time is likely understated, and state what the analyst should verify before correcting it.
Read the worked answer
The wait-time field is populated only for completed visits, so cancelled and 'did not attend' appointments are silently excluded. If those patients disproportionately waited long before abandoning, the completed-only average is biased low. The analyst should not simply assume this - they must check whether blank wait records correlate with longer waits (e.g., compare wait times of appointments cancelled late vs. early) and confirm with the operations director whether 30 minutes includes abandoned patients. The fix is a documented denominator decision: report wait time for completed visits and separately report abandonment rate, rather than blending them into one misleading number.
2. The billing source double-counts patients. Explain the consequence for any per-patient metric and the correct treatment.
Read the worked answer
Any metric expressed per patient (average spend, visits per patient, patient counts) will be inflated or distorted because the grain is invoice, not patient. A patient with three invoices contributes three rows. Before aggregating, the analyst must either aggregate invoices to patient-month grain (summing amounts, counting distinct visit dates) or explicitly define the metric at invoice grain and label it as such. The dashboard should state its grain in a footnote; a metric whose grain is unstated is not trustworthy.
3. Recommend how to handle the stale headcount sheet and what governance change prevents recurrence.
Read the worked answer
The eight-week-old headcount cannot support any per-staff ratio (waits per clinician, cost per employee) without a freshness caveat. Short term: flag the metric as 'as of [date]' and suppress ratios that depend on it. Long term: move headcount into a maintained source with an owner and refresh schedule, and add a data-freshness indicator to the dashboard so consumers can see the age of each input. The governance lesson is that every dashboard input needs an owner, a refresh cadence, and a visible last-updated stamp.