Results 1 to 11 of 11
  1. #1
    New Lounger
    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?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #3
    New Lounger
    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 ill-defined problem but i believe that solver should be able to give an optimum result still? Can you advise again?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.
    Attached Images Attached Images
    • File Type: png x.png (5.7 KB, 3 views)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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
    Attached Files Attached Files

  8. #8
    New Lounger
    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! =)

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    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
  •