Hospital Mortality Analysis in Excel

Introduction

30 Second Summary

When a hospital reports a death rate higher than the national average, the immediate question is whether it signals a genuine patient safety crisis or just statistical noise from a complex patient mix. Answering that question with rigour is what separates professional data analysts from people who simply make charts.

In this project, you will take on the role of a healthcare data analyst investigating a hospital's elevated mortality rate. You'll follow the professional Six-Stage Analytics Framework to determine whether the numbers signal genuine concern, identify root causes, and deliver actionable recommendations to the Chief Medical Officer.

What You'll Build

You'll build a complete Hospital Mortality Analysis Workbook in Excel, structured around the Six-Stage Analytics Framework (Discovery, Exploration, Modelling, Projection, Actionable Insight, Monitoring).

By the end of this project, you'll have:

• A risk-adjusted mortality analysis with statistical significance testing that definitively answers whether the hospital's elevated rate is a real problem or noise.
• An interactive dashboard with PivotCharts, Slicers, and an executive summary that translates statistical findings into clear recommendations a CMO can act on.
• A KPI monitoring framework with traffic-light indicators and escalation pathways that keeps the analysis alive beyond a one-off report.
• Secret Mission: Build a Monte Carlo simulation and dynamic what-if control panel to model the probability distribution of mortality outcomes under different intervention scenarios.

Are there any prerequisites?

You'll need Excel 2016 installed on Windows. Power Query (called Get & Transform in Excel 2016) is built into the Data tab, so no add-ins are required. Basic familiarity with opening Excel and navigating tabs is helpful, but no prior analytics experience is needed.

Stage 1 — Discovery (Problem Framing)

Every analytics project worth doing starts with a question. In our case, the Chief Medical Officer at a 500-bed London teaching hospital has noticed that the 30-day post-surgery mortality rate is 3.2%, compared to a national benchmark of 2.1%.

The Discovery stage of the Six-Stage Analytics Framework forces you to document the problem clearly before touching any data. You will create the analytical brief, assess feasibility, and build the sample datasets that power the rest of the project.

In this step, get ready to:

• Document a structured analytical brief and feasibility assessment in Excel.
• Create three CSV data files with intentional quality issues for later cleaning.
• Save the workbook and data files to your Desktop.

Document the analytical brief

In a real analytics engagement, you would sit down with the decision-maker and capture exactly what question they need answered. Documenting this prevents scope creep and anchors the entire project.

• Open Excel 2016 (it should already be running).
• You will see a blank workbook with a sheet tab at the bottom labelled Sheet1.
• Double-click the Sheet1 tab at the bottom of the screen.
• Type 1_Discovery and press Enter to rename the sheet.

Why name the sheet 1_Discovery?

Each sheet in the workbook maps to one stage of the Six-Stage Analytics Framework. Numbering them keeps the tabs in order and makes the workbook self-documenting for anyone reviewing your work.

• In cell A1, type the heading content below and fill in the rest of the brief exactly as shown. Each row uses column A for the label and column B for the value:

Cell A1: ANALYTICAL BRIEF
Cell A3: Decision to be impacted
Cell B3: Whether to launch a mortality reduction programme
Cell A4: Stakeholder
Cell B4: Chief Medical Officer (CMO)
Cell A5: Key Question
Cell B5: Is the 3.2% vs 2.1% gap a genuine patient safety problem or statistical noise?
Cell A7: Sub-questions
Cell B7: Which departments have the highest mortality?
Cell B8: Which patient groups are most affected?
Cell B9: Is there a time trend (getting worse or improving)?
Cell B10: How much is explained by patient mix vs genuine underperformance?
Cell A12: Success Criteria
Cell B12: Statistically rigorous answer with confidence intervals and risk adjustment
Cell A13: Constraints
Cell B13: Administrative data only (no clinical chart review). 200-patient sample.

What does this brief accomplish?

The brief captures the decision, stakeholder, question, sub-questions, success criteria, and constraints. This is the framework's first checkpoint. Without it, you risk delivering analysis that doesn't actually help anyone make a decision.

Notice the sub-questions. These break the big question into testable parts that guide your modelling approach later.

Frame the analytical problem

Below the brief, you will document what data you have access to, what analytical approach you will take, and what limitations exist. This is the feasibility assessment.

• Starting in cell A16, enter the feasibility assessment below:

Cell A16: FEASIBILITY ASSESSMENT
Cell A18: Available Data
Cell B18: Patient demographics (age, gender, department, risk category)
Cell B19: Surgical records (procedure type, date, surgeon, duration, emergency flag)
Cell B20: 30-day outcomes (survived/deceased, complications, discharge date)
Cell B21: Source: Three separate hospital administrative systems (CSV exports)
Cell A23: Analytical Approach
Cell B23: Indirect standardization for risk adjustment (SMR calculation)
Cell B24: Chi-squared test for statistical significance
Cell B25: 95% confidence intervals to quantify uncertainty
Cell B26: Funnel plots for department-level comparison accounting for volume
Cell A28: Known Limitations
Cell B28: 200-patient sample (12 months of data)
Cell B29: No comorbidity detail beyond broad risk category
Cell B30: Administrative data only - no clinical notes or chart review
Cell B31: Single hospital - findings may not generalise

Why document limitations upfront?

The framework explicitly warns about three pitfalls: generalisability, excluded-variable bias, and misinterpreting causation. Writing them down now ensures your conclusions stay appropriately humble.

• Save the workbook by pressing Ctrl+S.
• In the Save As dialog, navigate to your Desktop.
• Name the file Hospital_Mortality_Analysis.xlsx and click Save.

You should see the file name appear in the Excel title bar at the top of the window.

Sheet tab still says Sheet1?

Make sure you double-clicked directly on the tab text at the bottom of the screen (not the cell area). The tab name should become editable with a blinking cursor. Type 1_Discovery and press Enter.

Help me rename my Excel sheet tab.

Create the sample data files

Real hospital data comes from multiple administrative systems and is never clean. You will create three CSV files that simulate this reality, complete with messy formatting, duplicates, and inconsistencies that you will fix with Power Query in the next step.

What is a CSV file?

A CSV (Comma Separated Values) file stores tabular data as plain text. Each line is a row, and commas separate the columns. CSV is the most common format for exchanging data between systems.

• Open Notepad by pressing the Windows key, typing Notepad, and pressing Enter.
• Copy and paste the entire content below into the empty Notepad window to create Patients.csv:

