Hospital Performance tracker

This project is about assessing hospital processes, resource utilization, and operational efficiency, the analysis seeks to uncover areas for improvement and develop strategies to enhance patient experience and optimize healthcare delivery. The study dwells mainly on the patient diagnoses ,outcomes and the bed occupancy .This study will further investigate factors influencing patient satisfaction, length of stay, and the billing amount charged by the hospital with relation to the health insurance amount of each patient. This analysis is conducted on a data containing records of 7157 patients segmented by 11 distinct fields.

Objectives :

1. Patient info according to patient id .
2. Billing Information.
3. Bed occupancy Breakdown .
4. Overall feedback for doctor .
5. Diagnosis -wise Test .
6. Billing vs Insurance status .

Data Preparation :

After Loading the data in power bi through GET DATA option we found that there was no proper column header and the actual column header data was present there in the first row , So we selected the First Row and used the USE FIRST ROW AS HEADERS option from the ribbon and made it as the COLUMN HEADERS .

We checked all the column types of the columns and everything was found ok so we CLOSED AND APPLY the data and stepped further for the analysis part .

Key Metrices :

• Bed Occupancy.
• Patient Count By Disease.
• Feedback Volume for Doctors.
• Total Billing Amount.
• Billing vs Health Insurance Amount.
• Date Range Filtering .

Lets dive into the analysis .

Analysis :

1. Bed Occupancy :

A) Private ward has the highest bed occupancy (2531 beds) compared to the General ward (1686 beds) and the ICU (843 beds). The higher occupancy in the Private ward indicates the Patients with better financial condition prefers for personalized care and medical treatment amenities . It is essential to understand whether the current private ward capacity is meeting the demand or if there is a need for expansion or more efficient resource allocation. Analyzing patient profiles, length of stay, and revenue generation from private rooms could provide insights into the financial viability and profitability of this ward.

B) The General ward has the second-highest occupancy, which is expected as it caters to a broader patient population and is likely more affordable than private rooms. It would be valuable to examine if the occupancy levels vary across different departments or specialties within the general ward, as this could help identify potential bottlenecks or areas requiring additional resources. Comparing the general ward occupancy with the overall hospital capacity could indicate if there is a need for increased capacity or if resources could be reallocated based on demand patterns.

C) The lower occupancy in the ICU is expected due to the specialized nature of intensive care units and the limited availability of such resources. Investigating the length of stay and patient outcomes in the ICU could provide insights into the efficiency of care delivery and potential areas for improvement. The ICU occupancy is the lowest which also explains the fact that ICU beds are reserved for critically ill patients and are typically in limited supply due to the specialized care .

D) The Gap between the demand for Private and General ward is (2531- 1686) which is (845 beds) and the Gap between the demand for General and ICU is (1686-843) which is ( 843 beds) . This suggest a significant gap of the resource allocation and demand between the private and general facilities compared to the gap between general and intensive care units .

Overall, the bed occupancy data highlights the relative demand and utilization patterns across different hospital facilities, which can inform resource allocation, capacity planning, and operational decisions to optimize patient care and operational efficiency.

2). Patient Count By Disease :

The Funnel chart provides a visual comparison of patient counts for six different health conditions. Each disease is represented by a horizontal bar, with the length of the bar corresponding to the number of patients .

Analysis :

A) Viral Infection

• Patient Count: 1.42K
• Proportion of Total: (1.42 / 5.06) * 100 = 28.06%
• Analysis: This is the most prevalent condition, accounting for over a quarter of the total patient count. This high figure suggests a significant impact on healthcare resources and might warrant priority in healthcare planning and resource allocation.

B) Flu

• Patient Count: 1.21K
• Proportion of Total: (1.21 / 5.06) * 100 = 23.91% .
• Analysis: The flu is the second most common condition. Its high patient count is indicative of seasonal outbreaks and highlights the importance of vaccination and public health campaigns.

C) Malaria

• Patient Count: 1.01K
• Proportion of Total: (1.01 / 5.06) * 100 = 19.96%
• Analysis: Malaria is significant, suggesting it is an endemic issue in the region. Preventive measures such as mosquito control and malaria prophylaxis are critical.

