The FORECAST.ETS function in Excel is essential for predicting future values based on historical data. This powerful tool uses the Exponential Smoothing (ETS) algorithm to create more accurate forecasts by considering seasonal patterns in data. By analyzing historical trends, users can generate forecasts that are useful for making informed decisions in various fields, such as finance, inventory management, and sales planning.

Syntax

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

• target_date: The date for which you want to predict the future value.
• values: A range of historical values that you want to base the forecast on.
• timeline: A range of dates that correspond to the historical values.
• seasonality: An optional parameter that defines the number of data points in a seasonal cycle. Default is detected automatically.
• data_completion: An optional flag to specify if missing data points should be filled (TRUE) or not (FALSE). Default is TRUE.
• aggregation: An optional parameter defining the method for aggregating duplicate values, with default as AVERAGE.

Example #1

FORECAST.ETS(DATE(2023, 10, 1), A2:A10, B2:B10)

This function predicts the value for October 1, 2023, based on historical data from the ranges A2 to A10 for values and B2 to B10 for corresponding dates. Example Result: $1500.

Example #2

FORECAST.ETS(DATE(2024, 1, 1), C2:C12, D2:D12, 12)

This function forecasts the value for January 1, 2024, utilizing the past 12 months of historical data set in C2 to C12 against the timeline in D2 to D12. Example Result: $2000.

Example #3

FORECAST.ETS(DATE(2023, 7, 15), E2:E14, F2:F14, 6, TRUE, 1)

This function projects a value for July 15, 2023, based on 6 seasonal cycles with data completion enabled. Example Result: $1800.

Error handling

• N/A: Indicates that the input data is insufficient or cannot be used to generate a forecast.
• VALUE: Happens when the data types for the target_date, values, or timeline parameters are inconsistent or invalid.
• NUM: This error occurs if there are not enough data points to calculate the projection or if the specified seasonality value is invalid.

Conclusion

In conclusion, the FORECAST.ETS function is a highly effective tool for projecting future values from historical data. By leveraging the ETS algorithm, businesses and analysts can make informed decisions based on reliable forecasts. Understanding its syntax and potential errors enhances its usability, allowing for better data-driven strategies.