+ Reply to Thread
Results 1 to 3 of 3

solver - not always optimal solution ?

  1. #1
    Registered User
    Join Date
    05-31-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    47

    solver - not always optimal solution ?

    Hi all
    Using solver add-in for the first time. The aim is to get the maximum possible profit from a price/quantity/marketing spent variation (quite complex, many formulas on the way to the "profit" cell but I hope it should work)

    So far I tried it on easier example and seems ok. What worries is me: if everything works will it always be the optimal solution or only when all conditions are met? The article from MS website that I cite below concluded that it is not always the case ("acceptable solution")?
    Can you try to explain the point from there for me ?

    "What if there is more than one solution to the problem?

    In the previous example, the Solver determined that you could sell 100 mochas, 175 lattes, and 100 regular coffees to reach your sales goal of $700. But you can also reach the $700 mark using a different product mix; for example, you could sell 94 regular coffees, 151 lattes, and 125 mochas to reach $700. (Using this mix, your revenue would actually be $700.75.) So, how did the Solver decide what the optimum product mix would be? The Solver simply started with the current numbers in the variable cells and adjusted them until it found an acceptable solution (subject to the constraints described in the previous example). This is why, if you use different starting values in the variable cells before you run the Solver, you can get different results from a problem with multiple solutions."

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: solver - not always optimal solution ?

    I think most optimization methods get stuck with local minima and maxima. For example,
    Please Login or Register  to view this content.
    Col D is a 4th order fit of cols A & B from LINEST. (You could scatter-plot A2:B6 and add a 4th order polynomial trendline to see it).

    G1 is =SERIESSUM(F2, 4,-1,$D$2:$D$5) + D6

    Set F1 to 0 and use Solver to set G1=0 by varying F1 -- won't work.

    Set F1 = 1.9 and use Solver to set G1=0 by varying F1 -- works fine.

    Frontline Systems (makers of Solver) offers other solutions; www.solver.com
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-31-2009
    Location
    Europe
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: solver - not always optimal solution ?

    Thanks fot that previous reply. Has anybody experienced premium solver?
    Does it also "get stuck" or returning not always optimal solution ?
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1