MAVEN HOSPITAL CHALLENGE
Setting
THE CHALLENGE
For the Maven Hospital Challenge, you’ll play the role of an Analytics Consultant for Massachusetts General Hospital (MGH). You’ve been asked to build a high-level KPI report for the executive team, based on a subset of patient records. The purpose of the report is to give stakeholders visibility into the hospital’s recent performance, and answer the following questions:
- How many patients have been admitted or readmitted over time?
- How long are patients staying in the hospital, on average?
- How much is the average cost per visit?
- How many procedures are covered by insurance?
The dashboard should scale to accommodate new data over time, but the CEO has asked you to summarize any insights you can derive from the sample provided.
POWER BI
DASHBOARD HOSPITAL CHALLENGE
ENCOUNTERS
The number of visits to Massachusetts General Hospital shows a significant increase in February 2014. This must be a consequence of the Boston Marathon bombing (https://en.wikipedia.org/wiki/Boston_Marathon_bombing). The second notable increase was in 2021 (Covid), but it was smaller than I expected. To distinguish between admissions and readmissions, I used a 30-day period. In doing so, I only looked at the start date and not the reason for admission. The location data of the patients (longitude and latitude) seem to be falsified for privacy reasons, but if the real data were used, they might indicate in which areas certain conditions occur more frequently than in others.You’ve been asked to build a high-level KPI report for the executive team, based on a subset of patient records. The purpose of the report is to give stakeholders visibility into the hospital’s recent performance, and answer the following questions.
DURATION
The duration of the admission shows a wide range. However, the extremes seem to be a result of incorrect input of the end date. Some admissions in 2014 only end in 2019 and 2020. This leads to the peak in the column chart in 2014.
COST
In the sheets for costs and insurance, I have made it possible to show the amounts as totals or averages. This provides interesting insights.
INSURANCE
For a Dutch person, this sheet provides the most striking data. The portion of the costs that the patient has to bear themselves is enormous by Dutch standards. It is also notable that five of the nine insurance companies do not reimburse anything. Only Medicare and Medicaid make a significant contribution to the costs.