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.
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:
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!
14 responses to "Excel FORECAST and other forecasting functions with formula examples"
I DON'T UNDERSTAND THESE FUNCTIONS.
Good one.
zdorovatsya Svetlana,
thanks so much for your great effort in explaining Excel formulas, it really helps.
my question is which is better absolute or relative reference cell while using various forecasting functions ?? because each reference generates different forecasted figures and I am building a forecasting model for my department budget and I am a little hesitated to choose the accurate reference.
T
Hi Ahmed,
Generally, the choice of the cell reference type is determined by the structure of your data, which is why it's impossible to give any advice without seeing how your data is organized. Hopefully, the following article will help you get clear understanding of how Excel cell references work: Absolute and relative cell references in Excel.
Hi,
I am exploring forcast.linear and not able to get the results in Nov-19 column.
Here's my data set
Col A Col B Col C Col D Col E Col F Col G Col H Col I
R1 Category Description Apr-18 May-18 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19
R2 Food Biscuit 2 2 7 3 2 3 0
R3 Food Tea 2 1 2 0 0 0 0
R4 Food Coffee 5 4 15 7 6 12 0
R5 Food Cup Noodles 10 15 16 10 5 20 0
My formula is "=FORECAST.LINEAR(I2,$C2:$H2,$C1:$H1)"
What went wrong?
April and May year in 2018. and from Jul it's 19. give symmetrically spaced data.
Hi,
What formula we can use to incorporate growth in the forecast formula?
I had a set of daily sales values. I forecasted based on the Office 365 data tab's forecast function icon. I put in a 99% confidence interval. It turned out a lower confidence Bound that had minus values. However, I know for sure in reality, these values cannot be possible. How can I change the setting to obtain values that are only positive and how can I change the setting to exclude the - values on the axis in my scenario?
Due to this turnout, I do not even dare to use this function.
Hi Svetlana,
Can we use FORECAST.ETS.CONFINT to calculate confidence interval for FORECAST or FORECAST.LINEAR function.
Thanks
Use the summary statistics in the data validation tab.
Hi,
I tried and tested these functions on my requirement to identify the future date when a certain value is reached. However, some showed past dates instead of future dates. What am I missing here?
Hello!
I am not sure I fully understand what you mean. Could you please describe it in more detail?
Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Please sir my question is outline the basic steps involved in using Ms Excel forecast function . Thank you sir
Using Excel in Windows 7 Using Forecast function - - worked well on spreadsheet for 3 yrs of data, but today, next date, getting a 1 digit number - - totally out of line with the series (index going to 7.1 next week, from a month-long series of values in the 300's. Cell shows a error spot upper left corner, but have tried all the "fixes" inside the "formula bar" and nothing works.