MS Excel Awesome (Sales) FORECAST Function

The FORECAST function is a rare function, none - even the most seasoned Excel users have never asked me how to use this rather mysterious function. I have always included a topic on forecasting in my classes, showing professionals how to insert a graph, then a linear trend line and finally how to show the R coefficient and the Linear equation which is commonly used to build Forecasts. 

Though FORECAST is a powerful tool, it cannot independently be used to generate forecasts. A good forecast system involves dealing with seasonality, noise, trends and events. So what does this mean? 

Linear regression is a form of regression analysis and can be used to calculate a mathematical relationship between two (or more) sets of data. In forecasting, you would use this if you thought one set of data could be used to predict another set of data. For example, if you sold building supplies, you may find that changes in interest rates can be used to predict sales of your products.

This is a classic example of using regression to calculate a relationship between an external variable (interest rates) and an internal variable (your sales). However, as we will see later, you can also use regression to calculate a relationship within the same set of data.

The FORECAST(x, known_y's,known_x's) function returns the predicted value of the dependent variable (represented in the data by known_y's) for the specific value, x, of the independent variable (represented in the data by known_x's) by using a best fit (least squares) linear regression to predict y values from x values.

If you assume that data pairs are plotted in a scatter plot with x values that are measured on the horizontal axis and with y values that are measured on the vertical axis, FORECAST returns the height of the best fit regression line at the specific value x on the horizontal axis. FORECAST is the value of y that would be predicted based on both the value of x and the regression line (characterized by its slope and intercept that can be found by using Excel's SLOPE and INTERCEPT functions).

In the figure below, we have a spreadsheet that includes the average interest rate over the previous 6 years and unit sales during that same 6-year period. We also show a predicted interest rate for the 7th year. We can see in the example that our unit sales go up as interest rates come down, and go down as interest rates go up. Just looking at the example, we can probably guess that our sales for year 5 would be somewhere between 4,000 and 5,000 based on the observed relationship between interest rates and sales during the previous periods. We can use the Forecast Function to more precisely quantify this relationship and apply it to the 7th year.

 

In the figure below, you can see the Forecast Function being applied. In this case, the formula in cell H3 is =FORECAST(H2,B3:G3,B2:G2) . What we have within the parenthesis is known as an “argument”. An argument is really just a means of passing parameters on to the function being used (in this case, the Forecast function ). Each parameter is separated by a comma. In order for the Forecast Function to work, it needs to know the value we are using to predict our output (our year 6 sales). In our case, the parameter (our Year 6 interest rate) is in cell H2, so the first element of our argument is H2. Next, it needs to know where it can find the existing values it will use to determine the relationship to apply to H2. First we need to enter the cells that represent the values of our dependent variable. In our case, this would be our units sold during the previous 6 years, therefore we enter B3:H3. Then we need to enter the cells that represent the values of our predictor variable. In our case, this would be the interest rates during the previous 6 years, therefore we enter B2:H2) .

The Forecast Function can now compare the units sold during years 1 through 6 to the interest rates in those same years, and then apply that relationship to our predicted Year 6 interest rate to get our forecasted sales for Year 7 of 4,523 units.