Free Excel Erlang Calculator

We have developed a free Erlang C calculator to help with your call centre planning.

This is a simple Excel spreadsheet tool that allows you to work out how many agents you will need.  You just need to enter your call volume and the service level that you would like to achieve.  The calculator then looks at the agent capacity you would need to achieve the service level.

And because it is all done in an Excel spreadsheet it makes it pretty easy for you to model different call volumes across the course of the day.

The calculator is based on Microsoft Excel with a bit of nifty software programming from Joanne Sparkes at Expedio Spreadsheets using a mathematical technique called Erlang Theory*. Jo has coded it from scratch and it seems instinctive to use.

Click here to download the Erlang calculator.

To get the calculator to work you will need to have macros enabled on your computer.  

In excel select Tools > Options > Security > Macro Security and then select medium.

Macro security in Excel

You can then “Enable macro” when you open the spreadsheet.

You are free to use the Free Erlang C Calculator in all of your projects, provided that you do not resell or distribute the calculator on the web.

* Erlang Theory  is named after the Danish mathematician Agner Krarup Erlang (1 January 1878 – 3 February 1929) , who invented the fields of traffic engineering and queueing theory.  He published his Erlang C formula in 1917 and it has been widely used ever since.  The Erlang model is used in all major call centre forecasting and workforce optimisation (WFO) and workforce management (WFM) solutions available today.

If you prefer we also have an online version available. Click here for the online staffing calculator and here for the spreadsheet version.

7 Jan 2014 - Read more about Technology , , ,

Comments on: Free Excel Erlang Calculator

I am a big fan of your erlang calculator as the organization I work for does not have a workforce management suite with which to model call stats and staffing. I have used your tool for years to produce the Staffing Requirements (or other values)for cut and paste into Excel or Access tools. In the absence of a WFM software I have recently built a staffing model in Excel 2010 that requires I have multiple instances of the Erlang Calculator embedded in the multiple worksheets of the same workbook.

The Calculator works perfect when I use it as a stand-alone excel workbook. If I copy the agentcalc worksheet from the stand-alone calculator and paste it into a new Excel 2010 worksheet, Excel no longer recognizes the erlang formulas for servicelevel, agentno, erlangc. The VBA shows, but it seems as though it has lost the connection to the .xla file.

Do you have any recommendations on how or if I can do this.

Posted by Paul Burns — 21 Feb @ 1:00 am

Hi Paul.

Nice to see you find it useful!

In order to duplicate it within a workbook you’ll need to follow a couple of steps.

1) When you copy it over also copy the module from the VBA Window.

2) servicelevel, agentno, erlangc are all named ranges you’ll need to change. I suggest just appending _1 (VBA will need the underscore)

3) Change the variables in the new module (Anything that has a DIM statement at the top)
again I suggest appending a _1 (same caveat applies)

Give it a go, see what happens, and, if you get problems post them back here and I’ll try to help.

Just as a suggestion have you thought about just calling a second workbook from the first and running
the macro from the first book? That would eliminate the whole problem as you can then automate the whole cut and paste of the data…

Regards

DaveA

Posted by Dave Appleby — 21 Feb @ 11:17 am

Thanks for your quick response. I’ll report back after I try this. Kind Regards.
Paul

Posted by Paul Burns — 24 Feb @ 2:18 am

In a call center environment, AHT is rarely consistent every 30 minutes. Do you plan to create a version of the Erlang Calculator where you can input the AHT forecast everyin 30 minute intervals along side the CV intervals?

Posted by Anita — 14 Mar @ 3:48 pm

I have AHT 240 , ASA 20, Calls 30 min interval as 40 , Target sl 80%, Now if i have to calculate req agent and forcasted sl , without using erlang what would be the step by step process

Posted by Rohan — 13 May @ 9:55 pm

I’m not sure why you cannot use Erlang, as this would be the easiest way to do it.

The only other approaches are
– Using Workforce Management software. There are lots of companies who do this in the directory.
– Creating some form of simulation computer program. This would take a lot of time and effort.

I’m not aware of any way to do it on paper.

Posted by jonty pearce — 14 May @ 2:55 pm

Its just a challange given to me and i really hope i could get soe help , pOisson probabilty migh help but i m unbale to make data to use Poisson , if some one can help with the data points

Posted by Rohan — 14 May @ 6:36 pm

The calculator works just fine – i plugged in my actuals and it is showing i should be doing fine while day on day or even on an interval basis I am failing. My assumption is that i have too much in office shrinkage which is not factored in this tool. I notice my Outbound activity and other auxes are taking away my staffing. how do i incorporate these to my planning for better coverage?

Posted by Josh - philippines — 17 Jul @ 8:59 pm

Do you have this for a weekly call profile calculation, this currently is a daily calculator.

Posted by KC — 28 Jul @ 10:00 pm

did you include the shrinkage in this calculator ?

Posted by Anonymous — 6 Aug @ 1:12 pm

I got this one in your Web :
3. Does it allow for shrinkage?

The short answer is no, it gives a required number of staff to be available on the phone for any given interval. Shrinkage is a bit too much of a local variable to be able to factor. Different companies use different criteria, personally I’ve seen:
•Sickness
•Late
•1:1 sessions
•Breaks
•Coaching
•Training

However, everyone has their own criteria.

It can simply be corrected for by using: (FTE/Shrinkage %)*100

WHERE we can apply this formula “by using: (FTE/Shrinkage %)*100” ??

Posted by Quatiabh Allan — 6 Aug @ 1:17 pm

Hi,
My self Sudip.
Now I have download Erlang sheet V3 it is brilliant thank you so much for providing this tools.
Need help on this.
I want to know how much occupancy included in this tools?
Can I able to change the target of occupancy?

Thanks.

Posted by Sudip — 25 Aug @ 11:18 am

Hi Dave, Thanks you for this tool!!! Worked with several WFM tools in the past, at my new job we don’t have one, and I really like this for the basic requirements. HAve one question, how can an interval with 3 calls have a service level of 95,45%?? I think it can only be 0%, 33,3%, 66,6% or 100% (0,1,2 or 3 calls within SLA). Can you please explain what I am missing?
Thanks!!!

Posted by Peet Holland — 7 Sep @ 4:06 pm

Can someone tell me what is “Required service level”. I am very new to this and i am in need of this kind of template

Posted by Santhosh — 20 Nov @ 2:37 pm

Hello, I was questioned whether this calculator has agent utilization built into the formulas and if we can adjust? I do not normally add utilization in my analysis, but am being asked to do so. I am unsure whether to include it in the Erlang-C calculation or in Shrinkage. Thanks!

Posted by Liz Stilwell — 26 Jan @ 3:46 pm

Post a comment






Subscribe to the free Call Centre Helper Newsletter
InIn - Omnichannel adverts
Button Adverts
Latest from the Community
 
call centre | call centre jobs | presentation | powerpoint templates | business helper
css.php