Resource Forecasting using Excel (Call Centre)

Dancey_ · Jun 28, 2011

Hey guys,

You (the members) have always been invaluable when I have been stuck with excel tasks in the past so thought I would post this here.

Unfortunately this is very broad question, no doubt with a thousand (at least!) different answers.

My position is this:

I have information for calls received into our contact centre for the last 13 months (no more), I have this information readily available as total by day. I also have it by interval (ours being 15 minute) for the same historic period.

I need to forecast the incoming call volumes for the next twelve months and then beyond.

 I had done some work on it that looked quite good but now that I am actually testing the accuracy of the forecast it is miles out. Overall, year to date the accuracy is within 1.4%, which is great, but on any given day the accuracy is all over the shop. I have tried to do it just using weekdays, then just using a particular day of the week, with Bank Holidays etc removed but it doesn’t really help, and as we are looking at long term trends here it shouldn’t matter what day of the week we analysis.

Any help would be greatly appreciated around what method you would use to forecast the contact?

What I had done so far was to look at 2010 Vs 2011 (by day) and then that gave me a year to date average change in contact, then applied that % change to 2010’s numbers to give me a forecast for 2011. Then I looked at the average accuracy % of the initial forecast and applied that percentage accuracy as a multiplier to the initial forecast to increase the overall accuracy and create forecast 2.

My idea was to create a forecast by day, then create various call profiles (each day of the week for each month of the year) that would be percentage distrubution acorss that day which would breakdown the forecasted daily total into calls per interval!

PLEASE HELP

Dancey_ · Jun 29, 2011

Can anyone help at all please? I am majorly stuck!

ELLKK · May 18, 2017

Forecasting can be pretty tough. So much so that even the big expensive workforce management systems tend to not be able to get it super accurate either, so don't feel bad. But it sounds like you are really on the right track overall, just need to get your day of week patterns a little better. It might help if you had a little visibility into what is happening with your volumes. I think I made reference to these guys a few weeks back here, but I would seriously look at this forecasting tool that is spreadsheet based.

It is kind of funny, there are these large well-know companies out there that get so many calls they are probably spending like half a billion dollars a year on call center agents, but they find they can forecast more accurately using dedicated forecasters and spreadsheets than what they would get from their expensive workforce management system.

Resource Forecasting using Excel (Call Centre)

Dancey_

New Member

Excel Facts

Dancey_

New Member

ELLKK

New Member

Similar threads

Forum statistics

Share this page

Resource Forecasting using Excel (Call Centre)

Dancey_

New Member

Excel Facts

Dancey_

New Member

ELLKK

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock