FORECAST.ETS

Excel Functions

Predict value with a seasonal trend

Syntax

EXCEL

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

Arguments

Parameter	Description
`target_date`	The time or period for the prediction (x value).
`values`	Existing or historical values (y values).
`timeline`	Numeric timeline values (x values).
`seasonality`	[optional] Seasonality calculation (0 = no seasonality, 1 = automatic, n = season length in timeline units).
`data_completion`	[optional] Missing data treatment (0 = treat as zero, 1 = average). Default is 1.
`aggregation`	[optional] Aggregation behavior. Default is 1 (AVERAGE). See other options below.

Return Value

Predicted value

Details

The FORECAST.ETS function predicts a value based on existing values that follow a seasonal trend. FORECAST.ETS can be used to predict numeric values like sales, inventory, expenses, etc. with a seasonal pattern. To calculate predicted values, FORECAST.ETS uses something called triple exponential smoothing. This is an algorithm that applies overall smoothing, trend smoothing, and seasonal smoothing. In the example shown above, the formula in cell D13 is: where sales (C5:C12) and periods (B5:B12) are named ranges. With these inputs, the FORECAST.ETS function returns 618.29 in cell D13. As the formula is copied down the table, FORECAST.ETS returns predicted values in D13:D16, using values in column B for target date. The chart to the right shows this data plotted in a scatter plot.

Examples

Example

In the example shown above, the formula in cell D13 is: