Excel FORECAST.ETS.CONFINT Function: Syntax, Examples & Tips

target_date:
The date for which you want to calculate the confidence interval.

values:
The range of historical values that you want to use for the forecast.

timeline:
The range of dates that corresponds to the historical values.

confidence_level:
An optional parameter that specifies the confidence level for the confidence interval, expressed as a decimal (e.g., 0.95 for 95% confidence).

seasonality:
An optional parameter that indicates the seasonality of the data. It can be set to a specific number or left blank for automatic detection.

data_completion:
An optional parameter that specifies how to handle missing data. It can be set to TRUE or FALSE.

aggregation:
An optional parameter that determines how to aggregate multiple values for the same timestamp, with options for different aggregation methods.

What is a FORECAST.ETS.CONFINT Formula in Excel?

The FORECAST.ETS.CONFINT function in Excel is a powerful tool that allows you to calculate the confidence interval for a forecasted value based on a time series. This function is particularly useful for understanding the uncertainty associated with your forecasts, providing a range within which you can expect the actual values to fall.

In simpler terms, the FORECAST.ETS.CONFINT function helps you quantify the reliability of your predictions, making it easier to make informed decisions based on your data.

A Practical Example

Imagine you are analyzing monthly sales data for a retail store, and you have the following data in an Excel spreadsheet:

Sales Data Table:

You want to forecast the sales for June and determine the confidence interval for that forecast.

FORECAST.ETS.CONFINT Formula

To calculate the confidence interval for the forecasted sales for June, you would use the FORECAST.ETS.CONFINT formula as follows:

=FORECAST.ETS.CONFINT(DATE(2023,6,1), B2:B6, A2:A6, 1, 1, 1)

Breakdown of the Formula:

• DATE(2023,6,1): This is the target date for which you want to forecast the sales.
• B2:B6: This is the range of historical sales data that you are using for the forecast.
• A2:A6: This is the range of dates corresponding to the sales data.
• 1: This indicates the confidence level (1 = 95% confidence).
• 1: This specifies the seasonality (1 = auto-detect).
• 1: This indicates the data completion (1 = no missing data).

Result of the Formula

When you apply the formula, it calculates the confidence interval for the forecasted sales for June. The output would provide a lower and upper bound, indicating the range within which the actual sales for June are expected to fall.

Here, the confidence interval suggests that the actual sales for June are expected to be between 380 and 420.

Why Use FORECAST.ETS.CONFINT?

The FORECAST.ETS.CONFINT function is particularly useful when you need to assess the reliability of your forecasts. It allows you to understand the potential variability in your predictions, enabling you to make more informed decisions based on the level of uncertainty.

Key Takeaways:

• FORECAST.ETS.CONFINT: Calculates the confidence interval for a forecasted value based on a time series.
• Understanding Uncertainty: Helps you quantify the reliability of your predictions.
• Common Use Cases: Ideal for financial forecasting, sales predictions, and any scenario where understanding the variability of forecasts is crucial.

Understanding how to use the FORECAST.ETS.CONFINT function can significantly enhance your forecasting capabilities in Excel, enabling you to make data-driven decisions with a clearer understanding of the associated risks.

Happy forecasting!