FORECAST.ETS.SEASONALITY function

The FORECAST.ETS.SEASONALITY function in Excel is used to detect the seasonality of a given time series data. It automatically identifies the length of the seasonal cycle (the period over which the data repeats) based on the historical data provided. This function is especially useful when you do not know the seasonality of the data or want to let Excel determine it for you.

Seasonality refers to patterns that repeat at regular intervals in time series data (e.g., monthly sales data with yearly cycles or daily traffic data with weekly patterns). Understanding seasonality is essential for accurately forecasting future values.

Syntax:

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

Arguments:

• values: The array or range of known dependent values (Y-values) that correspond to the given timeline. These are the historical data points that you want to analyze for seasonality.
• timeline: The array or range of known independent values (X-values, typically dates or time points) corresponding to the known dependent values.
• data_completion (optional): A logical value (TRUE/FALSE) that indicates whether missing data should be handled. If TRUE, missing values in the data will be automatically handled by Excel. If FALSE, missing values are treated as errors.
• aggregation (optional): A numeric value that specifies how to aggregate multiple data points in the timeline with the same timestamp. Possible values are:
  • 1: AVERAGE
  • 2: COUNT
  • 3: COUNTA
  • 4: MAX
  • 5: MIN
  • 6: SUM

How It Works:

The FORECAST.ETS.SEASONALITY function uses the historical data provided in the values and timeline ranges to analyze patterns of seasonality in the data. It then calculates the length of the seasonal cycle, which represents the number of time periods over which the data exhibits repetitive patterns.

This function is most useful when you’re working with time series data that has regular cycles (e.g., monthly sales data with a yearly seasonal pattern). By identifying the seasonal cycle length, you can improve the accuracy of your forecasts using other forecasting functions like FORECAST.ETS.

Example:

Suppose you have monthly sales data for the past year:

• 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}

To detect the seasonality of this sales data, you would use the FORECAST.ETS.SEASONALITY function:

=FORECAST.ETS.SEASONALITY(B2:B13, A2:A13)

This will return the length of the seasonal cycle. In this case, since the data appears to follow yearly cycles, Excel would likely return a value of 12, indicating a yearly seasonality with monthly cycles.

Key Points:

• The FORECAST.ETS.SEASONALITY function helps automatically detect the seasonality of your time series data, which is useful when you don’t know the period over which your data repeats.
• The function works by analyzing the historical data in the values range and determines the periodicity of seasonal fluctuations.
• It is particularly helpful for time series data where seasonal patterns are present, such as monthly sales or yearly traffic data.

Use Cases:

• Sales Forecasting: Detect seasonal patterns in monthly sales data, and use that information to improve future sales forecasts.
• Weather Data: Identify seasonal patterns in temperature or precipitation data.
• Traffic or Website Data: Analyze website traffic data to identify daily, weekly, or monthly seasonal cycles.
• Financial Data: Detect seasonal trends in financial data like stock prices or quarterly earnings.

Notes:

• The FORECAST.ETS.SEASONALITY function works best when there is a clear repeating pattern in the data. If there is no apparent seasonality, Excel may not be able to detect a valid seasonal cycle.
• The data_completion argument can help handle missing values if the time series data is incomplete.
• Once the seasonality has been detected, you can use this information to improve other time series forecasting functions (e.g., FORECAST.ETS) by specifying the detected seasonality.