Solving a Staffing Model Using an Overbooking Simulation

This paper adapts a Monte Carlo overbooking simulation approach to determine appropriate levels of staffing when there is a clear trade-off between assigning too many versus too few workers. In the scenario considered, if a scheduled staff member fails to show up, one or more other employees will need to be assigned to additional (overtime) hours to “cover” the absent employee’s workload, leading to increased payroll costs. But even this can only be done up to a point since overtime hours are limited, by company policy, to a fraction of the regular time hours available during the shift. Therefore, if too few employees were scheduled to begin with, some demand may be left unmet, resulting in potential loss of revenue. This paper shows how the question of how many employees should be scheduled to each shift can be modeled similar to the well-known airline overbooking problem. We solve this staffing model using Oracle Crystal Ball, an Excel add-in for conducting Monte Carlo simulation, thereby demonstrating its easy applicability in practice.

Introduction

Airlines typically oversell the number of seats on flights, banking on ticketed passengers not showing up to board. Seats on a flight are a form of perishable inventory, and it is advantageous for the airline to oversell their capacity since an empty seat on a flight produces zero revenue. On the other hand, if too many ticketed passengers turn up at the gate, the airline would need to compensate “bumped” passengers, i.e., anyone who is denied boarding, either voluntarily or involuntarily. A question facing the airline is: what is the optimal level of overbooking, i.e., how many additional tickets beyond airplane capacity should the airline sell to maximize profit, keeping in mind that too few passengers (empty seats on the flight) or, at the other extreme, too many passengers showing up to board (excessive bumping costs) are both disadvantageous? This problem can be solved analytically by making certain assumptions (Chopra, 2019) or via simulation more generally (Ragsdale, 2015). Here we apply the latter approach, i.e., the overbooking simulation, to a staffing problem where employees not showing up to their assigned schedules would result in increased costs to the organization due to the need for at least some of the available employees to do overtime. Since overtime hours are limited to a fraction of available regular time hours, this could also result in loss of revenue if some demand must necessarily be left on the table (i.e., unsatisfied). The rest of this paper is organized as follows: We first review the analytic and simulation approaches to the airline overbooking problem in Sections 2 and 3, respectively. In Section 4 we present the staffing model and its solution using the simulation approach. Our concluding comments are in Section 5.

Overbooking: An Analytical Approach

An analytical approach to the overbooking problem is presented in Chopra (2019, p. 478–480). Let p be the price of a ticket, c the variable cost of fulfilling the demand for a seat, and b the compensation paid to any ticketed passenger denied boarding (i.e., the “bumping” cost). Suppose also that the distribution of cancellations is known to be Normal with a mean of μ_c and standard deviation σ_c. The optimal overbooking level can be obtained by balancing the cost of underage (p−c) and overage (b−p) as in the Newsvendor model, see e.g., Cachon and Terwiesch (2024). The critical ratio is given by:

$$\begin{array}{rl}{s}^{\ast }& =costofunderage/[costofunderage+costofoverage]\\ & =(p-c)/[(p-c)+(b-p)]=(p-c)/(b-c)\end{array}$$

In Excel the optimal level of overbooking can then be computed as:

$$\begin{array}{rl}{O}^{\ast }& =NORMINV(s^{\ast },{\mu }_c,{\sigma }_c)\\ & =NORMINV(\left((p-c)/(b-c)\right),{\mu }_c,{\sigma }_c)\end{array}$$

Here’s an example (adapted from Ragsdale, 2015): Piedmont, a commuter airline, has a capacity of 19 seats on a particular flight. Tickets are priced at $150, and the amount of compensation to be paid to a bumped passenger is $325. Suppose, additionally, it costs the airline $31 in variable costs per passenger on each flight. By how much should the airline overbook (beyond the capacity of 19) if the cancellation distribution is Normal with mean 2.15 and standard deviation 0.63? Solution: Per the given expression, the optimal level of overbooking can be computed as:

$$\begin{array}{rl}{O}^{\ast }& =NORMINV(\left((150-31)/(325-31)\right),2.15,0.63)\\ & =2\end{array}$$

The airline should attempt to sell 19 + 2 = 21 tickets for the flight.

Overbooking: A Simulation Approach

Unfortunately, if the cancellation distribution (equivalently, the demand distribution) is either unknown or only known empirically, one cannot use the above analytic approach. Instead, Monte Carlo Simulation can be used to derive the best overbooking level, using the approach presented in Ragsdale (2015, p. 587–594). Below is the solution to the same sample problem above, except that we use an empirical demand distribution in place of the cancellation distribution and also assume that the probability that a ticketed passenger will not show up to board, P (No Show), is 0.1. The assumed empirical demand distribution is shown in Table I.

Table I. Empirical Demand Distribution for the Airline Overbooking Model

Demand	Probability
14	0.03
15	0.05
16	0.07
17	0.09
18	0.11
19	0.15
20	0.18
21	0.14
22	0.08
23	0.05
24	0.03
25	0.02
Total	1.00

The model can be set up using Oracle Crystal Ball as shown in Fig. 1. The problem data is entered in cells B4:C8 and E4:F16. Cell C9 is the decision cell (“Reservations Accepted”) which will be iterated over an appropriate range to find the best value for this decision.

Fig. 1. Setting up the airline overbooking model.

The other cell formulas are entered as shown in Table II.

Table II. Excel Cell Formulas for the Airline Overbooking Model

Cell	Model parameter	Formula
C12	Tickets sold	Min (C11, C9)
C15	Ticket revenue	C5 × C12
C16	Total variable cost	Min (C13, C4) × C7
C17	Total bumping cost	Max (C13 − C4, 0) × C8
C18	Marginal profit	C15 – (C16 + C17)

