7 Jan 2009

80 Comments

the erlang c calculator doesnt seem to work?

Comment by bob smart — 8 Jan 2009 @ 2:00 pm

I think that this is due to macro security. I have added in some notes in the page on how to change Macro security to medium.

Comment by jonty pearce — 8 Jan 2009 @ 3:54 pm

Now that’s neat!

Comment by Dave Appleby — 8 Jan 2009 @ 4:19 pm

[...] the introduction of our Erlang Calculator  a couple of weeks ago, Dave Appleby explores the future of  Erlang as a method for Workforce [...]

Pingback by Is there life beyond Erlang? | Call Centre Helper — 21 Jan 2009 @ 4:18 pm

Is it possible to calculate over 10 or 5 min intervals?

Comment by Jo Davies — 25 Jan 2009 @ 10:58 am

No currently it is fixed at 30 minutes, but you should be able to divide the number by 3 to make it 10 minutes, which should be a good approximation.

We will see if we can do different time lengths in the next phase.

Comment by jonty pearce — 26 Jan 2009 @ 9:51 am

Jo,

Erlang seems to work best at 1/4 or 1/2 hr intervals for forecasting accuracy.

As you get more and more granualr with the data you actually drill down too far and end up in the unfortunate situation of actually trying to look too closely at the Erlang-Poisson distribution.

For a pure scheduling layout I’m not sure you want to get to scheduling breaks on the 5 or 10 minute level anyway, that would depend on your staff breaking at *exactly* the correct time. With 1/4 hr or even better 1/2 hr schedules you have a little flexability, getting too involved means you are constantly man-managing breaks and fire fighting as the adherence to rota slips.