PatientID,Age,Gender,AdmissionDate,Department,RiskCategory
P001,72,Male,15/03/2023,Cardiac Surgery,High
P002,45,female,03-18-2023,Orthopedics,Low
P003,68,Male,2023/03/20,General Surgery,Medium
P004,55,FEMALE,22/03/2023,Neurosurgery,Medium
P005,82,Male,03-25-2023,Cardiac Surgery,Very High
P006,39,Female,2023/03/28,Urology,Low
P007,61,male,01/04/2023,Orthopedics,Medium
P008,74,Female,04-03-2023,Cardiac Surgery,High
P009,48,,2023/04/05,General Surgery,Low
P010,66,Male,08/04/2023,Neurosurgery,High
P011,58,Female,04-10-2023,Urology,Medium
P012,76,male,12/04/2023,Cardiac Surgery,High
P013,43,Female,2023/04/15,Orthopedics,Low
P014,70,Male,04-17-2023,General Surgery,Medium
P015,63,MALE,19/04/2023,Neurosurgery,Medium
P016,51,Female,2023/04/22,Urology,Low
P017,79,Male,04-24-2023,Cardiac Surgery,Very High
P018,44,female,26/04/2023,Orthopedics,Low
P019,67,Male,2023/04/29,General Surgery,High
P020,54,Female,05-01-2023,Neurosurgery,Medium
P021,71,Male,03/05/2023,cardiac surgery,High
P022,46,FEMALE,05-05-2023,Orthopedics,Low
P023,80,Male,2023/05/08,Cardiac Surgery,Very High
P024,59,Female,10/05/2023,General Surgery,Medium
P025,65,male,05-12-2023,Neurosurgery,
P026,42,Female,2023/05/15,Urology,Low
P027,73,Male,17/05/2023,Cardiac Surgery,High
P028,50,female,05-19-2023,Orthopedics,Medium
P029,69,Male,2023/05/22,General Surgery,High
P030,56,Female,24/05/2023,Neurosurgery,Medium
P031,77,male,05-26-2023,Cardiac Surgery,High
P032,41,Female,2023/05/29,Urology,Low
P033,64,Male,31/05/2023,Orthopedics,Medium
P034,75,MALE,06-02-2023,General Surgery,High
P035,52,Female,2023/06/05,Neurosurgery,Medium
P036,83,Male,07/06/2023,Cardiac Surgery,Very High
P037,47,female,06-09-2023,Urology,Low
P038,62,Male,2023/06/12,Orthopedics,Medium
P039,71,Female,14/06/2023,General Surgery,High
P040,57,Male,06-16-2023,Neurosurgery,Medium
P041,78,FEMALE,2023/06/19,Cardiac Surgery,Very High
P042,44,Male,21/06/2023,Urology,Low
P043,66,female,06-23-2023,Orthopedics,Medium
P044,53,Male,2023/06/26,General Surgery,Low
P045,70,Female,28/06/2023,Neurosurgery,High
P046,49,Male,06-30-2023,Cardiac Surgery,Medium
P047,81,female,2023/07/03,Orthopedics,High
P048,60,Male,05/07/2023,General Surgery,Medium
P049,55,MALE,07-07-2023,Urology,Low
P050,73,Female,2023/07/10,Neurosurgery,High
P051,46,Male,12/07/2023,Cardiac Surgery,Medium
P052,84,female,07-14-2023,Orthopedics,Very High
P053,68,Male,2023/07/17,General Surgery,High
P054,41,Female,19/07/2023,Neurosurgery,Low
P055,75,male,07-21-2023,Urology,High
P056,58,Female,2023/07/24,Cardiac Surgery,Medium
P057,63,Male,26/07/2023,Orthopedics,Medium
P058,72,FEMALE,07-28-2023,General Surgery,High
P059,47,Male,2023/07/31,Neurosurgery,Low
P060,65,Female,02/08/2023,Urology,Medium
P061,79,male,08-04-2023,Cardiac Surgery,Very High
P062,43,Female,2023/08/07,Orthopedics,Low
P063,67,Male,09/08/2023,General Surgery,Medium
P064,54,female,08-11-2023,Neurosurgery,Medium
P065,76,Male,2023/08/14,Cardiac Surgery,High
P066,50,Female,16/08/2023,Urology,Low
P067,61,MALE,08-18-2023,Orthopedics,Medium
P068,74,Female,2023/08/21,General Surgery,High
P069,48,Male,23/08/2023,Neurosurgery,Low
P070,69,female,08-25-2023,Cardiac Surgery,High
P071,56,Male,2023/08/28,Urology,Medium
P072,82,Female,30/08/2023,Orthopedics,Very High
P073,45,male,09-01-2023,General Surgery,Low
P074,71,Female,2023/09/04,Neurosurgery,High
P075,59,Male,06/09/2023,Cardiac Surgery,Medium
P076,64,FEMALE,09-08-2023,Urology,Medium
P077,77,Male,2023/09/11,Orthopedics,High
P078,42,Female,13/09/2023,General Surgery,Low
P079,68,male,09-15-2023,Neurosurgery,Medium
P080,53,Female,2023/09/18,Cardiac Surgery,Medium
P081,80,Male,20/09/2023,Orthopedics,Very High
P082,47,female,09-22-2023,Urology,Low
P083,73,Male,2023/09/25,General Surgery,High
P084,57,Female,27/09/2023,Neurosurgery,Medium
P085,66,MALE,09-29-2023,Cardiac Surgery,High
P086,44,Female,2023/10/02,Orthopedics,Low
P087,75,Male,04/10/2023,General Surgery,High
P088,51,female,10-06-2023,Urology,Medium
P089,69,Male,2023/10/09,Neurosurgery,High
P090,62,Female,11/10/2023,Cardiac Surgery,Medium
P091,78,male,10-13-2023,Orthopedics,High
P092,40,Female,2023/10/16,General Surgery,Low
P093,74,Male,18/10/2023,Neurosurgery,High
P094,55,FEMALE,10-20-2023,Urology,Medium
P095,81,Male,2023/10/23,General Surgery,Very High
P096,49,Female,25/10/2023,Cardiac Surgery,Low
P097,67,male,10-27-2023,Orthopedics,Medium
P098,60,Female,2023/10/30,Neurosurgery,Medium
P099,72,Male,01/11/2023,Urology,High
P100,46,female,11-03-2023,General Surgery,Low
P101,65,Male,2023/11/06,Cardiac Surgery,High
P102,83,Female,08/11/2023,Orthopedics,Very High
P103,52,MALE,11-10-2023,Neurosurgery,Medium
P104,70,Female,2023/11/13,General Surgery,High
P105,43,Male,15/11/2023,Urology,Low
P106,76,female,11-17-2023,Cardiac Surgery,High
P107,58,Male,2023/11/20,Orthopedics,Medium
P108,64,Female,22/11/2023,General Surgery,Medium
P109,79,male,11-24-2023,Neurosurgery,Very High
P110,41,Female,2023/11/27,Urology,Low
P111,71,,29/11/2023,Cardiac Surgery,High
P112,54,Female,12-01-2023,Orthopedics,Medium
P113,68,Male,2023/12/04,General Surgery,High
P114,50,FEMALE,06/12/2023,Neurosurgery,Medium
P115,77,Male,12-08-2023,Cardiac Surgery,Very High
P116,45,Female,2023/12/11,Urology,Low
P117,63,male,13/12/2023,Orthopedics,Medium
P118,73,Female,12-15-2023,General Surgery,High
P119,48,Male,2023/12/18,Neurosurgery,Low
P120,66,Female,20/12/2023,Cardiac Surgery,High
P121,57,MALE,12-22-2023,Urology,Medium
P122,80,Female,2023/12/25,Orthopedics,Very High
P123,42,male,27/12/2023,General Surgery,Low
P124,74,Female,12-29-2023,Neurosurgery,High
P125,51,Male,2023/01/02,Cardiac Surgery,Medium
P126,69,female,04/01/2024,Urology,High
P127,78,Male,01-06-2024,Neurosurgery,Very High
P128,44,Female,2024/01/09,Orthopedics,Low
P129,67,MALE,11/01/2024,General Surgery,Medium
P130,55,Female,01-13-2024,Cardiac Surgery,Medium
P131,82,Male,2024/01/16,Orthopedics,Very High
P132,47,female,18/01/2024,Urology,Low
P133,72,Male,01-20-2024,General Surgery,High
P134,59,Female,2024/01/23,Neurosurgery,Medium
P135,65,Male,25/01/2024,Cardiac Surgery,High
P136,40,FEMALE,01-27-2024,Orthopedics,Low
P137,76,Male,2024/01/30,General Surgery,High
P138,53,Female,01/02/2024,Urology,Medium
P139,70,male,02-03-2024,Neurosurgery,High
P140,48,Female,2024/02/06,Cardiac Surgery,Low
P141,84,Male,08/02/2024,Orthopedics,Very High
P142,61,female,02-10-2024,General Surgery,Medium
P143,75,Male,2024/02/13,Neurosurgery,High
P144,43,Female,15/02/2024,Urology,Low
P145,68,MALE,02-17-2024,Cardiac Surgery,High
P146,56,Female,2024/02/20,Orthopedics,Medium
P147,79,Male,22/02/2024,General Surgery,Very High
P148,50,female,02-24-2024,,Medium
P149,64,Male,2024/02/27,Neurosurgery,Medium
P150,46,Female,29/02/2024,Urology,Low
P151,73,male,03-02-2024,Cardiac Surgery,High
P152,41,Female,2024/03/05,Orthopedics,Low
P153,67,Male,07/03/2024,General Surgery,Medium
P154,58,FEMALE,03-09-2024,Neurosurgery,Medium
P155,81,Male,2024/03/12,Cardiac Surgery,Very High
P156,52,Female,14/03/2024,Urology,Medium
P157,70,male,03-16-2024,Orthopedics,High
P158,45,Female,2024/03/19,General Surgery,Low
P159,77,Male,21/03/2024,Neurosurgery,High
P160,62,female,03-23-2024,Cardiac Surgery,Medium
P161,49,Male,2024/03/26,Urology,Low
P162,74,FEMALE,28/03/2024,Orthopedics,High
P163,66,Male,03-30-2024,General Surgery,High
P164,54,Female,2024/04/02,Neurosurgery,Medium
P165,83,male,04/04/2024,Cardiac Surgery,Very High
P166,47,Female,04-06-2024,Urology,Low
P167,71,Male,2024/04/09,Orthopedics,High
P168,60,Female,11/04/2024,General Surgery,Medium
P169,78,MALE,04-13-2024,Neurosurgery,Very High
P170,43,Female,2024/04/16,Urology,Low
P171,65,male,18/04/2024,Cardiac Surgery,High
P172,56,Female,04-20-2024,Orthopedics,Medium
P173,80,Male,2024/04/23,General Surgery,Very High
P174,51,female,25/04/2024,Neurosurgery,Medium
P175,69,Male,04-27-2024,Cardiac Surgery,High
P176,44,Female,2024/04/30,Urology,Low
P177,76,MALE,02/05/2024,Orthopedics,High
P178,63,Female,05-04-2024,General Surgery,Medium
P179,72,male,2024/05/07,Neurosurgery,High
P180,48,Female,09/05/2024,Cardiac Surgery,Low
P181,85,Male,05-11-2024,Orthopedics,Very High
P182,57,female,2024/05/14,Urology,Medium
P183,67,Male,16/05/2024,General Surgery,Medium
P184,53,FEMALE,05-18-2024,Neurosurgery,Low
P185,74,Male,2024/05/21,Cardiac Surgery,High
P186,41,Female,23/05/2024,Orthopedics,Low
P187,79,male,05-25-2024,General Surgery,High
P188,62,Female,2024/05/28,Urology,Medium
P189,68,Male,30/05/2024,Neurosurgery,High
P190,50,FEMALE,06-01-2024,Cardiac Surgery,Medium
P191,75,Male,2024/06/04,Orthopedics,High
P192,46,female,06/06/2024,General Surgery,Low
P193,81,Male,06-08-2024,Neurosurgery,Very High
P194,59,Female,2024/06/11,Urology,Medium
P195,70,male,13/06/2024,Cardiac Surgery,High
P196,55,Female,06-15-2024,Orthopedics,Medium
P197,64,Male,2024/06/18,General Surgery,Medium
P198,83,FEMALE,20/06/2024,Cardiac Surgery,Very High
P199,47,Male,06-22-2024,Urology,Low
P200,72,Female,2024/06/25,Neurosurgery,High

What makes this data messy?

Look closely and you will spot three deliberate quality issues. Dates appear in three formats (DD/MM/YYYY, MM-DD-YYYY, YYYY/MM/DD). Gender uses inconsistent casing (Male, male, MALE, Female, female, FEMALE). Some cells are blank (missing Gender for P009 and P111, missing Department for P148, missing RiskCategory for P025).

These issues mirror what you encounter with real hospital administrative data. Power Query will clean all of this automatically in Step 2.

• Save this file by clicking File then Save As in Notepad.
• Navigate to your Desktop.
• In the Save as type dropdown, select All Files (*.*).
• In the File name field, type Patients.csv and click Save.

Why select All Files?

If you leave the type as "Text Documents (*.txt)", Notepad appends .txt to the filename. Selecting All Files (*.*) ensures your file saves with the exact .csv extension you typed.

You should see Patients.csv on your Desktop.

• Open a new Notepad window (press Ctrl+N or open Notepad again from the Start menu).
• Copy and paste the content below to create Surgeries.csv:

