Excel FORECAST.ETS Function: Syntax, Examples & Tips

target_date:
The date for which you want to predict a value based on existing data.

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

timeline:
The range of dates corresponding to the historical values in the values parameter.

seasonality:
An optional parameter that specifies the length of the seasonal cycle. If omitted, Excel automatically detects the seasonality.

data_completion:
An optional parameter that determines how to handle missing data points. If omitted, Excel assumes that missing data points are not to be filled.

aggregation:
An optional parameter that specifies how to aggregate multiple values that occur at the same timestamp. If omitted, Excel uses the average.

What is a FORECAST.ETS Formula in Excel?

The FORECAST.ETS function in Excel is a powerful tool that allows you to predict future values based on historical data using the Exponential Triple Smoothing (ETS) algorithm. This function is particularly useful for time series forecasting, where you want to make predictions based on trends and seasonality in your data.

In simpler terms, the FORECAST.ETS function helps you estimate future values by analyzing past data, making it easier to plan and make informed decisions.

A Practical Example

Imagine you are managing a monthly sales report for a small business, and you have the following data in an Excel spreadsheet:

Monthly Sales Data Table:

You want to forecast the sales amount for July based on the previous months' data.

FORECAST.ETS Formula

To predict the sales for July, you would use the FORECAST.ETS formula as follows:

=FORECAST.ETS(A7, B2:B7, A2:A7)

Breakdown of the Formula:

• A7: This is the target date for which you want to forecast the value (July).
• B2:B7: This is the range of historical sales amounts that you want to analyze.
• A2:A7: This is the range of historical dates corresponding to the sales amounts.

Result of the Formula

When you apply the formula, it predicts the sales amount for July based on the historical data. The output would be:

Here, the forecasted sales amount is calculated based on the trends and patterns observed in the previous months.

Why Use FORECAST.ETS?

The FORECAST.ETS function is particularly useful when you need to make predictions based on historical data without manually analyzing trends. It allows you to quickly derive insights and make data-driven decisions for future planning.

Key Takeaways:

• FORECAST.ETS: Predicts future values based on historical data using the ETS algorithm.
• Efficient Forecasting: Helps you estimate future outcomes without complex calculations.
• Common Use Cases: Ideal for sales forecasting, inventory management, and any scenario where future trends need to be predicted based on past data.

Understanding how to use the FORECAST.ETS function can significantly enhance your forecasting capabilities in Excel, enabling you to make informed decisions based on data trends.

Happy forecasting!