Personally I feel it’s over engineering the situation.That said Jonty’s suggestion will work, you do however need to weight the calls toward the front of the 1/2 or 1/4hr to allow for human nature in play (People tend to call just after the hour or half hour, and to a lesser extent after the 1/4’s.

Regards

DaveA

Comment by Dave Appleby — 27 Jan 2009 @ 2:52 pm

i need erlang for contact center for forcasting tools.

Comment by Amel — 7 Feb 2009 @ 2:17 pm

Amel

Simply download the Erlang Calculator and you should be able to start making your own forecasting spreadsheets.

Comment by jonty pearce — 9 Feb 2009 @ 6:24 pm

I used the excel and it is very useful,but i faced a problem.When i tried to caculate how many agents I need to acheive service level 99.9% and the incoming call rate is only one call and the average call duration is 150 seconds,and the target answer time is 20 seconds, so please feed me back as it is logid if you have two agents and there is only one incoming call,the service level should be 100%.
So please I will appreciate your help in clarifying that to me.

Comment by Mahmoud Abdel Bar — 13 Feb 2009 @ 5:35 pm

Mahmoud.

One of the causes may be rounding error, the other is that the P.M.F of the POISSON / ERLANG sort are VERY unlikely to give a certainty. The rounding error would be my first choice. The function will say OK I have one call of X duration in Y frame so I need Z staff to achieve the required service level. However, there is ALWAYS some uncertanty.

As a rule of thumb anyone involved in Statistical / probability is reluctant to EVER say anything is either certain or will never happen, I mean, New Kids on the Block coming back. What’s the probaility of that?

Below is a breakdown I wrote for elsewhere.

Erlang being a standard adaptation of the Poisson process is a discrete probability distribution and works on events being independant. It models the P 0.95 for a single call it will indicate two staff are required.

Generally it’s very good for medium to high volumes over a given time period. Anything more than 5 calls OR 15min talk in a 1/2 hr should be 90%+ accurate.

The caveat for the whole thing is..

It’s only as good as the data you’re using.

The old addage Garbage In = Garbage out still applies.

Erlang models given P < 1 that a call will come in within a given time period. 1/2hr being best 1/4 works too. Anything else is either too granualar or gross and the P.M.F (probability mass function ) breaks down.

It all depends on HOW good you want the svc levels, it will always (in raw unmodified state) give a probability of a second call coming in, therefore even if P> 0.95 for a single call it will indicate two staff are required.

Hope it helps

Comment by Dave Appleby — 13 Feb 2009 @ 7:03 pm

What do you mean by the term “Target Answer Time”, can some one elaborate on this ?

Comment by Sameer Junaid — 23 Feb 2009 @ 11:29 am

Sameer.

Simply “Target Answer Time” is the time you wish
calls to be answered within.

Normally 16 / 20 / 25 / 30 seconds.

This leads to the common short hand for service levels
of 80/20 or 85/16 so 80% within 20 seconds or 85% within
16 seconds etc..

Hope it helps

DaveA

Comment by Dave Appleby — 23 Feb 2009 @ 11:33 am

Thanks DaveA for your quick response,

I think that I got your point of view, for example if an Agent is required to answer a call with-in 3 rings, i.e. 4 seconds per ring, equallling with in 12 seconds. hence, my TAT (target answer time) will be 12 seceonds.

please confirm if i am right.

Thanks & Best Regards.
Sameer.

Comment by Sameer Junaid — 23 Feb 2009 @ 11:48 am

Exactly.

The industry standards tend to be between 10 and
20 seconds.

Regards

DaveA

Comment by Dave Appleby — 23 Feb 2009 @ 2:25 pm

Thanks a lot DaveA

Thanks & Best Regards.
Sameer.

Comment by Sameer Junaid — 24 Feb 2009 @ 5:53 am

Is your calculator capable to calculate delay data i.e. number of callers waiting longer than x seconds?

Comment by Cornelius Timothy — 25 Feb 2009 @ 9:27 pm

Cornelius,

One of the problems you would have is that
Erlang, by it’s nature, is geared toward answering
calls.

It actually works assuming the maximum time you are
prepared for a caller to wait.

In theory you should be able to say that if you staff
correctly and pick a sensible target answer time callers
shouldn’t have to wait, however, we do know that this
isn’t the ideal world.

Having recently been asked something similar and to calculate staffing for various abandon rates and it’s
not as easy as it seems.

Not exacly what you’re after I know..

Regards

DaveA

Comment by Dave Appleby — 26 Feb 2009 @ 9:09 am

Can you elaborate on what calculation the user defined function “agent no” uses?

Comment by Lou — 1 Mar 2009 @ 3:16 pm

Lou,

The UDF is the actual staff required calculation based
on call length and service level required.

It actually loops incrementing the agent
number until the target is hit. The actual function
is driven by the Erlang UDF.

If you have a look at the Vba in the background
it’s the last function in the Module.

Hope it helps

DaveA

Comment by Dave Appleby — 2 Mar 2009 @ 1:00 pm

Hi Dave,

I have macro security disabled entirely and still can’t get the whole page to work. The daily half hour breakdown does not calculate.

I had built a very similar tool at my previous place of work, though it required the Erlang for Excel add-in. Is that required here?

If so, are you able to email it to me?

Thanks for you help.
Scott

Comment by Scott Allen — 27 Mar 2009 @ 5:32 pm

Scott here again – not sure what did it, but after clicking away and back a few times, it worked.

Looks great. Thanks again.

Comment by Scott Allen — 27 Mar 2009 @ 6:08 pm

Scott,

Can you let us know which version of Excel you’re using?

It sounds like there’s an issue with the automatic calculations.

I’ve not seen this with the calculator, however, I have
seen it with other UDF’s in, paradoxically, Excel97 and 2007. ALthough not 2002 et al…

Thanks

DaveA

Comment by Dave Appleby — 27 Mar 2009 @ 7:48 pm

In the first box “Calculate the number of agents required to reach an agreed service level:”, do you put in the average half hourly volume per day or the max half hourly volume? Good calculator though.

Comment by Claire Shirley — 1 Apr 2009 @ 1:06 pm

Also, does that include shrinkage?

Comment by Claire Shirley — 1 Apr 2009 @ 1:08 pm

Do you have the staff utilasation excel formula

Comment by Peter — 5 May 2009 @ 5:11 pm

OK,

Been away for a while….

Claire: It will give you agents required on a raw basis,
ie: this is what you need staffed at any given interval. Shrinkage is a bit too dependant on local variables to be factored into such as this

Peter: Utilistaion% is :-

(Talk + After Call Work + Shrinkage factor)/Login *100
I would suggest a productivity figure of 65-70 % to aim for.

Regards all…

DaveA

Comment by Dave Appleby — 6 May 2009 @ 7:49 am

[...] this may look scary you can always use the Call Centre Helper Excel Erlang Calculator to help you with [...]

Pingback by How to set up a call centre | Call Centre Helper — 6 May 2009 @ 12:44 pm

How would I use this tool to get an annual FTE required?

Comment by Rhonda Haddock — 22 May 2009 @ 5:41 pm

Agents are supposed to login for 9 hrs but due to any particular reason (eg CAB delay), Agent is logging only for 8 hrs. how can i calculate it on excel file, please suggest.

Comment by Siddharth — 2 Jun 2009 @ 11:56 pm

If I have my staffing and workload is it possible to use erlang to calculate what 80% of customers will be answered in? (ie not using a service level target)

Comment by Kerrie — 22 Jun 2009 @ 1:28 am

I would like to paste the calculator into an existing spreadsheet that has my call volume data. When I paste it though the “Numner of Agents Required” box shows #/Name (and the formula itself is gone.
I am able to downlaod and sucessfully use the calculator in its downloaded Excel file, jsut have the problem after the paste (I checked and Macros are enabled on the paste to file)

Thank you
Gregg Solomon

Comment by gregg solomon — 23 Jun 2009 @ 9:02 pm

Gregg

Jo has had a look through the code and has come up with the following pointers.

The reason they have disappeared is because all the formulae are based on user defined functions (rather than built in Excel functions). These do not show in the visible macros but can only be seen by going to Tools, Macros, Visual Basic Editor and then looking in Module 1. They won’t be transferred in a standard copy and paste of the spreadsheet.

It should be possible to manually do a copy and paste of the text in module 1 to module 1 of the visual basic editor in his new spreadsheet and then the function “agentno” will work. Otherwise, depending on the volume of data in his other spreadsheet, it may be simpler to copy the data in to new sheets in the Erlang spreadsheet.

Jo

Comment by jonty pearce — 25 Jun 2009 @ 9:24 am

How do you enable Macros in excel version of 2007? I don’t seem to fidn it.

Comment by Samuel — 7 Jul 2009 @ 5:30 am

I have made all the changes and settign adjustments and then tried to enter the required inforamtion, nothing changes and i don’t get what i want. what else am i supposed to do?

Comment by Samuel — 7 Jul 2009 @ 5:50 am

Samuel,

A little more info would be useful…

HAve you managed to enable Macros?

Is it giving an error message?

Have you opened the whole file or just copied
it into another worksheet?

let us know..

Comment by Dave Appleby — 7 Jul 2009 @ 10:18 pm

can you please explain me what formula did you use to get the required agents field? and the predicted service level? this would help me understand better how you was this done.. Thank you in advance,

Comment by JC — 6 Aug 2009 @ 10:46 pm

I prefer using computer simulators as Erlang assumes certain important activities. Anyone knows where to download a computer simulator software in excel?

Comment by Luther — 7 Aug 2009 @ 7:45 am

hi,

just wondering if there is a similar tool for non-voice contact centers. specifically a calculator to determine “threshold” manpower limit for a fixed volume of documents to be processed. tx!

Comment by Maynard — 19 Aug 2009 @ 1:35 pm

Something like Nuqleus will probably be what you’re
after, although that’s really a back office MI app.

Regards

DaveA

Comment by Dave Appleby — 19 Aug 2009 @ 2:03 pm

tx for the tip DaveA.

however,are there simpler excelbased spreadsheets. will use it more for basic stuff, specifically determining “threshold” levels for manpower before SLA production volume suffers. tx!

Comment by Maynard — 20 Aug 2009 @ 2:32 am

Hi, can some one please tell me that the “Average Call Time” refers to “Average TALK time” or “Average HANDLE time” ?

Comment by Nauman Cheema — 20 Aug 2009 @ 7:34 am

Nauman,
It refers to the total time required to handle the call
including any after call work required.

Regards

DaveA

Comment by Dave Appleby — 20 Aug 2009 @ 1:29 pm

Shrinkage isn’t factored into the model, right?

Comment by Nathan Fish — 25 Aug 2009 @ 3:27 pm

Nathan,

See my comments above on the 6th May
regarding shrinkage as a local variable.

Regards

Comment by Dave Appleby — 25 Aug 2009 @ 5:28 pm

Thanks, this an excellent tool. For those wishing to convert to 15 minute intervals, you can do this by changing the denominator in column G from 1800 to 900, then inputting data in 15-minute increments.

Comment by Nathan Fish — 25 Aug 2009 @ 7:32 pm

Hi
i tried to work with the attached formula but i fail because of the shrinkage factored i am not able to calculate ((Talk + After Call Work + Shrinkage factor)/Login *100))