SurgeryID,PatientID,ProcedureType,SurgeryDate,Surgeon,DurationMins,EmergencyFlag
S001,P001,CABG,2023-03-16,Dr Patel,240,No
S002,P002,Hip Replacement,2023-03-19,Dr Williams,180,No
S003,P003,Colectomy,2023-03-21,Dr Chen,150,No
S004,P004,Craniotomy,2023-03-23,Dr Patel,210,No
S005,P005,CABG,2023-03-26,Dr Smith,280,Yes
S006,P006,Prostatectomy,2023-03-29,Dr Williams,120,No
S007,P007,Knee Replacement,2023-04-02,Dr Chen,160,N
S008,P008,Valve Replacement,2023-04-04,Dr Smith,260,No
S009,P009,Appendectomy,2023-04-06,Dr Patel,90,1
S010,P010,Craniotomy,2023-04-09,Dr Chen,200,No
S011,P011,Cystectomy,2023-04-11,Dr Williams,140,No
S012,P012,CABG,2023-04-13,Dr Smith,250,Y
S013,P013,Knee Replacement,2023-04-16,Dr Chen,155,No
S014,P014,Hernia Repair,2023-04-18,Dr Patel,95,No
S015,P015,Spinal Fusion,2023-04-20,Dr Williams,230,No
S016,P016,Prostatectomy,2023-04-23,Dr Chen,125,No
S017,P017,CABG,2023-04-25,Dr Smith,270,Yes
S018,P018,Hip Replacement,2023-04-27,Dr Williams,175,No
S019,P019,Colectomy,2023-04-30,Dr Patel,145,No
S020,P020,Craniotomy,2023-05-02,Dr Chen,195,No
S021,P021,Valve Replacement,2023-05-04,Dr Smith,255,Y
S022,P022,Knee Replacement,2023-05-06,Dr Williams,165,No
S023,P023,CABG,2023-05-09,Dr Smith,290,1
S024,P024,Hernia Repair,2023-05-11,Dr Patel,100,No
S025,P025,Craniotomy,2023-05-13,Dr Chen,205,No
S026,P026,Cystectomy,2023-05-16,Dr Williams,130,No
S027,P027,CABG,2023-05-18,Dr Smith,245,No
S028,P028,Hip Replacement,2023-05-20,Dr Chen,170,No
S029,P029,Colectomy,2023-05-23,Dr Patel,155,Yes
S030,P030,Spinal Fusion,2023-05-25,Dr Williams,225,No
S031,P031,Valve Replacement,2023-05-27,Dr Smith,265,Y
S032,P032,Prostatectomy,2023-05-30,Dr Chen,115,No
S033,P033,Knee Replacement,2023-06-01,Dr Williams,160,No
S034,P034,Colectomy,2023-06-03,Dr Patel,150,No
S035,P035,Craniotomy,2023-06-06,Dr Chen,190,No
S036,P036,CABG,2023-06-08,Dr Smith,275,1
S037,P037,Prostatectomy,2023-06-10,Dr Williams,120,No
S038,P038,Hip Replacement,2023-06-13,Dr Chen,185,No
S039,P039,Colectomy,2023-06-15,Dr Patel,140,No
S040,P040,Spinal Fusion,2023-06-17,Dr Williams,220,No
S041,P041,CABG,2023-06-20,Dr Smith,260,Yes
S042,P042,Cystectomy,2023-06-22,Dr Chen,135,No
S043,P043,Knee Replacement,2023-06-24,Dr Williams,155,No
S044,P044,Appendectomy,2023-06-27,Dr Patel,85,No
S045,P045,Craniotomy,2023-06-29,Dr Chen,200,No
S046,P046,Valve Replacement,2023-07-01,Dr Smith,240,No
S047,P047,Hip Replacement,2023-07-04,Dr Williams,180,No
S048,P048,Hernia Repair,2023-07-06,Dr Patel,90,No
S049,P049,Prostatectomy,2023-07-08,Dr Chen,125,No
S050,P050,Craniotomy,2023-07-11,Dr Williams,210,Y
S051,P051,CABG,2023-07-13,Dr Smith,235,No
S052,P052,Knee Replacement,2023-07-15,Dr Chen,170,No
S053,P053,Colectomy,2023-07-18,Dr Patel,145,No
S054,P054,Craniotomy,2023-07-20,Dr Williams,185,No
S055,P055,Cystectomy,2023-07-22,Dr Chen,140,No
S056,P056,Valve Replacement,2023-07-25,Dr Smith,250,No
S057,P057,Hip Replacement,2023-07-27,Dr Williams,175,No
S058,P058,Colectomy,2023-07-29,Dr Patel,150,Yes
S059,P059,Craniotomy,2023-08-01,Dr Chen,195,No
S060,P060,Prostatectomy,2023-08-03,Dr Williams,120,No
S061,P061,CABG,2023-08-05,Dr Smith,285,1
S062,P062,Knee Replacement,2023-08-08,Dr Chen,160,No
S063,P063,Hernia Repair,2023-08-10,Dr Patel,95,No
S064,P064,Spinal Fusion,2023-08-12,Dr Williams,230,No
S065,P065,CABG,2023-08-15,Dr Smith,250,No
S066,P066,Cystectomy,2023-08-17,Dr Chen,130,No
S067,P067,Hip Replacement,2023-08-19,Dr Williams,180,No
S068,P068,Colectomy,2023-08-22,Dr Patel,155,Yes
S069,P069,Craniotomy,2023-08-24,Dr Chen,190,No
S070,P070,Valve Replacement,2023-08-26,Dr Smith,260,No
S071,P071,Prostatectomy,2023-08-29,Dr Williams,115,No
S072,P072,Knee Replacement,2023-08-31,Dr Chen,165,No
S073,P073,Appendectomy,2023-09-02,Dr Patel,80,No
S074,P074,Craniotomy,2023-09-05,Dr Williams,205,Y
S075,P075,CABG,2023-09-07,Dr Smith,240,No
S076,P076,Cystectomy,2023-09-09,Dr Chen,135,No
S077,P077,Hip Replacement,2023-09-12,Dr Williams,175,No
S078,P078,Hernia Repair,2023-09-14,Dr Patel,90,No
S079,P079,Spinal Fusion,2023-09-16,Dr Williams,225,No
S080,P080,Valve Replacement,2023-09-19,Dr Smith,245,No
S081,P081,Knee Replacement,2023-09-21,Dr Chen,170,No
S082,P082,Prostatectomy,2023-09-23,Dr Williams,120,No
S083,P083,Colectomy,2023-09-26,Dr Patel,150,No
S084,P084,Craniotomy,2023-09-28,Dr Chen,195,No
S085,P085,CABG,2023-09-30,Dr Smith,255,No
S086,P086,Knee Replacement,2023-10-03,Dr Williams,155,No
S087,P087,Colectomy,2023-10-05,Dr Patel,145,No
S088,P088,Prostatectomy,2023-10-07,Dr Chen,125,No
S089,P089,Craniotomy,2023-10-10,Dr Williams,200,1
S090,P090,Valve Replacement,2023-10-12,Dr Smith,240,No
S091,P091,Hip Replacement,2023-10-14,Dr Chen,180,No
S092,P092,Appendectomy,2023-10-17,Dr Patel,85,No
S093,P093,Craniotomy,2023-10-19,Dr Williams,210,No
S094,P094,Cystectomy,2023-10-21,Dr Chen,130,No
S095,P095,Colectomy,2023-10-24,Dr Patel,155,Yes
S096,P096,CABG,2023-10-26,Dr Smith,230,No
S097,P097,Knee Replacement,2023-10-28,Dr Williams,160,No
S098,P098,Spinal Fusion,2023-10-31,Dr Chen,220,No
S099,P099,Cystectomy,2023-11-02,Dr Williams,135,No
S100,P100,Hernia Repair,2023-11-04,Dr Patel,90,No
S101,P101,CABG,2023-11-07,Dr Smith,250,No
S102,P102,Knee Replacement,2023-11-09,Dr Chen,170,No
S103,P103,Craniotomy,2023-11-11,Dr Williams,195,No
S104,P104,Colectomy,2023-11-14,Dr Patel,150,No
S105,P105,Prostatectomy,2023-11-16,Dr Chen,120,No
S106,P106,Valve Replacement,2023-11-18,Dr Smith,260,Y
S107,P107,Hip Replacement,2023-11-21,Dr Williams,175,No
S108,P108,Hernia Repair,2023-11-23,Dr Patel,95,No
S109,P109,Craniotomy,2023-11-25,Dr Chen,210,1
S110,P110,Cystectomy,2023-11-28,Dr Williams,125,No
S111,P111,CABG,2023-11-30,Dr Smith,270,Yes
S112,P112,Knee Replacement,2023-12-02,Dr Chen,160,No
S113,P113,Colectomy,2023-12-05,Dr Patel,145,No
S114,P114,Spinal Fusion,2023-12-07,Dr Williams,230,No
S115,P115,CABG,2023-12-09,Dr Smith,280,Y
S116,P116,Prostatectomy,2023-12-12,Dr Chen,115,No
S117,P117,Hip Replacement,2023-12-14,Dr Williams,180,No
S118,P118,Colectomy,2023-12-16,Dr Patel,150,No
S119,P119,Craniotomy,2023-12-19,Dr Chen,190,No
S120,P120,Valve Replacement,2023-12-21,Dr Smith,245,No
S121,P121,Cystectomy,2023-12-23,Dr Williams,130,No
S122,P122,Knee Replacement,2023-12-26,Dr Chen,165,No
S123,P123,Appendectomy,2023-12-28,Dr Patel,80,No
S124,P124,Craniotomy,2023-12-30,Dr Williams,200,No
S125,P125,CABG,2024-01-03,Dr Smith,235,No
S126,P126,Cystectomy,2024-01-05,Dr Chen,135,No
S127,P127,Craniotomy,2024-01-07,Dr Williams,215,Yes
S128,P128,Hip Replacement,2024-01-10,Dr Chen,170,No
S129,P129,Hernia Repair,2024-01-12,Dr Patel,95,No
S130,P130,Valve Replacement,2024-01-14,Dr Smith,240,No
S131,P131,Knee Replacement,2024-01-17,Dr Williams,175,No
S132,P132,Prostatectomy,2024-01-19,Dr Chen,120,No
S133,P133,Colectomy,2024-01-21,Dr Patel,150,No
S134,P134,Spinal Fusion,2024-01-24,Dr Williams,225,No
S135,P135,CABG,2024-01-26,Dr Smith,255,No
S136,P136,Knee Replacement,2024-01-28,Dr Chen,155,No
S137,P137,Colectomy,2024-01-31,Dr Patel,145,Yes
S138,P138,Prostatectomy,2024-02-02,Dr Williams,120,No
S139,P139,Craniotomy,2024-02-04,Dr Chen,200,No
S140,P140,CABG,2024-02-07,Dr Smith,230,No
S141,P141,Hip Replacement,2024-02-09,Dr Williams,185,No
S142,P142,Hernia Repair,2024-02-11,Dr Patel,90,No
S143,P143,Craniotomy,2024-02-14,Dr Chen,210,Y
S144,P144,Cystectomy,2024-02-16,Dr Williams,125,No
S145,P145,Valve Replacement,2024-02-18,Dr Smith,260,No
S146,P146,Knee Replacement,2024-02-21,Dr Chen,160,No
S147,P147,Colectomy,2024-02-23,Dr Patel,155,1
S148,P148,Spinal Fusion,2024-02-25,Dr Williams,220,No
S149,P149,Craniotomy,2024-02-28,Dr Chen,195,No
S150,P150,Prostatectomy,2024-03-01,Dr Williams,115,No
S151,P151,CABG,2024-03-03,Dr Smith,270,No
S152,P152,Knee Replacement,2024-03-06,Dr Chen,155,No
S153,P153,Hernia Repair,2024-03-08,Dr Patel,90,No
S154,P154,Craniotomy,2024-03-10,Dr Williams,190,No
S155,P155,CABG,2024-03-13,Dr Smith,285,Yes
S156,P156,Cystectomy,2024-03-15,Dr Chen,130,No
S157,P157,Hip Replacement,2024-03-17,Dr Williams,180,No
S158,P158,Appendectomy,2024-03-20,Dr Patel,85,No
S159,P159,Craniotomy,2024-03-22,Dr Chen,205,No
S160,P160,Valve Replacement,2024-03-24,Dr Smith,240,No
S161,P161,Prostatectomy,2024-03-27,Dr Williams,120,No
S162,P162,Knee Replacement,2024-03-29,Dr Chen,165,No
S163,P163,Colectomy,2024-03-31,Dr Patel,150,No
S164,P164,Spinal Fusion,2024-04-03,Dr Williams,225,No
S165,P165,CABG,2024-04-05,Dr Smith,280,1
S166,P166,Prostatectomy,2024-04-07,Dr Chen,115,No
S167,P167,Hip Replacement,2024-04-10,Dr Williams,175,No
S168,P168,Hernia Repair,2024-04-12,Dr Patel,95,No
S169,P169,Craniotomy,2024-04-14,Dr Chen,215,Y
S170,P170,Cystectomy,2024-04-17,Dr Williams,125,No
S171,P171,Valve Replacement,2024-04-19,Dr Smith,250,No
S172,P172,Knee Replacement,2024-04-21,Dr Chen,160,No
S173,P173,Colectomy,2024-04-24,Dr Patel,155,Yes
S174,P174,Craniotomy,2024-04-26,Dr Williams,190,No
S175,P175,CABG,2024-04-28,Dr Smith,260,No
S176,P176,Prostatectomy,2024-05-01,Dr Chen,120,No
S177,P177,Hip Replacement,2024-05-03,Dr Williams,180,No
S178,P178,Hernia Repair,2024-05-05,Dr Patel,90,No
S179,P179,Craniotomy,2024-05-08,Dr Chen,200,No
S180,P180,CABG,2024-05-10,Dr Smith,230,No
S181,P181,Knee Replacement,2024-05-12,Dr Williams,170,No
S182,P182,Cystectomy,2024-05-15,Dr Chen,135,No
S183,P183,Colectomy,2024-05-17,Dr Patel,145,No
S184,P184,Craniotomy,2024-05-19,Dr Williams,185,No
S185,P185,Valve Replacement,2024-05-22,Dr Smith,255,No
S186,P186,Knee Replacement,2024-05-24,Dr Chen,155,No
S187,P187,Colectomy,2024-05-26,Dr Patel,150,1
S188,P188,Prostatectomy,2024-05-29,Dr Williams,120,No
S189,P189,Craniotomy,2024-05-31,Dr Chen,205,No
S190,P190,CABG,2024-06-02,Dr Smith,235,No
S191,P191,Hip Replacement,2024-06-05,Dr Williams,180,No
S192,P192,Appendectomy,2024-06-07,Dr Patel,80,No
S193,P193,Craniotomy,2024-06-09,Dr Chen,210,Y
S194,P194,Cystectomy,2024-06-12,Dr Williams,130,No
S195,P195,Valve Replacement,2024-06-14,Dr Smith,260,No
S196,P196,Knee Replacement,2024-06-16,Dr Chen,160,No
S197,P197,Hernia Repair,2024-06-19,Dr Patel,90,No
S198,P198,CABG,2024-06-21,Dr Smith,275,Yes
S199,P199,Prostatectomy,2024-06-23,Dr Williams,115,No
S200,P200,Craniotomy,2024-06-26,Dr Chen,200,No
S005,P005,CABG,2023-03-26,Dr Smith,280,Yes
S012,P012,CABG,2023-04-13,Dr Smith,250,Y
S023,P023,CABG,2023-05-09,Dr Smith,290,1
S031,P031,Valve Replacement,2023-05-27,Dr Smith,265,Y
S061,P061,CABG,2023-08-05,Dr Smith,285,1
S095,P095,Colectomy,2023-10-24,Dr Patel,155,Yes
S109,P109,Craniotomy,2023-11-25,Dr Chen,210,1
S127,P127,Craniotomy,2024-01-07,Dr Williams,215,Yes

What messiness is in this file?

Two issues to spot. First, the EmergencyFlag column uses inconsistent values: "Yes", "Y", "1" all mean the same thing, and "No", "N" mean the opposite. Second, the last 8 rows are exact duplicates of earlier rows. Power Query will remove duplicates and standardize these flags.

• Save this file using the same method: File then Save As, set type to All Files (*.*), name it Surgeries.csv, and save to your Desktop.
• Open a new Notepad window again for the final file.
• Copy and paste the content below to create Outcomes.csv:

