Solver Best Practice

Hey everyone,

I am currently using the excel solver's evolutionary engine to run a fairly tedious but simple pricing model. I have multiple constraints in the model, all of which are integers, with the exception of one final constraint that goes down to two decimal places. With the more complex problems, it usually won't take much longer than a 4 minute solving process, given a 30 second timeout. I have tried to set up the actual calculations as simply as possible for faster problem solving, and I am now working on tweaking the actual solver settings.

I have tried to research best practices but I seem to find mixed results so I was wondering if anyone could lend some insight.

For constraint precision, the default setting is at 0.000001 - technically I shouldn't need to go past 0.01 since my most precise constraint doesn't go past 2 decimals correct? The reason I am skeptical is because the amount of problems that it solves within 30 seconds, 1 minute, and 2 minutes, tends to go down the less precise I make the constraint precision.

In addition to this, any help on what to set for the convergence (default: 0.0001) and population size (default: 100) is much appreciated.

For population size, I deal with simulated metrics based off of 3 sets of inventory that can have prices ranging from 1-999, which can get reiterated for 4 different tiers (it works in such a way that whatever happens in Tier 1, will impact what Tier 2 can yield, so my optimization problem accounts for all this, under the assumption that each tier will be priced lower than the preceding tier).

Thanks in advance,

Paul

You stand a better chance of getting help if you could upload the file with the solver model.

Alf

Hi Alf,

I have uploaded my model, other than the actual maximization problem, hopefully this makes it easier to understand in terms of the numbers I am working with. There are 4 different models that are used, and they can be loaded in from rows 44 and below from Columns T through W.

Hi Paul

Looking at you model I could not see anything that could be changed in order to improve speed. Hopefully some other forum member may do better.

On my PC it took about 2 minute and 40 seconds to find a solution but this is probably a combination of “starting point”, CPU speed and HD speed. In my setup I use a SSD disk for operating system and programs.

What I found a bit strange was the results I got. Starting with initial data 400, 300, 200 and 100 in tier 1, 2, 3 and 4 and only running the 4Tier_model I first got an optimum value of 12532.1 then doing a rerun (4Tier_model) without changing anything optimum value was 14512.8 and further reruns yielded 14511.8, 14511.8 and 15561.3!

Not understanding the mathematics behind the evolutionary solver I do wonder what is the optimal value for your model and what starting point one should use in order to find it?

I've uploaded a png file with "best" solver result.

Alf

Hi Alf,

Thanks for your response. The Evolutionary engine tries a bunch of iterations and I believe it will adjust the variable cells based on directional impact on the figure being optimized. So it will work of a starting point and move figures around from there, and it will mutate the trial at a certain rate (this is configured as the mutation rate in solver options under evolutionary tab). The higher the mutation rate the more diverse the subproblems analyzed and therefore the higher chance you will find a better answer, but the longer it will take to find a solution). The idea is that the evolutionary engine, when dealing with a complex problem, will not find the absolute optimal solution, but will find a very 'good' solution. This is why you got different answers, the 'better' initial inputs you give it to work off of, the more likely it will find an even better answer, that's why running it multiple times can yield better answers.

Anyways, thanks for looking into it, if anyone has any thoughts on ideas that could potentially improve speeds within the solver settings or otherwise, I would love to hear em!

Cheers,

Paul

Perhaps this link could be of interest then.

http://www.solver.com/excel-solver-help

Alf