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".
Jack6627.... From the designers of Solver:
``Because the Evolutionary method does not rely on derivative or gradient information, it cannot determine whether a given solution is optimal – so it never really knows when to stop. It knows only that a new candidate solution is “better” than other solutions found earlier. Hence, the Evolutionary method stops and returns a solution either when certain heuristic rules (discussed below) indicate that further progress is unlikely, or else when it exceeds a limit on computing time or effort that you’ve set.``
For "discussed below", see https://www.solver.com/excel-solver-evolutionary-solving-method-stopping-conditions .
BTW, you might indeed get different results each time you run Solver without making "any changes" -- certainly with the Evolutionary method, but even with GRG Nonlinear, in my experience.
First, Solver seems to use the initial value in the objective cell as a starting point. So, if we do not reset the objective cell each time we run Solver, the solution might change based on the solution of the previous run.
Second, I suspect that Solver randomizes some internal parameters (e.g. "step value"), even for GRG Nonlinear. That might not be apparent for all system of formulas, simply as an accident of implementation. I suspect that it depends on the "rate of change" for each iteration.
The point is: You might not be able to duplicate the results of one Solver run, even if you use the same parameters and options.
The second observation is anedotal. It might be wrong or out of date. I came to that conclusion many years ago. Take it with a grain of salt.
Thank you for the elaborate explanation JoeUser2004! I had totally forgotten about local optimas and starting solutions. It was a nice refresher. 🙂