PatientID,Outcome,DaysPostSurgery,Complications,DischargeDate
P001,Survived,8,None,2023-03-24
P002,Survived,5,None,2023-03-24
P003,Survived,7,Infection,2023-03-28
P004,Survived,9,None,2023-04-01
P005,Deceased,4,Cardiac Arrest,
P006,Survived,4,None,2023-04-02
P007,Survived,6,None,2023-04-08
P008,Survived,10,Bleeding,2023-04-14
P009,Survived,3,None,2023-04-09
P010,Survived,8,None,2023-04-17
P011,Survived,5,None,2023-04-16
P012,Deceased,6,Cardiac Arrest,
P013,Survived,4,None,2023-04-20
P014,Survived,7,None,2023-04-25
P015,Survived,9,None,2023-04-29
P016,Survived,4,None,2023-04-27
P017,Survived,12,Pneumonia,2023-05-07
P018,Survived,5,None,2023-05-01
P019,Survived,7,Infection,2023-05-07
P020,Survived,6,None,2023-05-08
P021,Survived,9,None,2023-05-13
P022,Survived,4,None,2023-05-10
P023,Deceased,3,Cardiac Arrest,
P024,Survived,6,None,2023-05-17
P025,Survived,8,None,2023-05-21
P026,Survived,4,None,2023-05-20
P027,Survived,10,Bleeding,2023-05-28
P028,Survived,5,None,2023-05-25
P029,Survived,7,Infection,2023-05-30
P030,Survived,6,None,2023-05-31
P031,Deceased,5,Cardiac Arrest,
P032,Survived,4,None,2023-06-03
P033,Survived,6,None,2023-06-07
P034,Survived,8,None,2023-06-11
P035,Survived,7,None,2023-06-13
P036,Survived,11,Pneumonia,2023-06-19
P037,Survived,4,None,2023-06-14
P038,Survived,5,None,2023-06-18
P039,Survived,7,Infection,2023-06-22
P040,Survived,6,None,2023-06-23
P041,Survived,9,Bleeding,2023-06-29
P042,Survived,4,None,2023-06-26
P043,Survived,5,None,2023-06-29
P044,Survived,3,None,2023-06-30
P045,Survived,8,None,2023-07-07
P046,Survived,7,None,2023-07-08
P047,Survived,6,DVT,2023-07-10
P048,Survived,5,None,2023-07-11
P049,Survived,4,None,2023-07-12
P050,Survived,9,None,2023-07-20
P051,Survived,6,None,2023-07-19
P052,Deceased,7,Sepsis,
P053,Survived,8,Infection,2023-07-26
P054,Survived,5,None,2023-07-25
P055,Survived,7,None,2023-07-29
P056,Survived,6,None,2023-07-31
P057,Survived,5,None,2023-08-01
P058,Survived,9,Pneumonia,2023-08-07
P059,Survived,4,None,2023-08-05
P060,Survived,5,None,2023-08-08
P061,Survived,10,Bleeding,2023-08-15
P062,Survived,4,None,2023-08-12
P063,Survived,6,None,2023-08-16
P064,Survived,7,None,2023-08-19
P065,Survived,9,None,2023-08-24
P066,Survived,4,None,2023-08-21
P067,Survived,5,None,2023-08-24
P068,Survived,8,Infection,2023-08-30
P069,Survived,4,None,2023-08-28
P070,Survived,7,None,2023-09-02
P071,Survived,5,None,2023-09-03
P072,Survived,6,DVT,2023-09-06
P073,Survived,3,None,2023-09-05
P074,Survived,8,None,2023-09-13
P075,Survived,7,None,2023-09-14
P076,Survived,5,None,2023-09-14
P077,Survived,6,None,2023-09-18
P078,Survived,4,None,2023-09-18
P079,Survived,7,None,2023-09-23
P080,Survived,6,None,2023-09-25
P081,Survived,5,None,2023-09-26
P082,Survived,4,None,2023-09-27
P083,Survived,8,Infection,2023-10-04
P084,Survived,6,None,2023-10-04
P085,Survived,7,None,2023-10-07
P086,Survived,4,None,2023-10-07
P087,Survived,9,Pneumonia,2023-10-15
P088,Survived,5,None,2023-10-12
P089,Survived,8,None,2023-10-18
P090,Survived,6,None,2023-10-18
P091,Survived,7,None,2023-10-21
P092,Survived,3,None,2023-10-20
P093,Survived,9,None,2023-10-28
P094,Survived,5,None,2023-10-26
P095,Deceased,6,Sepsis,
P096,Survived,5,None,2023-10-31
P097,Survived,6,None,2023-11-03
P098,Survived,7,None,2023-11-07
P099,Survived,8,None,2023-11-10
P100,Survived,4,None,2023-11-08
P101,Survived,9,Bleeding,2023-11-16
P102,Survived,6,DVT,2023-11-15
P103,Survived,7,None,2023-11-18
P104,Survived,8,Infection,2023-11-22
P105,Survived,4,None,2023-11-20
P106,Survived,10,Pneumonia,2023-11-28
P107,Survived,5,None,2023-11-26
P108,Survived,6,None,2023-11-29
P109,Survived,9,None,2023-12-04
P110,Survived,4,None,2023-12-02
P111,Survived,8,Bleeding,2023-12-08
P112,Survived,5,None,2023-12-07
P113,Survived,7,None,2023-12-12
P114,Survived,6,None,2023-12-13
P115,Survived,11,Pneumonia,2023-12-20
P116,Survived,4,None,2023-12-16
P117,Survived,5,None,2023-12-19
P118,Survived,8,Infection,2023-12-24
P119,Survived,4,None,2023-12-23
P120,Survived,7,None,2023-12-28
P121,Survived,5,None,2023-12-28
P122,Survived,6,None,2023-12-31
P123,Survived,3,None,2023-12-31
P124,Survived,8,None,2024-01-07
P125,Survived,6,None,2024-01-09
P126,Survived,5,None,2024-01-10
P127,Deceased,3,Cardiac Arrest,
P128,Survived,4,None,2024-01-14
P129,Survived,6,None,2024-01-18
P130,Survived,7,None,2024-01-21
P131,Survived,6,DVT,2024-01-23
P132,Survived,4,None,2024-01-23
P133,Survived,8,Infection,2024-01-29
P134,Survived,7,None,2024-01-31
P135,Survived,9,None,2024-02-04
P136,Survived,4,None,2024-02-01
P137,Survived,8,None,2024-02-08
P138,Survived,5,None,2024-02-07
P139,Survived,7,None,2024-02-11
P140,Survived,5,None,2024-02-12
P141,Survived,6,None,2024-02-15
P142,Survived,5,None,2024-02-16
P143,Survived,9,None,2024-02-23
P144,Survived,4,None,2024-02-20
P145,Survived,8,Bleeding,2024-02-26
P146,Survived,5,None,2024-02-26
P147,Survived,7,Infection,2024-03-01
P148,Survived,6,None,2024-03-02
P149,Survived,7,None,2024-03-06
P150,Survived,4,None,2024-03-05
P151,Survived,9,None,2024-03-12
P152,Survived,4,None,2024-03-10
P153,Survived,6,None,2024-03-14
P154,Survived,7,None,2024-03-17
P155,Survived,11,Pneumonia,2024-03-23
P156,Survived,5,None,2024-03-20
P157,Survived,6,None,2024-03-23
P158,Survived,3,None,2024-03-23
P159,Survived,8,None,2024-03-30
P160,Survived,6,None,2024-03-30
P161,Survived,4,None,2024-03-31
P162,Survived,7,None,2024-04-04
P163,Survived,8,Infection,2024-04-08
P164,Survived,6,None,2024-04-09
P165,Deceased,5,Cardiac Arrest,
P166,Survived,4,None,2024-04-11
P167,Survived,6,None,2024-04-16
P168,Survived,5,None,2024-04-17
P169,Survived,9,None,2024-04-22
P170,Survived,4,None,2024-04-21
P171,Survived,8,Bleeding,2024-04-27
P172,Survived,5,None,2024-04-26
P173,Survived,7,Infection,2024-04-30
P174,Survived,6,None,2024-05-02
P175,Survived,9,None,2024-05-07
P176,Survived,4,None,2024-05-05
P177,Survived,7,None,2024-05-10
P178,Survived,5,None,2024-05-10
P179,Survived,8,None,2024-05-16
P180,Survived,5,None,2024-05-15
P181,Survived,6,DVT,2024-05-18
P182,Survived,5,None,2024-05-20
P183,Survived,6,None,2024-05-23
P184,Survived,4,None,2024-05-23
P185,Survived,9,Pneumonia,2024-05-31
P186,Survived,4,None,2024-05-28
P187,Survived,8,Infection,2024-06-03
P188,Survived,5,None,2024-06-03
P189,Survived,7,None,2024-06-07
P190,Survived,6,None,2024-06-08
P191,Survived,7,None,2024-06-12
P192,Survived,3,None,2024-06-11
P193,Survived,9,None,2024-06-18
P194,Survived,5,None,2024-06-17
P195,Survived,8,None,2024-06-22
P196,Survived,5,None,2024-06-21
P197,Survived,6,None,2024-06-25
P198,Survived,11,Pneumonia,2024-07-02
P199,Survived,4,None,2024-06-27
P200,Survived,8,None,2024-07-04

Where are the deaths concentrated?

Count the "Deceased" entries: P005, P012, P023, P031, P052, P095, P127, and P165. That is 7 deaths out of 200 patients (3.5%), close to the hospital's reported 3.2% rate.

Notice that 5 of the 7 are in Cardiac Surgery (P005, P012, P023, P031, P165), all with High or Very High risk categories and aged 71+. This concentration is what your statistical analysis will uncover in later steps.

• Save this file the same way: File then Save As, type set to All Files (*.*), name it Outcomes.csv, and save to your Desktop.
• Verify that all three files are on your Desktop: Patients.csv, Surgeries.csv, and Outcomes.csv.

Files have a .txt extension instead of .csv?

This happens when Save as type was left on "Text Documents (*.txt)". Delete the incorrect file, then redo the Save As. Make sure you select All Files (*.*) from the dropdown before typing the filename.

Help me save a CSV file correctly in Notepad.

Your Discovery stage is complete. You have a documented analytical brief that frames the problem, a feasibility assessment that scopes the approach, and three datasets ready for cleaning. Next up, you will use Power Query to import, clean, and merge these messy files into a single master dataset.

Stage 2 — Exploration (Data Preparation & EDA)

Your Discovery stage gave you a clear analytical brief. You know the question (is 3.2% vs 2.1% real or noise?) and you know what data is available. Now it's time to get that data into shape.

The Exploration stage is where you import, clean, and merge your raw data into a single analysis-ready dataset. You'll use Power Query to do this. Power Query records every transformation step, making your cleaning process repeatable and auditable.

In this step, get ready to:

• Import and clean all three CSV files using Power Query.
• Merge the cleaned tables into a single MasterData dataset with calculated columns.
• Perform exploratory data analysis with frequency distributions and basic statistics.

Import and clean all three datasets with Power Query

Your three CSV files have intentional messiness: mixed date formats, inconsistent text casing, duplicate rows, and inconsistent flag values. Power Query lets you fix all of this without altering the original files.

You'll import each CSV separately, apply cleaning transformations, then load them back into Excel as structured tables.

Import and clean Patients.csv

• In your workbook, click the Data tab on the ribbon.
• In the Get & Transform Data group, click From Text/CSV.
• Navigate to Patients.csv on your Desktop and click Import.
• A preview window appears. Click Edit to open the Power Query Editor (do not click Load directly).

What is Power Query Editor?

Power Query Editor is a dedicated window for data transformations. It keeps track of each transformation step in the Applied Steps section of the Query Settings pane on the right side. Every change you make is recorded so you can replay, edit, or undo it later.

Now clean the Patients data inside the Power Query Editor:

• Click the column header for Age. On the Home tab, click Data Type and select Whole Number.
• Click the column header for AdmissionDate. Set the Data Type to Date.
• Select all text columns (Gender, Department, RiskCategory) by holding Ctrl and clicking each column header.
• Go to Transform > Format > Capitalize Each Word to standardize casing.
• Click Home > Close & Load to load the cleaned Patients table into your workbook.

