The tutorial explains how to use Excel FORECAST and other related functions with example formulas.
There are several functions in Microsoft Excel 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.
In this tutorial, we will focus mainly on the two main forecasting functions, but we will also briefly cover other functions to help you understand their purpose and basic uses.
Forecast functions in Excel
There are six different forecasting functions in the latest versions of Excel.
Both modes worklinearpredictions:
- FORECAST: predicts future values using linear regression. Legacy function for compatibility with earlier versions of Excel 2013 and earlier.
- LINEAR – identical to the FORECAST function. Part of the new forecasting feature set in Excel 2016 and Excel 2019.
Four ETS functions are designed toexponential smoothingPredictions These features are only available in Excel for Office 365, Excel 2019, and Excel 2016.
- ETS – Predicts future prices based on the exponential smoothing algorithm.
- ETS.CONFINT - calculates the confidence interval.
- ETS.SEASONALITY - Calculates the duration of a seasonal or other recurring pattern.
- ETS.STAT: Returns statistical values for time series forecasting.
Excel forecast function
The FORECAST function in Excel is used to predict the future price usingLinear regression. In other words, FORECAST predicts the future value along the line of best fit based on historical data.
The syntax of the FORECAST function is as follows:
FORECAST(x, known_y, known_x)
Where:
- X(required): A numeric x value for which you want to predict a new y value.
- acquaintances(required): An array of known values and dependents.
- acquaintances_x(required): An array of known independent x values.
FORECAST 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.
UseIn Excel 2016 and 2019, this function has been replaced byLINEAR FORECAST, but is still available for backwards compatibility.
Excel function LINEAR FORECAST
FORECAST.LINEAR is the modern equivalent of FORECAST.LINEAR. It has the same purpose and structure:
FORECAST.LINEAR(x, known_y, known_x)
This feature is available in Excel for Office 365, Excel 2019, and Excel 2016.
How they calculate FORECAST and FORECAST.LINEAR future values
Both functions calculate the future value of y using the linear regression equation:
y = a + bx
WhereONEconstant (intercept) is:
And the coefficient b (slope of the line) is:
The x and ȳ values are sample means (average) of known x and y values.
Excel's FORECAST function does not work:
If the FORECAST formula returns an error, it is most likely due to the following reasons:
- If known_x and known_y values are of different lengths or are empty, #N/A. there is an error.
- If x is not a number, the formula returns #VALUE! error.
- If the variance of known x is zero, #DIV/0! there is an error.
How to use the FORECAST function in Excel - formula example
As already mentioned, the Excel functions FORECAST and FORECAST.LINEAR are used for linear trend forecasting. They work best with linear data sets and in situations where you want to predict a general trend while ignoring small variations in the data.
For example, we will try to predict traffic to our website for the next 7 days based on data from the previous 3 weeks.
With known y values (number of visitors) in B2:B22 and known x values (dates) in A2:A22, the prediction formula looks like this.
Excel 2019 — Excel 2000:
=PROGNOZA(A23; $B$2:$B$22;$A$2:$A$22)
excel 2016 y excel 2019:
= PRONÓSTICO.LINEAL.(A23, $B$2:$B$22, $A$2:$A$22)
Where A23 is the new value of x for which you want to predict the future value of y.
Depending on your version of Excel, enter one of the above formulas in any blank cell in row 23, copy it to as many cells as you want, and you'll get the following result:
Please note that we exclude rangesabsolute cell references(for example, $A$2:$A$2) to prevent them from changing when you copy the formula to other cells.
Plotted on the graph, our linear prediction looks like this:
The detailed steps for creating such a graph are described inLinear regression prediction graph.
If you want to predict future prices based onrepeating patternobserved in historical data, use the FORECAST.ETS function instead of Excel's FORECAST function. And the next section of our tutorial shows you how to do it.
Excel function FORECAST.ETS
The FORECAST.ETS function is used for this purpose.exponential smoothingpredictions based on a range of existing values.
More specifically, it predicts future value based on AAA buildstriple exponential smoothing(ETS), hence the name of the function. This algorithm smooths out small deviations in data trends by detecting patterns of seasonality and confidence intervals. "AAA" stands for additive error, additive trend and additive seasonality.
FORECAST.ETS is available in Excel for Office 365, Excel 2019, and Excel 2016.
The syntax of the FORECAST.ETS Excel file is as follows:
FORECAST.ETS(target_date, values, schedule, [seasonality], [supplement_data], [concentration])
Where:
- target date(required): The data point for which you want to predict a value. It can be represented by a date/time or a number.
- values(required): The range or matrix of historical data for which you want to predict future values.
- Chronology(required) - a series of date/time or independent numerical data with a constant step between them.
- seasonality(optional) - a number representing the duration of the seasonal pattern:
- 1 or omitted (default): Excel automatically detects seasonality using positive integers.
- 0 - no seasonality, i.e. linear forecast.
The maximum seasonality allowed is 8760, which is the number of hours per year. A higher seasonality number will result in #NUM! error.
- Full evidence(optional) - include missing points.
- 1 or omitted (default): Fill in missing points as an average of neighboring points (linear interpolation).
- 0: Treat missing points as zero.
- On the spot(optional): Specifies how multiple data values with the same timestamp are aggregated.
- 1 or omitted (default): The AVERAGE function is used for aggregation.
- Other options are: 2 - COUNT, 3 - COUNT, 4 - MAX, 5 - MEDIAN, 6 - MIN and 7 - SUM.
5 things to know about FORECAST.ETS
- For the PROGNOZA.ETS function to work correctly, the time axis must have aregular break- hourly, daily, monthly, quarterly, yearly etc.
- The function is best suited for non-linear datasets with seasonality or otherwiserepeating pattern.
- when protrudingcannot detect the pattern, the function returns to the linear prediction.
- The function can work withincomplete data setswhere up to 30% of data points are missing. Missing points are treated according to the pricedata completiondisagreement.
- Although a steady paced schedule is required, it can becopyin the date/time string. Values with the same timestamp are added as defined in the filein the placedisagreement.
The FORECAST.ETS function does not work:
If the formula throws an error, it's probably one of the following:
- #N/A appears ifvaluesItimetableTables are of different lengths.
- He#PROWESS! erroris returned ifseasonality,data completionlubricantin the placethe argument is not a number.
- He#NUMBER! errorYou can be expelled for the following reasons:
- Unable to detect fixed step sizetimetable.
- Heseasonalityvalue is outside the supported range (0-8.7600).
- Hedata completionvalue is different from 0 or 1.
- Hein the placevalue is outside the valid range (1 - 7).
How to use the FORECAST.ETS function in Excel: a formula example
To see how exponentially smoothed future values differ from linear regression forecasts, create the FORECAST.ETS formula for the same data set we used in the previous example:
=PROGNOZA.ETS(A23; $B$2:$B$22;$A$2:$A$22)
Where:
- A23 is the target date
- $B$2:$B$22 in historical data (values)
- $A$2:$A$22 in data (timetable)
Omitting the last three arguments (seasonality,data completionlubricantin the place) we rely on Excel's default settings. And Excel predicts the trend perfectly:
Excel FORECAST.ETS.CONFIDENCE
The FORECAST.ETS.CONFINT function is used to calculate the confidence interval for the predicted value.
A confidence interval is a kind of measure of the accuracy of the prediction. The smaller the range, the more certain the prediction is for a particular data point.
FORECAST.ETS.CONFINT is available in Excel for Office 365, Excel 2019, and Excel 2016.
The function has the following arguments:
FORECAST.ETS.CONFINT(target_date, values, schedule, [confidence_level], [seasonality], [datafill], [concentration])
As you can see, the FORECAST.ETS.CONFINT syntax is very similar to the syntaxPROGNOZY.ETSfunction, except for this additional argument:
Confidence level(optional) - a number from 0 to 1 that defines the confidence level for the calculated range. It is usually given as a decimal number, although percentages are also accepted. For example, to set the confidence level to 90%, enter 0.9 or 90%.
- If omitted, a default value of 95% is used, meaning that the forecast data point is 95% of the time within this radius of the value returned by FORECAST.ETS.
- If the confidence level is outside the supported range (0-1), the formula returns the #NUM! error.
FORECAST.ETS.CONFINT type example
To see how this works in practice, let's calculate the confidence interval for our example data set:
=PROGNOZA.ETS.CONFIN.(A23; $B$2:$B$22;$A$2:$A$22)
Where:
- A23 is the target date
- $B$2:$B$22 in historical data
- Data $A$2:$A$22
The last 4 arguments are ignored, which means Excel will use the default options:
- Set the confidence level to 95%.
- Automatic seasonality detection.
- Fill in the missing points by averaging the neighboring points.
- Add multiple data values with the same timestamp using the AVERAGE function.
To understand what the return values actually mean, take a look at the screenshot below (some history rows are hidden for spacing reasons).
The formula in D23 gives the result 6441.22 (rounded to 2 decimal places). This means that 95% of the time, the forecast for March 11 is within 6441.22 of the forecast value of 61,075 (C3). This is 61,075 ± 6,441.22.
To find out what range your predicted values are likely to fall into, you can calculate confidence limits for each data point.
Reachminimum limitsubtract the confidence interval from the predicted value:
=C23-D23
Reachupper limit, add a confidence interval to the predicted 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 formulas above, plot the results on a graph, and have a clear visual representation of the predicted values and confidence interval:
Advice.To automatically create such a chart, use the toolForecasting spreadsheet in Excelmode.
Excel function FORECAST.ETS.SEASONALITY
FORECAST.ETS.SEASONALITY calculates the duration of a repeating pattern on a specified timeline. It is closely related to FORECAST.ETS as both functions use the same algorithm to detect seasonality.
This feature is available in Excel for Office 365, Excel 2019, and Excel 2016.
The FORECAST.ETS.SEASONALITY syntax is as follows:
FORECAST.ETS.SEASONALITY(values, timeline, [add_data], [aggregation])
For our data set, the formula has the following form:
=PROGNOZA.ETS.SEZONALNOŚĆ(B2:B22; A2:A22)
And it returns a seasonality of 7, which perfectly matches the weekly pattern of our historical data:
Excel function FORECAST.ETS.STAT
The FORECAST.ETS.STAT function returns the statistic value specified for the exponentially smoothed forecast of the time series.
Like other ETS features, it is available in Excel for Office 365, Excel 2019, and Excel 2016.
The function has the following syntax:
FORECAST.ETS.STAT(prices, schedule, stats_type, [seasonality], [complete_data], [aggregation])
Hestatistic_typeargument indicates which statistic value to return:
- Esparto(base value): A smoothing value between 0 and 1 that controls the weighting of the data points. The higher the value, the more weight is given to the most recent data.
- Beta(voltage value) - a value between 0 and 1 that specifies the voltage calculation. The higher the price, the more attention is paid to the latest trends.
- Gamma(seasonality value) - a value between 0 and 1 that controls the seasonality of the ETS forecast. The higher the value, the more weight is given to the last seasonal period.
- MEN(mean absolute scale error): measure of prediction accuracy.
- HEAT(symmetric mean absolute error rate): A measure of accuracy based on percentages or relative errors.
- MAE(mean absolute error): measures the average size of the prediction errors, regardless of their direction.
- RMSE(mean squared error) - a measure of the differences between predicted and observed values.
- detected step size- Timeline step size detected.
For example, to return the Alpha parameter for our sample data set, we use the following formula:
=PROGNOZA.ETS.STAT(B2:B22; A2:A22; 1)
The following screenshot shows the formulas for other statistical values:
This is how time series forecasting is done in Excel. To explore all the types covered in this tutorial, you can download oursExcel Forecasting Workbook Example. Thanks for reading and we hope to see you on our blog next week!
You may also be interested in this
- Forecasting in Excel: forecasting models with linear and exponential smoothing
- TREND function and other ways to perform trend analysis in Excel
- Linear Regression Analysis in Excel
- How to Add a Trendline in Excel