The FORECAST.ETS.CONFINT function in Excel is essential for financial analysts and data scientists aiming to produce reliable projections. This function helps users determine the confidence interval around forecasted values, providing a statistical range in which future data points are expected to fall. By leveraging advanced time series algorithms, it enhances decision-making and risk assessment processes.

Syntax

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

• target_date: The specific date for which the forecast is calculated.
• values: An array or range that contains the historical data for which predictions are being made.
• timeline: An array or range indicating the corresponding dates or times for the historical data.
• seasonality: Optional. A number indicating how seasonality affects the forecast; can be auto-detected if omitted.
• data_completion: Optional. Specifies how missing data should be addressed; default is set to true.
• aggregation: Optional. Dictates how to combine multiple values for the same timestamp; the default is average.

Example #1

FORECAST.ETS.CONFINT(DATE(2023,5,30), B2:B10, A2:A10)

In this example, the function generates a confidence interval for forecasts on May 30, 2023, based on the historical data in B2:B10 and corresponding dates in A2:A10. It might return values like 1500 to 2000, indicating that future values are expected to fall in this range.

Example #2

FORECAST.ETS.CONFINT(DATE(2023,12,15), C2:C10, D2:D10, 1)

This function call forecasts and provides a confidence interval for December 15, 2023, utilizing data from C2:C10 and relevant dates from D2:D10. The seasonality is set to a yearly cycle (1). The output may show a range of 1800 to 2200.

Example #3

FORECAST.ETS.CONFINT(DATE(2023,8,20), E2:E10, F2:F10, 1, FALSE)

This example forecasts a confidence interval for August 20, 2023, using the data from E2:E10 and dates from F2:F10. Here, seasonality is considered, and data completion is set to false. One might receive results like 1600 to 2100, suggesting expected future values within this spectrum.

Error handling

• VALUE!: Indicates that one or more parameters contain invalid types, such as text instead of numbers.
• N/A: Occurs when there is insufficient historical data to generate a forecast.
• NUM!: Indicates that the function could not calculate due to a numerical issue, like unsupported dates.
• DIV/0!: Signals that no data was provided to generate a confidence interval, leading to a division by zero.

Conclusion

The FORECAST.ETS.CONFINT function is a powerful tool for users looking to create forecasts with clear statistical backing. By providing a confidence interval, it allows decision-makers to assess the reliability of their projections and make informed choices in uncertain environments. Mastering this function enhances the ability to interpret and utilize data effectively for strategic planning.