Results 1 to 5 of 5

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

  1. #1
    New Member HillDragon's Avatar
    Join Date
    Jun 2014
    Location
    Kansas City, MO.
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    Board Regular GDRIII's Avatar
    Join Date
    May 2014
    Location
    Southern Chester County, PA
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  5. #5
    New Member
    Join Date
    May 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •