FORECAST.ETS.CONFINT function

The FORECAST.ETS.CONFINT function in Excel calculates the confidence interval for a forecasted value using the Exponential Smoothing method. This function returns the upper and lower bounds of a forecasted value’s confidence interval, which helps in assessing the uncertainty around the forecast. This is useful when you want to estimate the possible range within which the actual value might fall, given a certain level of confidence.

The confidence interval is especially useful in time series forecasting to provide a measure of the reliability of the forecast, taking into account historical trends, seasonality, and variability in the data.

Syntax:

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

Arguments:

• target_date: The date for which you want to calculate the confidence interval. This should be 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 try to automatically detect seasonality.
• data_completion (optional): A logical value (TRUE/FALSE) that indicates whether missing values should be handled automatically. TRUE allows for filling in missing data, while FALSE treats missing values 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
• confidence_level (optional): A numeric value between 0 and 1 that specifies the confidence level for the interval. For example, 0.95 would give a 95% confidence interval, meaning there is a 95% chance that the actual value will fall within the range. If omitted, the default is 0.95 (95%).

How It Works:

The FORECAST.ETS.CONFINT function calculates the upper and lower bounds of the confidence interval based on the Exponential Smoothing method used for forecasting. It uses historical data to model the underlying trends, seasonality, and variability, and then calculates the range within which the forecasted value is expected to fall with a certain level of confidence.

The function outputs a single value, which represents the distance (error margin) between the forecasted value and the upper or lower bound of the confidence interval.

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 calculate the confidence interval for the forecasted sales for January 2025 (target_date = “2025-01-01”) with a 95% confidence level.

The formula would be:

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

This formula will return the confidence interval for the forecasted sales in January 2025. The result will be the error margin, and you can use it to calculate the actual upper and lower bounds of the forecast.

Key Points:

• The FORECAST.ETS.CONFINT function calculates the confidence interval for the forecasted value, which provides a range of values within which the actual value is likely to fall.
• It uses the Exponential Smoothing method to account for seasonality, trends, and variability in the data.
• The confidence level determines the width of the interval. A higher confidence level results in a wider interval.

Use Cases:

• Sales Forecasting: Calculate the range of potential sales for the next period, considering uncertainty in the prediction.
• Demand Forecasting: Assess the possible variation in product demand over a specific time period.
• Risk Analysis: In financial forecasting, use the confidence interval to assess the potential range of future values, helping to identify the risk of extreme outcomes.
• Time Series Forecasting: In scenarios where past data exhibits both trends and seasonal patterns, calculate the confidence interval to evaluate the reliability of the forecast.

Notes:

• The seasonality argument should reflect the cyclic nature of your data. If your data has seasonal fluctuations (e.g., monthly sales data), it is important to specify the correct seasonality or let Excel automatically detect it.
• The confidence level can be adjusted based on how confident you want to be in your forecast. The default value is 0.95, corresponding to a 95% confidence level.
• The function returns the error margin (distance from the forecast value), which can be used to calculate the upper and lower bounds of the confidence interval manually by adding and subtracting the result from the forecasted value.