MS Excel VBA has uses in various facets of day to day activities of businesses. But it is a little known factthat this tool may also be used to carry out advanced functions like predicting future sales. This blogpost will try to represent in as an illustrative way as possible how it manages to do the same with thelimitations imposed by the format of this text based post.Suppose we have sales data of two sets for 24 periods within the time range of January 2013 to the December of 2014.
What we are trying to do is to utilize the function LINEST in order to predict sales for the year 2015 by making use of method of least squares as well as regression analysis. The elementary knowledge of this function suggests that it is worth sharing. In the lines that follow we will try to examine the rudiments of this function and the formula that we will put into use in the calculations we make.
The use of the LINEST function lies in regression analysis in order to make calculations about a line that uses the least squares method and return a straight line that is best fitted to the data that you input and outputs an array that serves as a description of the particular line.
The equation that represents the line is: y = mx + b
Here m stands for the slope, x represents the period of time dealt with the data and b is the y intercept.It is to be noted that there is necessity of having a column that indicates the period number of the existing as well as future sales. It should also be noted that while you use the LINEST function so that you may calculate the values of the Y- Intercept along with that of the slope those cells need to reside side by side.
So, what one has to do is to highlight the two cells that you want to use in order to make calculations of the Y-Intercept and the Slope before typing in the LINEST function. In such cases all that is needed is to include the “y”s that are already known. It is up to you whether you want to provide other arguments as they are optional. Then all you need to do is press Ctrl+Shift+Enter and you get the Y-Intercept and the Slope.
The magic that takes place in the background is basically this, by making use of the Slope and the Y-Intercept Excel does its crystal ball gazing and takes the last sales data of the precious 24 months and creates a straight-line that forecasts the trend that is likely to persist in the coming 12 months.
What Excel actually does is that it takes the actual values and manipulates them slightly to that is able to come up with a straight-line that serves as the trend line that extends out to the periods of forecast and thus the actual values and the values of the forecast have trends that are similar.This is the basics of crystal ball gazing through the use of MS Excel.
Want to shine bright in MS Excel? Visit DexLab Analytics – their Advanced Excel course is truly remarkable. Enrol for Advanced Excel training today.
Interested in a career in Data Analyst?
To learn more about Machine Learning Using Python and Spark – click here.
To learn more about Data Analyst with Advanced excel course – click here.
To learn more about Data Analyst with SAS Course – click here.
To learn more about Data Analyst with R Course – click here.
To learn more about Big Data Course – click here.