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

HillDragon

New Member
Joined
Jun 9, 2014
Messages
13
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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. :-P
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,455
Messages
6,178,772
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top