Estimate Data for Future Months

[PeterPivot]

Hi All,

I apologise if this question has been asked before, I've tried searching for an answer to no avail.

I'm trying to estimate data for a year that has not ended yet. Ok, let's say I have a dataset of unit orders from 2008 to August of current year. I want to build a table and chart that shows the trend, including the most accurate way of representing the current year.

2008: 376
2009: 243
2010: 344
2011: 390
2012: 422
2013: 401
2014 (August): 301

So the question is; how do I accurately show 2014. The way I've done it is:

=((AVERAGE(Previous Years)/12)*4)+301

Explanation: I want to see the average of one month over the course of the years, then add that one month how ever many times it'll take for the rest of the year. In this case 4.

Is that the best way to predict the rest of the months for 2014?

 

[Comfy]

The reasoning behind your formula is sound.

Is that the best way to predict the rest of the months for 2014?

I don't think there is a "best" way when predicting the future.

You could also do it this way:

Excel 2010
	J	K
2	12	376
3	12	243
4	12	344
5	12	390
6	12	422
7	12	401
8	8	301
9
10		421.8889
11		407.0833
Sheet1

Cell Formulas
Range	Formula
K10	=(AVERAGE(K2:K7)/12*4)+K8
K11	=AVERAGE(AVERAGE(K2:K7)/12,K8/J8)*12

Take a monthly average from the previous years.
Take a monthly average Year to Date
Take an average of the two and multiply by 12.

 

[PeterPivot]

Thank you for your help Comfy. I appreciate you taking the time in replying and also with your suggestions on how to tackle this. I've taken your approach on board and have used it in my presentation. Thanks once again