Question

Union Airways is adding more flights to and from its hub airport and so needs to hire additional customer service agents. These numbers are shown in the last column of table for the time periods given in the first column. The other entries in this table reflect one of the provisions in the company’s current contract with the union that represents the customer service agents. The provision is that each agent works an eight-hour shift. The authorized shifts are

• Shift 1: 6:00 AM to 2:00 PM.

• Shift 2: 8:00 AM to 4:00 PM.

• Shift 3: Noon to 8:00 PM.

• Shift 4: 4:00 PM to midnight.

• Shift 5: 10:00 PM to 6:00 AM.

The table shows the time periods covered by the respective shifts. Because some shifts are less desirable than others, the wages specified in the contract differ by shift. For each shift, the daily compensation (including benefits) for each agent is shown in the bottom row. The problem is to determine how many agents should be assigned to the respective shifts each day to minimize the total personnel cost for agents, based on this bottom row, while meeting (or surpassing) the service requirements given in the last column.

a. Formulate algebraic form for this problem. (Please clearly define all the decision variables, clearly write down the objective function and each constraints)

b. Formulate and solve this model on a spreadsheet.

TABLE Data for the Union Airways Personnel Scheduling Problem

Time Period	1	2	3	4	5	Minimum Number of Agents Needed
6:00am to 8:00am	X					45
8:00am to 10:00am	X	X				75
10:00am to 12:00pm	X	X				60
12:00pm to 2:00pm	X	X	X			81
2:00pm to 4:00pm		X	X			64
4:00pm to 6:00pm			X	X		73
6:00pm to 8:00pm			X	X		82
8:00pm to 10:00pm				X		42
10:00pm to 12:00am				X	X	52
12:00am to 6:00am					X	16
Daily Cost per agent	$172	$150	$165	$181	$190

Answer 1

a)

Let x_j = the number of agents reporting in shift-j for j=1,2,3,4,5

Min Z = 172 x₁ + 150 x₂ + 165 x₃ + 181 x₄ + 190 x₅

Subject to,

x₁ >= 45

x₁ + x₂ >= 75

x₁ + x₂ >= 60

x₁ + x₂ + x₃ >= 81

x₂ + x₃ >= 64

x₃ + x₄ >= 73

x₃ + x₄ >= 82

x₄ >= 42

x₄ + x₅ >= 52

x₅ >= 15

x_j >= 0 for j=1,2,3,4,5

(b)

Excel model:

Solver inputs:

Solution (Answer report):

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$H$4	Cost	0	$29,482
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No.s reports in Shift-1	0	45	Contin
	$D$3	No.s reports in Shift-2	0	30	Contin
	$E$3	No.s reports in Shift-3	0	40	Contin
	$F$3	No.s reports in Shift-4	0	42	Contin
	$G$3	No.s reports in Shift-5	0	16	Contin