Creating a rolling forecast based off of previous 4 "Tues"days in Excel 2010

[MichaelWMcDonald]

Hi All,

First post here, so pardon any errors I may make.

I work on the Customer Experience side of a big call center, and I am hoping to create a rolling forecast that will allow me to predict the amount of calls that we may receive on any given day, week, and month. Specifically, I would like to utilize the previous 5 days (i.e. Volume of calls for next Monday is based off of the volume of calls taken on the previous 5 Mondays - averaged). I am hoping to use this same formula for the remaining days of the week.

Here is a small example of what I am working with:

[TABLE="class: outer_border, width: 100"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Day of Week[/TD]
[TD]Call Volume[/TD]
[TD]Forecasted
Call Volume[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/2/2014[/TD]
[TD]15520[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/3/2014[/TD]
[TD]15483[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/4/2014[/TD]
[TD]32542[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2/5/2014[/TD]
[TD]19586[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2/6/2014[/TD]
[TD]16524[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2/7/2014[/TD]
[TD]14528[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2/8/2014[/TD]
[TD]15478[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="class: outer_border, width: 100"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Day of Week[/TD]
[TD]Call Volume[/TD]
[TD]Forecasted Call Volume[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]3/2/2014[/TD]
[TD]15489[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]3/3/2014[/TD]
[TD]12658[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]3/4/2014[/TD]
[TD]32657[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]3/5/2014[/TD]
[TD]14585[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]3/6/2014[/TD]
[TD]15965[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]3/7/2014[/TD]
[TD]14789[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]3/8/2014[/TD]
[TD]16987[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

For purpose of size, I skipped weeks 2-4, hence the cells jumping from 8-30.

Can anyone help me forecast what the 6th Monday would look like, based on the previous 5 Mondays? What would this look like?

I apologize if this is not very clear, please let me know if you have anyquestions.

Thanks in advance!

 

[MichaelWMcDonald]

Asnwered my own qeustion -- sorry!

Sometimes it just takes the opportunity to step back and look at the big picture.

To get next Monday's forecasted volume, based on the average of the past 4 Mondays: =(A30+A23+A16+A9)/3 *face in palm*

 

[chrism216]

Although the average might be a good first try, you might want to do a regression to get more accurate results.
Using named ranges you can set the regression range to always be the last X days in your data. Then, with the LINEST function you can calculate the regression coefficients to predict the next day. There are a ton of videos on youtube explaining how LINEST works. You can even use different regression types.

It's all a little fancy, but if you have the time, its quite useful.
Cheers

 

[MichaelWMcDonald]

Chris,

Thank you for your reply and assistance! I think you're right, and my goal is to be as accurate as possible. I do not have a strong statistical background, so it would be best to avoid "airing my dirty laundry" by setting up a mediocre table. I am going to check out some of these videos tonight.

Thanks again!