May i ask some help, how to go about it?
My aim of service level is 80%/20.

Comment by SAB — 19 Sep 2009 @ 9:36 am

Hi ,

I have downloaded your calculator which is working properly. Thanks a lot .

I have a one question.

Why did you divide =I14 / 1800
why we are using 1800.
Could you explain . please

Comment by Kivanc Solak — 22 Oct 2009 @ 6:16 pm

Kivanc,

Simply put, it’s the number of seconds in half an hour.

Regards

DaveA

Comment by Dave Appleby — 22 Oct 2009 @ 6:34 pm

Can anyone give an explanation of what the ‘Traffic Intensity (Erlang)’ means. If it’s increased, the performance level falls. Thanks!

Comment by David Blackman — 12 Jan 2010 @ 11:40 am

David,

Essentially and ‘Erlang’ is a descriptive unit of measurement in the same way as a Newton or Watt. It describes the loading on a telephony system.

Effectivly it is Time available on a system so…

Time interval / (Calls*average duration)

3 calls of 20 minutes in an hour would be one Erlang
(3*20)/60

3 calls of 15 minutes in an hour would be 0.75 of an Erlang
(3*15)/60

15 calls of 23 minutes in 2 hours would be 2.85 Erlangs

Anything greater than 1 means there is more traffic than one line can take.

