No products in the cart.
MODULE 24: ADVANCED ANALYTICS & PREDICTIVE MODELING
Section 4: Operational Forecasting and Staffing Models
Apply predictive analytics to the business of pharmacy. Learn how to build models that can forecast future medication dispensing volumes, allowing you to optimize inventory and create data-driven staffing models for the pharmacy.
SECTION 24.4
Operational Forecasting and Staffing Models
From Reactive Firefighting to Proactive Planning.
24.4.1 The “Why”: Taming the Chaos of Pharmacy Operations
Every pharmacist and pharmacy technician has lived through the chaotic reality of pharmacy operations. It’s the unexpected STAT order that arrives when you’re already buried in work. It’s the frustration of telling a nurse that a critical, high-cost medication is out of stock because of an unexpected surge in demand. It’s the whiplash of a Monday morning, drowning in order verification queues, followed by a strangely quiet Wednesday afternoon. For decades, pharmacy has managed this chaos through experience, intuition, and a philosophy of “just-in-case” over-staffing and over-stocking—an expensive and inefficient strategy.
The core problem is uncertainty. We are constantly reacting to the future because we lack the tools to anticipate it. Operational forecasting is the application of data science to dramatically reduce this uncertainty. It is the practice of using historical data to make statistically rigorous predictions about future events, specifically related to workload and resource consumption. This is not about gazing into a crystal ball; it is about systematically identifying the hidden patterns in your own data to create a powerful competitive advantage: the ability to prepare.
Imagine if you knew, with 90% confidence, what your IV room workload would be next Tuesday between 2 PM and 4 PM. How would that change your staffing? Imagine if you could predict a surge in demand for albumin three weeks in advance. How would that change your purchasing strategy and prevent a stockout? This is the power that forecasting unlocks. It allows a pharmacy department to move from a reactive, firefighting posture to a proactive, evidence-based planning posture.
In this section, we will delve into the world of time-series analysis, the specific branch of data science dedicated to analyzing and forecasting data points collected over time. You will learn how to decompose your operational data into its core components—trend, seasonality, and noise—and how to apply forecasting models to predict future demand. We will cover two of the most vital applications for any pharmacy department: optimizing inventory for high-cost medications and building dynamic, data-driven staffing models. Mastering these skills will enable you to have a profound impact on the two largest components of any pharmacy budget: drug spend and labor costs, transforming your role from a clinical expert into a strategic operational leader.
Pharmacist Analogy: The Flu Season Forecaster
Think back to your time as a community pharmacy manager in August. The most important operational question on your mind is: “How much flu vaccine should I order, and how many extra staff hours will I need for the next four months?” You are, in essence, a forecaster.
How do you make this decision? You don’t guess. You use a mental model based on historical data.
- Trend Analysis: You look at your dispensing records. “Last year we did 1,000 flu shots, the year before we did 900, and the year before that we did 800. Our volume is trending upwards by about 100 shots per year.”
- Seasonality Analysis: You know that demand is not flat. “The real rush starts the last week of September, peaks in mid-October, and then tails off through November. We get a little secondary bump after Christmas.” You instinctively understand the seasonal pattern of demand.
- External Factors (Exogenous Variables): You consider other data points. “The news is reporting this will be a particularly bad flu season. And the new competitor pharmacy down the street just closed. That might drive more traffic to us.” You are factoring in external variables that could influence your forecast.
Based on this multi-faceted analysis, you make a forecast. You order 1,150 doses of vaccine and schedule your technicians for more hours in October than in December. The formal time-series forecasting models we will explore in this section do the exact same thing. They just replace your intuition with rigorous mathematical algorithms that can analyze thousands of data points and uncover much more subtle and complex patterns. The goal, however, is identical: to use the patterns of the past to prepare for the demands of the future.
24.4.2 Deconstructing Time: The Anatomy of a Time Series
The first step in forecasting is to understand the nature of your data. Time-series data is unique because it has a natural temporal ordering. This order is not arbitrary; it contains the very patterns we are trying to exploit. Any time series can be decomposed into four key components.
The Components of a Time Series
1. Trend
The trend is the long-term, underlying direction of the data. It’s the “big picture” movement that remains after you’ve smoothed out the short-term fluctuations.
- Upward Trend: The total number of doses dispensed by your pharmacy is likely increasing year-over-year as the hospital grows and acuity increases.
- Downward Trend: The spend on a specific brand-name drug after its patent expires and generics become available.
- No Trend: The volume of a medication used for a stable, chronic disease population might remain flat over time.
2. Seasonality
Seasonality refers to predictable, repeating patterns or cycles that occur at fixed intervals. This is often the most important component for operational forecasting in a hospital.
- Hourly: The mid-morning peak in medication orders after physician rounding.
- Daily: Higher order volume on Mondays due to weekend admissions, lower volume on Saturdays.
- Monthly: Increased demand for certain chemotherapy agents during specific weeks of the month.
3. Cyclicality
Cycles are longer-term, less predictable fluctuations that are not tied to a fixed calendar interval. These are often driven by broader economic or demographic shifts.
- Economic Cycles: During a recession, a decrease in elective surgeries might reduce demand for specific perioperative drugs.
- Pandemics: A global event like a pandemic can create massive, multi-year cycles in demand for specific drugs (e.g., sedatives, antivirals).
4. Noise (or Residual)
This is the random, unpredictable variation that is left over after you account for the trend, seasonality, and cyclical components. It’s the “stuff that just happens.” A forecasting model’s job is to capture the predictable patterns and separate them from the noise.
- A single, unusually large STAT order for an unexpected trauma case.
- A computer system downtime that causes a temporary backlog of orders.
24.4.3 The Forecaster’s Toolkit: From Simple to Sophisticated
There are many different forecasting models, each with its own strengths and weaknesses. As an analyst, you don’t need to be able to derive the complex mathematics, but you do need to understand the conceptual differences between them to know when they might be appropriate.
| Forecasting Model | How It Works (Conceptually) | Best For… | Pharmacist’s “Gotcha” |
|---|---|---|---|
| Naïve Forecast | The simplest possible forecast: “Tomorrow will be the same as today.” The forecast for the next period is simply the value from the current period. | Establishing a baseline. Any model you build must perform better than this simple approach to be considered useful. | It’s obviously wrong most of the time, as it completely ignores trends and seasonality. But it’s a critical benchmark. |
| Simple Moving Average (SMA) | Averages the values from the last ‘n’ periods to make the forecast for the next period. For a 7-day SMA, the forecast for tomorrow is the average of the last 7 days. | Data with no strong trend or seasonality. Good for smoothing out noisy data to see a clearer underlying signal. | It inherently lags behind trends. In a growing trend, it will always under-forecast. It also treats all ‘n’ data points with equal importance. |
| Exponential Smoothing (e.g., Holt-Winters) | A more sophisticated averaging method that gives exponentially more weight to more recent data points. Advanced versions can also explicitly model trend and seasonality. | A huge range of business forecasting problems. It’s often a very robust and hard-to-beat model for many pharmacy operational datasets. | Choosing the right smoothing parameters can be complex, but modern software packages can often automate this. It can struggle with very complex seasonality patterns. |
| SARIMA (Seasonal Autoregressive Integrated Moving Average) |
A powerful, traditional statistical model that forecasts a value based on its own past values (“autoregressive”), past forecast errors (“moving average”), and seasonality. | Data with very strong and regular seasonality, like daily order volumes or monthly drug spend. It is a workhorse in classical statistics. | It can be very difficult and time-consuming to configure correctly (choosing the right p,d,q,P,D,Q parameters). It doesn’t easily incorporate external factors (exogenous variables). |
| Prophet | A modern forecasting library developed by Facebook. It’s designed to be more automated and intuitive, automatically detecting multiple types of seasonality (e.g., daily, weekly, and yearly) and easily incorporating holidays and other special events. | Business forecasting problems where you have multiple layers of seasonality and want to include the impact of external factors. It is very user-friendly. | It can sometimes be “too automatic” and may not perform as well as a finely-tuned SARIMA model for some specific types of data. It’s a tool, not a magic bullet. |
24.4.4 Masterclass Use Case: Forecasting High-Cost Drug Inventory
The Clinical Problem: Your hospital has a growing CAR-T cell therapy program. The conditioning chemotherapy agent used, Fludarabine, is expensive and has a relatively short shelf life. The number of patients being treated is increasing but fluctuates month to month. The inventory manager is constantly struggling to balance the risk of a catastrophic stockout (delaying a patient’s transplant) against the risk of expiring thousands of dollars of drug on the shelf.
Your Task (Framed as a Forecasting Problem): Can we create a monthly forecast for the next 6 months of the total number of Fludarabine vials we will dispense, including a reliable estimate of uncertainty?
The Forecasting Process
- Data Gathering: You collect the last 36 months of dispensing data for Fludarabine from your pharmacy purchasing system. You also work with the transplant coordinators to get data on the number of new patient consults and scheduled transplant dates, as these will be powerful leading indicators (exogenous variables).
- Visualization & Decomposition: You plot the monthly vial usage on a line chart. You immediately see an upward trend as the program has grown. You don’t see strong seasonality, but you notice a few strange dips. Your clinical knowledge tells you these correspond to the Christmas holiday period when transplants are rarely scheduled. This is a key insight!
- Model Selection: This is a perfect use case for a model like Prophet, because it can easily handle trends and the specific, named holiday events (like a two-week “holiday” effect for Christmas/New Year’s). You also include the `number_of_scheduled_starts_next_month` as an external regressor.
- Training & Forecasting: The model is trained on the historical data. It learns the overall growth trend, the effect of the holidays, and the relationship between scheduled starts and actual vial usage. You then ask it to project this pattern forward for the next 6 months.
- Interpreting the Output: The model doesn’t just give you a single number. This is the most critical part. It provides a full forecast component plot and a prediction interval.
Fludarabine Monthly Vial Forecast
How to Read This Chart:
- The black dots are the actual, historical dispensing data.
- The dark blue line is the model’s forecast. Notice how it captures the upward trend.
- The light blue shaded area is the uncertainty interval (or prediction interval). The model is saying, “I’m most confident the value will land on the blue line, but I’m 80% sure it will fall somewhere inside this shaded region.”
From Forecast to Action: Setting Dynamic PAR Levels
This forecast is now a powerful inventory management tool. Instead of a static PAR level, you can create a dynamic, data-driven one.
- The Base Forecast (Blue Line): This becomes your target for on-hand inventory at the beginning of the month. For next month, the forecast is 150 vials.
- The Uncertainty Interval (Shaded Area): This informs your safety stock. The upper bound of the forecast is 175 vials. This means that to be 90% confident you won’t stock out (assuming an 80% interval), you should have a safety stock of 25 vials.
- The Action: Your new policy is to review the forecast on the 20th of each month and place an order to ensure your on-hand inventory will equal the upper bound of the forecast by the 1st of the next month. You have replaced guesswork with a statistically-driven inventory policy that balances the cost of carrying inventory with the risk of stocking out.
24.4.5 Masterclass Use Case: Building a Data-Driven Staffing Model
The Clinical Problem: The central pharmacy feels perpetually chaotic. Pharmacists complain of being overwhelmed during peak times, leading to burnout and an increased risk of error. Conversely, during off-peak hours, there seems to be too much downtime. The pharmacy manager’s requests for additional FTEs are denied by finance due to a lack of objective data to support the need. The current staffing schedule is based on historical tradition, not actual workload.
Your Task (Framed as a Forecasting Problem): Can we create an hourly forecast of pharmacy workload to build a “demand-based” staffing schedule that matches our labor resources to our actual operational needs?
The Workload Index: Not All Orders Are Created Equal
The first step is to recognize that simply counting “orders per hour” is a flawed metric. A STAT IV admixture is not the same as a routine refill of an oral tablet. Your first task is to use your clinical expertise to create a weighted workload index. You work with frontline staff to assign a “difficulty score” to different types of orders.
| Order Type | Complexity Weight | Rationale |
|---|---|---|
| Routine Oral Tablet | 1.0 | Baseline, least complex task. |
| Routine IV Admixture | 3.0 | Requires IV room prep, more time-consuming. |
| STAT Oral Tablet | 2.5 | Requires interruption of workflow, immediate attention. |
| STAT IV Admixture | 7.0 | Highest priority, most complex, requires immediate workflow disruption. |
| TPN Verification | 10.0 | Extremely complex, high-risk, requires significant pharmacist time. |
You now have a way to translate raw order volume into a more accurate measure of true workload. An hour with 10 STAT IVs (70 workload points) is far busier than an hour with 50 routine oral tablets (50 workload points).
The Forecasting Process
- Data Preparation: You extract every timestamped pharmacy order from the last two years. For each order, you join it with data that tells you its type (IV, oral) and priority (STAT, routine). You then apply your weights to calculate a `workload_score` for every single order.
- Aggregation & Visualization: You aggregate this data into the total `workload_score` for every hour of every day. You then create line charts to visualize the patterns. Immediately, you see a clear story:
- A massive peak every day from 9 AM to 11 AM.
- A second, lower peak from 3 PM to 5 PM.
- A significant drop in workload overnight.
- A huge spike on Monday mornings that is 50% higher than any other day of the week.
- Forecasting: You use a time-series model (like SARIMA or Prophet) that can handle multiple seasonalities (daily and weekly patterns). You train it on your historical `workload_score` data. You then ask it to forecast the expected workload score for every hour of every day for the next month.
From Forecast to a New Staffing Model
The output of your model is a granular, hour-by-hour demand curve for pharmacy services. This is now the objective data you can use to redesign your staffing model.
“Old Way”: Anecdotal Staffing
Staffing Level
Time of Day
Day Shift
(7a-3p)
(7a-3p)
Evening Shift
(3p-11p)
(3p-11p)
Night Shift
(11p-7a)
(11p-7a)
The old model had three core shifts with overlapping coverage at midday. It completely missed the Monday morning spike and was heavily overstaffed on the night shift relative to the workload.
“New Way”: Demand-Based Staffing
Staffing/Demand
Time of Day
The new model, driven by your forecast, uses staggered shifts. You add a 7 AM – 3 PM “peak volume” pharmacist shift only on Mondays. You create shorter 4-hour shifts for technicians to cover the midday rush. You are able to reduce overnight pharmacist overlap. You haven’t necessarily increased total FTEs, but you have reallocated the existing hours to precisely match the predicted demand.
When the pharmacy manager goes back to finance, she is no longer armed with complaints and anecdotes. She is armed with a data-driven forecast that clearly and objectively shows the mismatch between the old staffing model and the actual patient care demand. This is how you use forecasting to win arguments, secure resources, and create a safer, more efficient, and less stressful environment for the entire pharmacy department.
