FORECAST.ETS function

The FORECAST.ETS function in Excel is used to forecast future values based on an Exponential Smoothing method, which is a more advanced forecasting technique. This function can handle both seasonality and trends in the data and is ideal for time series data. It is part of Excel’s suite of forecasting tools that are available for time series analysis, especially useful for situations with historical data that exhibits patterns like trends or seasonality.

Syntax:

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

Arguments:

• target_date: The date for which you want to forecast the future value. This is the date in the timeline where the forecasted value will appear.
• values: The array or range of known dependent values (Y values) that correspond to the known timeline.
• timeline: The array or range of dates or times that correspond to the known values. This represents the independent variable (X values) and should contain date or time values in chronological order.
• seasonality (optional): A numeric value that specifies the length of the seasonal cycle. For example, a seasonality of 12 would indicate yearly data with monthly cycles. If omitted, Excel will try to automatically detect seasonality.
• data_completion (optional): A logical value (TRUE/FALSE) that determines whether to use missing values in the data. If set to TRUE, Excel will automatically handle missing data, filling in missing values with estimates. If set to FALSE, missing values will be treated as errors.
• aggregation (optional): A numeric value that determines how to aggregate multiple data points in the timeline with the same timestamp. Common values are:
  • 1: AVERAGE
  • 2: COUNT
  • 3: COUNTA
  • 4: MAX
  • 5: MIN
  • 6: SUM

How It Works:

The FORECAST.ETS function uses Exponential Smoothing to fit a model to the historical data and predict future values. It accounts for:

• Seasonality: Patterns in the data that repeat over time, like monthly or quarterly cycles.
• Trends: A general direction the data is moving in (increasing, decreasing).
• Random Variability: Unpredictable fluctuations in the data.

It also uses smoothing factors to apply more weight to recent observations, which is particularly useful for capturing patterns over time in time series data.

Example:

Suppose you have the following monthly sales data:

• Timeline (in cells A2:A13): {Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec}
• Sales (in cells B2:B13): {200, 220, 210, 250, 270, 290, 320, 310, 330, 340, 350, 380}

You want to forecast the sales for the next month (January of the next year), and you expect seasonal patterns in the data.

The formula would look like this:

=FORECAST.ETS("2025-01-01", B2:B13, A2:A13, 12)

This forecasts the sales for January 2025 based on the known sales data for the previous year (12 months of data).

Key Points:

• The FORECAST.ETS function uses Exponential Smoothing to model both seasonality and trends in time series data.
• It automatically handles seasonality (you can specify the length of the cycle or let Excel detect it) and can manage missing values.
• This method is ideal for time series forecasting, where data is collected over time (e.g., daily, monthly, or quarterly).

Use Cases:

• Sales Forecasting: Predict future sales based on past data, while considering seasonality (e.g., monthly or quarterly sales patterns).
• Stock Price Prediction: Forecast stock prices based on historical trends and patterns.
• Demand Forecasting: Predict product demand in retail or manufacturing, especially when trends and seasonal cycles influence demand.
• Weather Forecasting: Predict future weather patterns, considering seasonal changes.

Notes:

• FORECAST.ETS is typically used for time series data that exhibits a regular pattern over time. It works best with data that is chronologically ordered.
• The function automatically detects and adjusts for seasonality if the seasonality argument is left blank, making it easier for users to apply without manually specifying seasonal cycles.
• If your data contains missing values, you can specify how Excel should handle these gaps with the data_completion argument.