Clinic Performance - Data Analysis
In this project, I acted as a Freelance Data Analyst to report on the performance of a health clinic in Singapore. The brief was to report the relationship between the number of patients and the processing time for each patient. Other than that, I was also asked to report profit gained from each type of patient. As per the request of the Clinic Manager, the currency used is USD.
Business Requests & Details
For this Data Analysis project, business requests & details contain directions from the client on how to approach the analysis. Based on the table below, I followed the user requirement to fulfill the project analysis and delivery as well as to ensure that acceptance criteria were maintained throughout the project.
Data Cleaning & Pre-Processing
In order to analyze the data, I need to do some cleaning on the spreadsheet given. Not just that, but I also need to standardize certain values and add additional rows to support the analysis. All of the mentioned processes is done by using Ms Excel.
Here is the spreadsheet given by the clients, notice that there is no standardization on the currency and the process time per patient are also not yet calculated.
The spreadsheet below is already cleaned, standardized, and added extra rows for analysis purposes. The extra rows added are the process time (completion time - entry time), process time in minutes, entry hour of each patient, and day of the week (Monday - Sunday).
DIM_Date
Collect Important Data Points
Once the last step is done, I created a series of pivot tables to gather the aggregate data required for the visualization report. There are 4 pivot tables that I made, as seen below:
1. For Average Process Time and Number of Patients comparison chart
2. For detailed analysis on Number of Patients
3. For detailed analysis on Average Patients Process Time
4. For Profit Statement chart
Presentation Report
After collecting important data points, a presentation report is compiled. Not only does this presentation contains charts, but this also contains business recommendations for the Clinic Manager. Originally, the report is made in Ms Powerpoint, but for more reader-friendly interface, the report is presented as seen below:
Findings:
-
The busiest period because of the influx of patients is on Monday and Tuesday while the longest average process time occurs on Monday and Wednesday. The number of patients doesn't necessarily have positive correlation with the average process time.
-
Monday and Tuesday are the most affected days by the influx of patients, especially between 8-11 in the morning and on 14 afternoon.
-
Average Process Time on Monday, Wednesday, and Thursday are the longest, especially between 8-10 in the morning and on 13 afternoon.
-
Insurance patients contribute the largest to the profit (44%) followed by Corporate (21%) and HMO (19%).
Recommendations:
-
Ensure adequate staffing on Monday and Tuesday to handle the influx of patients that are historically occurring. Additional personnel might be needed during busy hours (8-11 and 14).
-
The aggregate average process time is 44 minutes, nothing can be changed about it since patients require different medical treatments. However, using the heatmap, we could provide the next patients with an estimate of how long they may need to wait as the current patient are undergoing his/her treatments.
-
Focus on improving Insurance services, for example by acquiring more partnerships or providing exclusive discounts for insurance patients.