FORECAST.ETS.STAT function

The FORECAST.ETS.STAT function in Excel provides statistical information about the Exponential Smoothing forecasting model. This function allows you to retrieve specific statistics that help you evaluate the accuracy and fit of the forecast, such as error values and confidence intervals. These statistics can be helpful for understanding how well the model fits the data and for diagnosing potential issues with the forecast.

Syntax:

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

Arguments:

• target_date: The date for which you want to get the statistical information. This is typically a specific date in the future where the forecasted value is expected.
• values: The array or range of known dependent values (Y-values) corresponding to the given timeline (time series data).
• timeline: The array or range of known independent values (X-values, typically dates or time points) corresponding to the known dependent values.
• seasonality (optional): The length of the seasonal cycle. For example, a seasonality of 12 would indicate yearly data with monthly cycles. If omitted, Excel will attempt to automatically detect seasonality.
• data_completion (optional): A logical value (TRUE/FALSE) that indicates whether missing data should be handled. If TRUE, Excel will automatically fill in missing data. If FALSE, missing values will be 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
• stat_type: A numeric value that specifies the type of statistical information you want to retrieve. Here are the possible values for stat_type:
  • 0: MAE (Mean Absolute Error) — Measures the average magnitude of the errors in the forecast, without considering their direction.
  • 1: MSE (Mean Squared Error) — Measures the average of the squared errors, giving more weight to larger errors.
  • 2: MAPE (Mean Absolute Percentage Error) — Measures the average absolute percentage error, giving an idea of the relative accuracy of the forecast.
  • 3: RMSE (Root Mean Squared Error) — Measures the square root of the mean squared errors, representing the standard deviation of the forecast errors.
  • 4: Confidence Interval — Provides the confidence interval (upper and lower bounds) of the forecasted value, based on the confidence level.

How It Works:

The FORECAST.ETS.STAT function helps you evaluate the quality and reliability of your forecast by calculating specific statistical metrics. These metrics help you understand how well the Exponential Smoothing model fits the historical data, and can help you assess the forecast’s accuracy.

The statistical information returned by this function is particularly useful when:

• You want to assess how much error is present in the forecast.
• You need to understand the degree of confidence in the forecasted value.
• You are comparing different forecasting methods or models.

Example:

Suppose you have the following 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 get the Mean Absolute Percentage Error (MAPE) for the forecast based on this data, you would use the following formula:

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

This formula will return the MAPE, which gives you the average percentage error in the forecast for January 2025. A lower MAPE indicates a more accurate forecast.

Key Points:

• The FORECAST.ETS.STAT function provides specific statistical metrics that help you evaluate the performance and accuracy of the Exponential Smoothing model.
• The stat_type argument determines which statistical value to return (e.g., MAE, MSE, MAPE, RMSE, or confidence interval).
• This function is valuable for understanding the quality of your forecasts, particularly when assessing the accuracy or uncertainty of the predictions.

Use Cases:

• Forecast Evaluation: You can use this function to evaluate how well the Exponential Smoothing model fits your data by calculating error metrics like MAE, MSE, or MAPE.
• Model Comparison: If you’re comparing different forecasting models, FORECAST.ETS.STAT helps you quantify the accuracy of each model.
• Time Series Analysis: The statistical output can help identify if the forecasting method captures the underlying trends and seasonality well, or if adjustments need to be made.

Notes:

• Seasonality: If you’re working with time series data that has clear seasonal patterns, you should specify the correct seasonality to improve forecast accuracy.
• Error Metrics: The statistical measures like MAPE or RMSE help you assess how well the model predicts future values and can guide you in adjusting your approach or understanding model limitations.