D) Typhoid

• Patient Count: 0.81K
• Proportion of Total: (0.81 / 5.06) * 100 = 16.01%
• Analysis: Typhoid’s patient count is notable and suggests issues with sanitation and clean water. Addressing these through public health interventions could reduce its incidence.

E) Pneumonia

• Patient Count: 0.41K
• Proportion of Total: (0.41 / 5.06) * 100 = 8.10%
• Analysis: Pneumonia has a lower patient count but still represents a significant health concern, particularly for vulnerable populations such as the elderly and children.

F) Fracture

• Patient Count: 0.20
• Proportion of Total: (0.20 / 5.06) * 100 = 3.95%
• Analysis: Fractures are the least common condition in this dataset. While not a disease, the inclusion indicates the importance of injury prevention and effective treatment protocols.

Implications :

Resource Allocation: The high patient counts for viral infections and flu suggest that healthcare resources such as medical staff, hospital beds, and medication should be prioritized for these condition .

Preventive Measures: Malaria and typhoid’s significant counts indicate a need for strong preventive measures, including vaccination, improved sanitation, and public health education.

Emergency Preparedness: The lower but significant counts for pneumonia and fractures highlight the need for emergency medical services and preparedness to handle acute conditions.

Recommendations :

• Enhanced Surveillance: Implement robust disease surveillance systems to monitor and respond to outbreaks .
• Public Health Campaigns: Increase awareness and preventive measures for the most common conditions, especially viral infections and flu.
• Resource Distribution: Allocate healthcare resources efficiently based on the prevalence data to ensure readiness for high-incidence conditions.
• Sanitation and Hygiene: Improve infrastructure related to water and sanitation to reduce the incidence of diseases like typhoid and malaria .
• Emergency Care: Strengthen emergency care services to better handle conditions like pneumonia and fractures.

3) Feed Back Volume For Doctors :

Analysis :

A) Equal Distribution: The most striking feature is the almost perfectly equal distribution of feedback volume across all doctors. Each doctor receives 14.28% or 14.29% of the total feedback, suggesting a remarkably balanced patient load or feedback collection system.

B) Feedback Nature: The chart doesn't specify whether this feedback is positive, negative, or mixed. Understanding the nature of feedback would provide more insight into patient satisfaction and care quality.

C) Specialization: There's no indication of the doctors' specialties. In a typical healthcare setting, different specialties might see varying patient volumes, so this equal distribution is unusual unless it's a general practice or the feedback is normalized .

D) Time Period: The data does not specify the time period of the feedback, Knowing whether this represents a week, month, or year would help in assessing workload and patient interaction frequency.

E) Team Composition: The team appears diverse based on names, potentially indicating a multicultural staff. This could be beneficial for serving a diverse patient population .

F) Performance Metrics: While feedback volume is important, it doesn't necessarily correlate with quality of care. Additional metrics like patient outcomes, satisfaction scores, and peer reviews would provide a more comprehensive evaluation.

G) Workload Implications: The equal distribution might indicate a well-managed workload among doctors, which could contribute to preventing burnout and maintaining consistent care quality.

H) Patient Assignment System: This distribution could suggest an efficient patient assignment system that evenly distributes cases among available doctors.

4. Total Billing Amount :

Analysis :

a) Metric: Total Billing Amount This is a crucial financial metric in healthcare, representing the sum of all charges for services rendered.

b) Value:( $190M ) The total billing amount is $190 million, which is a significant figure for a healthcare organization.

c) Time Period: The card doesn't specify a time frame. This could represent monthly, quarterly, or annual billing. For accurate analysis, we'd need to know the exact period this covers.

d) Context : Without comparative data (e.g., previous periods, targets, industry benchmarks), it's challenging to fully interpret this figure's significance.

e) Revenue Implications: While this represents billed amount, actual collected revenue may differ due to factors like insurance adjustments, patient inability to pay .

f) Scale of Operations: A $190M billing suggests a large-scale operation - possibly a major hospital system, a large group of clinics, or a regional healthcare network.

g) Service Mix: This figure encompasses all billable services, potentially including inpatient care, outpatient procedures, laboratory tests, imaging, and professional fees.

