Creating a Call Center staffing tool in PowerPivot using Erlang C and Weighted Moving Averages

[HillDragon]

Ok, if you have ever had to staff a call center you'll know that you are either large enough to afford the purchase of staffing tools or you're not. It's that cut and dry. The company I work for falls into the latter and ever since I was placed into a position that dealt with those sorts of things I've been looking for a solution the the arduous task of the Erlang-C equation. Like most people in this situation, I've searched the web in hopes of a solution but have only come up with 3 or 4 excel examples that can calculate Erlang-C and 1 or 2 that can solve the equation in reverse to determine what every work force manger wants to know: The number of Agents needed to achieve a desired Service Level. And though these tools are useful, they are also very limited and time consuming. So I have created a Tutorial for anyone else that may need to do something similar.

note: even if you are not working with the same sort of issues as described, the core of this tutorial is Ranking data on the fly, Summing the total of multiple iterations of one equation with variable data, and Using a complex equation to filter a table against it's own data.


The Task:

• Create a pivot table that will provide the number of agents needed for the number of calls expected.

The Problems:

• Our call volume is weighted to be more heavily influenced by the most recent data and is comprised of the last 4 "good" weeks. This means that we need a measure that can tell, of the 4 weeks selected, which is the most recent and calculate a weighted average appropriately.
• Erlang-C is calculated in Excel using the Poisson function. DAX does not have a Poisson function.
• Taking the same equation and solving for X..

The Solutions:

• Rank X on the dates to calculate weight.
• Create Poisson and Cumulative Poisson measures
• Using the Erlang-C equation as a table filter in a SumX.

The Tutorial contents:
With the images included it turned out to be about 30 pages so I've compiled it all into a Word file to save the length of this post.

• Erlang_Example.docx
• Erlang_Example.xlsx
• /Images. containing the full size images from the .docx.

The doc has smaller images that link to ~workingDirectory/images so I have made a .zip available if you want to grab them all at once.
And they can all be found here.

Please let me know if you have any question, comments, or critiques.

 

[scottsen]

I didn't actually read the forum rules to know how many I am breaking, but in this case, it seems appropriate, just to say this: Holy ****.

Also "or as his friends call him, Bobby C" -- um, I am the only person I know that has called him that.

 

[GDRIII]

Guest PP Post in the making, erh, uhhhh, made.

 

[scottsen]

Post live:
You’re “Poisson” Running Through My Veins: A Truly Epic Guest Post on Call Centers and Erlang C « PowerPivotPro

 

[ELLKK]

You might also want to check out spreadsheetscheduler.com. They have some call center forecasting and scheduling stuff that use erlang, and some of their spreadsheets are free. Their capacity/hiring plan one is pretty cool, especially since it is free.

 

[life_in_picture_format]

I know I am VERY late to the party here but I supervise a call center which includes many hats, one being workforce management. This is a very intesting find for me, please share any other info, tips and tricks you have used in the call center enviroment. Thanks!

 

[life_in_picture_format]

Ok, if you have ever had to staff a call center you'll know that you are either large enough to afford the purchase of staffing tools or you're not. It's that cut and dry. The company I work for falls into the latter and ever since I was placed into a position that dealt with those sorts of things I've been looking for a solution the the arduous task of the Erlang-C equation. Like most people in this situation, I've searched the web in hopes of a solution but have only come up with 3 or 4 excel examples that can calculate Erlang-C and 1 or 2 that can solve the equation in reverse to determine what every work force manger wants to know: The number of Agents needed to achieve a desired Service Level. And though these tools are useful, they are also very limited and time consuming. So I have created a Tutorial for anyone else that may need to do something similar.

note: even if you are not working with the same sort of issues as described, the core of this tutorial is Ranking data on the fly, Summing the total of multiple iterations of one equation with variable data, and Using a complex equation to filter a table against it's own data.


The Task:

• Create a pivot table that will provide the number of agents needed for the number of calls expected.

The Problems:

• Our call volume is weighted to be more heavily influenced by the most recent data and is comprised of the last 4 "good" weeks. This means that we need a measure that can tell, of the 4 weeks selected, which is the most recent and calculate a weighted average appropriately.
• Erlang-C is calculated in Excel using the Poisson function. DAX does not have a Poisson function.
• Taking the same equation and solving for X..

The Solutions:

• Rank X on the dates to calculate weight.
• Create Poisson and Cumulative Poisson measures
• Using the Erlang-C equation as a table filter in a SumX.

The Tutorial contents:
With the images included it turned out to be about 30 pages so I've compiled it all into a Word file to save the length of this post.

• Erlang_Example.docx
• Erlang_Example.xlsx
• /Images. containing the full size images from the .docx.

The doc has smaller images that link to ~workingDirectory/images so I have made a .zip available if you want to grab them all at once.
And they can all be found here.

Please let me know if you have any question, comments, or critiques.

your Queue names are super funny by the way