Forum Discussion
Excel solver issues
Jack6627 wrote: ``I tried it and the results are a mixed bag compared my manually built Triple Exponential smoothing.``
I've read that FORECAST.ETS does additive seasonality, whereas your calculations do multiplitive seasonality. But the source is not authoritative (i.e. MSFT documentation). See the comments at the end of https://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-method .
-----
Jack6627 wrote: ``The problem I have with using the function is that I don't have visibility into how the values of the smoothing coefficients are calculated, or the bucketing criteria for the seasonality``
I agree, to an extent. I have a problem with the entire methodology.
As for the smoothing parameters, the FORECAST.ETS.STAT function returns their values. But I, too, am concerned that we don't know how they are derived. OTOH, the STAT function returns several measures of fit, including (S)MAPE. They might give you some insight into the goodness of fit.
I'm not sure what you mean by "bucketing criteria" for seasonality. If you mean the seasonal period (pattern length), the STAT function returns that, as well. But note that it is only necessary if we enter 1 for seasonality. We have the option to enter >1, which is our own knowledge of the seasonal period.
That said, I have a concern about that even when we enter >1. I need to experiement with the algorithm and the Excel function to know if my concerns are valid.
-----
Jack6627 wrote: ``my MAPE values are much closer to optimal with the Evolutionary solver than if I had used the GRG non-linear solver.``
I concur -- but only a little closer (5%). But the real question is: do the different smoothing parameters improve the forecast? (Rhetorical.)
With "your" example, I was surprised by the results with the smoothing parameters that GRG Nonlinear derives; but at least they are in the ballpark. Not true with the parameters that "Evolutionary" derives, despite the smaller MAPE. And as you said initially, who wants to wait 30+ sec for results -- for each of 300+ models?
However, I modified your example because I disagree with some of your formulas. So you and I are not looking at the same results. YMMV. Bottom line: "to each his own".
Regarding your earlier question - yes, I removed the ROUND function within the forecast column and use the GRG non-linear solver and it gives me several local optimal values for alpha, beta and gamma depending on my starting values. I think I will stick with the Evolutionary solver for now.
Yes, I have built the macro and even though it takes time to run, I feel much more confident that my MAPE values are much closer to optimal with the Evolutionary solver than if I had used the GRG non-linear solver. 🙂
Jack6627 wrote: ``I tried it and the results are a mixed bag compared my manually built Triple Exponential smoothing.``
I've read that FORECAST.ETS does additive seasonality, whereas your calculations do multiplitive seasonality. But the source is not authoritative (i.e. MSFT documentation). See the comments at the end of https://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-method .
-----
Jack6627 wrote: ``The problem I have with using the function is that I don't have visibility into how the values of the smoothing coefficients are calculated, or the bucketing criteria for the seasonality``
I agree, to an extent. I have a problem with the entire methodology.
As for the smoothing parameters, the FORECAST.ETS.STAT function returns their values. But I, too, am concerned that we don't know how they are derived. OTOH, the STAT function returns several measures of fit, including (S)MAPE. They might give you some insight into the goodness of fit.
I'm not sure what you mean by "bucketing criteria" for seasonality. If you mean the seasonal period (pattern length), the STAT function returns that, as well. But note that it is only necessary if we enter 1 for seasonality. We have the option to enter >1, which is our own knowledge of the seasonal period.
That said, I have a concern about that even when we enter >1. I need to experiement with the algorithm and the Excel function to know if my concerns are valid.
-----
Jack6627 wrote: ``my MAPE values are much closer to optimal with the Evolutionary solver than if I had used the GRG non-linear solver.``
I concur -- but only a little closer (5%). But the real question is: do the different smoothing parameters improve the forecast? (Rhetorical.)
With "your" example, I was surprised by the results with the smoothing parameters that GRG Nonlinear derives; but at least they are in the ballpark. Not true with the parameters that "Evolutionary" derives, despite the smaller MAPE. And as you said initially, who wants to wait 30+ sec for results -- for each of 300+ models?
However, I modified your example because I disagree with some of your formulas. So you and I are not looking at the same results. YMMV. Bottom line: "to each his own".
- Thanks joe. That article was quite informative.
You say "I've read that FORECAST.ETS does additive seasonality, whereas your calculations do multiplicative seasonality". Yes, the sample model I sent you has the formulas for multiplicative seasonality but as I mentioned before, I have built other models which have additive seasonality components as well and then calculated the MAPE values using Excel Macro. I have also played around with half-yearly, quarterly and yearly bucketing. Turns out, I did not need to forecast for all 300 products, just about 80 of them so I unleashed all possible combinations I could think of 😉
You mentioned that your formulas are different to mine for H-W. Did you mean to imply that my formulas are wrong? Or are there different ways to do H-W's. Are all your formulas different to mine, or just the part where you initialize the model? (cells C13 and D13)This will be my last comment on the subject because (a) we are no longer talking about Excel; and (b) I do not want to risk misdirecting you, due to my inexperience with this methodology.
-----
Jack6627 wrote: ``You mentioned that your formulas are different to mine for H-W. Did you mean to imply that my formulas are wrong? Or are there different ways to do H-W's.``
I cannot speak to "different ways to do" triple ETS. As I said before, all I know about it is based on a quick read of the wikipage ( https://en.wikipedia.org/wiki/Exponential_smoothing ).
But certainly, your forecasting formulas in F44:F48 are incorrect, since they refer to non-existent values in C87 and D87, and they return zero. Arguably, simple typos; easy to fix.
However, I believe that they are not robust enough (the way that I understand they should be) to extend beyond one cycle -- which I think is essential to demonstrate the "correctness" (or not) of the forecast, albeit "in the eye of beholder" (read: subjective).
-----
Jack6627 wrote: ``Are all your formulas different to mine, or just the part where you initialize the model? (cells C13 and D13)``
IMHO, the initialization starts with E7:E12, not just C13:E13. And those formulas do not seem to be consistent with the wikipage equations for initializing the model.
OTOH, I have doubts about the wikipage equations for initialization. So I did not make any changes to your formulas in E7:E12 and C13:E13.
I agree with your formulas in C14:F42. But I would start with a formula in F13, the beginning of the second seasonal cycle. (That is, if your example data were seasonal, which I believe it is not, as I demonstrated previously.)
PS.... Also, I extended the forumlas in C42:F42 down through C48:F48 and started the forecasting paradigm ( F[t+m] ) in C49:F49, because your example has actual data in B43:B48, and I was not interested in backtesting the forecasting paradigm, considering the nature of the data. That resulted in the 2nd chart in my 3rd posting (above).