h) Growth Analysis: Tracking this KPI over time would reveal growth trends, seasonal variations, and the impact of new service lines or expanded operations.

i) Operational Capacity: This billing amount indirectly indicates the volume of services provided, reflecting on the organization's operational capacity and utilization .

j) Market Position: Comparing this figure with regional competitors could indicate the organization's market share and competitive position.

k) Financial Health Indicator: While high billing is generally positive, it must be balanced against costs and collection efficiency to truly gauge financial health.

l) Compliance Consideration: Large billing amounts can attract regulatory scrutiny, emphasizing the need for robust compliance programs

Recommendations :

• Analyze the cost structures associated with generating this billing amount.
• Investigate collection rates to understand the gap between billed and collected amounts.
• Benchmark against industry standards for similar-sized organizations.
• Review the distribution of billing across different service lines and payers.
• Assess the efficiency of the billing process and claim submission timeliness.

5) Billing vs Health Insurance Amount :

The chart compares the sum of billing amounts to the sum of health insurance amounts for six medical conditions: Viral Infection, Flu, Malaria, Typhoid, Pneumonia, and Fracture .

Analysis :

A) Billing vs. Insurance Coverage: For all conditions, the billing amount exceeds the insurance coverage, indicating out-of-pocket expenses for patients or potential revenue gaps for healthcare providers.

B) Condition-wise Analysis: a) Viral Infection: Highest billing ($53,416K) and insurance coverage ($48,075K) . Gap: $5,341K (10% of billing not covered)

b) Flu: Second highest in both billing ($45,878K) and coverage ($41,290K). Gap: $4,588K (10% not covered)

c) Malaria: Third highest billing ($38,006K) and coverage ($34,205K). Gap: $3,801K (10% not covered)

d) Typhoid: Fourth in billing ($30,353K) and coverage ($27,317K) .Gap: $3,036K (10% not covered)

e) Pneumonia: Fifth in billing ($15,216K) and coverage ($13,695K) . Gap: $1,521K (10% not covered)

f) Fracture: Lowest billing ($7,564K) and coverage ($6,808K). Gap: $756K (10% not covered)

C) Consistency in Coverage: Interestingly, all conditions show a consistent 90% insurance coverage rate, with 10% of billing not covered across the board.

D) Volume Implications: The varying total amounts suggest different volumes or severity levels for each condition. Viral infections and flu appear to be the most common or costly.

E) Seasonal Factors: The high billing for viral infections and flu might indicate data from a peak season for these diseases .

F) Treatment Costs: Despite being potentially more acute, fractures have the lowest total billing, suggesting either lower incidence or lower average treatment costs compared to the other conditions.

G) Insurance Policy Insights: The consistent 90% coverage across all conditions suggests a standardized insurance policy structure, possibly indicating data from a specific insurance plan or provider.

H) Patient Financial Burden: While the coverage rate is consistent, the absolute out-of-pocket costs vary significantly, from $756K for fractures to $5,341K for viral infections.

Recommendations :

• Investigate the reasons behind the consistent 10% gap in coverage across all conditions.
• Analyze the cost-effectiveness of treatments, especially for high-billing conditions like viral infections and flu.
• Explore preventive measures to reduce the incidence and thus the overall cost of the most expensive conditions.
• Consider negotiating with insurance providers to improve coverage rates, particularly for high-cost conditions.
• Develop patient education programs to help manage out-of-pocket expenses, especially for commonly occurring conditions.
• Conduct a more granular analysis of the 'viral infection' category to identify specific viruses driving high costs.

6. Date Range Filtering :

This filter allows for the selection of a specific time period, likely for analyzing healthcare data between two admit dates.

The period between the earliest admit and discharge dates is approximately 6 months and 26 days. This suggests:

a)Potentially complex or severe cases requiring long-term care

b) Could indicate chronic conditions or complications

c) Might represent data from a long-term care facility or specialized treatment center .

The follow-up occurs 14 days after the earliest discharge , indicating:

a) A relatively prompt follow-up protocol.

b) Adherence to best practices for continuity of care.

c) Potential focus on reducing readmission rates .