Hospital Operations & Patient Flow Analysis

I found a predictable bottleneck hiding in plain sight — and two workforce patterns most hospital operations teams don't know exist.

Hospitals generate enormous volumes of operational data with every patient encounter — yet most of it sits idle in administrative systems, never reaching the people whose decisions it could directly inform. Waiting times lengthen. Clinicians are overloaded unevenly. Revenue is generated, but profit is eroded by inefficient workflows. None of this is inevitable; all of it is measurable.

This case study documents a complete, end-to-end data analytics project I delivered using Microsoft Power BI. The subject is NewLife’s outpatient department. The dataset captures individual patient visit records across 11 dimensions, including patient classification, clinical staffing, financial class, hourly arrival patterns, waiting time, treatment time, total revenue, and visit profit.

The result is a single-page interactive dashboard titled Hospital Operations & Patient Flow Analysis, carefully designed visual objects that collectively answer the questions hospital operations directors, clinical leads, and finance teams need answered every single day.

❓ Business Problem

New Life operates at the intersection of two fundamentally misaligned forces. Demand is stochastic, human, and emotionally urgent — patients do not arrive on a schedule, and their clinical needs vary wildly in complexity and duration. Supply, by contrast, is expensive, credentialled, and slow to flex; consultant shifts are negotiated weeks in advance, and the margin for last-minute capacity adjustment is narrow.

When demand spikes and supply is flat, the queue grows. Waiting times rise. Patient satisfaction scores fall. In value-based care environments, falling satisfaction scores reduce reimbursement rates. Overloaded clinicians make more errors. Staff burnout accelerates attrition, which increases recruitment and locum costs. One bottleneck — a predictable Hour 9 surge, for example — can cascade into clinical, financial, and reputational consequences that persist long after the morning rush has cleared.

Most hospitals know this is happening. Few have a reliable way to see it in real time, or even in a clean retrospective view, because the underlying data is scattered across patient administration systems, payroll records, and finance ledgers that were never designed to integrate.

📖 Domain

Healthcare Ops & Finance

📤 Output

14-visual interactive Dashboard

The Five Questions This Dashboard Was Built to Answer

📋 The Data

One clean, structured table. Eleven fields. Each row is one patient visit. No joins, no external feeds — just a well-prepared analytical model ready for Power BI.

Headline Findings

A Power BI dashboard that exposes a predictable staffing bottleneck, a hidden workforce imbalance, and an untapped efficiency advantage — built on hospital patient visit data.

What I Built — Dashboard in 30 seconds

A hospital had detailed records of every patient visit — and no way to see patterns across them. I built a single-page Power BI dashboard with 14 objects.

• 5 KPI cards surfacing visit count, average waiting time, total visit time, total revenue, and profit per visit.
• A dual-axis line chart mapping both patient volume and waiting time against each hour of the day.
• A clustered bar chart comparing Anchor and Locum Doctors across three performance metrics.
• A bar chart showing Revenue Efficiency by doctor type.
• A doughnut chart showing patient volume split.
• An interactive data table; three slicers (Doctor Type, Financial Class, Patient Type).
• A plain-language insight annotation panel with the three key findings written directly on the canvas.

🔍 The Findings

Three findings. Each is surfaced by a specific visual. Each is translated into a specific management action.

Finding 01: Peak Congestion Hits at Hour 9 — Every Day The dual-axis line chart plots patient volume and average waiting time against every hour of the operational day. Both lines spike together at Hour 9. That co-spike is the analytical signal: waiting time doesn't just rise with volume — it overshoots it, meaning the department is absorbing demand reactively rather than being positioned for it in advance.

Finding 02: Anchor Doctors Are Carrying a Disproportionate Load The clustered bar chart compares Anchor and Locum Doctors across three dimensions simultaneously: average waiting time, average treatment time, and average visit profit. Anchor Doctors show higher waiting times — a leading indicator of queue saturation. Their treatment episodes run longer. Their visit profit is lower. The doughnut chart confirms they are also handling the majority of patient volume. This is a systemic resource distribution problem, not an individual performance issue.

Finding 03 Locum Doctors Deliver Higher Revenue Efficiency The bar chart showing Revenue Efficiency by Doctor Type surfaces the dataset's most counterintuitive finding: Locum Doctors generate more revenue per unit of clinical time than their Anchor counterparts. Because Revenue Efficiency is a ratio — not a raw total — this comparison controls for the fact that Anchor Doctors see more patients. Something in the Locum workflow produces better financial efficiency per minute of clinical time. That is a process signal worth investigating and standardising.

✅ Recommendations

Each recommendation is tied directly to a finding, scoped to a specific decision-maker, and designed to produce a measurable result.

REC 01 Redesign shifts to pre-load capacity into the Hour 8–11 surge window Owner: Director of Operations / Workforce Planning Expected impact: 15–25% reduction in peak waiting time. In value-based care models, lower waiting times directly improve HCAHPS patient satisfaction scores — and scores affect reimbursement rates.

REC 02 Set a dynamic workload threshold: when Anchor queues exceed the target, route non-complex cases to available Locums Owner: Clinical Lead / Department Head Expected impact: More equitable workload distribution reduces Anchor Doctor burnout risk. Preventing one avoidable senior clinician departure saves 100–200% of annual salary in recruitment costs.

REC 03 Commission a workflow process review to identify the source of the Locum efficiency advantage — then standardise it Owner: Clinical Quality & Improvement Lead Expected impact: 5–10% improvement in system-wide Revenue Efficiency if best-practice workflow elements are adopted across both doctor types.

REC 04 Use the Financial Class slicer to audit payer-by-payer profit performance and take findings into the next contract negotiation Owner: Finance Director / Revenue Cycle Manager Expected impact: 2–5 percentage point improvement in net patient revenue margin through targeted payer mix optimisation.

⚙️ Technical Approach

Four stages. Each one is a deliberate analytical decision — not just a technical step.

TOOLS & METHODS

🧠 Skills Demonstrated

This project covers the full analytics value chain — from raw data to business decisions. The skills below map directly to what was built, not to a generic competency list.

⚠️ Limitations & What Comes Next

• No date dimension — Cannot confirm whether Hour 9 congestion holds across seasons or days of the week. Adding a calendar table would unlock trend and seasonality analysis.
• No clinical outcomes — Revenue Efficiency and profit metrics are operational and financial only. This analysis makes no claim about clinical care quality.
• Snapshot, not live — Connecting to a live PAS/EPR data source via scheduled refresh in Power BI Service would turn this into a real-time operational monitor.
• Single table scope — A star schema with separate Doctor, Financial Class, and Date dimension tables would support richer cross-dimensional analysis and better model scalability.

Full Case Study — Download PDF

Open to data analyst roles in healthcare and operations analytics—let's connect.