Excel FORECAST.ETS Function
Forecasting based on the series of known data values are very important. It benefits the users to predict numeric data such as sales, inventory, expenses, etc. To conclude this, Microsoft introduced the FORECAST.ETS function with the version of Excel 2016.
In this tutorial, we will cover the definition of the FORECAST.ETS function, its syntax, parameter, error type and its reasons, and examples to comprehend how this formula returns a specified statistical value relating to a time series.
What is FORECAST.ETS Function?
“The FORECAST.ETS in an inbuilt Excel function that is used to compute the exponential smoothing forecasts based on a series of existing data values.“
In other words, this function forecasts a value based on known values that follow a seasonal trend. The FORECAST.ETS predicts a future value based on the AAA (where it stands for additive error, additive trend and additive seasonality) version of the Exponential Triple Smoothing (ETS) algorithm. Therefore, this function is named as ETS. The ETS algorithm smoothes out irrelevant deviations in known data trends by detecting seasonality patterns and confidence intervals.
Note: The FORECAST.ETS function was added in Microsoft with Excel 2016. Therefore, if you are using an Excel version below Excel 2016, this function will be not available. It is also not available in Excel 2016 for Mac.
Syntax
Parameter
Target_date (required) – This argument represents the data point for which to forecast a value. It can be represented by a date/time or number.
Values (required) – This argument signifies a range or array of historical data for which you want to predict future values.
Timeline (required) – This parameter represents an array of dates/times or independent numeric data with a constant step between them.
Seasonality (optional): This argument indicates the algorithm should be used to detect seasonality in the data.
Since its an optional parameter, it could be omitted as well. But if supplied, this parameter must be a positive integer between 0 and 8784, where the seasonality values indicate the following:
| [seasonality] | Argument |
|---|---|
| 0 | 0 indicates no seasonality value (i.e., use the linear algorithm for the forecast). |
| 1 (or omitted) | If 1 is passed, this function automatically calculates the seasonality value and take positive, whole numbers for the length of the seasonal pattern. |
| integer ≥ 2 & ≤ 8784 | This value use patterns of this length as the seasonality. |
NOTE: A higher seasonality number will result in the #NUM! error.
Data completion (optional) – This parameter represents how the algorithm should handle missing points in the timeline.
Since it is an optional parameter, it could be omitted as well. But if supplied, this parameter argument can have the value 0 or 1 indicating the following:
| [data completion] | Algorithm |
|---|---|
| 0 | Treat missing points as having the value zero.. |
| 1 (or omitted) | Calculate the value for missing points to be the average of the neighbouring values. |
Aggregation (optional)- This parameter specifies how the algorithm should aggregate values that have the same timestamp.
Since it is an optional parameter, it could be omitted as well. But if supplied, this parameter argument can be any integer between 1 and 7 indicating the following:
| [aggregation] | Aggregation Method |
|---|---|
| 1 (or omitted) | It represents Average |
| 2 | It represents Count |
| 3 | It represents Counta |
| 4 | It represents Max |
| 5 | It represents Median |
| 6 | It represents Min |
| 7 | It represents Sum |
Points to Remember- FORECAST.ETS
- If you want the FORECAST.ETS function to return the accurate output, make sure that the timeline parameter should contain a regular interval – hourly, daily, monthly, quarterly, yearly, etc.
- The function commonly used for non-linear data sets with seasonal or other repetitive pattern.
- When Excel cannot detect a pattern, the function reverts to a linear forecast.
- ETS function can also operate with incomplete datasets where up to 30% data points are not specified. The missing points are considered as per the value defined in the data completion parameter.
- Although the timeline parameter must contain a consistent step, there may be duplicates in the date/time series.
Examples
Example 1: Using the FORECAST.ETS function predicts the sales based on given values that follow a seasonal trend.
As you can see in the below table, we are given a monthly sales table for 2020, and based on the previous value, we have to forecast the sales figure for Jan 2021 and Feb 2021 to predict the value use the existing FORECAST.ETS function.
The Excel FORECAST.ETS function predicts a value based on known values that follow a seasonal trend. To compute the future sales value using the Excel FORECAST.ETS () function follow the below-given steps:
STEP 1: Select the cell
In the above question, we have introduced a helper column to predict the sales value. Therefore, we will select the first cell of this column, i.e., D15, to apply the FORECAST.ETS() formula.
STEP 2: Type the FORECAST.ETS.STAT function
To predict the sales value for the given seasonal trend, we will use the inbuilt FORECAST.ETS() function. Therefore, start typing the function with the equal to (=) symbol followed by the pre-defined FORECAST.ETS function.
Our formula will become: =FORECAST.ETS(
STEP 3: Supply the Parameters
- At first, this function will ask you to specify the ‘Target_Date’ parameter. Here, we will specify data point for which to forecast a value. The formula will be =FORECAST.ETS (B15
- At first, this function will ask you to specify the ‘Value’ parameter. Here, we will specify an array of historical known values corresponding to an array of dates/times. The formula will be =FORECAST.ETS(B15,$C$3:$C$14,
- The next argument is Timeline. This parameter should contain independent array of dates/times. The formula will be =FORECAST.ETS(B15,$C$3:$C$14,$B$3:$B$14)
- The last 3 arguments of these functions are optional. Therefore, we have omitted them. Excel will use the following default value in its place:
- Automatically calculate the seasonality and use positive, whole numbers for the length of the seasonal pattern.
- For the parameter Data completion, 1 will be supplied as default option so as to complete missing points as the average of the neighbouring points.
- In the Aggregation parameter, value 1 will be passed as a default option so it can aggregate multiple data values with the identical timestamp by using the AVERAGE function.
Therefore, the overall formula becomes:
=FORECAST.ETS(B15,$C$3:$C$14,$B$3:$B$14)
NOTE: Since we will copy the formula to other cells, therefore using the absolute cell references (like $C$3:$C$14) we have locked the cells to prevent them from changing their values when copying the formula to other cells.
Step 4: The FORECAST.ETS function will return the output
As a result, the FORECAST.ETS.STAT function will forecast the future sales values for Jan-21.
STEP 5: Drag the formula cell to replicate the formula
Next, we will predict the sales values for more date values using the same formula. For the rest of the cells, the Values and Timeline parameter will be the same (therefore, we have used absolute references), only changing the date parameter.
Select the F4 formula cell and move your cursor towards the right corner of the selected box. As a result, the cursor will change into a plus (+) icon. Drag the icon to the following cells, and your surprise formula will be replicated down the cells.
You will have the following Statistical Values for different statistic_type.
Step 6: Make a graph for predicted Sales
Next, we will depict the output visually using a graph. Select the data, go to insert-> charts-> line charts. Excel will create a detailed linear regression forecasting chart.
Refer to the below output.
Eureka! using the inbuilt Excel FORECAST.ETS function in the above excel table, wehave successfully forecasted the future sales values for two months.
#FORECAST.ETS Example 2: Calculate the future value if the values and timeline arguments are not the same size.
The FORECAST.ETS function returns the #VALUE! error if values and timeline arguments are not the same size. Let’s understand this using the below given data.
Follow the below-given steps to analyse the output of the FORECAST.ETS() function if values and timeline arguments are not the same size:
STEP 1: Select the cell
In the above question, we have introduced a helper column to predict the sales value. Therefore, we will select the first cell of this column, i.e., D15, to apply the FORECAST.ETS() formula.
STEP 2: Type the FORECAST.ETS.STAT function
To predict the sales value for the given seasonal trend, we will use the inbuilt FORECAST.ETS() function. Therefore, start typing the function with the equal to (=) symbol followed by the pre-defined FORECAST.ETS function.
Our formula will become: =FORECAST.ETS(
STEP 3: Supply the Parameters
- At first, this function will ask you to specify the ‘Target_Date’ parameter. The formula will be =FORECAST.ETS (B15
- At first, this function will ask you to specify the ‘Value’ parameter. The formula will be =FORECAST.ETS(B15,$C$3:$C$14,
- The next argument is Timeline. In this parameter, we have added two extra dates. The formula will be =FORECAST.ETS(B15,$C$3:$C$14,$B$3:$B$16)
- We have omitted the last 3 arguments of these functions because they are optional.
Therefore, the overall formula becomes:
=FORECAST.ETS(B15,$C$3:$C$14,$B$3:$B$16)
NOTE: Since we will copy the formula to other cells, therefore using the absolute cell references (like $C$3:$C$14) we have locked the cells to prevent them from changing their values when copying the formula to other cells.
Step 4: The FORECAST.ETS function will return the output
As a result, the FORECAST.ETS.STAT function will return an #N/A error because the size of timeline is greater than the Values parameter.
Excel FORECAST.ETS function not working
Sometimes while working the Excel FORECAST function can throw an error, it could most likely be because of the following reasons:
- Excel FORECAST.ETS function will return #N/A error, if the values and timeline are not the same size.
- This function returns the #VALUE! error if the specified seasonality, data completion or aggregation parameter is non-numeric.
- This function returns a #NUM error, if:
- The Consistent step cannot be found in the timeline parameter
- The numeric value for seasonality does not fall under 0-8784
- The numeric value for data_completion is not 0 or 1
- The numeric value for aggregation does not fall within the range of 1-7