Optimizing Resource Allocation Based on Demand Forecasts
This analysis used Excel, Tableau Public for the dashboard, and iMovie for the video presentation. To follow along in greater detail please click the links below.
Problem and Background
A staffing agency is looking to aid medical communities across America during the upcoming influenza season by identifying when and where to send additional staff each month.
Needs
-
Determine levels of influenza throughout the year.
-
Identify high risk communities and demographics.
-
Develop a resource allocation strategy for the upcoming year.
Data Cleaning, Wrangling, and Integration
Data Cleaning
Two sets of data were used in this analysis, the first sourced from the Centers for Disease Control and Prevention focusing on the amount of deaths attributed to influenza from 2009 to 2017. The second set is from the US Census Bureau on the population of each state by age group. These sets were inspected and cleaned in Excel, with each step being documented. Each set underwent data profiling, quality, consistency, and integrity checks, and statistical analysis in preparation for integration.
​
Data Integration and Wrangling
​
Both data sets had information that overlapped enough to create a key for data integration, but they first underwent wrangling to match each other and also remove unnecessary information. The combined key was made up of the state and year columns, and partial information from the US Census Bureau was successfully integrated into the CDC data set. All checks performed before integration were redone to document and check for any inconsistencies in the data.
Analysis Highlights
Understanding the Ebb and Flow of Influenza
Although we often hear about flu season during the winter months, it is important to see what the peaks and falls of influenza in the data. Over the whole country the amounts of fatalities by influenza begin to increase around October and peak in January before decreasing. This pattern stayed consistent throughout all states.
​
Medical facilities across the US will need additional staff to combat the increased need during these months.
Click Image to Enlarge
Statistical Hypothesis Testing
Click Image to Enlarge
According to the CDC, people 65 and older are considered at a higher risk of infection and complications resulting from influenza. As this is the demographic in the data I applied inferential statistics towards my population sample and conducted a t-test. This way I could show if this remains true in my data and by how much.
​
Research Hypothesis: If a state has a large vulnerable population, then this state will require more staffing during flu season.
Null Hypothesis: The mortality rate for those aged 65+ years will not be higher than the mortality rate for those under 65 years.
​
I calculated the mortality rates for both groups, and in using a t-test I proved statistically that indeed the demographic 65 and older have a higher mortality rate. I could then continue to build on this basis as a guide to allocating staff to areas with larger populations of this demographic.
Examining Need Across America
Building on my hypothesis I began to take note of states with large populations of people 65 and older. After I assessed which areas had higher concentrations, I could move onto looking into communities that had a high percentage of senior death.
This group of vulnerable people made up 50% of total deaths in 33 states with California, New York, Florida, Texas, and Pennsylvania having the highest death counts.
​
In a similar vein, California, New York, Florida, Pennsylvania, along with Illinois had the highest percentages of fatalities in their senior population. The overlap in some of these states highlighted them as high priority areas.
Click Image to Enlarge
Conclusion and Next Steps
Conclusion:
Every year medical facilities across the US see a steady increase in influenza cases. Those in vulnerable populations, such as people 65 years and older, have an increased risk of death due to infection. Lack of medical professionals in an area can lead to lapses in medical care for the community, and in order to combat this a priority system was set up based on each state's needs.
​
First priority was given to states with the highest number of influenza deaths, namely California, New York, Texas, Pennsylvania, and Florida. Second priority was given to rural communities so that any rise in infections does not spiral to endanger the whole community. Third priority was given to states where the percentage of senior death was over 50% of their demographic, noting a significant risk.
This analysis still has its limits to decipher individual staffing needs, and so it is recommended to create a hotline for medical facilities to call in staffing changes. This would allow for immediate notification and action and help begin to gather more data on how the staffing agency fared during peak months.
​
This project was completed with a data dashboard created on Tableau, and a recorded video presentation to be delivered to various stakeholders. Detailed reports and documentation of each step were also compiled during this analysis and attached to the project deliverables.
​
​
Next Steps:
​
-
Actively access how this staffing plan handles the upcoming influenza season.
-
Continue to collect data on influenza deaths, and expand focus to different vulnerable communities to gain deeper understanding.
-
Survey communities and medical workers on concerns for influenza season to gauge how staffing recommendations are being handled.