To answer your main question, if demang goes up service goes down fr the same number of staff. You do need to remember that with Traffic intensity, it is an average over a period of time and not indicitive of any queuing behaviour in teh system.

Hope it helps.

DaveA

Comment by Dave Appleby — 12 Jan 2010 @ 11:57 am

Hi,
Please provide me with the exact formula for calculating Shrinkage % and Present % .
Thnaks !
Ravi Verma

Comment by Ravi Verma — 26 Jan 2010 @ 1:46 pm

Hi Dave,

Is Erlang suitable to estimate agents for email queues, where the SLA may be 2 hours? So in the calculator if the SLA is set to 95% in 6480 seconds (7200 sec – 720 to complete the effort to respond to the email) would it produce a viable result?

Would you recommend another approach?
Thanks

Comment by Mit — 3 Feb 2010 @ 4:38 pm

When using this model am I correct in assuming this is core number of bums on seats not allowing for shrinkage thanks

Comment by Marie — 4 Feb 2010 @ 11:59 am

Marie,

Spot on

Ravi,

You can only factor shrinkage in IF you know what it
is for your particular centre, there is no standard
formula I’m afraid.

Mit,

It will work IF you fator the queue / Svc time the way
you suggested, although a more ‘analogue’ system may
work as well in this situation.

Regards to all..

Comment by Dave Appleby — 4 Feb 2010 @ 2:00 pm

Does the incoming call rate include or exclude abandoned calls?

Thanks for your excellent tool!

Comment by Ian Logan — 18 Feb 2010 @ 2:57 pm

Hi,

This is a great calculator.
However, can some1 please inform me the formula to calculate FTE with AHT, Service Level, and Shrinkage.

For example:
Our company operates Mon-Fri, 8am-8pm;
Average calls per day is 2000;
Service Level is 85%
Shrinkage set to 35%
AHT is 310.
How can I calculate the FTE now??
Please help.

THANKS HEAPS,

-Weera-

Comment by Weera — 3 Mar 2010 @ 11:48 am

Hi,

