Free Excel Erlang Calculator

Comments on: Free Excel Erlang Calculator

the erlang c calculator doesnt seem to work?

Posted 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.

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

Now that’s neat!

Posted 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 [...]

Posted 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?

Posted 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.

Posted 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

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

i need erlang for contact center for forcasting tools.

Posted 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.

Posted 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.

Posted 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

Posted 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 ?

Posted 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

Posted 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.

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

Exactly.

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

Regards

DaveA

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

Thanks a lot DaveA

Thanks & Best Regards.
Sameer.

Posted 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?

Posted 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

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

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

Posted 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

Posted 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

Posted 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.

Posted 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

Posted 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.

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

Also, does that include shrinkage?

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

Do you have the staff utilasation excel formula

Posted 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

Posted 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 [...]

Posted 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?

Posted 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.

Posted 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)

Posted 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

Posted 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

Posted 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.

Posted 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?

Posted 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..

Posted 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,

Posted 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?

Posted 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!

Posted 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

Posted 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!

Posted 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” ?

Posted 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

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

Shrinkage isn’t factored into the model, right?

Posted 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

Posted 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.

Posted 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.

Posted 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

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

Kivanc,

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

Regards

DaveA

Posted 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!

Posted 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

Posted 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

Posted 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

Posted 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

Posted 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..

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

Does the incoming call rate include or exclude abandoned calls?

Thanks for your excellent tool!

Posted 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-

Posted 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.

Posted 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….

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

Hi,

Can you define me WFM service level formula please.

Regards,
Abdul

Posted 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

Posted 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

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

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

Posted 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.

Posted 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

Posted 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

Posted 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

Posted 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.

Posted 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

Posted 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.

Posted 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

Posted 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?

Posted 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

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

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

Posted 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

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

How do I find the VB code for this tool?

Posted 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

Posted 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

Posted 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.

Posted 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?

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

I was looking for similar tool which I can use to plan my resources for back office process. Can anybody help me with this tool? Where I can find this tool? I’ve prepared the planner which includes the incoming volume and RPH (Rate Per Hour) but I was looking for some standard format.

Posted by Pravin — 14 Sep 2010 @ 3:26 pm

May I suggest Queuing Theory Calculator which is a simple, yet powerful tool to get the numbers done for queing models. http://www.supositorio.com/rcalc/rcalclite.htm I hope it helps you with your studies or job.

Posted by Xavi — 17 Sep 2010 @ 7:11 pm

Hello.
Does anybody know to estimate the call volume demand when you implement a new system – ERP (SAP , Oracle, JDE) for example 3 calls a month per user ?
Thanks.

Posted by Miguel — 22 Sep 2010 @ 10:16 pm

Hi
How can i please change the VBA function for agentno so that it returns decimals?

Thanks

Posted by Alice — 28 Sep 2010 @ 1:08 pm

Is there any way to get this to work based on call answer rate rather than service level?

Cheers

Posted by Mark — 26 Nov 2010 @ 6:06 pm

no puedo descargar, me aparece un sitio de google docds pero no me deja descargar la calculadora

Posted by leonardo — 30 Nov 2010 @ 9:26 pm

Leonardo

Utiliza el ‘link’

http://www.callcentrehelper.com/images/erlang-calculator-v1.xls

el azul “Click here to download the Erlang calculator.”

en la parte superior de la página

DaveA

Posted by DaveA — 30 Nov 2010 @ 11:11 pm

Hi,

Read above you cannot use calcualtor to forecast split service levels but can you forecast and include a max call wait time as an output? Basically I need to calculate FTE on GoS 80/20 on 400 calls but I also need to factor in a new SLA of 100/60 on the same calls which is where we current fail to focus attention by only targeting calls still in our SLA. Any ideas?

Thanks,
ChrisO

Posted by Chris — 8 Dec 2010 @ 2:35 pm

