Now free to download is our new Excel call centre forecasting template, which will assist your call centre’s capacity planning, demand and call volume forecasting processes.
This tool needs you to have 24 previous months of data available. You add these to the spreadsheet and it will calculate the next 12 months’ predicted values for you. If you do not have multiple years worth of data, take a look at our article: How to Forecast with Minimal Data.
Free Excel Forecasting Template Designed to Work with Call Volumes
You can then use these reports for capacity planning purposes. The forecasting template has been designed to work with call volumes, but could easily be adapted for other types of monthly forecast.
Our forecaster works using the Holt-Winters, or triple exponential smoothing, method, which assumes that there are three components to any forecast:
A seasonal adjustment – based on previous data for the same months, e.g. you might expect customer calls to go down in December or rise in July.
A trend – whether your values are generally growing or shrinking over time.
A level – a baseline for your values once the other components have been removed.
You will need to have at least 24 months of data in order to produce the next 12 predicted months for call volumes. You can probably get this data from the call volume reports on the Call centre system.
When you open up the tool you will see that it has some dummy data in it, and you should be able to see a chart, with the data that feeds it to the left-hand side, starting with the oldest month in cell A3 and the oldest value in cell B3.
You can add as many values as you wish in columns A and B. Please don’t leave any gaps in the data.
You can replace the values by simply overwriting them or copying and pasting data from another spreadsheet, as long as it is the same format of date in the first column (dd/mm/yyyy) and value in the second column.
It’s best to Copy and Paste Special (Paste as Values) to be absolutely sure that you don’t inadvertently copy over any formulas from elsewhere.
If you select the current data and press the delete key, you may get the following message
This is fine, click OK and the chart will refresh and show just one point in the centre. When you enter your new data, the chart will redraw with the data you’ve entered and everything should work.
Click the blue “Get Forecast” button and Excel will start to use Solver to find the most likely next points for your data. You will need to be patient at this point. It can take around 20-30s for Solver to settle on a most probable solution and you will see numbers changing at the bottom left of your screen as different values are tried out.
After this time, you will be taken to the Forecast and you will see a new chart with the next 12 predicted values showing in red. The next 12 predicted months and forecast values are shown to the left of the chart.
Your original data also exists in this sheet but the rows have been hidden, leaving only your forecast visible. If you’d like to forecast call volumes even further in to the future then you could take these values and transfer them back in to the data worksheet at the end of your current data, then run the forecast again to get another 12 months. In this way you could try to forecast a few years.
The forecasting spreadsheet just works out the monthly predicted call volumes. If you want to use it workforce management, for scheduling your call centre, you will need to break down the outputs into a daily and then hourly forecast. You can then plug the numbers into an Erlang calculator to get the numbers of Agents or FTE required.
Terms and Conditions
Use of the Forecasting Spreadsheet is subject to our standard terms and conditions.
Seems to come up with a Compile Error, Tried leaving it with the dummy data and also tried keying my own data
Adam
19 Mar at 13:27
Hi Adam
Please could you email me a screenshot of the error and a copy of the spreadsheet. The details are on the contact us page.
Thanks
Jonty
jontypearce
19 Mar at 15:44
I have sent the details
Many Thanks
Adam
Adam
20 Mar at 08:52
How come it outputs a different forecast everytime i run it with the same historical data?.
Eduardo Rivera
6 Apr at 20:46
Can the spreadsheet be configured for weekly forecasts? If so, how would the spreadsheet be edited?
James Wilkshere
15 Aug at 08:19
Hi,I can not did the forecast,the respon was compact file error cant find project or library
Steven
27 Aug at 06:28
unable to get forecast due to error on macro, dont know how to fix it
Anusorn
29 Sep at 10:31
Try re-downloading the spreadsheet. If you still have problems send us in the spreadsheet and we will see if we can fix it.
Jonty Pearce
2 Oct at 13:07
I am getting the same error (Compile error: Can’t find project or library) I tried running it with the sample data, it didn’t work. I also tried my data and it didn’t work either. Maybe the download file needs to be replaced.
Max
15 Oct at 20:31
It appears that this version of the template requires excel 2010 as in the 2007 version evolutionary options are not available.
Mike
6 Nov at 14:20
When I click the calculate button, I get an pop up window that states
Show Trial Solution – The maximum number of subproblems was reached; Continue anyway?
My choices are continue, stop or save scenario? What is causing this pop up alert?
Angela
2 Feb at 18:30
Hi Angela. This tool uses a feature in Excel called Solver, which will run through many options for the future values until an optimal solution is found that fits well for a number of the original values. Sometimes if the original data is not regular or well defined then it can take longer to reach a solution as there can still be large differences in the new values that Solver has found – and there are constraints on how many gos it has so you’re not sat waiting forever! When you get that message you can either choose to continue and solver will carry on trying to find a good best fit, or you can show the trial solution and see if you’re fairly happy with what it has come up with. You could then run it again and see how different the forecast is, which will give you an idea of how much it’s struggling to make sense of your original numbers.
Joanne
3 Feb at 15:13
Hi. Good day, it seems there is a Macro error. Is there a way we can make it work?
Jeff
31 May at 15:44
received a compile error but enabling the solver in excel add ins solves the issue
Adam
2 Jun at 08:10
anyone getting the solver issue when running? I have solver installed but errors on MAXTIMENOIMP, Solutions anyone?
Mike
21 Jul at 09:33
encountered error “compile error”
on the VB codes, “solveroptions…” is highlighted
thats under Sub Runsolver ()
william
12 Aug at 21:22
Hello there,
I was VERY interested in using your forecasting template … alas, we have Excel 2010 installed, and the Solver seems to be incompatible with your macro.
For example, the MaxTimeNoImp as well as MaxSubproblems are not recognized and give a compile error with “Named agument not found” explanation.
Would you have an older version of your Forecast Template cmpatible with Excel 2007 Solver?
Thanks in advance,
Michel
5 Sep at 14:57
sorry for the typo in my previous comment.
We have Excel 2007 installed not 2010 …
Michel
5 Sep at 14:58
Re Mike’s comment (Jul 21st), seems you have the same issue than I have encountered.
Older version of Solver …
Michel
5 Sep at 15:01
Hi, The template is really good , working fine, just have a question, what is the reason for keep initial value for level, trend and seasonal as 40%,
Shabbir
20 Sep at 15:19
Can this week on weekly values
Andrew Walker
24 Oct at 16:42
Good one , but need to know how to perform this to get the required FTE
“If you want to use it for scheduling you will need to break down the outputs into a daily and then hourly forecast. You can then plug the numbers into an Erlang calculator to get the numbers of Agents or FTE required”.
Allan
23 Jan at 18:46
Hi,I cannot did the forecast, the respond was compact file error cant find project or library
allan
24 Jan at 06:03
Hello,
Does anyone have the version that works with Excel 2010 or higher ?
Thank you
Danny
29 Jan at 21:08
It should work with the latest version of Excel. You need to have the add-in packs in Excel enabled.
Jonty Pearce
2 Feb at 14:21
Thanks Jonty,
another question :
How come it outputs a different forecast every time i run it with the same historical data ?I ran it 5 times and I got ( although very close) different data.
Danny
13 Feb at 21:32
This is because it is a simulator. It generates a random arrival of calls, emails and web chats – just as happens in real life. Sometimes the calls will be bunched together and sometimes they will be spaced out. If you run it a few times you will get a better idea of the average and peak number of staff needed.
Jonty Pearce
14 Feb at 15:08
Hi,
I am interested in using this, but want to make it a weekly version. Is there a weekly version available? If, not what needs to be changed to make it so?
Also, In the Calcs tab I did not see a note in the video if any of this data needs to change? Does it? If so what needs to be changed? Where does the Factors data come from?
Nick
22 Feb at 21:30
Hi Nick
It’s not very easy to convert it to a weekly chart as all of the formulas need to be reset. We are working on an online version that should be able to take weekly inputs.
Jonty Pearce
23 Feb at 11:16
Thanks Jonty.
Do you have an answer to my question for the Cals tab?
Nick
9 Mar at 16:22
How does one break down the outputs into daily and hourly intervals?
Mark
15 Apr at 12:25
Hi
I am getting the same compile error. What is the work around for it or is there any other similar excel sheets available.
Thanks
Andy
21 Apr at 01:58
How to handle compile error. I am using Excel 2010
Simone
9 May at 11:56
Hi,
Thanks for the contribution, I Tried running it with the dummy data and I’m having an error: compile error Can’t find project or library. I’m using excel 365, do you know what would be my best option to have it running properly? thanks in advance
Eduardo
18 May at 20:43
Hi I am receiving the below error when trying to run the forcaster. Complie error cant find project or libary. i have macros enabled and solver ticked in the add ons and advice please
ryan
19 May at 12:19
Is it necessary to change the data on the Calcs? I noticed that if I change the information on the data tab, that it doesn’t change it on the Calcs tab.
Phil
5 Jul at 23:29
So is there any response about the macro error?
David
20 Sep at 23:14
Hi just had the compile error that people have been mentioning. I resolved it on excel 2016 by going into options and enabling the “analysis tool pack” and “solver” add ins and the “analysis tool pack – vba” and “solver” add ins.
Works fine for me now. Hope that helps people with the macro/ compile error.
Seems to come up with a Compile Error, Tried leaving it with the dummy data and also tried keying my own data
Hi Adam
Please could you email me a screenshot of the error and a copy of the spreadsheet. The details are on the contact us page.
Thanks
Jonty
I have sent the details
Many Thanks
Adam
How come it outputs a different forecast everytime i run it with the same historical data?.
Can the spreadsheet be configured for weekly forecasts? If so, how would the spreadsheet be edited?
Hi,I can not did the forecast,the respon was compact file error cant find project or library
unable to get forecast due to error on macro, dont know how to fix it
Try re-downloading the spreadsheet. If you still have problems send us in the spreadsheet and we will see if we can fix it.
I am getting the same error (Compile error: Can’t find project or library) I tried running it with the sample data, it didn’t work. I also tried my data and it didn’t work either. Maybe the download file needs to be replaced.
It appears that this version of the template requires excel 2010 as in the 2007 version evolutionary options are not available.
When I click the calculate button, I get an pop up window that states
Show Trial Solution – The maximum number of subproblems was reached; Continue anyway?
My choices are continue, stop or save scenario? What is causing this pop up alert?
Hi Angela. This tool uses a feature in Excel called Solver, which will run through many options for the future values until an optimal solution is found that fits well for a number of the original values. Sometimes if the original data is not regular or well defined then it can take longer to reach a solution as there can still be large differences in the new values that Solver has found – and there are constraints on how many gos it has so you’re not sat waiting forever! When you get that message you can either choose to continue and solver will carry on trying to find a good best fit, or you can show the trial solution and see if you’re fairly happy with what it has come up with. You could then run it again and see how different the forecast is, which will give you an idea of how much it’s struggling to make sense of your original numbers.
Hi. Good day, it seems there is a Macro error. Is there a way we can make it work?
received a compile error but enabling the solver in excel add ins solves the issue
anyone getting the solver issue when running? I have solver installed but errors on MAXTIMENOIMP, Solutions anyone?
encountered error “compile error”
on the VB codes, “solveroptions…” is highlighted
thats under Sub Runsolver ()
Hello there,
I was VERY interested in using your forecasting template … alas, we have Excel 2010 installed, and the Solver seems to be incompatible with your macro.
For example, the MaxTimeNoImp as well as MaxSubproblems are not recognized and give a compile error with “Named agument not found” explanation.
Would you have an older version of your Forecast Template cmpatible with Excel 2007 Solver?
Thanks in advance,
sorry for the typo in my previous comment.
We have Excel 2007 installed not 2010 …
Re Mike’s comment (Jul 21st), seems you have the same issue than I have encountered.
Older version of Solver …
Hi, The template is really good , working fine, just have a question, what is the reason for keep initial value for level, trend and seasonal as 40%,
Can this week on weekly values
Good one , but need to know how to perform this to get the required FTE
“If you want to use it for scheduling you will need to break down the outputs into a daily and then hourly forecast. You can then plug the numbers into an Erlang calculator to get the numbers of Agents or FTE required”.
Hi,I cannot did the forecast, the respond was compact file error cant find project or library
Hello,
Does anyone have the version that works with Excel 2010 or higher ?
Thank you
It should work with the latest version of Excel. You need to have the add-in packs in Excel enabled.
Thanks Jonty,
another question :
How come it outputs a different forecast every time i run it with the same historical data ?I ran it 5 times and I got ( although very close) different data.
This is because it is a simulator. It generates a random arrival of calls, emails and web chats – just as happens in real life. Sometimes the calls will be bunched together and sometimes they will be spaced out. If you run it a few times you will get a better idea of the average and peak number of staff needed.
Hi,
I am interested in using this, but want to make it a weekly version. Is there a weekly version available? If, not what needs to be changed to make it so?
Also, In the Calcs tab I did not see a note in the video if any of this data needs to change? Does it? If so what needs to be changed? Where does the Factors data come from?
Hi Nick
It’s not very easy to convert it to a weekly chart as all of the formulas need to be reset. We are working on an online version that should be able to take weekly inputs.
Thanks Jonty.
Do you have an answer to my question for the Cals tab?
How does one break down the outputs into daily and hourly intervals?
Hi
I am getting the same compile error. What is the work around for it or is there any other similar excel sheets available.
Thanks
How to handle compile error. I am using Excel 2010
Hi,
Thanks for the contribution, I Tried running it with the dummy data and I’m having an error: compile error Can’t find project or library. I’m using excel 365, do you know what would be my best option to have it running properly? thanks in advance
Hi I am receiving the below error when trying to run the forcaster. Complie error cant find project or libary. i have macros enabled and solver ticked in the add ons and advice please
Is it necessary to change the data on the Calcs? I noticed that if I change the information on the data tab, that it doesn’t change it on the Calcs tab.
So is there any response about the macro error?
Hi just had the compile error that people have been mentioning. I resolved it on excel 2016 by going into options and enabling the “analysis tool pack” and “solver” add ins and the “analysis tool pack – vba” and “solver” add ins.
Works fine for me now. Hope that helps people with the macro/ compile error.