Excel 2016 introduced a number of new forecasting functions based on the triple exponential smoothing (i.e. Holt-Winter Additive) approach. This is also called ETS forecasting for error, trend, seasonal.
Excel Worksheet Functions
Excel 2016 Functions: In the following, R1 contains the observed data values (also called historical values) and R2 contains the time values. For Example 2 of Holt-Winters Forecasting, R1 is range M4:M19 in Figure 2 of Holt-Winters Forecasting and R2 can be either range K4:K19 or L4:L19.
FORECAST.ETS(x, R1, R2, seasonality, missing, aggregation) = the forecasted value at the time value x
FORECAST.ETS.SEASONALITY(R1, R2, missing, aggregation) = the seasonality value (1 for no seasonality, 4 for quarterly, 12 for monthly, etc.) based on the data in R1 and R2
FORECAST.CONFINT(x, R1, R2, 1 – α, seasonality, missing, aggregation) = k such that (x-pred – k, x-pred + k) is the 1 – α confidence interval for the forecasted value x–pred at the time value x; the default value for 1 – α is .95.
Function Arguments
Here missing takes the value 0 or 1, where 0 means that any missing data values in the time sequence are replaced by the value 0, where 1 (default) means that any missing data values are determined using linear interpolation (i.e. is the missing R2 value corresponds to an R1 value determined by linear interpolation). Note that the functions are able to do this provided at most 30% of the data is missing.
seasonality takes the value 1 for no seasonality, 4 for quarterly, 12 for monthly, etc. The value 0 (default) means that the value from FORECAST.ETS.SEASONALITY(R1, R2, missing, aggregation) is used.
aggregation is used when range R2 contains elements with the same value. The possible values are: 1 = AVERAGE (default), 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MEDIAN, 6 = MIN, 7 = SUM.
Note that R2 does not need to be in sorted order, but when the elements are in sorted order, the difference between any element and the next element in the sort order (i.e. the step size) should be constant (otherwise this will be considered to be a missing element). The elements in R2 need to correspond to the elements in R1 and so R1 and R2 must have the same number of elements.
As we have seen, the 1 – α, seasonality, missing, aggregation parameters are optional and take the default values described above.
Statistics Function
There is one other function in this suite, namely:
FORECAST.ETS.STAT(R1, R2, stat-type, seasonality, missing, aggregation)
The value outputted from this function depends on the value of stat-type, defined as follows
| param | output | definition |
| 1 | alpha | base parameter |
| 2 | beta | trend parameter |
| 3 | gamma | seasonality parameter |
| 4 | MASE | mean absolute scaled error |
| 5 | SMAPE | symmetric mean absolute percentage error |
| 6 | MAE | mean absolute percentage error |
| 7 | RMSE | root mean squared error |
| 8 | step size | difference between time values |
Note that Excel uses the abbreviation MAE for what we call MAPE (see Forecast Error Statistics).
Forecasting Worksheet
Excel 2016 Forecasting Worksheet: Excel 2016 also provides an automated version of the functions described above. To access this capability, you need to select Data > Forecast|Forecast Sheet.
Like Excel’s Goal Seek and Solver you fill in a dialog box and the output of the various forecast function values is calculated for you along with a chart of the results.
References
ExcelJet(2016) Excel FORECAST.ETS function
https://exceljet.net/excel-functions/excel-forecast.ets-function
Microsoft (2016) Create a forecast in Excel for Windows
https://support.microsoft.com/en-us/office/create-a-forecast-in-excel-for-windows-22c500da-6da7-45e5-bfdc-60a7062329fd
Hi Charles, I am working with weekly level data on a forecast for the upcoming year. I have 4 years of actuals at the weekly level. I’m forecasting at the weekly level as well, and I have used the FORECAST.ETS method in excel.
My question is regarding absolute vs relative references in the ETS formula for the observed values (R1). Is it best practice to hold the range of observed values absolute using the ‘$’? Experimenting with both approaches and the FORECAST.ETS produces different estimates for absolute and relative references. I’ve been using both to provide a ranged estimate but not sure if this is valid. Any guidance you could offer would be appreciated.
Regards,
Brian
Brian,
I would guess that it depends on how you are using the ETS formula. If you use the formula once, it probably doesn’t matter whether you use absolute or relative addressing. It can make a big difference if you need to replicate the formula.
If you send me an Excel file with your data and what you are trying to accomplish, I will try to help you decide on the appropriate addressing in your situation.
Charles
I am on a research to do a time series analysis on the WQI (water quality index) for several rivers in my country starting from 2005 to 2018.
Could you aid me on how can I do my research about that?
Hello Muhammad,
Sure, what questions do you have?
Charles
1-Jul-02 2,258
2-Jul-02 1,594
3-Jul-02 1,078
4-Jul-02 605
5-Jul-02 843
6-Jul-02 619
7-Jul-02 883
8-Jul-02 3,094
9-Jul-02 3,559
10-Jul-02 2,502
11-Jul-02 2,041
12-Jul-02 1,306
13-Jul-02 719
14-Jul-02 981
15-Jul-02 3,531
16-Jul-02 4,003
17-Jul-02 2,627
18-Jul-02 2,114
19-Jul-02 1,556
20-Jul-02 739
21-Jul-02 916
22-Jul-02 2,276
23-Jul-02 2,248
24-Jul-02 2,130
25-Jul-02 1,876
26-Jul-02 1,301
27-Jul-02 661
28-Jul-02 830
29-Jul-02 3,370
30-Jul-02 3,898
31-Jul-02 2,803
How do I forcast for August?
Aman,
The website explains a number of ways of performing such a forecast. See
https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/
https://real-statistics.com/time-series-analysis/arima-processes/arima-forecasting/
Charles
Hi
I have been asked to use scientific method to predict/forecast for post implementation review result.
Post implementation review is a survey to seek end user’s feedback on the overall project delivery. The rating scale is a 5 point scale. Our goal is to achieve 50% for rating <=2.
Attached a set of data set. Management would like to predict what would be the result for the rest of months in FY17. I used excel and add the trendline but I don’t understand it. I surfed nets and there are lots of overwhelming information online. The more I read the more I confuse, like alpha, Std Dev, linear, regression, exponential, etc.
I read through and decided to use the simplest model and something I can understand is the Forecast () function to do the prediction. My colleague questioned me amongst so many models what is the reason you use the forecast () function. I don’t know how to answer them and I am not sure whether this is the right technique to use or not.
MMM-YY Rating<=2 Trend
Apr-16 43% 48%
May-16 50% 48%
Jun-16 43% 48%
Jul-16 43% 49%
Aug-16 75% 49%
Sep-16 45% 49%
Oct-16 57% 49%
Nov-16 60% 49%
Dec-16 33% 49%
Jan-17 29% 49%
Feb-17 50% 49%
Mar-17 58% 50%
Apr-17 57% 50%
May-17 63% 50%
Jun-17 31% 50%
Jul-17 17% 50%
Aug-17 67% 50%
Sep-17 67% 50%
Oct-17 51%
Nov-17 51%
Dec-17 51%
Jan-18 51%
Feb-18 51%
Mar-18 51%
Yinyin,
FORECAST() is the forecast that result from linear regression. I can’t say, without a lot more information, whether this is the right choice. You might have issues of autocorrelation and seasonality to deal with. There are quite a few choices for time series data: Holt-Linear model, Arima, etc. I can’t say which is best for your situation.
Charles
Hi Charles, it seems to me that aggregation is used when range R2, and not R1, contains elements with the same value. In the same way, R2 does not need to be in sorted order and not R1. Do you agree?
Hi Paolo,
Thank you for finding these errors. I have now corrected these mistakes and have added further explanation to make things a little clearer.
I really appreciate your help in making the website more accurate and therefore easier to understand.
Charles