Results 1 to 10 of 10
Thread: Solver (2000)

20031023, 11:58 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Solver (2000)
I asked for some help here before re a file I had created utilizing the Solver. Attached is the fileI can't seem to get it to workthe 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...

20031023, 12:03 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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 ...?

20031023, 12:05 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
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).

20031024, 00:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20031024, 09:38 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.
Thanks for your help,
Jeff

20031024, 10:53 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20031024, 10:53 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Solver (2000)
Sorryforgot to attach the file.

20031024, 10:56 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20031024, 11:09 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Solver (2000)
Yesthat did the trick, but it is still trying to bring in some August purchasesany idea?
Thanks for your help; its funny what I thought was an innocent addin (i. e., rounding) would cause!
Jeff

20031024, 13:18 #10
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Solver (2000)
My understanding is that the solver is not a very "smart" algorithm for nonlinear 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.