What do these transforms do?

Setting the data type ensures Age is treated as a number (for calculations like averages) and AdmissionDate is treated as a proper date (for sorting and grouping by month).

Capitalize Each Word fixes inconsistent casing. For example, "cardiac surgery" and "CARDIAC SURGERY" both become "Cardiac Surgery". This prevents duplicate categories in your analysis later.

Import and clean Surgeries.csv

• Click Data > From Text/CSV again.
• Select Surgeries.csv and click Import.
• Click Edit to open Power Query Editor.
• Set SurgeryDate data type to Date.
• Set DurationMins data type to Whole Number.
• Select all text columns and apply Transform > Format > Capitalize Each Word.
• Remove duplicate rows by clicking Home > Remove Rows > Remove Duplicates.

Now standardize the EmergencyFlag column so it only contains "Yes" or "No":

• Click the EmergencyFlag column header.
• Click Home > Replace Values.
• In the Value To Find box, type Y. In Replace With, type Yes. Click OK.
• Repeat Replace Values: find 1, replace with Yes.
• Repeat Replace Values: find True, replace with Yes.
• Click Home > Close & Load.

Why standardize the EmergencyFlag?

The raw data uses "Yes", "Y", "1", and "True" interchangeably to mean the same thing. If you leave these as-is, a PivotTable would treat them as four separate categories. Standardizing to just "Yes" and "No" ensures accurate counts later.

Import and clean Outcomes.csv

• Click Data > From Text/CSV.
• Select Outcomes.csv and click Import.
• Click Edit to open Power Query Editor.
• Set DaysPostSurgery data type to Whole Number.
• Set DischargeDate data type to Date.
• Click the Outcome column header. Use Home > Replace Values to ensure all values are either Survived or Deceased (fix any variations like lowercase or abbreviations).
• Click Home > Close & Load.

You should now see three new sheets in your workbook, each containing a cleaned table loaded from Power Query.

Don't see all three tables?

Check the right side of your workbook for the Queries & Connections pane. You should see three queries listed (Patients, Surgeries, Outcomes), each showing a row count. If a query shows an error, double-click it to re-open the Power Query Editor and check the Applied Steps for a red exclamation mark.

Help me troubleshoot my Power Query import

Merge into a master dataset

You now have three cleaned tables. To analyse mortality across patient demographics, surgery details, and outcomes, you need them joined into a single table. Power Query's Merge feature works like a database JOIN.

• Click Data > Get Data > Combine Queries > Merge.
• In the top dropdown, select Patients. Click the PatientID column to highlight it.
• In the bottom dropdown, select Surgeries. Click the PatientID column in the Surgeries preview.
• Set the Join Kind to Left Outer (all from first, matching from second).
• Click OK. The Power Query Editor opens with a new merged query.

Why Left Outer join?

A Left Outer join keeps every patient from the first table, even if they have no matching surgery record. This ensures you don't accidentally lose patients from your analysis. In this dataset all patients have surgeries, but using Left Outer is the safe default.

• In the Power Query Editor, you'll see a column called Surgeries containing Table values. Click the expand icon (double-arrow) at the top of that column.
• Uncheck PatientID (you already have it from Patients). Keep all other columns checked.
• Uncheck Use original column name as prefix.
• Click OK to expand the surgery columns into your merged table.

Now merge Outcomes into the same query:

• In the Power Query Editor, click Home > Merge Queries.
• Click the PatientID column in the top preview.
• In the bottom dropdown, select Outcomes and click its PatientID column.
• Keep the join kind as Left Outer and click OK.
• Expand the Outcomes column the same way: click the expand icon, uncheck PatientID, uncheck Use original column name as prefix, and click OK.

Add calculated columns

You need two calculated columns for your analysis: a numeric flag indicating whether a patient died, and an age group classification for risk adjustment.

• In the Power Query Editor, click Add Column > Custom Column.
• In the New column name box, type Died.
• In the Custom Column Formula box, enter the following formula:

if [Outcome] = "Deceased" then 1 else 0

• Click OK.

What does this formula do?

This is a Power Query M language expression. It checks the Outcome column for each row. If the value is "Deceased", the new Died column gets a 1. Otherwise it gets a 0. This numeric flag makes it easy to calculate mortality rates using averages and sums later.

• Click Add Column > Custom Column again.
• Name it AgeGroup and enter this formula:

if [Age] < 50 then "Under 50" else if [Age] < 65 then "50-64" else if [Age] < 75 then "65-74" else "75+"

• Click OK.

Why group patients by age?

Mortality risk increases with age. Grouping into bands (Under 50, 50-64, 65-74, 75+) allows you to compare mortality rates across age groups and perform risk adjustment. These are the standard NHS age bands used in hospital mortality reporting.

• Rename the query to MasterData by double-clicking the query name in the left-hand Queries pane and typing the new name.
• Click Home > Close & Load to load the merged dataset into your workbook.

You should see a new sheet with your MasterData table. It contains all patient demographics, surgery details, outcomes, and your two new calculated columns.

Seeing errors in the Died or AgeGroup columns?

If you see Error values in the new columns, double-click the MasterData query in the Queries & Connections pane to re-open the editor. Check that your formula uses square brackets around column names (e.g. [Outcome], [Age]) and that the text values match exactly ("Deceased" with a capital D).

Help me fix errors in my Power Query custom columns

Perform exploratory data analysis

With your MasterData table ready, you can now explore the data's basic properties. The Exploration stage requires you to assess data quality, identify patterns, and check whether the sample has enough variability for meaningful analysis.

• Right-click any sheet tab at the bottom of your workbook and select Insert > Worksheet.
• Double-click the new tab and rename it 2_Exploration.

Start by documenting data quality findings. In cell A1, type a heading: Data Quality Summary.

• In cell A3, type Total Records. In B3, enter this formula to count rows in MasterData:

=COUNTA(MasterData[PatientID])

• In cell A4, type Blanks Cleaned. In B4, enter the number of blanks you observed during import (check your Applied Steps in Power Query for reference).
• In cell A5, type Duplicates Removed. In B5, enter the count of duplicate rows removed from Surgeries.
• In cell A6, type Data Completeness %. In B6, calculate the percentage of complete records after cleaning (aim for close to 100%).

Next, build frequency distributions. Starting in row 9:

• In cell A9, type Frequency Distributions as a section header.
• Create a Department count table. List each department name in column A (rows 11 onwards) and use COUNTIF to count patients per department:

=COUNTIF(MasterData[Department],A11)

• Repeat the same pattern to create frequency counts for AgeGroup and RiskCategory in adjacent sections of the sheet.

What does this tell you?

Frequency distributions reveal whether your sample has enough patients in each subgroup to produce reliable rates. If a department has only 5 patients, its mortality rate could swing wildly from a single death. Identifying small groups now helps you interpret results with appropriate caution in the Modelling stage.

Now calculate basic statistics. Find a free area of the sheet and enter:

• Mean age of patients:

=AVERAGE(MasterData[Age])

• Gender split (percentage female):

=COUNTIF(MasterData[Gender],"Female")/COUNTA(MasterData[Gender])

• Emergency vs. elective ratio (percentage emergency):

=COUNTIF(MasterData[EmergencyFlag],"Yes")/COUNTA(MasterData[EmergencyFlag])

Why calculate these statistics?

These numbers describe your patient population. A hospital with older patients or more emergency cases would naturally have higher mortality. Documenting the baseline demographics now sets up your risk adjustment argument in the Modelling stage.

Finally, create a bar chart showing patient distribution across departments:

• Select your Department names and their corresponding COUNTIF values.
• Click Insert > Charts > Clustered Bar.
• Add a chart title: Patient Distribution by Department.
• Save your workbook.

Your chart should show the relative volume of patients across departments. Look for any departments with very few patients, as these will produce unreliable mortality rates.

Chart looks empty or wrong?

Make sure you selected both the department names (labels) and the count values (numbers) before inserting the chart. If you only selected numbers, the chart won't have labels. You can right-click the chart and select Select Data to fix the source range.

Help me fix my Excel chart

Your Exploration stage is complete. You've imported and cleaned three messy datasets, merged them into a single analysis-ready MasterData table, and documented the basic properties of your sample. Next, you'll use this data to build statistical models that answer the CMO's question.

Stage 3 — Modelling (Statistical Analysis)

In the Exploration stage, you cleaned and merged three messy datasets into one unified MasterData table. Now you know what the data looks like. The question is: does it prove anything?

The Modelling stage constructs a simplified, data-based representation of the mortality problem. You'll build three layers of analysis: crude rates to see the surface picture, risk-adjusted rates to account for patient mix, and statistical tests to determine whether the numbers are real or noise.

In this step, get ready to:

• Calculate crude and risk-adjusted mortality rates using COUNTIFS, SUMPRODUCT, and the Standardized Mortality Ratio.
• Test statistical significance with chi-squared and build 95% confidence intervals.
• Build a funnel plot and document methodology limitations.

Calculate crude and risk-adjusted mortality

Before applying any statistical adjustment, you need a baseline. Crude rates show the raw mortality percentage for each group without accounting for differences in patient complexity.

• Right-click the 2_Exploration sheet tab at the bottom of your workbook.
• Click Insert, then select Worksheet.
• Double-click the new tab and rename it 3_Modelling.

Now build the crude mortality rate table. You'll use COUNTIFS to count deaths in each group and COUNTA to count total patients in each group.

• In cell A1, type Crude Mortality Rates as the section header.
• In cell A3, type Department. In B3, type Total Patients. In C3, type Deaths. In D3, type Mortality Rate.
• In cells A4 downward, list each department from your MasterData (e.g. Cardiac Surgery, Orthopaedics, General Surgery, Neurosurgery, Urology).
• Enter the following formulas in row 4 (assuming your first department is in A4), then copy them down for each department:

B4: =COUNTIFS(MasterData[Department],A4)
C4: =COUNTIFS(MasterData[Department],A4,MasterData[Died],1)
D4: =C4/B4

What do these formulas do?

COUNTIFS(MasterData[Department],A4) counts every row in MasterData where the Department column matches the value in A4.

Adding MasterData[Died],1 as a second criteria pair narrows the count to only rows where the patient died. Dividing deaths by total patients gives you the crude mortality rate as a decimal.

• Repeat the same pattern to create crude rates by ProcedureType (rows below the department table) and by AgeGroup (rows below that).
• Format column D as a percentage by selecting the cells, right-clicking, choosing Format Cells, selecting Percentage, and setting 1 decimal place.

Now build the risk-adjustment model. The crude rate alone is misleading because a hospital treating sicker patients will naturally have higher mortality.

What is risk adjustment?

Risk adjustment answers the question: given this hospital's patient mix, how many deaths would you expect if it performed at the national average? The method is called indirect standardization.

You apply national mortality rates for each risk category to the hospital's actual patient numbers. The total gives you "expected deaths." Comparing observed deaths to expected deaths produces the Standardized Mortality Ratio (SMR).

• Leave a few blank rows below your crude rates, then in the next available row type Risk Adjustment Table as a section header.
• Create column headers: RiskCategory, Patient Count, National Expected Rate, Expected Deaths.
• List the four risk categories and enter the national expected mortality rates as shown below:

RiskCategory    | Patient Count                              | National Rate | Expected Deaths
Low             | =COUNTIFS(MasterData[RiskCategory],"Low")  | 0.5%          | =B*C (Patient Count × Rate)
Medium          | =COUNTIFS(MasterData[RiskCategory],"Medium")| 2.0%          | =B*C
High            | =COUNTIFS(MasterData[RiskCategory],"High") | 5.0%          | =B*C
Very High       | =COUNTIFS(MasterData[RiskCategory],"Very High")| 12.0%      | =B*C

Where do the national rates come from?

In a real engagement, these rates come from published national audit data. For this project, the values (Low: 0.5%, Medium: 2.0%, High: 5.0%, Very High: 12.0%) represent simplified national benchmarks per risk category.

• Below the risk-adjustment table, calculate total expected deaths using SUMPRODUCT:

Total Expected Deaths: =SUMPRODUCT(COUNTIFS(MasterData[RiskCategory],{"Low","Medium","High","Very High"}),{0.005,0.02,0.05,0.12})

