FORECAST.ETS Excel

What Is FORECAST.ETS Excel Function?

The FORECAST.ETS Excel function is used to predict a future value based on the existing values utilizing the exponential smoothing (ETS) algorithm to generate accurate predictions based on historical data patterns. It is designed for time series analysis and forecasting. This function is particularly advantageous as it can handle datasets with missing values or irregular time intervals, making it suitable for real-world scenarios where data is often imperfect.

The ETS algorithm incorporates multiple components such as trend, seasonality, and error terms, allowing for comprehensive forecasting capabilities.

In the following example, let us look into the concept of the FORECAST.ETS Excel function.

In the above table, we calculate the Forecast ETS, and for this, we choose cell C5. Input the formula as depicted below:
=FORECAST.ETS(A5,$B$2:$B$4,A2:A4)
The resultant value will be exhibited in cell C5.

What Is FORECAST.ETS Excel Function?

The FORECAST.ETS Excel function predicts a future value based on the Exponential Triple Smoothing (ETS) algorithm using existing values following a seasonal trend.
In the FORECAST.ETS function, users can easily estimate future values and trends by simply inputting their data range and specifying the desired forecast length.
It provides uncertainty measures like confidence intervals to gauge the reliability of predicted values. This feature enables decision-makers to understand the potential range of outcomes and make informed choices based on the level of confidence they require.
The FORECAST.ETS Excel function facilitates robust forecasting analysis in a user-friendly manner, helping make data-driven decisions effectively.

Syntax

Target Date – The designated time or period for the prediction.
Values – The existing or historical values
Timeline – Numeric values that represent the timeline.
Seasonality – This is an Optional argument. An optional calculation for seasonality (0 = no seasonality, 1 =automatic, n = season length in timeline units).
Data Completion – This is an Optional argument. A method to handle missing data (0 = treated as zero, 1 = average). The default option is 1.
Aggregation – This is an Optional argument. The default option is 1 (AVERAGE). Other available options are listed below.

How To Use FORECAST.ETS Function In Excel?

To effectively utilize the Excel FORECAST.ETS function, follow these steps.

#1 – Access From The Excel Ribbon

Choose the empty cell which will contain the result. Go to the “Formulas” tab and click it.
Select the “More Functions” option from the menu.
Select the “Statistical” option from the drop-down list. Select FORECAST.ETS” from the drop-down menu.
A window called “Function Arguments” appears. As the number of arguments, enter the value in the “target_date,” “timeline,” “seasonality,” “data_completion,” and “aggregation.”
Select OK.

Thus, you can calculate the FORECAST.ETC value through the Excel ribbon.

#2 – Enter The Worksheet Manually

Step 1: Select an empty cell for the output.
Step 2: Type “=FORECAST.ETS (” in the selected cell. Alternatively, type “=F” and double-click the FORECAST.ETS function from the list of suggestions shown by Excel.

Step 3: Enter the required values and close the braces. Press the “Enter” key to get the result.

Examples

Let us look at some examples of how to implement the FORECAST.ETS function.

Example #1

In this example, we’re going calculate the predicted future value of the given values with the FORECAST.ETS Excel function and see how it can be applied. The data in the table provided is organized as follows:

To calculate the desired output in cell C14, follow these steps.

Step 1: Choose the cell where you want to enter the formula and calculate the result. Let’s use cell C14 for this example. Enter the following values for the different arguments.

Target date – A14.
Values – $B$2:$B$13.
Timeline – $A$2:$A$13
Seasonality – 3 (The seasonality argument here represents the seasonal pattern length expressed in timeline units.)
Data Completion as 1 for Automatic Completion uses linear Interpolation
Aggregation – 1 for AVERAGE function

Step 2: Enter the FORECAST.ETS Excel formula exactly as shown below:

=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13,3,1,1)

Step 3: Once you’ve entered the formula, press the Enter key. You will see the corresponding value displayed in cell C14 and the graph, too, as in the provided image.

By following these simple steps, you’ll be able to calculate the forecast value using the FORECAST.ETS Excel function.

Example #2 – Predict sales using seasonality with graph

In this example, we have a monthly sales table for 2022. We must forecast the sales value of Jan, Feb & Mar 2023 based on these previous values using the concept of the FORECAST.ETS Excel function and its practical application. Our focus will be on predicting sales using seasonality with a graph based on the given data.

The data in the provided table is organized as follows:

To calculate the desired output in cell C14, follow these steps:

Step 1: Select the cell where you wish to enter the formula and obtain the result. For this example, let’s use cell C14.

Enter the following values:

Target date: A14
Values: $B$2:$B$13
Timeline: $A$2:$A$13

The last three arguments of these functions are optional, and we omit them. Excel will automatically apply the default values.

Regarding the parameter for data completion, the default option will be set to 1. This means that missing data points will be completed by taking the average of the neighbouring points.

As for the Aggregation parameter, the default option will be set to 1. This allows for the aggregation of multiple data values with the same timestamp using the AVERAGE function.

Enter the formula exactly as shown below in C14.

=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13)

Step 2: Now, press Enter. You will get the result for Jan-23^rd. Now, drag the formula to cells A15 and A16.

The result is the corresponding value displayed in cells C14 to C16, along with the graph, just like in the provided image.

So, by following these simple steps, you’ll be able to calculate the forecast value. Predicting sales using seasonality with a graph using the FORECAST.ETS Excel function.

Example #3 – Predict sales if the values and timeline arguments are not the same size.

In this example, we will delve into the monthly sales table, and forecast the sales value. Our approach will revolve around using FORECAST.ETS Excel function and its practical application. Our primary objective is to predict if the values and timeline arguments are not the same size based on the provided data.

Below is the organization of data in the table:

To calculate the desired output in cell C14, we need to follow these steps.

Step 1: Begin by selecting the cell where you intend to enter the formula and obtain the result. For this example, let’s utilize cell C14.

Enter the following values:

Target date: A13
Values: $B$2:$B$13
Timeline: $A$2:$A$15

We have excluded the last three arguments of the function as they are considered optional.

Now, input the formula exactly as shown below:

=FORECAST.ETS(A13,$B$2:$B$13,$A$2:$A$15)

Step 2: Consequently, the FORECAST.ETS Excel function will generate an #N/A error due to the timeline size exceeding the Values parameter, as shown in the image provided.

Important Things To Note

Should the target date precede the end of the historical timeline, Excel will generate the #NUM! Error.
Similarly, if a consistent step cannot be discerned within the timeline, Excel will produce the #NUM! Error.
In case the seasonality value does not conform to the specified guidelines, Excel will return the #NUM! Error.
If duplicate values are found within the timeline, Excel will generate the #VALUE! Error.
If the sizes of the timeline and value ranges do not align, Excel will return the #N/A error.

Frequently Asked Questions (FAQs)

1. What kind of data can I use with the FORECAST.ETS function?

The FORECAST.ETS function in Excel allows users to generate accurate forecasts based on time series data. It utilizes the Exponential Smoothing State Space model, which is capable of handling various types of data. The FORECAST.ETS can handle missing values in the input series by applying an interpolation technique based on available data points nearby. It is important to note that this function assumes the absence of seasonality or trend changes in the dataset. However, if such patterns exist, pre-processing steps might be required before utilizing this feature to ensure more accurate forecasts.

2. Can I use the FORECAST.ETS function to predict future values in Excel?

Yes, the FORECAST.ETS function in Excel can be used to predict future values based on an existing time series of data. This function utilizes the Exponential Smoothing (ETS) algorithm, which is a widely used and effective method for forecasting in time series analysis. By applying this function, Excel calculates the best-fit curve that represents the pattern within the given data and uses it to extrapolate future values. The FORECAST.ETS Excel function takes into account various factors such as the trend, seasonality, and cyclicality of the data to provide accurate predictions.

In the following example, we will explore the concept of the FORECAST.ETS Excel function to achieve the future value.

To demonstrate this, let’s take cell C5. Just enter the formula as shown below:
=FORECAST.ETS(A5,$B$2:$B$4,$A$2:$A$4)
The resulting value will be displayed in cell C5.

3. Are there any limitations or restrictions to using the FORECAST.ETS function in Excel?

The FORECAST.ETS Excel function is a powerful tool for time series forecasting, but like any other tool, it has its limitations and restrictions.
• Firstly, the function requires a minimum of two data points to generate a forecast, making it unsuitable for datasets with too few observations.
• Moreover, the accuracy of the forecasts heavily relies on the quality and nature of historical data, meaning that if there are outliers or irregular patterns in the dataset, the function may not be able to predict future values accurately.
• Additionally, when dealing with seasonal datasets or those with multiple trend patterns, FORECAST.ETS may struggle to capture these complex behaviors effectively, leading to less accurate predictions.
• Lastly, this function assumes that past trends will continue without considering external factors such as economic changes or policy shifts that could impact results.

Download Template

This article must help us understand the FORECAST.ETS Excel Function’s formula and examples. You can download the template here to use it instantly.

FORECAST.ETS-ExcelDownload

FORECAST.ETS Excel