1. ## Solver (2000)

I asked for some help here before re a file I had created utilizing the Solver. Attached is the file-I can't seem to get it to work-the solution is in the red shaded area.
Any help?
I have reviewed all of the constrraints, and they all appear to be ok.
I dunno...

2. ## Re: Solver (2000)

>> I can't seem to get it to work.

In what way? The solver already seems to have arrived at a solution. Is it incorrect, or not what you expected, or ...?

3. ## Re: Solver (2000)

It appears to be telling me to buy in months when the prices are higher than in other months when there are no other limiting constraints (i. e., August vs. May).

4. ## Re: Solver (2000)

I don't know what causes it, but it seems that Excel can't really solve this. Changing one of the input cells can lead to quite different solutions.

5. ## Re: Solver (2000)

Hans,
Here's a simpler version. Want to take a stab?
I tried going back through old postings to find where we addressed this issue before (sometime last April, I think), but the archive of old posts appear to only go back to May 1.
Jeff

6. ## Re: Solver (2000)

Hans,
Heres another, simpler version. By my analysis, it appears at certain minimum levels of 10/31/04 SCQ, the solution brings in a small amount of August purchases, even if I adjust the price to be higher than October.
Interesting...
Jeff

7. ## Re: Solver (2000)

Sorry-forgot to attach the file.

8. ## Re: Solver (2000)

The problem is in the expression you want to minimize. You round the result of each multiplication such as AprilPurchases*April03NYMEX to 2 decimals. This makes different values for the input cells result in the same end value, so the Solver is tricked into believing that a solution has been found.
If you take out all those ROUND(...,2) functions, the Solver finds a stable solution, both in the workbook you posted today and in the one from yesterday.

9. ## Re: Solver (2000)

Yes-that did the trick, but it is still trying to bring in some August purchases-any idea?
Thanks for your help; its funny what I thought was an innocent add-in (i. e., rounding) would cause!
Jeff

10. ## Re: Solver (2000)

My understanding is that the solver is not a very "smart" algorithm for non-linear problems.

Let me illustrate. Imagine your problem has just 2 variables and you want to minimize some objective function. If you plotted all possible combinations of your 2 variables on an x and y axis and the corresponding value of the objective function on the z axis, you would get a surface. One can imagine a complex objective function such that the surface would have all kinds of peaks and valleys. Now imagine that you drop a marble onto the surface. Naturally, it will fall into a valley, but it may not fall into the lowest valley. It depends on where you drop the marble. Similarly with the Solver, the initial values in the variable cells determine where Excel "drops the marble". Excel then changes the value of the variables by small amounts to try and find "a direction" to move to find a better answer. If the solver gets stuck in a valley and can't find a better solution nearby, it stops even though there may be a lower valley somewhere else. By using the ROUND function, you've essentially created a surface with lots of plateaus so Solver can't find a direction to move to a better answer.

Of course, if you extend this analogy to n variables, the surfaces get complex very quickly. I have often found that the solver gives radically different answers depending on the initial values of the variables.

For more info, check out http://www.frontsys.com This is the company that wrote the solver routine for Excel. They offer enhanced Solver products if you're willing to pay for them.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•