What does SUMPRODUCT do here?

SUMPRODUCT multiplies each risk category's patient count by its national expected rate, then sums all the products. This gives you the total number of deaths you'd expect if this hospital performed exactly at the national average for each risk group.

• Calculate Observed Deaths and the SMR below that:

Observed Deaths: =COUNTIFS(MasterData[Died],1)
SMR: =Observed Deaths cell / Expected Deaths cell

• Add a label next to the SMR result explaining the interpretation: "SMR > 1.0 = worse than expected. SMR < 1.0 = better than expected."
• Save your workbook by pressing Ctrl+S.

Your SMR value tells you how much of the mortality gap remains after accounting for the hospital's patient mix. If the SMR is still above 1.0, the elevated mortality is not fully explained by having sicker patients.

SMR showing a very unexpected value?

Check that your SUMPRODUCT formula references the correct ranges. The national rates must be entered as decimals (0.005, 0.02, 0.05, 0.12), not percentages (0.5, 2, 5, 12).

Also confirm your Observed Deaths formula counts where Died equals 1 (numeric), not the text "Deceased". Help me troubleshoot my SMR formula — I'm getting an unexpected result.

Test statistical significance

The SMR tells you the hospital is performing worse than expected. But with only 200 patients, could this just be random variation? Statistical significance testing answers that question.

• Below your SMR calculation, add a section header: Statistical Significance Testing.
• Calculate the chi-squared statistic using the formula below:

Chi-squared: =((Observed_Deaths - Expected_Deaths)^2 / Expected_Deaths) + ((Observed_Survived - Expected_Survived)^2 / Expected_Survived)

How does chi-squared work here?

The chi-squared test compares what you observed against what you'd expect by chance. Larger values mean the gap between observed and expected is less likely to be random.

You need both the "died" and "survived" components. Observed_Survived = Total Patients minus Observed Deaths. Expected_Survived = Total Patients minus Expected Deaths.

• To get these values, add helper cells:

Total Patients: =COUNTA(MasterData[PatientID])
Observed Survived: =Total Patients - Observed Deaths
Expected Survived: =Total Patients - Expected Deaths

• Now calculate the p-value using CHISQ.DIST.RT:

p-value: =CHISQ.DIST.RT(chi_squared_cell, 1)

What does CHISQ.DIST.RT tell you?

CHISQ.DIST.RT(x, deg_freedom) returns the right-tailed probability of the chi-squared distribution. The first argument is your chi-squared value. The second argument is degrees of freedom (1 for a 2×2 comparison).

If the p-value is less than 0.05, the difference between observed and expected deaths is statistically significant at the 5% level. This means it's unlikely to have occurred by chance alone.

• Next, calculate the 95% confidence interval for the hospital's mortality rate:

Overall Rate: =Observed Deaths / Total Patients
Lower CI: =Overall Rate - 1.96 * SQRT(Overall Rate * (1 - Overall Rate) / Total Patients)
Upper CI: =Overall Rate + 1.96 * SQRT(Overall Rate * (1 - Overall Rate) / Total Patients)

How do confidence intervals help?

The 95% confidence interval gives a range within which the true mortality rate likely falls. If the national benchmark of 2.1% sits outside this range (below the Lower CI), you can say with 95% confidence that the hospital's rate is genuinely higher than the benchmark.

• Add a conclusion cell below the confidence interval. Type text stating whether 2.1% falls within or outside the confidence interval, and what that means for statistical significance.
• Save your workbook by pressing Ctrl+S.

Getting a #VALUE! error in the confidence interval formula?

Make sure your Overall Rate cell contains a decimal (e.g. 0.032), not a formatted percentage. If you formatted it as a percentage for display, reference the underlying decimal value in your CI formula.

Also check that Total Patients is a single number, not a range. Help me fix my confidence interval formula in Excel.

Build the funnel plot and address pitfalls

A funnel plot is the standard way to compare multiple units (departments) against a benchmark while accounting for sample size. Departments with more surgeries have tighter control limits, so a high rate in a small department might be within normal variation while the same rate in a large department is a genuine outlier.

• Below your significance testing section, add a header: Funnel Plot Data.
• Create a table with columns: Department, Volume (surgery count), Mortality Rate, Upper Limit, Lower Limit.
• For each department, calculate the 95% control limits using the benchmark rate of 2.1%:

Volume: =COUNTIFS(MasterData[Department],A_dept)
Mortality Rate: =COUNTIFS(MasterData[Department],A_dept,MasterData[Died],1)/Volume
Upper Limit: =0.021 + 1.96 * SQRT(0.021 * (1-0.021) / Volume)
Lower Limit: =0.021 - 1.96 * SQRT(0.021 * (1-0.021) / Volume)

Why do the limits narrow as volume increases?

The formula divides by volume inside the square root. Larger samples produce smaller random variation, so the acceptable range around the benchmark tightens. A department with 100 surgeries has much narrower limits than one with 20.

• Select the Volume and Mortality Rate columns (including department labels) for all departments.
• Click Insert on the ribbon, then choose Scatter (X Y Scatter with markers only).
• This plots each department as a dot with surgery volume on the X-axis and mortality rate on the Y-axis.

• To add the funnel limits as curved lines, create a helper table with a range of volume values (e.g. 10, 20, 30, 40, 50, 60, 70, 80, 100) and calculate the Upper and Lower limits at each volume using the same formula.
• Right-click the chart and select Select Data.
• Click Add to add a new series. Set the X values to your helper volume column and Y values to the Upper Limit column. Name it "Upper 95% Limit".
• Repeat to add the Lower Limit series.
• Right-click each limit series in the chart and select Format Data Series. Change the marker to None and connect with a smooth line.
• Add a chart title: Funnel Plot - Department Mortality vs Volume.

Departments plotted above the upper funnel limit are genuine statistical outliers. Those within the funnel are performing within expected random variation.

• Below the funnel plot data, add a section header: Methodology Notes.
• Document the three pitfalls from the Six-Stage Analytics Framework by typing the following notes into cells below the header:

1. Generalisability: 200 patients, single hospital, 12-month period. Findings may not generalise to other hospitals or time periods.

2. Excluded-variable bias: No detailed comorbidity data (e.g. Charlson Index). No surgeon-level risk adjustment. Results may be confounded by unmeasured factors.

3. Misinterpreting causation: Association between department and mortality does not prove the department causes deaths. Patient selection, referral patterns, and case complexity all contribute.

Why document limitations?

The Six-Stage Analytics Framework identifies three principal pitfalls in the Modelling stage: generalisability, excluded-variable bias, and misinterpreting causation.

Including these in your workbook demonstrates analytical maturity. You're not just running numbers. You're thinking critically about what the numbers can and cannot tell you.

• Save your workbook by pressing Ctrl+S.

Funnel chart lines looking jagged?

Use more volume points in your helper table (every 5 or 10 units) for smoother curves. Also confirm you set the line type to Smooth Line in Format Data Series rather than the default straight segments.

If departments are not showing, check that the original scatter series uses the department volume and rate columns, not the helper table. Help me fix my funnel plot in Excel — the lines or points aren't displaying correctly.

Your Modelling sheet now contains a complete statistical picture. Next up, you'll use these findings to project the impact of targeted interventions and answer the CMO's real question: what should we actually do about it?

Stage 4 — Projection (Scenario Analysis)

Your Modelling stage confirmed that the hospital's elevated mortality rate is statistically significant. The CMO now needs to know: what happens if we intervene?

The Projection stage uses your statistical model to answer what-if questions. You'll project the impact of three intervention scenarios so the CMO can prioritize where to focus resources and budget.

In this step, get ready to:

• Build three intervention scenarios with projected mortality rates, deaths avoided, and SMR improvement.
• Create a sensitivity analysis using a one-variable data table with conditional formatting.
• Summarize all scenarios in a comparison table with a Clustered Column chart.

Build intervention scenarios

Each scenario models a different level of organisational response to the mortality findings. This lets the CMO see the range of possible outcomes and the lives at stake.

• Right-click the 3_Modelling sheet tab at the bottom of Excel.
• Click Insert, select Worksheet, and click OK.
• Double-click the new tab and rename it to 4_Projection.

Now set up the scenario parameters. You'll reference values from your Modelling sheet to keep everything connected.

• In cell A1, type the header PROJECTION: INTERVENTION SCENARIOS and press Enter.
• In cell A3, type Current overall mortality rate: and in B3 enter 3.2%.
• In cell A4, type National benchmark: and in B4 enter 2.1%.
• In cell A5, type Annual surgical patients (extrapolated): and in B5 enter 2000.
• In cell A6, type Current SMR: and in B6 reference the SMR value you calculated on the Modelling sheet (or type it directly if you prefer).

Next, build the three scenario rows starting at row 8.

• Set up column headers in row 8: Scenario in A8, Description in B8, Projected Mortality Rate in C8, Projected Annual Deaths in D8, Deaths Avoided vs Baseline in E8, and Projected SMR in F8.
• In row 9, enter Scenario A (Baseline): no change. Projected mortality rate stays at 3.2%. Projected annual deaths = =B5*C9. Deaths avoided = 0. SMR remains unchanged from your Modelling sheet.
• In row 10, enter Scenario B (Targeted intervention): reduce Cardiac Surgery mortality to the national specialty average. Estimate the blended projected mortality rate based on how much Cardiac Surgery contributes to overall deaths. Calculate projected annual deaths with =B5*C10.
• In row 11, enter Scenario C (Comprehensive programme): reduce all above-benchmark departments to benchmark levels. Projected mortality rate = 2.1% (i.e. the national benchmark). Projected annual deaths = =B5*C11.
• For Deaths Avoided vs Baseline in column E, use a formula like =D$9-D9 for Scenario A (which gives 0) and =D$9-D10 for Scenario B, etc.
• For Projected SMR in column F, calculate the ratio of projected deaths to expected deaths (using the expected deaths value from your Modelling sheet).

What do these scenarios mean?

Scenario A is your control. It shows what happens if the hospital takes no action and the current trend continues.

Scenario B targets the single biggest contributor (Cardiac Surgery) and models what happens when just that department improves. Scenario C is the aspirational case where every department meets the benchmark.

• Save your workbook.
• Confirm your three scenario rows show different projected mortality rates, with Scenario C at 2.1% and Scenario A at 3.2%.

Deaths avoided showing negative numbers?

Make sure your formula subtracts the scenario's deaths from the baseline (Scenario A) deaths, not the other way around. The baseline row should always have a higher death count than the intervention rows.

help me fix my Deaths Avoided formula so Scenario B and C show positive numbers

Create a sensitivity analysis with a data table

The targeted intervention in Scenario B depends on how much improvement Cardiac Surgery actually achieves. A sensitivity analysis shows the CMO the full spectrum of outcomes as that single variable changes from 0% improvement to 100% reduction to the benchmark.

• Starting around row 14, type a section header in A14: SENSITIVITY ANALYSIS: Cardiac Surgery Improvement.
• In cell A16, type Improvement % as a label. In B16, type Projected Overall Mortality Rate as a second header.
• Choose an input cell (for example D14) to hold the improvement percentage. Type 50% there as a starting value.
• In cell B17, build a formula that calculates the overall hospital mortality rate as a function of the input cell. The formula should reduce Cardiac Surgery's mortality by the improvement percentage and recalculate the blended overall rate.
• In cells A17 through A28, list improvement percentages from 0% to 100% in 10% increments (0%, 10%, 20%, ... 100%).

Now use Excel's one-variable Data Table feature to fill in all results automatically.

• Select the range from A16 to B28 (the header row, the formula cell, and all input values).
• Click the Data tab on the ribbon.
• Click What-If Analysis in the Forecast group, then click Data Table.
• In the Data Table dialog, leave the Row input cell field blank.
• In the Column input cell field, click cell D14 (your improvement percentage input cell).
• Click OK.

What does a Data Table do?

Excel substitutes each value in column A into the input cell one at a time, recalculates the formula in B17, and fills in the results. You get 11 projected mortality rates instantly without writing 11 separate formulas.

Now apply conditional formatting so the CMO can see at a glance which improvement levels meet the benchmark.

