Analyse Trends

JimmyR

Hi,

I want to estimate future turnover. I have got a total figure for each of the last ten years. Is linear regression the best way to do this? Rather than do the actual maths I discovered there is a way of doing quite simply on excel.

However from what you usually have two values from which you predict trends.

maybe theres a better way, has anyone any ideas?

bnt

The simplest way I know to do something like this in Excel goes something like this:

• create a chart with all the data you have (all ten years)
• right-click on the data line and select "Add Trend". Select "Linear", check the options to "Display Equation" and "Display R-Squared".
• The equation it gives you is what you use to extrapolate the trend in to the future. For example, if your X-axis is year in the form 2001 .. 2010, then you insert e.g. 2011 in place of X in the formula to get the estimate for 2011.
• The R-Squared value is a basic "goodness of fit" for the trend: the closer to 1 the better. If it's much less than 1, I wouldn't be confident that the data follows a linear trend, and I'd try selecting a different type of trend.

The procedure might vary between Excel versions, and I don't have Excel installed on this (Linux) computer, so my memory might have some of the details wrong.

JimmyR

Thanks BNT I have actually done that but I'm wondering if this is the correct method I should use.

Lets say theses are my figures:
2000 - 1
2001 - 2
2002 - 3
2003 - 4
2004 - 5

Its obvious that the sales are going up by one every year, you wouldn't have to do an equation to calculate this.

If the figures were alot bigger and the trend could not be worked out as simple as this would linear regression still be the way to go?

Taking the above again I could easily calcualte that the percentage increase is halfing each year but with bigger figures and possible decreases this would not be as clear.

heres my definition of linear regression from memory which may be very wrong. " Linear Regression gives you the trend of events in the future based on events in the past, this generally involves two variables in each period (period being year, month etc)."

I am open to correction on all of the above and thanks for the feedback.

bnt

JimmyR wrote: »

If the figures were alot bigger and the trend could not be worked out as simple as this would linear regression still be the way to go?

That's kinda what I was getting at - I wouldn't assume that linear regression is the best way, so I'd want to see that R-Squared value, which would tell me just how linear the data is. You don't have to guess, you can let the data tell you whether a linear regression is the right way to go. How big or small the numbers are doesn't matter, really, if they follow a trend.

pjmn

On the basis that you are attempting to predict future turnover, (as a general statement) I would have considered basing future turnover on past performance a dangerous strategy given the change in the economic climate - for the majority of businesses future turnover may be totally unrelated to past performance...

pjmn

RoundTower

I think growth of a business, like other investments, is usually better modelled as exponential growth. In other words, if it took you five years to double your turnover, you might estimate that you would double it again in another five years, while the linear model would suggest the same doubling would take ten years.

But really, like the last poster said, this isn't very useful, and if you know anything further about the business other than the turnover you can make a much more informed guess than just assuming it will continue to grow in a similar way.

Sev

There are a lot of models and methods out there for trying to forecast future values in a time series based on previous observations. That's not to say that any of these methods may actually work for your particular data, it could be random noise, or you may not have enough points to infer any particular pattern or behaviour. Take a look at ARMA models and ARIMA models.

http://en.wikipedia.org/wiki/Autoregressive_moving_average
http://en.wikipedia.org/wiki/Autoregressive_integrated_moving_average
http://en.wikipedia.org/wiki/Time_series_analysis

Your definition of linear regression is wrong though, it's just a method of modelling observations of data to a straight line.

Sev

And take a look at this too

http://en.wikipedia.org/wiki/Box%E2%80%93Jenkins