FAQs – Call Forecasting With Spreadsheets

Our expert panel provide answers to questions about Erlang calculations, abandoned calls – and forecasting using a Chinese calendar!

1. How do you do forecasting using a Chinese calendar? (The specific challenge here is that their holidays fall on a different date year over year, normally 2 weeks off from the previous year.)

Think Easter in the Western world. Find out impact and remove it from historic data.

Then forecast without the holidays and add (or subtract) the impact numbers you identified for that particular holiday from previous years.

2. What is the current global industry average shrinkage applied to FTE requirements?

Dimension Data, who do a global benchmarking report each year, are currently showing agent sickness at 10% (25 days a year), with an additional 25% of time at work involved in activities such as one-to-ones and training.

This would give an industry average shrinkage of 35%.

3. Is there a way to apply Erlang to daily or weekly forecasts – either as a straight Erlang calculation or as an availability target?

This question refers to non-normal forecasting. Yes, you can use this method but you have to understand that it is a best guess and not something you can rely on. This is a “broad strokes” approach and there are better ways of doing it.

While you can use Erlang for any amount of time you want (from 10 mins to a week), you will only produce high-level numbers.

The second stage is how you break them down afterwards. Here you’ll need to look at the difference between forecasting volume and headcount – headcount is a static equation once you have the volume.

4. How do you remove outliers in data?

• I remove the top and bottom 25% – Kevin
• I don’t trim any calls. We just use a weighted average AHT in our calculations – Fiona
• I look at a mean average historically – Matt
• I replace an outlier value using distribution from a normal day or week –Michael
• Remove anything more than 1 standard deviation from the mean – Colin
• We have a team that analyses the reasons for contacts so we can identify when something is an outlier and quantify it. I then use those figures to adjust the weekly numbers – Corbi

5. Is there some way of producing a formula that could work out the call volumes we can expect from our new (more needy) customer volume as well as the volume generated from our older (and less demanding) customers?

This will always be a long equation. Unfortunately, if you are forecasting 3 months in advance, it is going to be a large data table, whether you like it or not.

However, you may find a pivot table useful, as well as filtering by customer, to better handle large quantities of data.

6. How do you schedule for breaks during a shift?

You need to be able to break down your forecast into intervals – ideally 15 minutes, as this links to a typical break length.

Then check your initial schedule for scheduled versus staff needed and link your staffing to either: (a) periods of overstaffing or (b) when being short staffed is likely to have lowest impact, e.g. quietest time periods.

7. How would you forecast for a call centre that is extending their operating hours and there is no data for the period?

I would start by looking at ACD data to see if you can get an indication of calls attempted. This will show you if you have any latent demand.

I would then extrapolate the volume in a graph for the last couple of hours and follow the trend.

Overstaff slightly to start with as it is always easier to ask staff to come in earlier than it is to ask them to stay later. Within a week or two, you should start to see if your assumptions are generally correct.

8. How do you use Excel to calculate calls to a particular driver?

Create rows (or columns) to track each element.

Then when you believe the specific driver to be happening, you adjust your volume calculation to include that driver.

9. When forecasting call volumes, is it a better practice to use calls offered or calls handled? Do we need to factor in abandoned calls?

I like to use offered, but in cases of extreme abandon rates, I would consider adjusting the offered data to something closer to your normal run rate for forecast.

If you normally have 5% abandon, but on one day you are offered 100 calls and 20% abandons, I would bring it down for future forecast purposes to around 85-86.

However, always keep original data, as a temporary issue today could start to repeat and become a trend in a few weeks’ time.

10. What online websites do you recommend for Excel courses and certification?

Try local colleges. A lot of institutions offer night classes and the face-to-face time with a tutor can be more useful. They are usually low cost as well.

For certifications, there is the MOS (Microsoft Office Specialist) certification:

• 77-420: Excel 2013
• 77-427: Excel Expert Part 1
• 77-428: Excel Expert Part 2

11. Over what period should calls be measured?

The more data the better. Some experts argue that you need 2-3 years of data to allow any forecasting algorithm to accurately identify trends.

12. Forecast accuracy (usually plus or minus 5%) is considered very important in many businesses to the extent I have been asked in the past to re-forecast the week I am in to make the accuracy report “green”. How would you go about trying to change this culture to instead show that the forecast is guiding the correct decisions rather than aiming for a specific number?

I know one forecaster who refined his forecast on arrival at work each day. He achieved 1-2% accuracy. Great but no use. Forecasts need to be measured at different time periods. However, I always think that a key measurement is at the time period when the scheduling people get started on their processes.

They rely on accurate forecasts as any errors will lead to inaccurate schedules. Forecasting on the day helps intraday teams identify where to focus changes, but it hides previous inaccuracies.

13. How do you forecast abandoned calls without a WFM system?

Some of the Erlang calculators can do this if you have calls offered, AHT and staff available.

However, these are only indicative as they take static scenarios and can’t look at simulations like some of the more sophisticated WFM systems.

As long as you accept that it gives you ‘ball park’ equations in the add-on, it will help.

For more information, read our article How to bring down your call-abandon rates

14. Does shrinkage include planned and unplanned shrinkage?

An allowance for unplanned shrinkage (short-term sickness, for example) should always be in a calculation.

15. How can you forecast calls for a busy season when contact volumes have increased over the years?

You need to look back at previous years to see what impact the same season had before and then increase your forecasts by that same percentage.

16. I haven’t been blown away by my WFM forecasts. How good are they?

All forecasts in WFM systems are only as good as the people using the system. You need to cleanse data and also think about how to include future events. This is why many people come out to Excel as sometimes it is easier to check what is included.

Because WFM vendors are sensitive about their intellectual property on algorithms, this means we as users don’t always know what is happening and are less able to influence/ adjust settings.

Try to have an open dialogue with your account manager and get some high-level process flows on the stages the system looks at – and then think about what data cleansing / data manipulation you need.

17. I use an average of the last few weeks’ AHT to determine how many agents we need to answer calls. Would you recommend this method? Or is it better to use the previous week’s data to predict the following week?

Either should work, but be careful! If a lot of new staff started last week then you would be using too high a number. This is why an average of a few weeks will average out exceptions.

A lot of WFM systems seem to think an average of 4–6 weeks – either straight average to weighted average, where last week has a higher weighting than older data would be appropriate.

For more information and advice on forecasting using spreadsheets, watch our free recorded webinar Excel Masterclass – Designing a Better Forecasting Spreadsheet

You also also read our article 11 Top Tips for Spreadsheet Forecasting in Excel

Click here to download our Monthly Forecasting Excel Spreadsheet Template

Click here to download our Excel Erlang Staffing Calculator – including Shrinkage

With thanks to: Dave Appleby – Resource Planning, Jo Sparkes – Spreadsheet Design Consultant, and John Casey – The Forum

Author: Megan Jones