• Select the result cells in column B (B17:B28).
• Click the Home tab, then click Conditional Formatting in the Styles group.
• Click Highlight Cells Rules, then Less Than.
• Enter 0.021 (the benchmark as a decimal) and choose Green Fill with Dark Green Text. Click OK.
• With the same cells selected, click Conditional Formatting again, then Highlight Cells Rules, then Greater Than.
• Enter 0.021 and choose Light Red Fill with Dark Red Text. Click OK.

Finally, calculate the break-even improvement needed for Cardiac Surgery alone to bring the whole hospital to benchmark.

• Below the data table (around row 30), type a label in A30: Break-even improvement for Cardiac Surgery:.
• In B30, calculate the percentage improvement Cardiac Surgery must achieve for the overall hospital rate to reach 2.1%. Use your blended rate formula solved algebraically, or read the approximate value from your data table (the row where the result crosses the benchmark).
• Save your workbook.
• Confirm that cells in your data table show green fill for values at or below 2.1% and red fill for values above.

Data table cells showing the same value in every row?

The most common cause is that the formula in B17 does not reference your input cell (D14). All formulas in a one-variable data table must reference the same input cell for the substitution to work.

help me debug my one-variable data table that shows the same result in every row

Create a projection summary with chart

The CMO needs a single view that compares all three scenarios side by side. A well-structured comparison table paired with a chart makes the case visually compelling.

• Starting around row 33, type a section header: SCENARIO COMPARISON SUMMARY.
• Build a comparison table with columns: Scenario, Projected Mortality Rate, Projected Annual Deaths, SMR, Lives Saved vs Baseline.
• Enter rows for Scenario A (Baseline), Scenario B (Targeted), and Scenario C (Comprehensive). Reference the values you already calculated in rows 9-11 using cell references like =C9, =D9, etc.

Now create the chart to visualise the comparison.

• Select the comparison table (all rows and columns including headers).
• Click the Insert tab on the ribbon.
• In the Charts group, click Insert Column or Bar Chart, then select Clustered Column.
• Click the chart title and type Scenario Comparison: Impact of Interventions.
• Resize and position the chart below your comparison table.

Why a Clustered Column chart?

Clustered Column charts place related values side by side for each scenario, making it immediately obvious how mortality rate, annual deaths, and SMR compare across interventions. The CMO can see at a glance that Scenario C saves the most lives.

Finally, add a regulatory timeline estimate at the bottom of the sheet.

• Below the chart (around row 50 or wherever there is space), type a header: REGULATORY TIMELINE ESTIMATE.
• Add a note documenting your estimate: if the current trend continues, how many consecutive quarters above benchmark would likely trigger a regulatory review. A common threshold is 4 consecutive quarters above benchmark.
• Type a conclusion such as: At current rate (3.2%), the hospital has been above benchmark for the full 12-month study period. Without intervention, regulatory review is likely within the next 1-2 quarters.
• Save your workbook.
• Confirm you have a Clustered Column chart showing the three scenarios and a clear regulatory timeline note at the bottom of the sheet.

Chart showing numbers instead of scenario names on the axis?

Right-click the chart and click Select Data. Under Horizontal (Category) Axis Labels, click Edit and select the cells containing your scenario names (A, B, C or the full descriptions).

help me fix my chart axis labels so it shows scenario names instead of numbers

Your projection analysis now gives the CMO a clear decision framework. Next, you'll translate these findings into an interactive dashboard with executive-ready recommendations.

Stage 5 — Actionable Insight (Dashboard & Recommendations)

Your statistical models have answered the question. Now you need to translate those answers into something the Chief Medical Officer can act on immediately.

The Actionable Insight stage is where analysis becomes communication. You'll build an interactive dashboard with PivotCharts and Slicers that lets stakeholders explore the data themselves, plus a structured executive summary with specific recommendations.

In this step, get ready to:

• Build an interactive dashboard with four PivotTables, PivotCharts, and connected Slicers.
• Write a structured executive summary with findings and three actionable recommendations.
• Format the workbook for portfolio presentation with a cover sheet and professional colour scheme.

Build the interactive dashboard

An interactive dashboard lets the CMO filter and explore mortality data without needing to understand the underlying formulas. You'll create four PivotTables that slice mortality from different angles, then visualise each one with a chart.

• Right-click a sheet tab at the bottom of your workbook and select Insert > Worksheet.
• Double-click the new tab and rename it 5_Dashboard.

Now create the first PivotTable to show mortality by department.

• Click any cell in the MasterData table on its sheet.
• Go to Insert > PivotTable.
• In the Create PivotTable dialog, select Existing Worksheet and click a cell on your 5_Dashboard sheet (for example, cell A3).
• Click OK.
• In the PivotTable Fields pane, drag Department to Rows.
• Drag Died to Values.
• Click on Sum of Died in the Values area, select Value Field Settings, and change it to Average. This gives you the mortality rate for each department.

Why Average of the Died column?

Because Died contains 1 (deceased) or 0 (survived), the average equals the proportion who died. An average of 0.032 means a 3.2% mortality rate.

Repeat this process to create three more PivotTables on the same sheet, leaving space between them.

• Create PivotTable 2 (Mortality by Age Group): drag AgeGroup to Rows and Died to Values (set to Average).
• Create PivotTable 3 (Monthly trend): drag SurgeryDate to Rows and Died to Values (set to Average). Right-click any date in the Rows area, select Group, and choose Months.
• Create PivotTable 4 (Emergency vs. Elective): drag EmergencyFlag to Rows and Died to Values (set to Average).

Now add a PivotChart for each PivotTable.

• Click inside PivotTable 1 (departments).
• Go to PivotTable Tools > Analyze > PivotChart.
• Select Clustered Bar and click OK.
• Repeat for PivotTable 2: choose Clustered Column.
• Repeat for PivotTable 3: choose Line.
• Repeat for PivotTable 4: choose Stacked Bar.

Add the 2.1% benchmark reference line to each chart so the CMO can instantly see which groups exceed the national average.

• Click on a chart to select it.
• Go to Chart Tools > Design > Select Data.
• Click Add to create a new series. Name it Benchmark and point the values to cells containing 0.021 repeated for each category.
• Right-click the benchmark series in the chart, select Change Series Chart Type, and switch it to a Line on a secondary axis. Format the line as a dashed red line.
• Repeat this for all four charts.

Simpler benchmark line alternative

If the secondary series approach feels complex, you can also insert a horizontal line shape at the 2.1% level on each chart. Go to Insert > Shapes > Line and position it manually at the benchmark level. Label it "National Benchmark 2.1%".

Now add Slicers to make the dashboard interactive.

• Click inside any of the four PivotTables.
• Go to PivotTable Tools > Analyze > Filter > Insert Slicer.
• Check the boxes for Department, RiskCategory, EmergencyFlag, and AgeGroup. Click OK.

Connect each Slicer to all four PivotTables so clicking a filter updates every chart simultaneously.

• Click on the Department Slicer to select it.
• Go to Slicer Tools > Options > Report Connections.
• Check all four PivotTables in the list and click OK.
• Repeat the Report Connections step for the RiskCategory, EmergencyFlag, and AgeGroup Slicers.

Finish the dashboard layout.

• Go to View tab and uncheck Gridlines to hide them on the 5_Dashboard sheet.
• Click cell A1 and type Hospital Mortality Analysis Dashboard. Format it as bold, size 18.
• Arrange the four charts and four Slicers so they fit neatly on one screen. Position Slicers along the top or left side.

• Save your workbook.
• Test the dashboard by clicking a department in the Department Slicer. All four charts should update to show only that department's data.

Charts not updating when you click a Slicer?

This means the Slicer is not connected to all PivotTables. Click the Slicer, go to Slicer Tools > Options > Report Connections, and check all four PivotTables.

Help me connect my Slicers to multiple PivotTables.

Write the executive summary

A dashboard shows the data. An executive summary tells the decision-maker what it means and what to do about it. You'll write a structured findings section that a CMO can read in under two minutes.

• Scroll to an empty area at the top of your 5_Dashboard sheet, or create a separate sheet named Executive_Summary. Either approach works.
• Type a section header: EXECUTIVE SUMMARY — MORTALITY ANALYSIS FINDINGS. Format it bold, size 14.

Structure your summary with four sections. Use the values from your 3_Modelling sheet.

• Section 1 — Headline Finding: State whether the elevated mortality rate is statistically significant. Reference your chi-squared result and p-value from the Modelling sheet. For example: "The difference between observed (3.2%) and expected mortality is statistically significant (chi-squared = [your value], p = [your value], p < 0.05)."
• Section 2 — Key Driver: Identify which department and patient group are responsible for the majority of excess mortality. Reference the specific department that appeared outside the funnel plot limits.
• Section 3 — Risk Adjustment Conclusion: State the SMR value and interpret it. An SMR above 1.0 means the hospital has more deaths than expected after accounting for patient mix.
• Section 4 — Recommendations: Write three specific, actionable recommendations.

What should the three recommendations be?

Your recommendations should be specific enough that someone could start acting on them this week. Base them on your modelling findings:

• Recommendation 1: Launch a mortality and morbidity review in Cardiac Surgery (or whichever department your data identified as the outlier).
• Recommendation 2: Implement enhanced monitoring for Very High Risk patients.
• Recommendation 3: Establish quarterly mortality reporting with funnel chart tracking.

• Type out all four sections in your summary area, using the actual values from your 3_Modelling and 4_Projection sheets.
• Format section headers in bold and leave a blank row between each section for readability.
• Save your workbook.

Not sure what values to reference?

Switch to your 3_Modelling sheet tab. Your chi-squared value, p-value, SMR, and confidence intervals should all be there from Step 3. Copy them directly into your executive summary.

Help me write my executive summary using my modelling results.

Format for portfolio presentation

A portfolio-ready workbook communicates professionalism before anyone reads the analysis. You'll add a cover sheet, apply consistent formatting, and organise the tabs to follow the Six-Stage Analytics Framework.

• Right-click a sheet tab and insert a new worksheet.
• Rename it Cover.
• Drag the Cover tab to the far-left position so it becomes the first sheet in the workbook.

Add the cover sheet content.

• In cell B3, type the project title: Hospital 30-Day Post-Surgery Mortality Analysis. Format it bold, size 20.
• In cell B5, type today's date.
• In cell B6, type your name as the analyst.
• Starting at cell B9, create the Six-Stage Framework roadmap. Type each stage on its own row:

• Row 9: Stage 1: Discovery.
• Row 10: Stage 2: Exploration.
• Row 11: Stage 3: Modelling.
• Row 12: Stage 4: Projection.
• Row 13: Stage 5: Actionable Insight — highlight this row with a coloured fill to indicate the current stage.
• Row 14: Stage 6: Monitoring.

Apply professional formatting across the workbook.

• Choose a consistent colour scheme: hospital blue for headers (e.g. RGB 0, 102, 153), white backgrounds, and grey for borders and secondary text.
• Apply the blue fill to the header row or title cell on each sheet (Home > Fill Color).
• Define named ranges for key metrics: select the cell containing your overall mortality rate, go to Formulas > Define Name, and name it OverallMortalityRate. Repeat for SMR and NationalBenchmark.

Arrange the sheet tab order to follow the framework.

• Drag the sheet tabs so they appear in this order from left to right: Cover, 1_Discovery, 2_Exploration, 3_Modelling, 4_Projection, 5_Dashboard, 6_Monitoring.
• If you created a separate Executive_Summary sheet, place it directly after 5_Dashboard.
• Save your workbook.

Why named ranges?

Named ranges let you reference key values by meaningful names (like SMR) instead of cell addresses (like D14) in formulas throughout the workbook. This makes formulas readable and prevents errors when you rearrange cells.

Sheet tabs not letting you drag them?

Make sure you click and hold on the sheet tab itself (not the sheet name text), then drag horizontally. A small arrow icon should appear between tabs showing where the sheet will land. If the tabs feel stuck, check that no sheets are protected (Review > Unprotect Sheet).

Help me reorder my Excel sheet tabs.

Your workbook now tells a complete story from data to decision. The CMO can interact with the dashboard, read the executive summary, and see exactly what action to take. One stage remains: making sure these insights don't gather dust. Next, you'll design the monitoring framework that keeps this analysis alive.

