🏥 Hospital Patient and Revenue Analysis
📌 Overview
This project analyzes hospital patient health metrics and financial performance across 2,000 patients and 8,000 claims. The analysis provides actionable insights for improving patient care quality and optimizing revenue operations through two comprehensive Excel dashboards.
🎯 Objectives
- Patient Health Analysis: Monitor key health metrics and identify high-risk patient populations
- Financial Performance: Track revenue trends, collection rates, and identify optimization opportunities
- Operational Efficiency: Analyze claims processing, payment cycles, and specialty performance
📈 Dashboard 1: Patient Health Metrics Dashboard

Metrics Analyzed
- 2,000 total patients with average age of 46
- Vital signs monitoring (respiratory rate, heart rate, temperature, SpO₂)
- Top diagnoses, clinical risk factors, and BMI distribution
- Length of stay analysis across departments

Metrics Analyzed
- $29.7M total charges, $16.7M payments received
- 56.2% collection rate, 7.5% denial rate
- Revenue trends by specialty, payer type, and age group
- Claims distribution and A/R cycle analysis
📥 Download Files
📊 Download Excel Workbook - Full analysis with all data and calculations
🔍 Key Insights
Patient Health
- 27.3% hypertensive, 18.7% diabetic, 18% smokers - high chronic disease burden
- Age group 35-54 represents highest patient volume (842 patients)
- 58% of patients are overweight/obese, indicating need for weight management programs
Financial Performance
- Collection rate of 56.2% below industry standard (60-65%), leaving $13M outstanding
- Radiology generates highest revenue ($2.4M) as top-performing specialty
- Age group 35-54 generates most revenue ($2.6M), aligning with patient volume
- December 2025 showed critical 70% payment decline requiring investigation
Operational Challenges
- 32-day A/R cycle exceeds optimal 30-day benchmark
- 11.8% YoY revenue decline indicates market pressures
- Top diagnoses (diabetes, hypertension, low back pain) require targeted care programs
💡 Recommendations
- Launch chronic disease management programs targeting 27.3% hypertensive and 18.7% diabetic populations
- Improve collection rate from 56.2% to 60-65% through enhanced billing practices and follow-up
- Investigate November 2025 payment anomaly and implement corrective measures
- Reduce denial rate from 7.5% to <5% through better coding and documentation
- Expand high-performing specialties (Radiology, OB/GYN) and strengthen Employer Plan A partnership