7 Jan 2009

55 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

Leave a comment