Staffing Problem | Complete Solution

Staffing Problem

The problem of staffing the customer service representatives (CSR) in a call center becomes a major problem.  When there are too few CSRs, the average customer wait for service becomes longer than desired.  When there are too many CSRs, they sit around waiting for customers to call.
The demand for CSRs at a particular call center is given below.
From    8am    9am    10am    11am    Noon    1pm    2pm    3pm    4pm    5pm    6pm
To    9am    10am    11am    Noon    1pm    2pm    3pm    4pm    5pm    6pm    7pm
CSRs    7    9    13    15    19    20    17    14    12    6    4
Full-time CSRs work an 8-hour shift.  There are 4 possible full-time shifts and the full-time CSRs are paid $19.00 per hour.  Part-time CSRs work either a 2-hour or a 4-hour shift.  There are 18 possible part-time shifts and part-time CSRs are paid $15.00 per hour.
The must be a minimum number of CSRs manning the phones during all time periods.  It is OK to have additional CSRs in any given time period if necessary to fulfill the other constraints.
Procedure
Download the staffing problem EXCEL spreadsheet from Carmen for Exercise #4.
On the “Shift Determination” tab, determine the unique full-time and part-time shifts (when does each shift start and how many hours do the CSRs work on that shift).
On the “Scenario 1” tab, determine the objective function to minimize the total cost.  Use the information from the “Shift Determination” tab and formulate the constraints for minimum number of CSRs for the 11 time periods listed in the table above.
Complete the linear programming formulation by adding the objective function coefficients, the formulas for the LHS column, the constraint signs, and the constants for the RHS.  
Scenario 1:    Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer).
There are three additional sets of scenarios that the call center management wants in investigate as integer programs.  Using the formulations for Scenario 1 as the foundation, add 2 constraints to the formulation for each of the following:
•    At least 35% but no more than 60% of the total employees working (not total employees required) must be full-time employees.
Scenario 2:    Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer).
•    At least 35% but no more than 60% of the total employee working hours (not employee required hours) must be full-time employee hours.
Scenario 3:    Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer
•    At least 35% but no more than 60% of the total employee working cost (not employee required cost) must be full-time employee cost.
Scenario 4:    Using Solver, run the model as an integer program (adding a constraint in Solver to have all 22 decision variables to be integer
For scenarios 2, 3, and 4, it is incorrect to multiply the RHS values of the constraints by 35% and 60% (this is a percentage of the CSRs scheduled; the problem asks for the percentage of the CSRs working).  Hint:  Think ratios.
Please note that multiple optimal solutions exist for all of the scenarios.  Students only have to find one of the solutions for the scenarios.