Staffing at Call Center Case Study
Staffing at Call Center Case Study
Staffing at Call Center Case Study
Number of English
Avg number of Total number of Number of Number of Speaking Employees
English Spannish
calls/hr calls/shift required in each shift
calls/shift calls/shift
Shift (Mathematical Value)
Work Shift Number
7am-9am 1 40 80 64 16 5.3333
9am-11am 2 85 170 136 34 11.3333
11am-1pm 3 70 140 112 28 9.3333
1pm-3pm 4 95 190 152 38 12.6667
3pm-5pm 5 80 160 128 32 10.6667
5pm-7pm 6 35 70 56 14 4.6667
7pm-9pm 7 10 20 16 4 1.3333
830 664 166 55.3333
h minimum cost
next highest number because the calls cannot be left incomplete or unattended
6 1.3333 2
12 2.8333 3
10 2.3333 3
13 3.1667 4
11 2.6667 3
5 1.1667 2
2 0.3333 1
59 13.8333 18
Solver solution for case with English-speaking, Spanish-speaking & Part-time employees in each shift with minimum cost
Cost 40 40 40 44
40 40 40 44
Objective Function:
Minimize 1640.00
Subject to following constraints:
Number of employees/shift LHS Inequality RHS Slack
E1 6 >= 6 0 Total number of englis
E2 12 >= 12 0 Total number of englis
E1+E3 11 >= 10 1 Total number of englis
E2+E4 13 >= 13 0 Total number of englis
E3+E5+P5 11 >= 11 0 Total number of englis
E4+P5+P6 5 >= 5 0 Total number of englis
E5+P6 2 >= 2 0 Total number of englis
S1 2 >= 2 0 Total number of spanis
S2 3 >= 3 0 Total number of spanis
S1+S3 4 >= 3 1 Total number of spanis
S2+S4 5 >= 4 1 Total number of spanis
S3+S5 3 >= 3 0 Total number of spanis
S4 2 >= 2 0 Total number of spanis
S5 1 >= 1 0 Total number of spanis
with minimum cost
44
48 48
Variable Cells
Cell Name Original Value Final Value Integer
$C$7 E1 0 6 Integer
$D$7 E2 0 12 Integer
$E$7 E3 0 5 Integer
$F$7 E4 0 1 Integer
$G$7 E5 0 2 Integer
$C$10 S1 0 2 Integer
$D$10 S2 0 3 Integer
$E$10 S3 0 2 Integer
$F$10 S4 0 2 Integer
$G$10 S5 0 1 Integer
$G$13 P5 4 4 Integer
$H$13 P6 0 0 Integer
Constraints
Cell Name Cell Value Formula Status Slack
$B$24 E1 LHS 6 $B$24>=$D$24 Binding 0
$B$25 E2 LHS 12 $B$25>=$D$25 Binding 0
$B$26 E1+E3 LHS 11 $B$26>=$D$26 Not Binding 1
$B$27 E2+E4 LHS 13 $B$27>=$D$27 Binding 0
$B$28 E3+E5+P5 LHS 11 $B$28>=$D$28 Binding 0
$B$29 E4+P5+P6 LHS 5 $B$29>=$D$29 Binding 0
$B$30 E5+P6 LHS 2 $B$30>=$D$30 Binding 0
$B$31 S1 LHS 2 $B$31>=$D$31 Binding 0
$B$32 S2 LHS 3 $B$32>=$D$32 Binding 0
$B$33 S1+S3 LHS 4 $B$33>=$D$33 Not Binding 1
$B$34 S2+S4 LHS 5 $B$34>=$D$34 Not Binding 1
$B$35 S3+S5 LHS 3 $B$35>=$D$35 Binding 0
$B$36 S4 LHS 2 $B$36>=$D$36 Binding 0
$B$37 S5 LHS 1 $B$37>=$D$37 Binding 0
$C$10 S1 2 $C$10>=0 Not Binding 2
$D$10 S2 3 $D$10>=0 Not Binding 3
$E$10 S3 2 $E$10>=0 Not Binding 2
$F$10 S4 2 $F$10>=0 Not Binding 2
$G$10 S5 1 $G$10>=0 Not Binding 1
$C$7 E1 6 $C$7>=0 Not Binding 6
$D$7 E2 12 $D$7>=0 Not Binding 12
$E$7 E3 5 $E$7>=0 Not Binding 5
$F$7 E4 1 $F$7>=0 Not Binding 1
$G$7 E5 2 $G$7>=0 Not Binding 2
$G$13 P5 4 $G$13>=0 Not Binding 4
$H$13 P6 0 $H$13>=0 Binding 0
$C$10:$G$10=Integer
$C$7:$G$7=Integer
$G$13:$H$13=Integer
Solver solution for case with English-speaking, Spanish-speaking & Part-time employees in each shift with minimum cost wit
Cost 40 40 40
40 40 40
Objective Function:
Minimize 1680.00
Subject to following constraints:
Number of employees/shift LHS Inequality RHS Slack
E1 6 >= 6 0
E2 12 >= 12 0
E1+E3 13 >= 10 3
E2+E4 13 >= 13 0
E3+E5+P5 11 >= 11 0
E4+P5+P6 5 >= 5 0
E5+P6 2 >= 2 0
S1 2 >= 2 0
S2 3 >= 3 0
S1+S3 4 >= 3 1
S2+S4 5 >= 4 1
S3+S5 3 >= 3 0
S4 2 >= 2 0
S5 1 >= 1 0
E4 1= 1 0
E5 1= 1 0
each shift with minimum cost with constraint of only one employee available to start shift at 1 pm
44 44
44 48 48
Variable Cells
Cell Name Original Value Final Value Integer
$C$7 E1 0 6 Integer
$D$7 E2 0 12 Integer
$E$7 E3 0 7 Integer
$F$7 E4 0 1 Integer
$G$7 E5 0 1 Integer
$C$10 S1 0 2 Integer
$D$10 S2 0 3 Integer
$E$10 S3 0 2 Integer
$F$10 S4 0 2 Integer
$G$10 S5 0 1 Integer
$G$13 P5 0 3 Integer
$H$13 P6 0 1 Integer
Constraints
Cell Name Cell Value Formula Status Slack
$B$24 E1 LHS 6 $B$24>=$D$24 Binding 0
$B$25 E2 LHS 12 $B$25>=$D$25 Binding 0
$B$26 E1+E3 LHS 13 $B$26>=$D$26 Not Binding 3
$B$27 E2+E4 LHS 13 $B$27>=$D$27 Binding 0
$B$28 E3+E5+P5 LHS 11 $B$28>=$D$28 Binding 0
$B$29 E4+P5+P6 LHS 5 $B$29>=$D$29 Binding 0
$B$30 E5+P6 LHS 2 $B$30>=$D$30 Binding 0
$B$31 S1 LHS 2 $B$31>=$D$31 Binding 0
$B$32 S2 LHS 3 $B$32>=$D$32 Binding 0
$B$33 S1+S3 LHS 4 $B$33>=$D$33 Not Binding 1
$B$34 S2+S4 LHS 5 $B$34>=$D$34 Not Binding 1
$B$35 S3+S5 LHS 3 $B$35>=$D$35 Binding 0
$B$36 S4 LHS 2 $B$36>=$D$36 Binding 0
$B$37 S5 LHS 1 $B$37>=$D$37 Binding 0
$B$38 E4 LHS 1 $B$38=$D$38 Binding 0
$B$39 E5 LHS 1 $B$39=$D$39 Binding 0
$C$10 S1 2 $C$10>=0 Not Binding 2
$D$10 S2 3 $D$10>=0 Not Binding 3
$E$10 S3 2 $E$10>=0 Not Binding 2
$F$10 S4 2 $F$10>=0 Not Binding 2
$G$10 S5 1 $G$10>=0 Not Binding 1
$C$7 E1 6 $C$7>=0 Not Binding 6
$D$7 E2 12 $D$7>=0 Not Binding 12
$E$7 E3 7 $E$7>=0 Not Binding 7
$F$7 E4 1 $F$7>=0 Not Binding 1
$G$7 E5 1 $G$7>=0 Not Binding 1
$G$13 P5 3 $G$13>=0 Not Binding 3
$H$13 P6 1 $H$13>=0 Not Binding 1
$C$10:$G$10=Integer
$C$7:$G$7=Integer
$G$13:$H$13=Integer
LPP Formulation for all employees to be bilingual
Points taken into consideration
Per shift = 2 hours
Avg # of calls/employee/hour = 6
Therefore, average # of calls/employee/2 hr shift = 12
Since the number of employees cannot be in fraction we have taken integer value rounding off to next highest number becaus
Number of
Avg number of Total number of Number of Employees Employees
required in each shift required in
calls/hr calls/shift (Mathematical Value) each shift
(Integer value)
Work Shift Shift Number
7am-9am 1 40 80 6.6666666667 7
9am-11am 2 85 170 14.1666666667 15
11am-1pm 3 70 140 11.6666666667 12
1pm-3pm 4 95 190 15.8333333333 16
3pm-5pm 5 80 160 13.3333333333 14
5pm-7pm 6 35 70 5.8333333333 6
7pm-9pm 7 10 20 1.6666666667 2
415 830 69.1666666667 72
off to next highest number because the calls cannot be left incomplete or unattended
English Speaking Employees: Bi
Shift timings 7-9 am 9-11am 11am-1pm 1-3pm
Shift # 1 2 3 4
Variable name E1 E2 E3 E4
English Speaking Employees 7 15 7 1
Variable name
Part time employees
Cost 40 40 40 44
40 40 40 44
Objective Function:
Minimize 1512.00
Subject to following constraints:
Number of employees/shift LHS Inequality RHS Slack
E1 7 >= 7 0
E2 15 >= 15 0
E1+E3 14 >= 12 2
E2+E4 16 >= 16 0
E3+E5+P5 14 >= 14 0
E4+P5+P6 6 >= 6 0
E5+P6 2 >= 2 0
Part time employees : Pi
3-5pm 5-7pm 7-9pm Shift timings 7-9 am 9-11am 11am-1pm 1-3pm
5 6 7 Shift # 1 2 3 4
E5 E1 E2 E1+E3 E2+E4
2 S1 S2 S1+S3 S2+S4
P5 P6
5 0
44
48 48
3-5pm 5-7pm 7-9pm
5 6 7
E3+E5 E4 E5
S3+S5 S4 S5
P5 P5+P6 P6
Microsoft Excel 16.0 Answer Report
Worksheet: [New Microsoft Excel Worksheet.xlsx]Part f
Report Created: 2/7/2018 11:10:13 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.125 Seconds.
Iterations: 11 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Variable Cells
Cell Name Original Value Final Value Integer
$C$6 E1 0 7 Integer
$D$6 E2 0 15 Integer
$E$6 E3 0 7 Integer
$F$6 E4 0 1 Integer
$G$6 E5 0 2 Integer
$G$9 P5 0 5 Integer
$H$9 P6 0 0 Integer
Constraints
Cell Name Cell Value Formula Status Slack
$B$20 E1 LHS 7 $B$20>=$D$20 Binding 0
$B$21 E2 LHS 15 $B$21>=$D$21 Binding 0
$B$22 E1+E3 LHS 14 $B$22>=$D$22 Not Binding 2
$B$23 E2+E4 LHS 16 $B$23>=$D$23 Binding 0
$B$24 E3+E5+P5 LHS 14 $B$24>=$D$24 Binding 0
$B$25 E4+P5+P6 LHS 6 $B$25>=$D$25 Binding 0
$B$26 E5+P6 LHS 2 $B$26>=$D$26 Binding 0
$C$6 E1 7 $C$6>=0 Not Binding 7
$D$6 E2 15 $D$6>=0 Not Binding 15
$E$6 E3 7 $E$6>=0 Not Binding 7
$F$6 E4 1 $F$6>=0 Not Binding 1
$G$6 E5 2 $G$6>=0 Not Binding 2
$G$9 P5 5 $G$9>=0 Not Binding 5
$H$9 P6 0 $H$9>=0 Binding 0
$C$6:$G$6=Integer
$G$9:$H$9=Integer
Cost Operations
Cost with monolingual operators 1640
Cost with bilingual operators 1512 -
128
0.0846560847 =128/1512
Maximum percentage increase in
the hourly wage rate 8.4656084656 %