Forecasting volumes before and after specific holiday dates / events

Topic Views - 1176


I have been tasked with producing an forecast of daily volumes for 2 weeks before and 2 weeks after holiday dates / specific event dates (e.g. Reviewing the 2 weeks before and after Easter Monday (14/03/2016 this year)).

We have no WFM system, it needs to be Excel driven. I have 7 months of 2014 daily volumes (Jan-July), and then May onward data for 2015/2016. So I realise I am missing a good chunk of required data.

What would be the best way to approach this? A majority of what I've read online is about forecasting month, day, interval volumes seasonally using time series analysis.

I've worked something up, which to simplify, takes the date of the holiday minus e.g. 14 (for the 14th day prior to the event) from historical data and works out the difference between that days volume and a regular equivalent days volume in the same month to find a percentage uplift/drop that can be applied to my future normal day projection. This is repeated for all days within the two weeks before and after the holiday date.

Due to the fact that holidays fall on different days and dates, I'm not sure at all if this is at all accurate, or if there is a better way to do this.

I'd be grateful for any advice or suggestions on this. Can you think of a better way to do this, major floors in the logic, anything that can assist me towards producing what my people want from me.

Many thanks in advance guys.


Call Centre Helper

This is a combination of both manual and automatic forecasting.

To do the monthly forecast this spreadsheet may help you

but you don't fully have enough data to make it an accurate forecast.

In terms of holidays what you have to do is work out the pattern of the special days, take the holiday element out and then add it back in when you have done your forecast.

This seems to be what you describe so you are probably on the right lines.

For Easter you do have the added advantage that you can also compare one year against another as the date of Easter changes.

If you like you could email me the spreadsheet (send it in through the newsdesk) and I could see if I could make it more accurate.

Hi Jonty,

Many thanks for the advice.

I have downloaded the tool, and although we do not have anywhere near the 24 months, I tried it anyway as something is better than nothing.

I have Macro's enabled etc but I just get an error saying "Compile error: Can't find project or library". When I let it run the debugger, it highlights the Sub RunSolver() line.

Any thoughts on what could be happening?


Call Centre Helper


The "Can't find project or library" error occurs when Solver isn't installed as an add-in in Excel.

For more instructions and to install it, please have a look at this post

Installing Solver should sort out this issue for you and you'll be able to use the tool.

Hi josparkes,

Many thanks. I managed to get a bit further following that guide.

Now though, when I enter my data into the first two columns and press the Get Forecast button, it says "Setting up problem" in the bottom left corner and staying like that churning away continuously.

I understand it should take a few minutes, but when I do it with 12 months data or 2 months it still does the same.

Any thoughts?


Call Centre Helper

Hi JCTalk

Do you want to send in the spreadsheet to us and we can take a look to see what the problem is.

Send it to and please mark it for my attention.

Want to add a comment?

Not found what you were looking for?

1. Try searching through our site.
2. Still not got an answer?

Why not ask the Call Centre Helper Community? Click here to ask your question