A to Z of Excel Functions: The FORECAST.LINEAR Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FORECAST.LINEAR function.

The FORECAST.LINEAR function

A common approach to forecasting is known as simple linear regression. This is a technique where historical data is plotted on a chart and a “best straight line” is drawn through the data points to determine a linear relationship, viz.

The chart constructed usually is created as a scatter plot, with the independent variable (x) on the horizontal axis and the dependent variable (y) on the vertical axis.  To be clear:

• the independent variable (x) is one you either have control of or else may select
• the dependent variable (y) is the result of that control or choice, sometimes referred to as y = f(x).

For example, if you are trying to forecast sales, x would be the period (e.g. months) and y would be the forecast / actual amount of sales (e.g. dollar amount).

Not all relationships are linear, but a surprising number may be determined by plotting log x against log y (it does not matter what base is used for the logarithms as long as the same one is chosen).  Using this technique to forecast the relationship is known as simple linear regression, which is formally described as a linear approach to modelling the relationship between a scalar response (or dependent variable) and one explanatory variable (or independent variable).  

The technique most commonly used to find the “best straight line” is the ordinary least squares (OLS) method, where geometrically, the sum of the squared distances between the line and the observed data parallel to the y-axis is minimised.

The FORECAST.LINEAR function calculates, or predicts, a future value by using existing values.  The predicted value is a y-value for a given x-value.  The known values are existing x-values and y-values, and the new value is predicted by using linear regression.

This function is new to Excel 2016, and replaces the legacy FORECAST function as part of the new set of forecasting functions.  FORECAST is still available for backward compatibility, but consider using the new function from Excel 2016 / Office 365 onwards.

The FORECAST.LINEAR function employs the following syntax to operate:

The FORECAST.LINEAR function has the following arguments:

• x: this is required and represents the data point for which you wish to predict a value
• known_y’s: this is required.  This is the dependent range of data
• known_x’s: this is also required.  This denotes the independent range of data.

It should be further noted that:

• if x is non-numeric, FORECAST.LINEAR returns the #VALUE! error value
• if known_y's and known_x's are empty or contain a different number of data points, FORECAST.LINEAR returns the #N/A error value
• if the variance of known_x's equals zero, then FORECAST.LINEAR returns the #DIV/0! error value
• the equation for FORECAST.LINEAR is a + bx, where:

and:

and where x bar and y bar are the sample means AVERAGE(known_x's) and AVERAGE(known y's).

Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.