# Thread: Solver results are wrong (Excel 2007)

1. ## 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?

2. ## 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 ill-defined 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!

3. ## 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 ill-defined problem but i believe that solver should be able to give an optimum result still? Can you advise again?

4. ## 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.

5. ## 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?

6. ## 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.

7. ## 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 0-800 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

9. ## 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

10. ## 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

11. ## Re: Solver results are wrong (Excel 2007)

Love is grand.
Divorce is twenty grand.

I rest my case

zeddy

#### Posting Permissions

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