Attempting Staff Scheduling using Excel

[demetri2]

Hello

I have been trying to model my own scheduling method in excel but i have come to the last part and i am experiencing lots of difficulties. Can anyone help?

I run a 24/7 call center and the problem is this. To staff a call center we use a formula called Erlang C to calculate "how many heads are needed per hour". After that i used a few other parameters which i factored in and got to the final no of "heads per hour".

Using excel solver add-in i managed to map these required headcount using a set of available shifts (28 variations of shifts to be precise). So now each week i know which shifts should be staffed, with how many persons for each shift in order to schedule according to my forecast.

I have now come to the difficult part which i don't know how to do:

1. Agents should select shifts (preferably 2 options (one preferred option and one backup option)
2. Some shifts should automatically be selected if another shift is chosen (if i have a shift running 8:00-14:00(A), and another running from 8:00 to 12:00(B), if A is selected then B should automatically be selected as well
3. Using solver (maybe) calculate which combination of agents shifts (based on their preferences) gives me a result as close to the one i calculated earlier

Tnks for any help, i know its a lot to ask but i am stuck half way through solving this

 

[Fede Viola]

Hi Demetri2. Did you solve this issue? I´m using a spreadsheet with Erlang C formula for staffing but couldn´t figure out how to calculate agents required per interval as an output instead of an input that you change to achieve a target SL. It would be great if you can help me with this problem. Thanks!