how do I calculate ‘Calls per hour’,required for ‘SLA’ calculation,if I have monthly call volumes.I am trying to figure out what will be my Service level for the month.

Comment by Sandy — 25 Mar 2010 @ 8:23 am

I just downloaded this calculator it seems very interesting and most of my difficulties related to center staffing seems to come to an end today….. Lets see how effectively it will work….

Comment by Nitesh Ajmera — 19 Apr 2010 @ 6:07 pm

Hi,

Can you define me WFM service level formula please.

Regards,
Abdul

Comment by Abdul — 28 Apr 2010 @ 1:05 pm

Abdul,

Basically the simplest formula is:-

Of x calls answered what percentage were
answered within the target time of y seconds.

Regards

DaveA

Comment by Dave Appleby — 28 Apr 2010 @ 1:28 pm

Hello

Is Erlang suitable to estimate agents for email queues, where the SLA may be 2 hours? So in the calculator if the SLA is set to 100% in 7200 seconds, would it produce a viable result?

Would you recommend another approach?

Regards – Sayan

Comment by Sayan — 27 May 2010 @ 11:07 am

How can I calculate the required agents on monthly bases???

Comment by Mohamed — 1 Jun 2010 @ 12:08 pm

the example shows how to calculate the requierd agents in one day.

What if I want to add this up to decide the required agents in one month? shoud I summ the agents in the day and divide by shift hours (8 hours for example?)?

or should I divide the total number of agents into 2 then divide into 8 hourse?

Please explain… as this is very important for the annual planning.

Comment by ahmed — 1 Jun 2010 @ 1:12 pm

I need to use the erlang C formula in my budget annual model.

How to calculate the required staff from the interval bases to the months bases

Comment by Mahmoud — 1 Jun 2010 @ 5:43 pm

Hi Dave,
This VB code is great and I’ve been using it for a while now, after integrating the code into my files. I have found it useful to calculate the required staff and then also calculate the predicted service level based on the staffing levels we think we can achieve.

However, I’ve run into a small snag. Is there a limit to the workload that can be used? For example, if I run through 80/20 with a 500s AHT and 700 calls, it calculates 213 staff needed with an estimated service level of 100%. If I reduce the agents predicted below 213 the service level remains at 100% until I get to 132 agents at which point it promptly drops to zero.

I’ve gone through the VB script and the math seems sound to me. Do you know what this oould be?

Thanks for any assistance you can provide!
Andrew

Comment by Andrew — 11 Jun 2010 @ 12:48 am

Andrew…

Humm, not sure, can you let me know which version of
Excel you’re using?

I get someting similar with 2007 but not anything earlier.

Odd, as at lower volumes it looks fine.

Let me do some digging!

Regards

DaveA

Comment by Dave Appleby — 11 Jun 2010 @ 8:07 am

Do you know what happens to Erlang if you have a hybrid of two different call types with different qualities of service? For instance, we have all of the regular help desk calls that have to be serviced at 90% answered in <45 seconds, but a subset of same call center workforce also handles another split/skill with service level requirements at 95% at < 30 seconds. I don’t think that that would be an additive thing. How would you figure the right staffing? In other words, can erlang handle 2 service levels for 1 call center and calculate total required staffing? Thanks.

Comment by Bill — 11 Jun 2010 @ 12:45 pm

Hi Dave, thanks so much for the help!

I typically use Excel 2007 but the same thing happens in 2003.

I think I figured out where the issue begins. As you know the script runs by incrementing the agent number by one, checking the predicted svl and then incrementing again until the service level is achieved. This means the service level predicted will always be just a bit above the target. However, when you reach a workload which allows perfect achievement of the target, any workload input beyond that instantly yeilds a predicted 100% service level (and it seems to keep incrementing the agents for some reason).

Do you think Clearly Presented would support looking into this? I’m kind of stuck as I’ve integrated this code in everything!

Bill, you cannot enter multiple service levels into ErlangC. My suggestion would be to staff to the service level that is most strict (90/30 in your case) unless the other split/skill represents the vast majority of the volume. Alternatively, you could run an ErlangC calculation on both service levels individually and then apply a percentage to each based on the workload distribution. For example
90/45 on all calls = 40 agents
90/30 on all calls = 45 agents
If 60% of your workload (volume x AHT) is on the 90/45 service level then apply (60%x40)+(40%x45)= 42 agents required

