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:

	A	B	C
1	Day of Week	Call Volume	Forecasted Call Volume
2	2/2/2014	15520
3	2/3/2014	15483
4	2/4/2014	32542
5	2/5/2014	19586
6	2/6/2014	16524
7	2/7/2014	14528
8	2/8/2014	15478

<TBODY>
</TBODY>

	A	B	C
	Day of Week	Call Volume	Forecasted Call Volume
30	3/2/2014	15489
31	3/3/2014	12658
32	3/4/2014	32657
33	3/5/2014	14585
34	3/6/2014	15965
35	3/7/2014	14789
36	3/8/2014	16987

<TBODY>
</TBODY>

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!