This tutorial demonstrates how to use the Excel FORECAST.EST Function in Excel.

FORECAST.EST Function Overview

The FORECAST.EST Function is used to do exponential smoothing forecasts based on a series of existing values.

To use the FORECAST.EST Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

FORECAST function Syntax and inputs:

FORECAST.ETS (target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Target_date – the data point for which to forecast a value. It can be represented by a date/time or number.

Values  – a range or array of historical data for which you want to predict future values.

Timeline – an array of dates/times or independent numeric data with a constant step between them.

Seasonality (optional) – a number representing the length of the seasonal pattern:

Data completion (optional) – accounts for missing points.

Aggregation (optional) – specifies how to aggregate multiple data values with the same time stamp.

 

What is FORECAST.ETS?

The FORECAST.ETS function in Excel is used to forecast data using an exponential smoothing algorithm.

Exponential smoothing is a method in statistics used for smoothing time series data by assigning exponentially decreasing weights to future values over time. This differs from a simple moving average where past observations are weighted equally. The predicted value is a continuation of the historical values in the target date range, which should be a continuous timeline with an equal interval between dates. It can be used to predict future sales, inventory requirements or general consumer trends.

Suppose I have a table of sales data by month:

I want to know what the predicted sales amount will be for October 2020 based on the historical data shown in the table:

=FORECAST.ETS(DATE(2020,10,1),C3:C12,B3:B12)

 

FORECAST.ETS returns a result of $21,202 in sales in October of 2020. We can visualize this and the predictions for months in-between by applying the formula to an expanded data table:

Visualizing the prediction:

How to use FORECAST.ETS

FORECAST.ETS has three required arguments and three optional arguments:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Where target_date is the date you want to predict a value for, values is the array of historical data (sales in our case) and timeline is the array of a timeframe with an equal interval, such as daily, the 1^st of the each month, the 1^st of each year or even a continuous numerical index.

Seasonality is a positive integer representing the length of the seasonal pattern. The default is 1 meaning Excel detects seasonality automatically. Zero indicates no seasonality.

Data_completion: FORECAST.ETS supports up to 30% missing data and can adjust for those missing data points by using zero in their place or interpolating based on the neighboring datapoints.

Aggregation: FORECAST.ETS will aggregate values with the same timestamp, even though the timestamps require a constant step or interval. The argument uses a number representing an aggregation option with the default being zero, or average, and other options including SUM, COUNT, MIN, MAX, or MEDIAN.

Effect of Seasonality

Using the example above, the October 2020 forecast value was $21,202 using the default value for seasonality. What if we did not apply seasonality?

Notice a zero was added as the last argument in the formula bar. The October predicted result is now $3,308 higher than the original prediction. Some data may not have any inherent seasonality, but in data that does have seasonality, it is a powerful option to ensure you have an accurate prediction. The below trend visualizes the same prediction, adding a forecast without seasonality for each forecasted month:

FORECAST.ETS Tips

• The target_date for the first argument must be chronologically after the date in the historical data array, timeline.
• If a constant step cannot be identified in the timeline, a #NUM! error will be returned.
• The max value for seasonality is 8,760 representing hours in a year. Any larger value will return a #NUM! error.

Interested in More Forecasting?

See our other articles on Forecasting with TREND, LINEST or FORECAST.LINEAR functions.