In this video we will take a look at using the LINEST function in Excel to calculate future sales utilizing regression analysis with the least squares method. Sound overwhelming? It’s not – take a look!
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here we have two sets of sales data for 24 periods from January 2013 to December 2014:
We are going to use the LINEST function to predict the sales for 2015 using regression analysis and the least squares method. Now, I’ll admit that I am not well versed in any way in these types of analysis, but found this function and felt it worthwhile to share. Here is a bit about this function and the formula we will use in our calculation:
Notice that we have to have a column indicating the period number of our existing and future sales in columns “A” and “L”. Also note that when using the LINEST function, in order to calculate the Slope and Y-Intercept values we need those cells to be side by side:
So, I can now highlight the two cells E2 & F2 where I want to calculate the Slope and Y-Intercept, then type in my LINEST function. In this scenario, all I need to include is the “known_y’s”. The other arguments are optional. Then, instead of hitting Enter, since this will calculate an array of values, I need to use Ctrl + Shift + Enter, and the results are the Slope and Y-Intercept:
Here is what this process will do: Using the Slope and Y-Intercept, Excel will take the 24 months of sales data we have and create a straight-line trendline then extend it for the next 12 months to continue that trendline for our forecast.
So, in cell D2, we’ll enter this formula:
Which is the Slope times the period plus the initial Y-Intercept. When we copy it down, we’ll get these results:
If we total up years 2013 & 2014 actual values, we get 35,180. If we do the same for the Forecast, we get the same results. What Excel has done is taken the actual values and manipulated them slightly to create a straight line trendline that it can then extend out to our forecast periods. So, now if we graph that data, we get the following:
Notice how the Actual Values and Forecast Values have a similar trend, the Forecast has just been smoothed to create that straight trendline.
We can now do the same for the other set of values. Since the sales have gone slightly down, the slope calculated is a negative one, resulting in a forecast of a lesser amount for 2015:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!