Stage 6 — Monitoring (KPI Framework)

Your dashboard delivers a powerful snapshot of hospital mortality performance. But a snapshot is just that. Without ongoing monitoring, today's insights become tomorrow's stale report.

The final stage of the Six-Stage Analytics Framework ensures your analysis has a life beyond the initial delivery. You'll define KPIs with clear thresholds, build a traffic-light display for instant status checks, and design escalation rules so decision-makers know exactly when to act.

In this step, get ready to:

• Define five KPIs with targets, thresholds, and escalation owners.
• Build a visual traffic-light KPI dashboard with sparkline trends.
• Design a decision-point framework with escalation rules and a process flowchart.

Define KPIs and build the monitoring sheet

A KPI without a target is just a number. Each metric needs a defined target, a threshold that triggers action, and a named owner responsible for responding.

• Right-click the 5_Dashboard tab at the bottom of your workbook.
• Click Insert, then select Worksheet.
• Double-click the new tab and rename it 6_Monitoring.
• In cell A1, type the heading KPI Monitoring Framework and bold it.

Now set up the KPI definition table. This documents the five core metrics the CMO will track.

• In row 3, create column headers across cells A3 to G3: KPI, Current Value, Target, Escalation Threshold, Data Source, Frequency, Owner.
• Bold the header row and apply a bottom border.

Fill in the five KPIs in rows 4 through 8 using these values:

• Row 4: Overall 30-day mortality rate | Current: 3.2% | Target: ≤2.1% | Threshold: >3.0% | Source: Outcomes database | Frequency: Monthly | Owner: CMO.
• Row 5: Cardiac Surgery mortality rate | Current: 5.8% | Target: ≤3.5% | Threshold: >5.0% | Source: Surgical records | Frequency: Monthly | Owner: Head of Cardiac Surgery.
• Row 6: Standardized Mortality Ratio (SMR) | Current: 1.35 | Target: ≤1.0 | Threshold: >1.2 | Source: Modelling sheet | Frequency: Monthly | Owner: Analytics team.
• Row 7: Emergency surgery mortality rate | Current: 4.5% | Target: ≤3.0% | Threshold: >4.0% | Source: Surgical records | Frequency: Monthly | Owner: Emergency lead.
• Row 8: High-risk patient mortality rate | Current: 8.0% | Target: ≤5.0% | Threshold: >7.0% | Source: Outcomes database | Frequency: Monthly | Owner: Risk Manager.

Why five KPIs and not more?

Monitoring too many metrics dilutes attention. Five KPIs cover the overall picture (KPI 1 and 3), the primary problem area (KPI 2), and two high-risk segments (KPI 4 and 5). Each one connects directly to a named decision-maker who can act on it.

• Save your workbook.

Table looking cramped?

Select columns A through G, then double-click the column border between any two selected column letters in the header. This auto-fits all columns to their widest content.

Help me format my KPI table so it's easy to read

Build a visual KPI dashboard

Numbers alone don't trigger action. A traffic-light display lets anyone glance at the monitoring sheet and instantly know which KPIs need attention.

You'll create a status column, apply icon set conditional formatting, add sparkline trends, and build a days-since counter.

• In cell H3, type the header Status.
• In cell H4, enter this formula to produce a numeric status value:

=IF(B4<=C4,3,IF(B4<=D4,2,1))

What does this formula do?

It compares the current value (column B) against the target (column C) and threshold (column D). A result of 3 means on-target (green). A result of 2 means between target and threshold (amber). A result of 1 means above threshold (red).

• Copy cell H4 down to H8 to apply the formula to all five KPIs.
• Select the range H4:H8.
• On the Home tab, click Conditional Formatting.
• Point to Icon Sets, then select 3 Traffic Lights (Unrimmed).

The default thresholds split the values into thirds, but your Status column uses 1, 2, and 3 specifically. You need to adjust the rule so the icons map correctly.

• With H4:H8 still selected, click Conditional Formatting again, then click Manage Rules.
• Select the Icon Set rule and click Edit Rule.
• Set the green icon threshold to: value >= 3 (Type: Number).
• Set the amber icon threshold to: value >= 2 (Type: Number).
• The red icon automatically applies to values below 2 (i.e., value 1).
• Click OK twice to apply.

What should I see?

Column H now shows traffic-light icons next to each KPI. Based on the current values, the Overall Mortality and SMR KPIs should show red icons (above threshold). Any KPI meeting its target shows green.

Next, add sparkline trends to show each KPI's trajectory over the last six months.

• In cells J3 to O3, type month headers: Month 1 through Month 6.
• In rows 4 through 8, enter simulated monthly values for each KPI that show a realistic trend. For example, Overall Mortality might trend: 3.5%, 3.4%, 3.3%, 3.2%, 3.1%, 3.2%.
• In cell I3, type the header Trend.
• Click cell I4 (this is where the first sparkline will appear).
• Go to the Insert tab, then in the Sparklines group, click Line.
• In the Data Range field, enter J4:O4.
• In the Location Range field, confirm it shows I4.
• Click OK.
• Repeat for cells I5 through I8, selecting the matching data range for each KPI row (J5:O5, J6:O6, etc.).

Highlight high and low points

Click any sparkline, then on the Sparkline Design tab, check High Point and Low Point in the Show group. This marks the best and worst months with coloured dots.

Finally, add a "Days Since Last Mortality Event" counter below the KPI table.

• In cell A10, type Days Since Last Mortality Event and bold it.
• In cell B10, enter this formula:

=TODAY()-MAX(IF(MasterData[Died]=1,MasterData[SurgeryDate]))

What does this formula do?

This is an array formula. It finds the most recent surgery date where the patient died, then calculates how many days have passed since that event. The result is a single number showing days of safety.

In Excel 2016, you must press Ctrl+Shift+Enter instead of just Enter. Excel wraps the formula in curly braces {...} to confirm it registered as an array formula.

• Save your workbook.
• Confirm that column H shows traffic-light icons (red, amber, or green) for each KPI, column I shows sparkline trend lines, and cell B10 shows a number representing days since the last mortality event.

Sparkline not appearing?

Make sure the data range (J4:O4) contains numeric values, not text. Percentages stored as text won't render. Retype them as decimals (0.035) or format the cells as Percentage after entering numbers.

Help me troubleshoot my sparklines in Excel 2016

Design the decision-point framework

Traffic lights tell you the current status. Decision rules tell you what to do about it. Without pre-agreed escalation pathways, a red indicator sits there until someone decides to care.

• In cell A12, type the heading Decision-Point Framework and bold it.
• In cell A14, type Escalation Rules: and bold it.
• In cell A15, type: If SMR exceeds 1.2 for two consecutive quarters, escalate to Board.
• In cell A16, type: If any department moves to outlier status on funnel chart, trigger clinical review within 2 weeks.
• In cell A17, type: If overall mortality exceeds 3.0% for 3 consecutive months, launch mortality reduction programme.
• In cell A18, type: If High-Risk patient rate exceeds 7.0%, implement enhanced monitoring protocol immediately.

Why pre-define decision rules?

The Six-Stage Framework principle is clear: analytics does not end with recommendations for action. Implementation plans should have decision points built in. Pre-agreed rules prevent the monitoring sheet from becoming wallpaper that nobody acts on.

Now create a simple process flowchart showing the escalation pathway. You'll use Excel's built-in shapes.

• Scroll down to leave some space below row 18 (around row 20).
• Go to the Insert tab, then click Shapes.
• Select a Rounded Rectangle from the Rectangles section and draw it on the sheet. Type inside it: Monthly KPI Review.
• Draw a Diamond shape (from the Flowchart section) below it. Type inside it: Any KPI at Red?
• Draw an arrow connector from the rectangle to the diamond by clicking Insert, then Shapes, then Line Arrow.
• To the right of the diamond, draw another Rounded Rectangle and type: No - Continue monitoring.
• Below the diamond, draw a Rounded Rectangle and type: Yes - Identify owner & escalation path.
• Below that, draw another Rounded Rectangle: Clinical review within 2 weeks.
• Draw a final shape at the bottom: Board escalation if unresolved after 1 quarter.
• Connect all shapes with arrow connectors so the flow reads top to bottom (with a branch to the right for the "No" path).

Format shapes consistently

Select all shapes by holding Ctrl and clicking each one. On the Format tab, apply a consistent fill colour (hospital blue or light grey) and font size (10pt). This keeps the flowchart looking professional.

• Save your workbook.
• Confirm the 6_Monitoring sheet now shows: the five-KPI table with traffic-light icons at the top, sparkline trends, a Days Since counter, escalation rules in text, and a flowchart below.

Shapes overlapping or misaligned?

Select all shapes, then on the Format tab click Align, then Align Center to line them up vertically. Use Align, then Distribute Vertically to space them evenly.

Help me align my flowchart shapes in Excel

Finally, verify your workbook tab order matches the Six-Stage Framework.

• Check that your sheet tabs read, left to right: Cover, 1_Discovery, 2_Exploration, 3_Modelling, 4_Projection, 5_Dashboard, 6_Monitoring.
• If any tab is out of order, drag it to the correct position.
• Save the workbook one final time with Ctrl+S.

Your analytics project is now complete from Discovery through Monitoring. The 6_Monitoring sheet ensures your analysis lives on as an active decision-support tool, not a one-off report gathering dust. Next up, you'll take on an optional Secret Mission to push your Excel skills even further.

Secret mission

Monte Carlo Simulation & Dynamic What-If Panel

Your analysis proved the hospital's mortality rate is statistically significant. But the CMO wants to know: what's the probability of seeing this rate purely by chance? And what happens if we adjust different variables? Build a Monte Carlo simulation and an interactive control panel that lets decision-makers explore scenarios in real time.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

• Excel workbook (eight sheets: Cover, 1_Discovery, 2_Exploration, 3_Modelling, 4_Projection, 5_Dashboard, 6_Monitoring, 7_MonteCarlo).
• Three CSV data files (Patients.csv, Surgeries.csv, Outcomes.csv).
• MasterData table loaded via Power Query.

Keep everything running

No action needed. Choose this if you're still actively building or plan to extend the workbook further.

• Your Excel workbook and CSV files remain on your Desktop (or wherever you saved them). They take up minimal disk space.
• Excel 2016 remains installed on your machine. It was a prerequisite for this project and is useful for future work.
• The Power Query connections inside the workbook will continue to reference the CSV file paths. If you move the CSV files, you will need to update the source paths in Power Query Editor.

Pause - I'll come back to this later

Since this project is entirely local with no running processes, pausing just means saving your work and closing Excel.

• Save your workbook one final time by pressing Ctrl+S.
• Close Excel.
• Keep the CSV files in the same folder as your workbook so Power Query can find them when you reopen.

Delete - I don't want to use this again

Remove all project files from your machine. This cannot be undone.

• Close Excel if it is still open.
• Open File Explorer and navigate to the folder where you saved your workbook and CSV files.
• Select the Excel workbook file, Patients.csv, Surgeries.csv, and Outcomes.csv.
• Press Delete or right-click and select Delete.
• Empty your Recycle Bin to permanently remove the files.

Nice Work!

Nice Work!

You've built a complete Hospital Mortality Analysis Workbook structured around the professional Six-Stage Analytics Framework. This demonstrates you can structure and execute a full analytics engagement from problem framing through ongoing monitoring.

You've learned how to:

• Structure an analytics project using the Six-Stage Analytics Framework (Discovery, Exploration, Modelling, Projection, Actionable Insight, Monitoring) to deliver rigorous, methodology-driven insights from problem framing through ongoing performance tracking.
• Build a reproducible ETL pipeline with Power Query and perform risk-adjusted statistical analysis including Standardized Mortality Ratio calculation, chi-squared significance tests, confidence intervals, and funnel plots.
• Create an executive-ready interactive dashboard with PivotCharts, Slicers, benchmark comparisons, scenario projections, and a KPI monitoring framework with threshold alerts and escalation pathways.
• Build a Monte Carlo simulation and dynamic what-if control panel using form controls to model probability distributions of mortality outcomes under different intervention scenarios.

Ready to quiz yourself?