Once You Have Installed These Programs Your Model Is Ready to Run

User’s guide

Getting Started

To begin the model implementation you will need to install both the QueueingToolpak created by ArmannIngolfsson and Premium Solver as the model is too large for regular Solver to handle

Once you have installed these programs your model is ready to run.

Running the Model

To set up the model, start on the tab labeled “Staff Optimizer 3000”. There you will find 3 yellow cells; these are the inputs to the model. You will need to enter expected call demand levels for the week you are scheduling for each call type of call into the appropriately labeled cell.

From there you will need to go to the tab titled “Scheduling Model”. Here you need to launch Premium Solver. Do this by clicking on the “Add-Ins” tab along the top and clicking on “Premium Solver V10.5” from the Menu Commands.

Solver should be preset to the appropriate settings for the model; however, in case something happens the settings are as follows (diagrams are in the Appendix):

• The decision cell is the yellow cell ($A$114) expressing the weeks total scheduled hours, this should be minimized.
• The changing cells should be the blue cells ($A$4:$A$113) to the left expressing the number of each shift required to meet expected demand.
• The model is constrained so that “Scheduled Servers” are greater than or equal to “Minimum Required Servers” ($I$115:$ZD$115>=I114:$ZD$114). The changing cells are also constrained to be integers ($A$4:$A$113=integer).
• The model should be set to “Standard LP Simplex” (linear) and under “Options” UseAutomatic Scaling, Assume Non-negative, and Use Dual Simplex for Subproblems should be checked. The tolerance is set to 0.1 and the precision is set to 0.001.

Once you are sure the settings are correct, hit solve. Because the model is so large Premium Solver will likely produce an error within the first few seconds that reads “The maximum iteration limit was reached; continue anyway?”, when this happens click “Continue” and Solver will continue to run.

Premium Solver should not run for more than 5 minutes before it finds a solution; when it has found a solution a box will appear that says “Solver found an integer solution within tolerance. All constraints are satisfied.” It will also have a box that says “Keep Solver Solution” checked; when you see this message click “OK”.

The optimal solution has then been reached. On this spreadsheet, in the bottom left corner, the yellow cell (A114) displays the total number of scheduled hours needed to fulfill the requirements.

To get the final output of the model go to the “Week’s Schedule” tab and click on the button in the right corner of the cell labelled “Number of Shifts” (A1) then deselect the box next to “0” to filter out the shifts that were not chosen and click “OK”.

If the is not there when you first arrive on the tab then to turn the filter on select cell A1 and click on the “Data” tab along the top and select “Filter” from “Sort & Filter” this will turn the filter on.

Once you have filtered out the 0s you are left with the complete schedule. The “Number of Shifts” column tells you how many of the shift to schedule and the remaining columns tell you the specifics of the shift (when it starts, how long it is, what day it is on, when the breaks are).

Unfortunately, in order for the model to work we were unable to include all the possible break combinations, so the schedule has fixed the breaks. However, if you wish you can adjust the breaks to better suit your purposes.

You now have the optimal schedule for the call centre and you can use it to schedule your staff.

To determine what task each employee should be doing during each hour, go to the “Total Required Employees” tab. Listed here is the breakdown of required call evaluators by hour. From the image below, in hour 0 on Sunday at least 1 of the scheduled call evaluators needs to be taking air calls and 2 are needed for ground emerg and IFT calls.

In order to account for breaks, a lot of the time there will be a small amount of over staffing, the extra employees can be placed wherever necessary.

Changing Inputs

If you wish to change the service level at any point in time (it is currently set at 95% of calls in 10 seconds), all you need to do is go to the “Staff Optimizer 3000” tab and type the desired service level (in decimal form) into the appropriately labeled cells in the box called “Adjusting Service Level and Threshold” (cell H11 for groundemerg and IFT calls and cell I11 for air calls).To alter the threshold time select the cells labeled Threshold Time (H12 for ground emerg and IFT calls and cell I12 for air calls), and enter the desired threshold time in hours (ie. type in =”desired number of seconds”/3600 seconds in an hour. 5, 10, and 15 seconds have been pre calculated and are below the threshold time cell). You may enter different standards for each call type if you wish.

The percentage of weekly demand experienced in each hour should remain relatively the same as demand rises, however, if you suspect it has changed at any point, particularly as CCC absorbs more demand from other call centres, it may be necessary to recalculate these percentages. To update the model in this case you will need to use the new data to calculate the average call arrival rate for each hour of the week. Then sum these values to find average demand for the week. To find the percentage of demand in each hour then divide the average arrival rate in each hour by the average weekly demand. Paste the new percentages on the old percentages in column D (of the relevant call type, ie. If you were updating air call arrival, then you would need to be on the “Air tab) labeled “% of Calls”; the rest of the model will update itself.

Adding Strathcona

We were given data for Strathcona, however, Strathcona will not be added right away so the model currently does not take this demand into account. When Strathcona is added there is one quick adjustment to be made to the model so that the added demand will be accounted for. In the “Ground Emerg and IFT” tab,to the right of the model there are hourly arrival rate percentages. Copy these new percentages and paste on top of the old percentages. The model is now updated to include Strathcona. On the “Staff Optimizer 3000” tab, we have also included average expected demand for each of the call types; these can be pasted on the demand inputs as well.

Appendix