Anyone survived a statistics course at uni without running a linear regression in SPSS? Come forward, please
Joke aside, linear regression is such a basic and fundamental forecasting tool that it is being taught across a range of bachelor and master programmes.
Yet, all too often, very little is explained how you could use it without SPSS, whereas the harsh reality still is that quite a high number of non-academic organizations will not have it as a corporate tool
- How do I make a projection for my next year sales then?
- How do I know which one of the 5 potential changes I could make will yield the highest change in sales revenue?
Don’t worry, there are a couple of ways to recreate the model in Excel:
1) Use the Data Analysis Add-In
NB! Alike SPSS, Excel expects you to have ‘coded’ your non-numeric predictor variables to (digit) numbers. Failing to do so will result in an error message.
Also, do make sure you tick the ‘Labels’ check box if you have included the data headings in your range selection
That done, the add-in will give you a summary of the model coefficients, pretty much, like the standard SPSS output would.
The ones you should watch out for are:
Let’s have a go!
These are the results of a linear regression run on monthly sales for the past year:
2) Use the forecasting functions based on the linear regression model
If you don’t like installing or running the Data Analysis add-in, you can always fall back on the following functions to come up with a linear regression model:
- forecast () in Excel 2013
- linear() in Excel 2016
- trend () in Excel 2013, 2016
The major drawback of those is that you won’t be able to see the R-coefficients that indicate the predictive capacity of the model unless you calculate them as well
3) Should you be in a exploratory mode, you can also re-create the model itself.
Then, apart from the R-coefficients, you will also need to calculate the intercept and slope.
So, which approach should I take?
They will all give the same results, so it’s pretty much a matter of personal preference and the data you need to forecast.
Personally, I tend to use the forecasting functions if, based on last year data, I need to make a projection for next year sales on a bunch of products
If the forecasting to be done is a bit more exploratory in nature, such as ‘which one of these will bring about the highest ROI’, then I’d rather use the add-in in combination with non-aggregated data