Excel solver issues

[Jack6627]

Hi,

I am running into problems with Excel's solver. I am running a time-series forecasting model and trying to optimize the values of the smoothing parameters alpha, beta, and gamma which will minimize my MAPE. The non-linear solver unfortunately does not optimize these values. Attached is a sample part.

Appreciate the help!

[#2]

@Jack6627 

Try Evolutionary instead of GRP Nonlinear

[#3]

Thank you @Hans Vogelaar. This worked. I would like to mention that when I did a couple of runs initially with the Non-linear solver, it would provide me with the optimal values within a few seconds. Not sure what went wrong after that.

The reason I say this is because the Evolutionary solver takes a good 45 sec to 1 min to provide the optimal solution and the idea is to write a macro that eventually forecasts 300+ products so I am worried about run times over here.

Any theories on what could have happened?

[#4]

@Jack6627 

Did you change the model significantly?

Did you change the options for GRG Nonlinear?

[#5]

No, @Hans Vogelaar I did not change a thing. If it's not too much trouble, could you please try to find the optimal values for alpha, beta, gamma using the non-linear solver from the sample file I have attached?

If you're successful, you can post screenshots of your solver settings and I can try and mimic the same.

[#6]

@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.

[#7]

Thank you for the elaborate explanation @Joe User! I had totally forgotten about local optimas and starting solutions. It was a nice refresher. :)

[#8]

@Jack6627  wrote: ``The non-linear solver unfortunately does not optimize these values.``

But later you write:  ``when I did a couple of runs initially with the Non-linear solver, it would provide me with the optimal values within a few seconds.``  Huh?!

When I try GRG Nonlinear with your Excel file, it reports "found a solution" without changing any of the values that I enter for alpha, beta and gamma (B2:B4).  Is that what you mean?

Ironically, much earlier today, I was going to point out that it is unwise to use ROUND in formulas (F14:F48) that affect the objective cell (G49) directly or indirectly (G14:G48).  It causes "discontinuities" in the iterative GRG Nonlinear algorithm.

Often, that causes Solver to fail with an error.  But in this case, the rounding causes the algorithm to mistakenly determine that the initial values in B2:B4 result in an optimal solution.

When I remove the explicit rounding in column F and set B2:B4 to zero initially, GRG Nonlinear seems to derive a very small MAPE in the objective cell.

Of course, I cannot say with impunity that it is an optimal solution.  But it is smaller than the MAPE values that Solver derives when I set B2:B4 to other initial values (the same for all three cells).

[#9]

@Jack6627  wrote: ``I am running a time-series forecasting model``

(Be sure to see my previous response for a solution to you problem with GRG Nonlinear Solver.)

I'm curious: why are you using a triple ETS forecast model, in the first place?

If that is what the assignment requires it, so be it.  And full disclosure:  I know nothing about triple ETS except what I learned from a brief read of the wikipage.

But when I chart the data in B14:B48, I do not see any pattern, much less a seasonal pattern.

And when I forecast 4 cycles (L=6), the forecast has an upward trend that is not apparent in the original data, which has a very slight downward trend.

Arguably, OTOH, forecasting is a crapshoot, and the "correctness" of any forecast is in the eye of the beholder.

[#10]

@Joe User,  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.

[#11]

@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.

[#12]

@joe I am using Excel 2016 but wasn't aware of the FORECAST.ETS function. I tried it and the results are a mixed bag compared my manually built Triple Exponential smoothing. I will hold on to it for now. 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 used but that is purely a personal issue. :)

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. :)

[#13 · Accepted solution]

@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-meth... .

-----

@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".

[#14]

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)

[#15]

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).