Cells C11 and C13 are the cells representing the random variables in the model and are called “Assumption Cells” in Crystal Ball. Cell C11 reflects the simulated empirical demand and Cell C13 reflects the simulated number of passengers showing up to board the flight. They are entered on the spreadsheet as shown in Table III.

Table III. Assumption Cells for the Airline Overbooking Model

Assumption cell	Model parameter	Distribution	Distribution parameters
C11	Seats demanded	Empirical	Custom data in Cells E5:F16
C13	Passengers to board	Binomial	P (Success) = 1 – P (No Show) = 1 − C6
			Number of trials = Tickets Sold = C12

Cell C18 is called “Forecast Cell” in Crystal Ball, and reflects our variable of interest, Marginal Profit. The result of simulating 5000 trials (flights) for each value of “Reservations Accepted,” iterated from 19 to 25, is shown in Fig. 2.

Fig. 2. Marginal profit vs. reservations accepted.

Thus, the largest marginal profit corresponds to 21 Reservations Accepted (note that this was the same result obtained in the previous analytical approach).

Staffing Model

Next, we consider a staffing model that can be solved in a similar fashion to the airline overbooking problem. Here’s the scenario: Employees are assigned to a shift schedule in advance. Once scheduled, the organization incurs payroll (wage) costs, regardless of whether a scheduled employee shows up for the shift. Each employee can only handle a fixed number of customers/demand units. (We will assume that the terms “customer” and “demand unit” are synonymous, i.e. each customer represents exactly one unit of demand). If a scheduled employee fails to show up for their shift, other employee(s) will be required to work additional hours to “cover” for the absent employee resulting in higher costs to the organization (overtime pay). Also, there is a limit to how many overtime hours can be utilized per shift (typically an agreement, say with the workers’ union, restricts overtime to only a fraction of the available regular time hours in that shift). The market demand is uncertain and only known empirically. The trade-off that the organization faces is between scheduling too many or too few employees in advance. Both extremes are bad. If they schedule too many employees, they will incur excessive (regular time) wage costs. On the other hand, if they schedule too few employees, they may have to cover any capacity shortfall with additional payroll (overtime) costs, and there is also a real likelihood that some demand might have to be left unmet, causing a loss in revenue.

Here is data for a sample problem: A shift is 8 hours long. Each customer (unit of demand) is assumed to take 0.5 hours of an employee’s time. The projected revenue per customer (demand unit) is $500. The RT wage and OT wage are, respectively, $100 and $200 per hour. Overtime hours are limited to 50% of the total regular time hours that were scheduled for that shift. The probability that a scheduled employee will not show up to their assigned shift, P (No Show), is 5%. The empirical demand distribution is as shown in Table IV below:

Table IV. Empirical Demand Distribution for the Staffing Model

Demand (Units, or number of customers)	Probability
400	0.01
416	0.03
432	0.03
448	0.04
464	0.11
480	0.16
496	0.19
512	0.15
528	0.13
544	0.10
560	0.05
Total	1.00

The model can be set up using Oracle Crystal Ball as shown in the spreadsheet in Fig. 3. First, the problem data is entered in cells B3:C8, E5:F16, and C26. Cell C9 is the decision cell (“Number of Employees Scheduled”) which will be iterated over an appropriate range to find the best value for this decision.

Fig. 3. Setting up the staffing model.

The other cell formulas are entered as shown in Table V.

Table V. Excel Cell Formulas for the Staffing Model

Cell	Model parameter	Formula
C12	Number of employee hours needed	= C11 × C4
C14	Number of employee hours available	= (C9 − C13) × C3
C15	Deficit in employee hours needed	= MAX (C12 − C14, 0)
C16	Number of OT hours utilized	= MIN (C7 × C14, C15)
C17	Final deficit in employee hours needed	= C15 − C16
C18	Demand units satisfied (# of Customers)	= (C12 − C17)/C4
C21	Total RT wages	= C9 × C3 × C5
C22	Total OT wages	= C16 × C6
C23	Total wages for shift	= C21 + C22
C27	Total revenue for shift	= C26 × C18
C29	Marginal profit	= C27 − C23

Cells C11 and C13 are the “Assumption Cells.” Cell C11 reflects the simulated empirical demand (# of Customers) and Cell C13 reflects the simulated number of employees not showing up to their assigned shift. They are entered on the spreadsheet as shown in Table VI.

Table VI. Assumption Cells for the Staffing Model

Assumption cell	Model parameter	Distribution	Distribution parameters
C11	Demand (# of Customers)	Empirical	Custom data in cells E6:F16
C13	Number of employees who do not show	Binomial	P (Success) = P (No show) = C8
			Number of trials = Number of employees scheduled = C9

Cell C29 is the “Forecast Cell”. The result of simulating 5000 trials (shifts) for each value of “Number of Employees Scheduled,” iterated from 26 to 35, is shown in Fig. 4.

Fig. 4. Marginal profit vs. Number of employees scheduled.

The highest marginal profit ($220,676) occurs when 33 employees are scheduled which represents the best staffing decision.

Conclusions

This paper applies the well-known airline overbooking simulation model to a staffing problem where the trade-off is between scheduling too many or too few employees in advance. It is solved using Oracle Crystal Ball, a widely available Excel add-in for conducting Monte Carlo simulation. While the advantage of simulation over an analytic approach is that it can consider virtually any type of relationship, the disadvantage is that there is no closed form solution (unlike some analytic methods) and the computational burden could be rather heavy. As an extension to the approach here, it will be interesting to explore the impact of demand seasonality on the staffing decision.