On that there is another website I use that does give a Avg. wait time (http://www.erlang.com/calculator/call/) so if I could add a calculation to add this to the calculator and staff to the point this <60sec that would solve. Any idea of the calculation?

Chris

Posted by Chris — 8 Dec 2010 @ 2:37 pm

This is heaven-sent! I’ve been searching for a free Erlang C calculator and I only found out about this just now. I’m not yet done using it but will give my feedback/input as soon as I get my results.
I’ll post this to my blog as well to help my co-WFAs.

Posted by Avee Balaag — 5 Feb 2011 @ 9:11 am

Hello, I’m ok using the calculator, I just noticed one formula. The one above the Incoming call rate at the left hand box. it says D14/1800. I’m just wondering where you got the 1800? Is that variable or fixed? Thanks for your help!

Posted by Avee Balaag — 5 Feb 2011 @ 10:48 am

1800 is the number of seconds in half an hour, this is the interval the calculator works to.

Regards

Posted by Dave — 5 Feb 2011 @ 1:12 pm

This calculator is fantastic for identifing interval requirements across the day. I’ve read many threads in quite a few forums and found several formulas for working out required FTE and I’m almost there but I’d really like the excel formula for working out required FTE for day/week/mth call volumes.
Using the following data day/wk/mth e.g.
Calls day/wk/mth – 1198/6049/23058
AHT – 440 secs
Occ – 82%
SLA – 85/60
Shrinkage – 22%
Hrs worked/day – 7.5

Looking forward to any assistance.
Regards,
Nigel

Posted by Nigel — 23 Feb 2011 @ 1:45 am

hy
i must calculate input time of the random time .
your help me
i am iranian i dont english
thank you

Posted by fatemeh — 6 Mar 2011 @ 10:26 am

Hi Dave,

I am a WFM Analyst here in Dubai and I’ve been a fan of Joanne’s VB codes for the past year.
I’ve been wondering though what is the relationship between Intensity,ASA,AHT,and SLA on the below formula..

=AgentNo(Intensity,ASA,AHT,SLA)

I love spreadsheets but i never had an experience in writing and understanding VB codes… once i see “Dim agents As Long” etc i loose my interest and close the file.

Is there anyway you can explain
=AgentNo(Intensity,ASA,AHT,SLA)in a layman’s term like using M.D.A.S.. I can understand Algebra and Trigonometry more than VB codes.. =)

Thanks,

Drebb

Posted by Drebb, Dubai — 15 Mar 2011 @ 8:55 pm

Hi
The erlang calculator looks great and is working -however, I am looking for a calculator to help forecast weather related calls. These calls are not predictable – would this calculator be of any benefit to me. I am having great diffilculty in producing a forecasting model that is within 8/10% accurate.

Many thanks

LM

Posted by Lynne Marie — 29 Mar 2011 @ 6:46 pm

LM,

It really depends on the kind of lead time you have, and, your trigger events are they sudden or do you have any lead time at all?.

A slightly more sophisticted model may be of more use, as, you’ll need to factor the sudden uplifts.

That said, if you are weather dependent you’re constrained by triggers, so, can probably build a lookup of…
Event A = xxx calls with a distibution of yyy minutes / hours.

Event B = ppp calls in qqq minutes / hours.

You can then factor that into the model and look at the staffing requirements for the sopt intervals. That’s something this calculator can do.

Regards

DaveA

Posted by Dave Appleby — 30 Mar 2011 @ 7:23 am

The below Erlang function doesnt match with the Actual ASA output with the given parameter
For example if i want to validate the actual ASA delivered with the given Agents, calls and AHT i get different output
ASA(Agents,CallsPerHour,AHT)

Posted by YR — 7 Apr 2011 @ 9:03 pm

YR,

Can you clarify a bit more, not sure what
you’re driving at.

When you say you go to validate, how do you validate?

Regards

Posted by Dave Appleby — 8 Apr 2011 @ 12:03 pm

Hi Dave

Many thanks for the advice on 30th March – sorry I have not replied earlier. I will certainly look at this and let you know how we get on.

Regards
LM

Posted by Lynne — 10 Apr 2011 @ 11:57 pm

Hi Dave,

If I wanted to use the total volumne of calls instead, would I just increase D14/1800 to the number of seconds for the week?

Posted by Andrew Z. — 3 May 2011 @ 4:57 pm

Dave,

I know this has been asked a couple of times but havn’t been able to see the answer… can this be used on for a daily, weekly or monthly call volume requirement?

Thanks,

DSL,
SATX

Posted by DSL — 11 May 2011 @ 8:34 pm

Sorry for the delay,
I’ll try and answer both questions at once…

Theoretically you can upscale the interval data, but, this destroys the granularity and really attacks one of the strengths of the Erlang model.

Uplifting to a one hour interval will work fine, however, when looking at at a week looking at 216000 seconds based on a 12/5 as your forecast plan will only ever give you a ballpark figure asthe daily /interval trends are washed out.

This error gets larger as you increase the interval, with a monthly you’ll lose the weekly trend throughout the month.

What that IS good for that tough is looking at longer term planning and advance scheduling of major training etc… What you can do is look at the variance, match it back and then see how big a gap is developing. You can then apply that to say the monthly plan and look at available days etc…

Hope that helps, drop a line back if you’d like anything more.

Regards

DaveA

Posted by Dave Appleby — 12 May 2011 @ 8:10 am

