This project analyzes hospital patient data to identify revenue drivers, healthcare utilization patterns, and patient risk segments. Using Excel, Power Query, Pivot Tables, and an interactive dashboard, the analysis provides insights that can help hospitals improve preventive care strategies and optimize billing performance.

Project Overview

Tool Used: Microsoft Excel (Power Query, Pivot Tables, Dashboard)
Dataset Size: 2000 Patients
Industry: Healthcare Analytics
Analysis Type: Patient Segmentation & Revenue Analysis
Objective: Identify high-value patient segments and healthcare utilization patterns

Business Problem

A hospital network collects large amounts of patient data but lacks a clear understanding of how patient demographics, medical conditions, and insurance plans influence hospital revenue and healthcare utilization.

Hospital executives want to identify:

Which patient segments generate the highest revenue • Which medical conditions lead to higher hospital visits • Which insurance plans contribute the most billing revenue • Whether preventive care reduces hospital visits • Which patients may be at risk of not returning for treatment

Dataset Description

Column	Description
PatientID	Unique identifier for each patient
Age	Patient age
Gender	Patient gender
State	Patient location
City	City of residence
Height_cm	Patient height
Weight_kg	Patient weight
BMI	Body Mass Index
Insurance_Type	Insurance plan used
Primary_Condition	Main diagnosed disease
Num_Chronic_Conditions	Number of chronic diseases
Annual_Visits	Visits per year
Avg_Billing_Amount	Average medical bill
Last_Visit_Date	Last hospital visit
Days_Since_Last_Visit	Days since last appointment
Preventive_Care_Flag	Preventive care indicator

Data Cleaning & Preparation

Before analysis, the dataset was cleaned and transformed using Power Query in Excel.

Key data preparation steps included:

Correcting column data types • Replacing missing values in the Primary Condition column • Standardizing categorical values such as Gender • Removing duplicate patient records • Creating a new Age Group segmentation column for analysis

Power Query allowed efficient transformation of the raw dataset into a structured analytical table ready for pivot analysis.

Data Loaded in PQ.png

Figure 1: Power Query editor used for data transformation