IOTB TECH Hospital wanted to make better sense of their patient records. They had years of data sitting in Excel but couldn't easily read or understand it. So my job was to go through that data, clean it up, analyze it, and present findings that can actually help the hospital make smarter decisions.
I used Pivot Tables and Pivot Charts in Microsoft Excel to break the data down and answer real business questions about patients, revenue, medications, and more.
The dataset contained 55,500 patient records covering a 5-year period from May 2019 to May 2024. After cleaning, I was left with 54,860 usable records.
The data covered information like patient age, gender, blood type, medical condition, the type of admission, how long they stayed, what medication they were given, what their test results were, and how much they were billed.
Before doing any analysis, I had to make sure the data was accurate and properly formatted. Here is what I found and fixed:
Duplicates — There were 534 rows that appeared more than once in the dataset. I removed all of them using the Remove Duplicates feature in Excel.
Negative Billing Amounts — 108 records had negative billing values, which makes no sense for a hospital bill. I filtered those out and deleted them since they were clearly incorrect entries.
Formatting — The Billing Amount column was not formatted as currency so I fixed that. The date columns also needed to be set to a proper date format so Excel could read them correctly.
New Columns Added — I created two new columns that were not in the original data:
- Length of Stay — calculated by subtracting the Admission Date from the Discharge Date. This tells us how many days each patient spent in the hospital.
- Age Group — grouped patients into brackets (Under 18, 18–29, 30–44, 45–59, 60–74, 75+) using a nested IF formula based on their age. This made it easier to spot trends by age.
After all this, the dataset was clean, properly formatted, and ready for analysis.
The hospital served 54,860 patients over 5 years. Looking at gender, the split was almost perfectly even — about 27,449 males and 27,411 females. So the hospital is not skewed towards one gender at all.
When it comes to age, the 45 to 59 age group had the most admissions with around 13,500 patients. This makes sense because this is the age range where chronic diseases tend to become more common. Younger patients under 18 had the least admissions at about 1,800.
For blood types, all 8 types were nearly evenly distributed across patients, with no single blood type standing out significantly. This tells the hospital they need to keep equal supplies of all blood types at all times.
The most common condition seen in the hospital was Arthritis, with around 9,200 patients. But when it comes to money, Diabetes actually generated the highest billing — about ₦248 million — even though it had fewer patients than Arthritis. This tells us that treating Diabetes costs more per patient than treating Arthritis.
Something else that stood out was Asthma. Asthma patients stayed in the hospital the longest on average — about 15.7 days. Every other condition averaged around 15.4 to 15.6 days. It may not seem like a big difference, but across thousands of patients it adds up to a lot of bed time and resources.
The hospital made a total of ₦1.4 Billion over the 5 years. That works out to roughly ₦280 million per year and about ₦25,500 per patient on average. The highest single bill in the entire dataset was ₦52,764.
Looking at the monthly trend, revenue was very stable — sitting between ₦22 million and ₦25 million every single month from 2019 to 2024. There were no major crashes or spikes. This is actually a really good sign. It means the hospital has a predictable, reliable income stream.
In terms of insurance, Cigna brought in the most billing revenue at around ₦285 million, followed by Medicare and Blue Cross. The differences between providers were not massive but Cigna consistently came out on top.
For admission types, Elective admissions generated the most revenue at about ₦475 million. Emergency and Urgent admissions were close behind. So planned procedures are actually the biggest money-maker for this hospital.
Something interesting here — all three admission types (Elective, Urgent, and Emergency) were almost equally common. Each had around 18,000 to 18,300 patients. This shows the hospital handles a really balanced mix of cases.
Monthly admissions were also very consistent throughout the year. January and March tended to be slightly busier, but there were no dramatic seasonal spikes. The hospital runs at a pretty steady pace year-round.
For emergency admissions specifically, female patients had slightly more than males — about 9,200 versus 9,050. It is a small gap but worth watching.
Out of all the test results recorded, 33.7% came back as Abnormal — that is the highest of the three categories, slightly above Normal (33.5%) and Inconclusive (32.8%). That means more than 1 in every 3 patients is getting an abnormal result. That is something the hospital really needs to pay attention to.
Arthritis was the condition most linked to abnormal results, followed closely by Diabetes and Hypertension. All conditions had similar numbers though, so this is a hospital-wide issue, not just linked to one condition.
Lipitor was the most prescribed medication with around 11,100 prescriptions. This makes sense given how many patients have chronic conditions like Diabetes and Hypertension that require cholesterol management.
However, even though Lipitor was prescribed the most, Ibuprofen generated more billing revenue — about ₦285 million compared to Lipitor's ₦283 million. This is an interesting gap. Ibuprofen being billed higher despite fewer prescriptions could point to pricing issues or more expensive treatment courses.
Here are the most important things I discovered from analyzing the hospital data:
- The hospital served 54,860 patients over 5 years and generated a total revenue of ₦1.4 Billion.
- Arthritis is the most common condition but Diabetes generates the highest billing at ₦248 million.
- The 45–59 age group has the most admissions, making middle-aged patients the hospital's biggest patient group.
- Asthma patients stay the longest in the hospital at an average of 15.7 days.
- Elective admissions generate the most revenue at approximately ₦475 million.
- Cigna is the top insurance provider contributing ₦285 million in billing.
- Over 33.7% of all test results came back as Abnormal — more than 1 in every 3 patients.
- Lipitor is the most prescribed medication but Ibuprofen generates the most billing revenue.
- Gender distribution is nearly equal — 50.03% male and 49.97% female.
- Monthly revenue remained stable between ₦22 million and ₦25 million every month without any major drops.
These are patterns and things I noticed while going through the data:
- All six medical conditions had very similar patient numbers, meaning the hospital treats a wide and balanced range of chronic diseases.
- All three admission types — Elective, Urgent, and Emergency — were almost equally common at around 18,000 each. The hospital is not overwhelmed by one type.
- All 8 blood types were almost equally distributed, which means the hospital needs to maintain equal blood supply for all types.
- Female patients had slightly more emergency admissions than males, which is worth monitoring over time.
- Monthly admissions were consistent throughout the year with no dramatic seasonal spikes — the hospital runs at a steady pace year-round.
- Despite Lipitor being the most prescribed drug, Ibuprofen costs more per patient — suggesting possible pricing or prescription inefficiencies.
- The data had quality issues — 534 duplicates and 108 negative billing records were found, pointing to gaps in how data is entered into the system.
- Arthritis leads in both patient count and abnormal test results, making it the condition that needs the most diagnostic attention.
On Revenue — The hospital is doing well financially but monthly revenue seems to have a ceiling around ₦25 million. Management should look at ways to push past that, perhaps through expanding elective surgery capacity since that is the highest-earning admission type.
On Diabetes — Since Diabetes costs the most to treat, the hospital should invest more in outpatient Diabetes management and prevention programs. Catching it early and managing it outside the hospital will reduce expensive inpatient admissions over time.
On Asthma — The long stay duration for Asthma patients is costing the hospital beds and resources. Reviewing Asthma care protocols and improving discharge planning could help bring that average stay down.
On Abnormal Test Results — Having over a third of all results come back abnormal is concerning. The hospital should review its diagnostic processes, invest in better testing equipment, and build a stronger follow-up care system for these patients.
On Cigna — As the top billing insurance partner, the hospital should prioritize renewing and strengthening this relationship. It may also be worth negotiating better terms given the high volume of Cigna-covered patients.
On Ibuprofen Costs — A pharmacy audit on Ibuprofen pricing and usage patterns would be worthwhile. If cheaper alternatives can achieve the same outcomes, that could reduce costs for patients and improve the hospital's billing efficiency.
On Data Quality — The 642 problematic records found during cleaning (534 duplicates and 108 negative billing entries) point to gaps in how data is being entered. The hospital should implement data validation rules in their billing system to stop bad records from being created in the first place.
Looking at this dataset as a whole, IOTB TECH Hospital is in a strong position. Revenue is stable, patient flow is balanced across genders, ages, and admission types, and the hospital manages a wide range of conditions and medications effectively.
The areas that need the most attention are the high rate of abnormal test results, the long stay duration for Asthma patients, and the data quality issues in the billing system. Addressing these three things would have the biggest positive impact on both patient outcomes and hospital performance.
1. The hospital is financially strong. IOTB TECH Hospital made ₦1.4 Billion over 5 years. Revenue was steady every month with no major drops, which shows the hospital is running well.
2. Diabetes costs the most to treat. Even though Arthritis has more patients, Diabetes generates the highest billing at ₦248 million. Treating Diabetes is simply more expensive per patient.
3. Middle-aged patients visit the most. Patients between 45 and 59 years old make up the largest group with about 13,500 admissions. The hospital should focus more resources and programs on this age group.
4. Asthma patients stay the longest. On average, Asthma patients spend 15.7 days in the hospital — longer than any other condition. Better Asthma care protocols could help reduce this and free up beds.
5. Elective admissions make the most money. Planned procedures generated about ₦475 million — more than Emergency or Urgent admissions. Expanding elective services would boost the hospital's revenue significantly.
6. Too many test results are coming back abnormal. 33.7% of all test results were Abnormal. That means more than 1 in 3 patients has an abnormal result, which is a serious concern that needs urgent attention.
7. Cigna is the hospital's most valuable insurance partner. Cigna brought in the highest billing revenue at ₦285 million. The hospital should protect and strengthen this relationship.
8. Ibuprofen bills more than Lipitor despite fewer prescriptions. Lipitor is prescribed the most but Ibuprofen generates more revenue. This suggests Ibuprofen treatments are more expensive and the hospital should review whether cheaper alternatives exist.
9. The data has quality problems that need fixing. 534 duplicate records and 108 negative billing entries were found during cleaning. The hospital needs better data entry controls to stop these errors from happening again.