My Site
Influenza Season - Staffing Allocation
A major USA medical staffing company providing temporary front-line staff to hospitals and clinics, find themselves inundated every year with reactive, ad-hoc and last-minute requests, which is exacerbated during the United States influenza season.
​
Some patients, especially those in vulnerable populations, develop serious complications and end up requiring hospitalization. This influx of patients means the hospitals and clinics need additional staff to adequately treat these extra patients.
​
The staffing agency has a limited number of nurses, physician assistants, and doctors on their staff. They do not have any budget to hire additional staff.
Project Particulars
Goal: To develop a pro-active staffing plan that utilizes all available agency staff per state requirements, without necessitating additional resources. Minimize instances of understaffing and overstaffing across states – a state is considered understaffed when the staff-to-patient ratio is lower than 90% of the required ratio, and overstaffed if greater than 110%.
​
Objective: Determine when to send staff, and how many, to each state. ​
Scope: The agency covers all hospitals in each of the 50 states of the United States, and the project will plan for the upcoming influenza season.
​​
Role: Data Analyst
Primary Stakeholders: Agency frontline staff, hospitals and clinics who are the agency’s client base and staffing agency administrators.​
Project Scale: 3 weeks
​​
Tools Used: Microsoft Excel (inc Power Query), Tableau
Client Assumptions
“Vulnerable populations suffer the most-severe impacts from the flu and are the most likely to end up in the hospital.”
​
Vulnerable populations: patients likely to develop flu complications requiring additional care, as identified by the Centers for Disease Control and Prevention (CDC). These include adults over 65 years, children under 5 years, and pregnant women, as well as individuals with HIV/AIDs, cancer, heart disease, stroke, diabetes, asthma, and children with neurological disorders.
The provider centres are to be treated as uniform in size and require Staff-to-Patient ratio ranges between 1:3 to 1:5
“Flu shots decrease the chance of becoming infected with the flu.”
​
- A count of the historical influenza deaths gives an indication of the severity of flu in an area. Deaths can be prevented with flu-shots and adequate medical staff. In the United States, each state has a different population composition, meaning that some states will have more vulnerable populations.
Validating Assumptions
To ensure optimal staff-to-patient ratios for the upcoming influenza season, we first validated the assumptions made by the agency. We identified several limitations in the provided datasets, which made it challenging to accurately determine the precise number of staff required per state.
​
To address these gaps, we conducted data scraping from various sources, including the CDC, Census data sites, and climate bodies. This additional data collection aimed to enhance the accuracy of our staffing models by incorporating more comprehensive and relevant information.
Data Wrangling Process
Confirming the Hypothesis
States with a higher mortality rate of people, aged 65 and over, will have the highest number of patients presenting for treatment at centers, in the coldest seasons of the year.
01
Age Demographics
We quickly identified that age does indeed prove to be a determining factor in increased cases of mortality, and therefore will use the age and seasonality variables to inform of our recommendations to proactively manage staffing levels. The analysis was conducted using pie graphs, box and whisker plots, and sectional analysis to confirm the findings.
02
Socio-Economic Factors
It appears that there is no direct correlation between average income and mortality rates, as evidenced by California's high average income but vulnerability to influenza. However, states with higher racial diversity seem to have a stronger correlation with higher mortality rates, indicating that certain ethnic groups may be more susceptible to the flu. The analysis was conducted using scatter plots and a combination of mapping techniques, including dot density and choropleth mapping to confirm the findings.
03
Seasonality
Based on statistical data, it has been observed that mortality rates are highest in the 1st and 4th quarters of the year. The 1st quarter, in particular, has a higher forecast projection of increased mortality cases. Therefore, it is recommended to concentrate staffing increases during these seasons to ensure adequate care for patients. The analysis was conducted using seasonally concentrated line charts, which utilized in-built calculated fields to project the forecast on patient numbers per US state.
04
Staffing vs Mortality Rates - Seasonal Insights
The analysis of normalized patient and mortality rates by state in Quarter4 data revealed that the West and Northeast regions have a higher demand for additional healthcare staffing due to the correlation between patients presenting for care and the percentage of deaths in relation to the patients being highest in these regions.
The Southeast region, despite receiving the highest number of patients, could be considered medium risk as the correlation between patients and mortality rates is low. The key observation is that in these areas, patient and mortality rates surpass the number of available providers.
It is important to note that provider data was unavailable prior to mid-2010, thus the years 2011-2017 were used for the analysis.
Meeting the Brief
Staffing numbers that matter.
To address staffing needs across states where no national standards or comprehensive data existed, sophisticated approach utilizing advanced Tableau techniques was employed. Given the assumption of uniformity in provider center sizes and an ideal staff-to-patient ratio ranging from 1:3 to 1:5 staff per region, a robust solution to deliver accurate staffing plans was created.
​
The key to delivering this information, is a dynamic Tableau visualization enhanced with a "Seasons and S2P Ratios" parameter. This parameter allows users to select from a variety of staffing scenarios, such as Staff Q1 3:1 or Staff Q2 4:1, and automatically recalculates staffing levels in real-time. This interactive feature ensures that users can instantly see how different ratios impact staffing requirements.
​
A calculated field was developed, using a CASE statement to match the selected ratio with the correct staffing values. This approach allows the bar chart to dynamically reflect total staff needed per centre, for each quarter. The visualization provides clear, interactive columns for each US region, and detailed charts for state-by-state staffing allocations.
​
Additionally, performance management dashboards will be designed from these charts to track and address under-staffing and over-staffing trends proactively, thus creating a comprehensive, data-driven staffing solution.
​
Interract with the chart here.
Recommendations and Next Steps
Based on the analysis, it is recommended to divide states into high risk and medium risk categories when prioritising rostering requirements.
-
High risk states with over 15,000 deaths per annum include: California, New York, Texas, Pennsylvania, Florida, Illinois, and Ohio.
-
Medium risk states with over 9,000 deaths per annum include: North Carolina, Michigan, Massachusetts, Tennessee, Georgia, Virginia, New Jersey, and Missouri.
-
The West and North-East regions have the highest mortality rates per 100,000 population and should establish KPI's in mortality rate reduction, as focus efforts to decrease rates. ​
Risk Classification
-
High Risk States: 7
-
​Medium Risk States: 8
-
Low Risk States: 35
Future Focus
​
-
Data Collection: Emphasize gathering detailed data from hospitals, clinics, and staffing agencies.
-
KPIs: Develop key performance indicators (KPIs) based on collected metrics.
-
Establish Regional Dashboards for proactive staffing management
Follow-Up
​
-
A detailed list of desired metrics and associated KPIs was provided.
-
Dashboard samples were shared with key stakeholders to align on management indicators.