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".
JoeUser2004, the idea here is to calculate the Training MAPE using different methods and then compare the results. The sample I sent you is just one variation that I am trying out. There's quite a few permutations and combinations one could try. You could have a model with multiplicative or additive seasonality, or multiplicative or additive trend. Then there's also ARIMA. You could also go one step further and decide on either quarterly/monthly/yearly buckets for the seasonality.
Since I am forecasting for 300+ products, it is highly unlikely that there is going to be one configuration that will give me the minimum value for the Training MAPE since each product is going to have it's own demand profile.
Jack6627 wrote: ``Since I am forecasting for 300+ products, it is highly unlikely that there is going to be one configuration that will give me the minimum value for the Training MAPE since each product``
First, you did not acknowledge my remedy for your GRG Nonlinear problem, which is the __Excel__ question that you asked. Did you see that? Is it "working" for you now, insofar as it produces __something__ that seems reasonable?
Second, I forgot that you mentioned "300+ products" before. I suspect that using Solver, especially manually, is not really a good way for you to determine an "optimal" alpha, beta and gamma -- if they are even "optimal" by your measure.
At the very least, Solver can be implemented in a macro. And the macro could be "instructed" to apply Solver repetitively to each of the products. Do you need help with that?
If so, we would need an example Excel file that demonstrates how you would organize sample data (included) for 2 or more products.
Finally, what is the earliest version of Excel that are you working with? Why aren't you using FORECAST.ETS?
My version of Excel does not support that; nor does free (non-premium) Excel Online. But I believe that FORECAST.ETS calculates alpha, beta and gamma internally. So there might be a different (better?) way to do that.
The wikipage does not provide any real insight, except for "use an optimization tool" (like Solver). You might search for a better explanation. IIRC, MSFT has not documented its internal methods. But I doubt that it relies on Solver per se internally.
Good luck! And thanks for __not__ using FORECAST.ETS, for my own edification. You have motivated me to explore this methodology.