Question

Epsilon Airlines services predominantly the eastern and southeastern united States. The vast majority of Epsilon’s customers make reservations through Epsilon’s website, but a small percentage of customers make reservations via phones. Epsilon employs call center personnel to handle these reservations and to deal with website reservation system problems and for the rebooking of flights for customers whose plans have changed or whose travel is disrupted. Staffing the call center appropriately is a challenge for Epsilon’s management team. Having too many employees on hand is a waste of money, but having too few results in very poor customer service and the potential loss of customers.

Epsilon analysts have estimated the minimum number of call center employees needed by day of the week for the upcoming vacation season (June, July, and the first two weeks of August). These estimates are as follows:

Day	Minimum Number of Employees Needed
Monday	50
Tuesday	90
Wednesday	45
Thursday	55
Friday	75
Saturday	55
Sunday	45

The call center employees work for five consecutive days and then have two consecutive days off. An employee may start work on any day of the week. Each call center employee receives the same salary. Assume that the schedule cycles and ignore start up and stopping of the schedule.

Develop a model that will minimize the total number of call center employees needed to meet the minimum requirements.

Let	Xi = the number of call center employees who start work on day i
	(i = 1 = Monday, i = 2 = Tuesday...)

Min	X1 +	X2 +	X3 +	X4 +	X5 +	X6 +	X7
s.t.
	X1 +			X4+	X5+	X6+	X7	≤, ≥, or =	________
	X1 +	X2+			X5+	X6+	X7	≤, ≥, or =	________
	X1 +	X2+	X3+			X6+	X7	≤, ≥, or =	________
	X1 +	X2+	X3+	X4+			X7	≤, ≥, or =	________
	X1 +	X2+	X3+	X4+	X5			≤, ≥, or =	________
		X2 +	X3+	X4+	X5+	X6		≤, ≥, or =	________
			X3 +	X4+	X5+	X6+	X7	≤, ≥, or =	________
	X1,	X2,	X3,	X4,	X5,	X6,	X7	≥	0

Find the optimal solution.

X1	=	________
X2	=	________
X3	=	________
X4	=	________
X5	=	________
X6	=	________
X7	=	________

Total Number of Employees = ________

Give the number of call center employees that exceed the minimum required.

Excess employees:

Monday	=	________
Tuesday	=	________
Wednesday	=	________
Thursday	=	________
Friday	=	________
Saturday	=	________
Sunday	=	________

Answer 1

Here the objective function will be x1+x2+x3+x4+x5+x6+x7 and this has to be minimized.

A person joining on Thursday will be available till Monday. So the constraint will be:

X1+x4+x5+x6+x7>=50

Similarly other constraints will be:

X1+x2+x5+x6+x7>=90

X1+x2+x3+x6+x7>=45

X1+x2+x3+x4+x7>=55

X1+x2+x3+x4+x5>=75

x2+x3+x4+x5+x6>=55

x3+x4+x5+x6+x7>=45

Lastly will be the non-negative constraint and so x1+x2+x3+x4+x5+x6+x7>=0

Solving the above in excel using the solver function we get the following optimal solution:

X1	=	25
X2	=	20
X3	=	0
X4	=	0
X5	=	35
X6	=	0
X7	=	10

The minimized no. of the objective function is 90.  

The number of call center employees that exceed the minimum requirement is the number above the given minimum. So for instance the minimum number desired for Monday is 50 while x1+x4+x5+x6+x7 = 70. Thus excess employees = 70-50. Similarly we have computed the same for all other days as well:

Excess employees
Monday	=	20
Tuesday	=	0
Wednesday	=	10
Thursday	=	0
Friday	=	5
Saturday	=	0
Sunday	=	0

Excel's solver image:

File Home Insert Page Layout q quant 2019 20 - Microsoft Excel Formulas Data Review View Connections * Clear Properties Reapply Refresh 21 Sort Filter Text to Remove Data Consolidate What If All Edit Links Advanced Columns Duplicates Validation Analysis Connections Sort & Filter Data Tools Solver Parameters 1 Boy - - 2 x s how Detail ? Solver Hide Detail Data Analysis From From Access Web From From Other Text Sources Get External Data Existing Connections Group Ungroup Subtotal Outline Analysis Set Objective: $C$11 1 To: Max Min Value Of: 3 X1 4 X2 5 X3 By Changing Variable Cells: $D$3:$D$9 6 7 X5 8 Add 10 Subject to the Constraints: $C$14 >= $E$14 $C$15 >= SE$15 $C$16 >= SE$16 $C$17 >= SE$17 $C$18 >= SE$18 $C$19 >= $E$19 $C$20 >= $E$20 $D$3:$D$9 = integer 553:$D$ = 0 Change Formula x1+x2+x3+x4+x5+x6+x7 11 Delete 12 13 Constraints 14 Reset All Load/Save Make Unconstrained variables Non-Negative 70 >= 90 >= 55 > 55 >= 80 >= 55 > 45 > X1+x4+x5+x6+x7>=50 X1+x2+x5+x6+x7>=90 X1+x2+x3+x6+x7>=45 X1+x2+x3+x4+x7>=55 X1+x2+x3+x4+x5>=75 x2+x3+x4+x5+x6>=55 x3+x4+x5+x6+x7>=45 Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve close K 26 M Point Oct Nov Dec Jan Sheet1 Sheet2 Sheet3 BOU 100% - EN 12:19 P 13/01/2020