Results 1 to 11 of 11

20090204, 05:12 #1
 Join Date
 Feb 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Solver results are wrong (Excel 2007)
I was hoping to use excel solver to help prepare a company's production plan such as to find the optimum quantity to produce at a certain price in order to maximize profits (minimize losses). I have set up the constraints as per attached excel workfile. But it seems that excel solver does not give the correct optimum results.
In particular, solver gives the optimum qty at 700 giving a loss of $55317. But if the qty is manually change to 650, the result is a loss of only $51091.
Have I missed out any constraints or key settings to solver options? Why doesnâ€™t solver give me the correct result?

20090204, 05:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Solver results are wrong (Excel 2007)
Welcome to Woody's Lounge!
You have set the Solver to find the minimum for H21, i.e. the highest loss!
But this appears to be an illdefined problem  since the rework costs are disproportionately high, there will always be a loss, so the best strategy to minimize loss is to produce nothing!

20090204, 06:22 #3
 Join Date
 Feb 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Solver results are wrong (Excel 2007)
Thanks, you are right but even if i correct that and set solver to find the maximum, it still does not give me the correct results.
On the other hand, the result given by solver does not minimize H21 either, logically minimizing H21 should require full production of 800 to give the highest losses.
I agree that this is an illdefined problem but i believe that solver should be able to give an optimum result still? Can you advise again?

20090204, 07:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Solver results are wrong (Excel 2007)
If you plot loss vs number of pens, you'll see that it is a sawtooth function. The picture below is an exaggerated representation of the chart.
Solver tries to find a local minimum or maximum, not a global minimum or maximum. So it'll always stop at the first "corner" it encounters in the chart.

20090204, 09:06 #5
 Join Date
 Feb 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Solver results are wrong (Excel 2007)
Thanks for pointing that out! I understand much better now. So is there any way we can set the options in excel solver such that it can seek the global maximum/minimum?

20090204, 09:29 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Solver results are wrong (Excel 2007)
No, Solver doesn't work that way. I'd create a chart such as the one I mocked up, you can easily read the global minimum or maximum off the chart.

20090204, 13:48 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Solver results are wrong (Excel 2007)
Here is how I would solve it.
I put the assumptions in one location for "playing". The calculations are done for 0800 pens and then plotted and the max determined. If desired you could go every 5, 10 or 25 pens instead of every pen to reduce the number of calcs...
Note: I think your reworks unit costs are much too high. Based on your presumed values it is best not to make any pens or not rework them. Rework a pen for over $300 when it only costs $30 to make a new one seems counter productive...
Steve

20090205, 06:15 #8
 Join Date
 Feb 2009
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Solver results are wrong (Excel 2007)
Dear Hans & Steve, thanks for your suggestions and advice! =)

20090209, 14:39 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Re: Solver results are wrong (Excel 2007)
Hi Steve
If my wife gave me a pen as a gift and I broke it I would rather pay $300 to have it reworked than have her discover I swapped it for a $30 replacement.
Sometimes, it's not just about the money.
zeddy

20090209, 14:47 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Solver results are wrong (Excel 2007)
But it is about the money when your goal is reducing losses and increasing profits... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
Steve

20090209, 15:00 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Re: Solver results are wrong (Excel 2007)
Love is grand.
Divorce is twenty grand.
I rest my case
zeddy