Forecasting Abandoned Calls

I’ve been tasked with forecasting abandoned calls by interval. I realise its not normal practice, but have been told it has to be done!

Anyone have a good “non erlang” formula (and an erlang) way of doing it? I don’t have a wfm system and my erlang availability is sporadic on our network.

Question asked by Laura

Abandon Formula in Erlang

use the abandon formula in erlang

=abandon(no.agents,abandontime,calls per hour,aht)

if I remember correctly this gives you abn % which is a % of the forecasted calls (forecast*abn %) will give you the number of calls. if you are using 30 min intervals remember to multiply your ‘calls per hour’ by 2

should have said that you will need to calculate the abandon time first if you don’t keep records of that already, average time to abandon

With thanks to mike

Thanks

Thanks Mike, I think i’ve got it working now. Just need to try and figure out whether I can do a daily figure without a 15min breakdown too.

With thanks to Laura

Sum Product in Excel

hi laura, are you familiar with sum product in excel you can use this then divide by the total calls

=sumproduct(abn% range,calls range)/totalcals

With thanks to mike

Break it Down by Interval

Thanks Mike, i’m just getting my head around sumproducts. I was wondering whether there is a way of getting an abandoned figure without the need to break it down by interval. Will the =abandon(no.agents,abandontime,calls per hour,aht)formula work for a whole day rather than a 15/30min interval?

With thanks to Laura

By Interval

you need to do this by interval, as it uses he number of agents you have per 30 min / 15 mins

With thanks to mike

Looking to Achieve the Same Goal

Hi Mike,

I’m looking to achieve the same goal but i’m using the online Online Erlang Call Centre Staffing Calculator (Version 3.1 Beta)

how would I be able to use the abandon formula in erlang ??

=abandon(no.agents,abandontime,calls per hour,aht)

thanks

With thanks to Danny

Issue With Formula

If you are calculating abandonment for 15min intervals do you multiply the volumes and number of agents by 4 or just the volumes. Do you also divide it by 4 once calculated. I am having issues with the abandonment coming out at 100% which, would be impossible unless no agents were logged on?

With thanks to Lena

Does This Article Help?

How to Predict Call Abandon Rates Based on Service Level

With thanks to Jonty

Expand On Last Point

Can anyone provide an update on the last point i too get extreme % when looking at 15 min intervals. I multiply the volumes by 4 but still it does not look right.

If i put 20×4 calls per 15 min, 300 aht, 10 agents and 60 secs wait i get 14 % which looks like could be correct

I test with 5 agent and says 100% abandoned which is incorrect.

With thanks to Bob

How Many Calls Per 15 Minutes Are You Getting?

Bob – are you getting 20 calls per 15 minutes, or 80 calls per 15 minutes?

Run it through the online Erlang calculator. This will get you the most accurate result

https://www.callcentretools.com/tools/erlang-calculator/

With thanks to Jonty

Abandon Rate Reply

Hello. Thanks for the reply.

Its 20 calls per 15 minute interval.

I need a formula to run it in excel. Not through the site sorry.

As will need to replicate several times.

If you run the calc online it does not show you less than 10 fte and i think this is because its flips straight to 100% abandon if your fte goes to low.

With thanks to Bob

The Correct Calculation

Which excel spreadsheet version are you using.

I Have run the figures online – This is the most accurate and the only calculator that we use that includes the abandon rate. This is taken from the Erlang A formula.

20 calls per 15 minutes. AHT 300 seconds. 80% of calls answered in 60 seconds. 30% Shrinkage.

I do not think that you had included shrinkage.

The results are

13 Agents (9 before shrinkage). Average Speed of Answer 40.3 seconds and abandon rate of 7.4%

If you use these figures in your calculator what results do you get?

With thanks to Jonty

Online Vs Download Sheet

Yes if I run the same numbers online it gives me the same 7% abandoned as you got thanks.

What I would like is to be able to create that in excel so it can calculate off my excel sheets rather than manually inputting each interval online to see the abandon rate.

Is this possible?

Do you have the link to the excel with this function? and associated vba code that allows you to calculate abandoned outside the website?

With thanks to Bob

I Don’t Know of An Abandon Excel Function

I’m afraid that the only abandon function we have uses the Erlang A formula.

We have no plans to add this into Excel. You may be able to look at the formula, but the maths is quite scary. It took me over 3 months to figure it out.

http://ie.technion.ac.il/serveng/References/Erlang_A.pdf

With thanks to Jonty

Forecast Abandoned Calls At Weekly Level Not Interval

Hi, can anyone help me?

I’ve been asked to look at ways to forecast abandoned calls at a weekly level, currently we use a slope and intercept formula looking at the relationship of occupancy vs abandoned calls. Is there other ways of doing this to give more accurate predictions?

With thanks to Rob

Possible Way

The best way to forecast abandon rates is to plot sla against abandon rate in a scatter diagram in excel. Then add a trend line and follow this line.

Example – it may say at 80% sla you achieve 10% abandon rate. You then know you can achieve 80% sla with x fte at x call vol and x aht based on erlang. Say 60 fte. So then you know if you put 70 dte on you should be better than 10% abandoned.

You then keep amending your sla % target in erlang to get to the abandon rate you want to achieve. This will mean increases in fte or reducing your aht.

With thanks to Bob