by Svetlana Cheusheva, updated on
The tutorial explains how to use Excel FORECAST and other related functions with formula examples.
In Microsoft Excel, there are several functions that can help you create linear and exponential smoothing forecasts based on historical data such as sales, budgets, cash flows, stock prices, and the like.
The main focus of this tutorial will be on the two main forecasting functions, but we will touch briefly on other functions as well to help you understand their purpose and basic uses.
In the recent versions of Excel, there exist six different forecasting functions.
The two functions do linear forecasts:
The four ETS functions are purposed for exponential smoothing forecasts. These functions are only available in Excel for Office 365, Excel 2019, and Excel 2016.
The FORECAST function in Excel is used to predict a future value by using linear regression. In other words, FORECAST projects a future value along a line of best fit based on historical data.
The syntax of the FORECAST function is as follows:
Where:
The FORECAST function works in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.
Note. In Excel 2016 and 2019, this function has been replaced with FORECAST.LINEAR, but is still available for backward compatibility.
The FORECAST.LINEAR function is the modern counterpart of the FORECAST function. It has the same purpose and syntax:
This function is available in Excel for Office 365, Excel 2019, and Excel 2016.
Both functions calculate a future y-value by using the linear regression equation:
y = a + bx
Where the a constant (intercept) is:
And the b coefficient (slope of the line) is:
The values of x̄ and ȳ are the sample means (averages) of the known x-values and y-values.
If your FORECAST formula returns an error, this is most likely because of the following reasons:
As already mentioned, the Excel FORECAST and FORECAST.LINEAR functions are purposed for linear trend forecasting. They work best for linear datasets and in situations when you want to forecast a general trend ignoring insignificant data fluctuations.
As an example, we will try to predict our web-site traffic for the next 7 days based on the data for the previous 3 weeks.
With the known y-values (no. of visitors) in B2:B22 and the known x-values (dates) in A2:A22, the forecast formula goes as follows.
Excel 2019 - Excel 2000:
=FORECAST(A23, $B$2:$B$22, $A$2:$A$22)
Excel 2016 and Excel 2019:
=FORECAST.LINEAR(A23, $B$2:$B$22, $A$2:$A$22)
Where A23 is a new x-value for which you wish to predict a future y-value.
Depending on your Excel version, insert one of the above formulas in any empty cell in row 23, copy it down to as many cells as needed and you will get this result:
Please pay attention that we lock the ranges with absolute cell references (like $A$2:$A$2) to prevent them from changing when copying the formula to other cells.
Plotted on a graph, our linear forecast looks as follows:
The detailed steps to make such a graph are described in Linear regression forecasting chart.
If you'd like to predict future values based on the recurring pattern observed in your historical data, then use FORECAST.ETS instead of the Excel FORECAST function. And the next section of our tutorial shows how to do this.
The FORECAST.ETS function is used to do exponential smoothing forecasts based on a series of existing values.
More precisely, it predicts a future value based on the AAA version of the Exponential Triple Smoothing (ETS) algorithm, hence the function's name. This algorithm smoothes out insignificant deviations in data trends by detecting seasonality patterns and confidence intervals. "AAA" stands for additive error, additive trend and additive seasonality.
The FORECAST.ETS function is available in Excel for Office 365, Excel 2019, and Excel 2016.
The syntax of the Excel FORECAST.ETS is as follows:
Where:
The maximum allowed seasonality is 8,760, which is the number of hours in a year. A higher seasonality number will result in the #NUM! error.
If your formula produces an error, this is likely to be one of the following:
To see how the future values calculated with exponential smoothing are different from a linear regression forecast, let's make a FORECAST.ETS formula for the same data set that we used in the previous example:
Where:
By omitting the last three arguments (seasonality, data completion or aggregation) we rely on Excel defaults. And Excel forecasts the trend perfectly:
The FORECAST.ETS.CONFINT function is used to calculate the confidence interval for a forecasted value.
The confidence interval is kind of a measure of the prediction accuracy. The smaller the interval, the more confidence in the prediction for a specific data point.
The FORECAST.ETS.CONFINT is available in Excel for Office 365, Excel 2019, and Excel 2016.
The function has the following arguments:
As you see, the syntax of FORECAST.ETS.CONFINT is very similar to that of the FORECAST.ETS function, except this additional argument:
Confidence_level (optional) - a number between 0 and 1 that specifies a level of confidence for the calculated interval. Typically, it is supplied as a decimal number, though percentages are also accepted. For instance, to set a 90% confidence level, you enter either 0.9 or 90%.
To see how it works in practice, let's calculate the confidence interval for our sample data set:
=FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22)
Where:
The last 4 arguments are omitted, telling Excel to use the default options:
To grasp what the returned values actually mean, please take a look at the screenshot below (some rows with historical data are hidden for the sake of space).
The formula in D23 gives the result 6441.22 (rounded to 2 decimal points). What it means is that 95% of the time, the prediction for 11-Mar is expected to fall within 6441.22 of the forecasted value 61,075 (C3). That is 61,075 ± 6441.22.
To find out the range within which the forecasted values are likely to fall, you can calculate the confidence interval bounds for each data point.
To get the lower bound, subtract the confidence interval from the forecasted value:
=C23-D23
To get the upper bound, add the confidence interval to the forecasted value:
=C23+D23
Where C23 is the predicted value returned by FORECAST.ETS and D23 is the confidence interval returned by FORECAST.ETS.CONFINT.
Copy the above formulas down, plot the results on a chart, and you will have a clear visual representation of the predicted values and the confidence interval:
Tip. To have such a graph created for you automatically, leverage the Excel Forecast Sheet feature.
The FORECAST.ETS.SEASONALITY function is used to calculate the length of a recurring pattern in the specified timeline. It is closely tied with FORECAST.ETS because both functions use the same algorithm to detect seasonality.
This function is available in Excel for Office 365, Excel 2019, and Excel 2016.
The syntax of FORECAST.ETS.SEASONALITY is as follows:
For our data set, the formula takes the following shape:
=FORECAST.ETS.SEASONALITY(B2:B22, A2:A22)
And returns the seasonality 7, which perfectly agrees with the weekly pattern of our historical data:
The FORECAST.ETS.STAT function in returns a specified statistical value relating to a time series exponential smoothing forecasting.
Like other ETS functions, it is available in Excel for Office 365, Excel 2019, and Excel 2016.
The function has the following syntax:
The statistic_type argument indicates which statistical value to return:
For example, to return the Alpha parameter for our sample data set, we use this formula:
=FORECAST.ETS.STAT(B2:B22, A2:A22, 1)
The screenshot below shows the formulas for other statistical values:
That's how you do time series forecasting in Excel. To investigate all the formulas discussed in this tutorial, you are welcome to download our Excel Forecast Sample Workbook. I thank you for reading and hope to see you on our blog next week!
Table of contents