Hope that helps. In the end ErlangC requires a single workload and service level target, blended queues you need to get into simulations.
Andrew

Comment by Andrew — 11 Jun 2010 @ 3:19 pm

Calculater doesn’t make sense… when you get to 1000 calls per hour or more, the occupancy calculation is almost 100%. You can’t have 80/20 service level and 100% occupancy.

Comment by David — 11 Jun 2010 @ 3:43 pm

Dave, I think I figured it out. Unfortunately it is a limitation of Excel. When you take the intesity to the power of the number of agents, that figure eventually exceeds the number of digits Excel can handle (9.99999999999999E+307), at which point the error kicks out of the function and the service level is set to 100%. This then stop the iterative agentno function from incrementing agents and your required agents is simply equal to the intensity (workload in erlangs).

I think I’ll add an upper and lower limit variable for occupancy and where that is exceeded the agents required becomes simply the workload divided by the maximum occupancy.

I’d be interested if you have any thoughts on this approach, and thanks again for the help. If you figure out how to make the macro work beyond this (80/20, AHT 500s with volume 500 should yeild a predicted service level close to 80%, instead of 100%) let me know!

Andrew

Comment by Andrew — 11 Jun 2010 @ 6:38 pm

FYI, there are other Excel Erlang Add ins that seem to work with much higher values. Perhaps Clearly Presented could adjust the code in some way?

Comment by Andrew — 11 Jun 2010 @ 10:21 pm

Hi!

I have very low number of incoming calls per hour, around 3-4, and between 200-500 seconds of handling time per call.

- Can I use the Erlang calculator for these values, or should I rather calculate on number of agents needed ideally without taking service levels into account?

BR,
Camilo from Sweden

Comment by Camilo — 16 Jun 2010 @ 4:49 pm

is that work on excel sheet?????????????

Comment by ema — 23 Jun 2010 @ 8:11 am

Andrew: I’ll pass it on.

BR: Erlang will always overstaff by one agent at very
low call volumes, for2-3 call with a 9-10 min AHT I’d suggest your manual model will work, although how expensive is it to lose a call Vs the cost of another agent?

Ema: Have a look at teh notes at the top, they’ll explain how to get it working in Excel.

Regards

DaveA

Comment by Dave Appleby — 23 Jun 2010 @ 8:24 am

How do I find the VB code for this tool?

Comment by Teddy — 27 Jul 2010 @ 1:14 pm

Hi Dave,

This tool is pretty good. Assuming we have multiple language skill sets with varied call volumes, do we have to assess the headcount required for each language skill set as different entity. I have to support 9 languages

Thanks,
Deepak

Comment by Deepak Vittala — 29 Jul 2010 @ 10:52 am

Hi Deepak
Your answer will depend on the degree to which your staff are multilingual. If you have 9 languages and everyone is multilingual, then you can use erlang on one worktype/skilltype.
If you have a more complex situation of some people being single and some multi-skilled, then simulation is the best route, but more complicated.
Failing that you can estimate how may skill groups you have, say 3 if in your case the average number of languages spoken was three. And then break your call demand into three, and run each through an erlang model. Not perfect, but can be quicker and simpler than full on simulation.
Hope that makes sense. Let me know if it does not.
Best regards, Steve

Comment by Steve Connolly — 29 Jul 2010 @ 1:34 pm

Thank you Steve for the suggestion. I am awaiting information on the skill groups so that I can estimate the headcount. Do i have to add any buffer head count or the Erlong model would take care of it. Also the support is not 24×7 support. The support window is just for 9 hours window.

Comment by Deepak Vittala — 29 Jul 2010 @ 3:08 pm

Good calculator.

If you wish to use the model by entering higher level numbers – such as calls received per hour or calls received per day, you can change cells D14 and E14 by switching 1800 with the number of seconds in your timeframe (per hour would be 3600).

Right?

Comment by John — 25 Aug 2010 @ 1:12 pm

Leave a comment