Hospital ER: wait times and patient experience

Why I picked this question

I wanted a project outside sales to show I can apply the same thinking to operations. An ER is a good test: every number is a real bottleneck, and the manager needs to know where to act first, not just see averages.

How I approached it

1. Cleaned the raw data in Power Query and kept a log of every fix.
2. Built the KPIs with Pivot Tables: visits, wait time, admission and readmission rates, satisfaction.
3. Created 4 linked Excel dashboards: overview, wait times, satisfaction and patient demographics.
4. Wrote a short executive summary with findings and recommendations.

Cleaning the data first

The raw file had problems that would have given wrong answers if I had gone straight to charts:

The last one was the most important decision. A patient who didn't leave a score isn't an unhappy patient, so filling the blanks with zeros or averages would have distorted the results. Satisfaction figures are based only on patients who actually responded.

What I found

1. The average wait hides the real problem

The average wait is 31 minutes, and it barely changes by department (from about 29 minutes in Gastroenterology to 33 in Cardiology), shift or day of the week. The problem is the tail: 8.81% of patients waited over an hour, and the longest wait was 199 minutes. Some physicians also had noticeably longer average waits, up to 36 minutes.

2. Longer waits didn't mean lower satisfaction

I expected satisfaction to drop as waits got longer. It didn't. Patients who waited over an hour actually scored slightly higher. So cutting wait times alone probably won't fix satisfaction.

3. Satisfaction depends more on the department

Gastroenterology (5.75) and Cardiology (5.55) scored highest. Physiotherapy (4.56), Emergency (4.71) and Neurology (4.71) scored lowest. Only 37.4% of patients who responded gave a score of 7 or higher.

4. Who comes in, and when

The ER sees about 28 visits a day. Mondays are the busiest (1,494 visits) and the night shift sees the most patients. The average patient age is 45, and the split between women and men is almost even.

What I would recommend

1. Track the long-wait group, not just the average. Report the share of patients waiting over 60 minutes every week and look at those cases one by one.
2. Start satisfaction work in Physiotherapy, Emergency and Neurology, which score lowest.
3. Collect more feedback. Most visits had no satisfaction score, so a simple survey at discharge would make this data far more reliable.
4. Staff for volume: plan extra cover for Mondays and night shifts.

What I'd look at next

With staffing data, I could check whether long waits happen when fewer doctors are on shift. I'd also want to know why patients who waited longer scored higher, for example whether they were more serious cases who got more attention once seen.