Basic stats analysis in Excel: Forecasting

[I.M.O.G.]

Say I have monthly datapoints for the past 3 years for site activity. I want to forecast 2012, taking into consideration the trend exhibited through that timeline, and the historical monthly variance values.

How best would you go about forecasting in Excel?

 

[ratbuddy]

Rather than type it all out, I'll link ya to http://office.microsoft.com/en-us/e...d-forecast-sales-with-charts-HA001087785.aspx

 

[I.M.O.G.]

Thanks, good read and a good baseline, but after reading that I think I'm looking for something a step or so beyond that.

Due to the nature of my data points, the moving average trendline would provide the best fit (or the best r-squared value ), however it does me no good for forecasting... If I use monthly datapoints with a range value of 3, then my forecasted value for april will be the average from January, February, and March - that is too simple of an estimated forecast. Using it on past data to test suitability, it isn't a good predictor of known datapoints at all, so I wouldn't want to use it for future estimates.

Maybe there isn't something better than that I should use, but I'm guessing there probably is something better than that statistically, that can compete pretty good with manual estimation after making inferences off past trends.

 

[turbohans]

This page is more of what you are looking for. http://office.microsoft.com/en-us/excel-help/perform-a-regression-analysis-HA001111963.aspx

I could dig out my textbooks from that class though if that is not enough.

From what I remember though. '07 & '10 have shortcuts for all that in the "formulas" tab, and you mathematically you just need an average with a nominal, minimal, & maximum...

EDIT:

If you figure that out, you should play with this. http://jasperforge.org/projects/jasperserver

If you want a book to explain this stuff buy this one!!!! ISBN-13: 9780135130032

 

[turbohans]

If you don't mind me asking Matt; what exactly are you trying to forecast?? I could help more if I knew a little more specifically what you need. For example, usage statistics, member statistics, posting statistics, or maybe something else. I see what data you have but get confused when thinking of exactly what you want out of it, and exactly what data you are using.

I'm almost done with my CIS/Micro Aplication BA. Still have the textbooks for my statistics and Excel classes.

 

[I.M.O.G.]

If we had 513 posts last January, 427 posts last February, 483 posts last March, and 601 posts last April, and this December we have 732 posts... And for the year posts grew by an average of .7% monthly, how many posts should we forecast this April?

If we understand what the past trends are telling us, we can take those numbers, and add a % or two to get our goals to aim for. This is obviously just an example, and not exactly how I track how a forum is doing, but it gets the point across for setting goals and the sort of forecasting I'm attempting to get at.

It could also be compared to the stock market. Looking at what a stock has done over the past 12, 24 months, or 36 months - how do you take that data to produce a fairly accurate forecast of what it will do in the next 12 months? (yes stock forecasts often aren't that accurate, but they are a best estimate of predicting the uncertain)

 

[turbohans]

Ok, that helps me think a lot!

There are some variables there that were confusing me at first but that eliminates a couple.
With figures for posts like that there is a 3D aspect of it that should allow interpretation.

EX,

posts(x), New_Users, External_stimulus(z)

External stimulus is vague though, you could correlate that with more than a few things. AdWords, Contests, shares(tweets 'n' stuff)


See what I am getting at? The math might also depend on those variables also. But if you just want a 2D representation of this it really will not show you much good data. As it would just be an average of increase/decrease but not why. (after all we all know we have grown, but why?)


The problem with this that creates the most inaccuracy is the lack of "control" data. However this is exactly how you would think about forecasting stocks, as you used for example. The thing there is they have the same lack of control data, but use other data to base things on.
So for example, you could obtain data to compare from other iNet websites. The trick is figuring out what you want your data to say!! Then worry about the math, and validation of such.