Thanks DaveA that certainly helps and is logical. I have a quick question about the agent planner – this is based staff (without shrinkage) as bums in seats… Once you add our 40% shrinkage in to get our rostered staff, what do you propose as been the best way to calculate total staffing needs over a 15/5 (40 hr week with 5*8 and 4*10 schedules)?

Posted by DSL — 12 May 2011 @ 4:26 pm

what type of occupance is calculated in or is it simply the number of poeple you need at any given interval to be available to take calls? I want to run it with 82% occupance and want to make sure I’m not under estimating my staff.

Thanks

Posted by Alexander — 24 May 2011 @ 5:51 pm

Hi Dave,

I just wanted to say this is a great spreadsheet.

You are also very patient in answering all the questions above – very detailed! (even for repetitive ones)

Greatly appreciate your hard work!

Cheers
Jo

Posted by Joanne — 26 May 2011 @ 4:25 pm

Loving the calculator and I would love to able to use the formula in a spreadsheet we have that tracks call volumes and plots this against a planned rota to highlight any potential gaps in our resource levels, but I cannot get excel to recognise the ‘agentno’ in the formula. Can you let me know which add-in I need to be able to get this to work.

thanks in advance for your help

Posted by Sarah — 27 May 2011 @ 1:36 pm

The Calculator is accurate, however i need to know the exact calculation of formula “agentno” & “Erlangc” used in the excel sheet.

Regards,
Nilesh T
+91 98 234 234 15

Posted by Nilesh — 19 Jun 2011 @ 4:46 am

I am trying to find a fomula that will help to calculate workload based AHT or OPHA (Outlets Processed per Hour (Average). It’s for an Outbound environment. We know how many calls we have for the day,how many agents are working each hour,AHT and Average Dials. Just wondering if anyone has experience with Outbound Daily Forecasting

Posted by Jonathan Freel — 5 Jul 2011 @ 9:42 pm

Hi – has there been an update so we can calculate the agent needs per 15 minutes? I am currently trying to fix the call volme planning / forecasting for my contact centre and I am slightly struggling. I need accurate figures per 15 minutes. My idea is the following:

Pull Volume figures (Calls offered) and Handling Time (AHT) over the last 3 years. This data will help my forecasting (simple calculation of trend, should be fairly accurate as far as I could tell). I will hopefully get figures for Calls Offered / 15 minuntes including AHT separately. Based on this info combined with Service Level I should then hopefully get correct staffing requirements which will help me plan the days and weeks ahead.

Any tips or input is very welcome. Thank you for having put this Erlang C Calculator online, it is a great help!

Regards, Andy

Posted by Andy McMullen — 18 Jul 2011 @ 12:03 pm

Hey – I’m trying to build a staffing model and I’m struggling – does anyone have any good examples of how to go about doing this. I understand that the calculater will ultimately tell me how many people I need depending on the variable inputs that I use; however, I want to take a step further and use the calculator to show me what my expected rates should be etc. etc. – just want to build something that has some functionality. I’m also looking to show this in 15min intervels. Any help would be greatly appreciated. Thanks!

Posted by erlangstruggle — 23 Aug 2011 @ 6:57 pm

Hi Need a simple calculation to know the number of agents required to answer 100 calls with an AHT of 12mins and shift of 9 hrs.

Posted by Kapil Rajpal Singh — 7 Oct 2011 @ 8:55 am

OK,

The calculator will do that if you know the distribution of calls.

Regards

DaveA

Posted by DaveA — 7 Oct 2011 @ 10:40 am

Dave,
You are a bit of a hero for both the calculator and your time to answer everyone’s questions.

Could you answer one more?

I am struggling with the Erlang cell.
I read your reply above, but can’t get it right in my head.

Itis set at ‘20′ in the calculator…. Can you explain in layman’s terms what I should put in that cell please!??

Can’t thank you enough.
If you take donations, I’ll happily forward one.

Posted by Allan — 18 Oct 2011 @ 10:18 pm

Hi
When I created a new module in my worksheet, and agentno function calculated there 18 agents for 19 calls in 15 min, AHT=121, target answer time=10, SL=77. But in native file same calculation shows 7 agents. How can it happen?
Best Regards,

Posted by Cumshud — 22 Oct 2011 @ 8:58 pm

OK….

Couple of answers(ish)

#1 Cumshaud.
I can’t really support anything outside the main download, I’d guess you’ve a reference wrong somewhere though. If it’s working fine in the native code can’t you leave it there?

#2 Allan.
Which cell are you referring to? The cell set to 20 as default is the target answer time in seconds, or, am I missing something?

Regards

DaveA

Posted by Dave Appleby — 24 Oct 2011 @ 11:35 am

Thanks for this calculator! Is there a way to vary an agent’s “productivity?” (i.e., the amount of his/her an agent would really be able to take calls. This calculation would include biobreaks, time to get a drink of water, etc. Also, to add “Wrap-up” time, would I ismply increment the call length to include, for exampole, 15 seconds between calls?

Posted by Pascalpal — 25 Oct 2011 @ 5:09 pm

Ok,

Wrap should really be included in the AHT for the call not just talk, so, that’s
not an additional factor it’s part of the requirement.

From a calculator like this you need to factor your
’shrinkage’, IOT (Indirect operational time),
productivity or what ever you’re calling it as an
additional factor above and beyond what the
calculator says.

This just gives you the ‘raw’ number required at any
given interval.

Regards

DaveA

Posted by applebyd — 25 Oct 2011 @ 6:50 pm

Thank you!

Posted by Pascalpal — 25 Oct 2011 @ 6:59 pm

Hi dave,

You still have not answered how you obtained the number of agents…

Can you explain it in simple math.

Thanks

Posted by omaiaa0p — 2 Nov 2011 @ 8:40 am

Sorry,

Not sure what you mean.

I can’t find a previous query from you.

If you mean in general how do we find the number of agents, it’s based on the Erlang C distribution calculations which in turn are a derivation of the Poisson distribution.

The Wikipedia page is a good place to start.

http://en.wikipedia.org/wiki/Erlang_(unit)

As always with Wikipedia, don’t trust it as the only source! Look at the references and go from there.

The basic theory in obtaining the number of agents is:

1) Look at the historical call arrival and handle time.

2) Use the probability model to calculate Agents required based on the base data.

Unfortunately it’s not a simple calculation, the upside is there are plenty of tools, like this one, available to help.

Regards

DaveA

Posted by DaveA — 2 Nov 2011 @ 12:34 pm

would u pls tell me if landed calls 500000 and AHT(avg handling time)500 wht should be the manpower forcasting with 40% shrinkage? productive hour:8 hr

Posted by nazia — 3 Nov 2011 @ 5:39 am

Nazia

That’s what the calculator is for:-)

DaveA

Posted by DaveA — 3 Nov 2011 @ 8:20 am

Hey!

Can I calculate staff breaks with this program anyhow? If not, can anyone tell me how can I calculate staff breaks in excel?

Thanks

Posted by oiro — 12 Nov 2011 @ 7:30 pm

Hi, can this be used to calculate agents required to do email servicing? Can it also show productivity %?

Thanks!

Posted by Aida K — 29 Nov 2011 @ 11:31 am

I don’t thik the free Erlang calculator is working.

Posted by Dmitri — 1 Dec 2011 @ 7:22 am

Dimitri.

Helps is you say why!

Have you enabled Macros?

Aida,

It can be used on e-mail volumes, but, the
Productivity count is outside the scope. This
is aimed at getting the correct staffing in place.

Regards

DaveA

Posted by DaveA — 1 Dec 2011 @ 8:51 am

Just a quick question if I may,

when inputting available agents into the erlang form, doe’s it allow for breaks or would I need to adjust accordingly, silly question I know.

Your help in this matter is much appreciated.

Regards

Jez

Posted by Jez — 16 Dec 2011 @ 4:55 pm

I have just downloaded your tool but for some reason the formulas in cell references do not work. I have to run the programme in Open Office is this why ?

Posted by Darren — 5 Jan 2012 @ 1:44 pm

Sorry the cell references are D25, I24 and I27

Posted by Darren — 5 Jan 2012 @ 1:50 pm

OK,

Not tried it in Open Office, I assume you’re
using Calc?

If so, lety me know which version and I’ll have a play.

Regards

Posted by Dave Appleby — 5 Jan 2012 @ 3:09 pm

Hi Dave

Thanks for getting back to me its V3.2

Posted by Darren — 6 Jan 2012 @ 2:06 pm

Darren,

Humm..
It’s the macro driven functions, however, they did run in an older version.

I’ve had a play with the macro settings, but, can’t immediately see the constraint that stopping the macro being called.

I’ll keep trying.

Regards

DaveA

Posted by DaveA — 10 Jan 2012 @ 11:31 pm

Dave

Thanks for you time and effort it is appreciated

Regards

Darren

Posted by Darren — 12 Jan 2012 @ 6:23 pm

Post a comment

Name

Website (optional)

Link to this page from your blog

We'd love it if you could link back to us on your blog. Here is the code. <a href="http://www.callcentrehelper.com/erlang-c-calculator-2473.htm" title="Free Excel Erlang Calculator">Free Excel Erlang Calculator</a>

Subscribe to the free Call Centre Helper Newsletter