+ Reply to Thread
Results 1 to 13 of 13

Find the best combination of scenarios

  1. #1
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    Find the best combination of scenarios

    I would like to get from different scenarios the combinations of scenarios which generates the highest revenue given a certain costsbudget.

    I think I will need a Macro that will go through all combinations of scenarios within the budget and than show me the one with the highest revenu. I have tried with formulas but that is not working at all. The number of chosen scenarios in the output is irrelevant. It can be one or it can be all.

    In the attachment I give an example how it should work. I work with up to a thousands scenarios. The scenarios or budget are not fixed. I am completely stuck on this, maybe anyone can help? thanks in advance!!!
    Attached Files Attached Files
    Last edited by plooiman; 08-26-2009 at 08:36 AM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    re: Find the best combination of scenarios

    Maybe . . .
    Create a ratio of cost to revenue and then rank the results and return the top-x?

  3. #3
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    re: Find the best combination of scenarios

    I cannot come up with a ratio that gives me the wanted result. Any ideas?

  4. #4
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    re: Find the best combination of scenarios

    This can be formulated as an optimization problem. Are you familiar with the Solver add-in? Simply set up a cell to be the total revenue, then maximize that cell such that the costs are less than or equal to the budget. If you have the solver I could provide an example.
    Go to Tools - Add-Ins, then check the Solver Add-in.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    re: Find the best combination of scenarios

    There is likely a better method to establish ranking and I'm not certain how this holds up in a larger data set, but the idea is sound enough.

    A percentage of yield (ratio of budge to revenue) is calculated.
    Then, a percentage (ratio of budget consumed to budget alotted) is calculated.
    Last a "rank" is calculated to provide some type of number as an index (sort of) by which to judge the combination of "yield" and "budget consumed" that provides the highest return.

    Maybe someone else has a better idea on how to rank the combination.

    See attached for various formula used.

  6. #6
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    re: Find the best combination of scenarios

    @Palmetto I tried the ranking option but is not working in my example nr 2, the first example gives me indeed exactly what I want. Maybe theres another ratio that will be 100% correct, and will cover the second example?

    @Davegugg I have the solver add in, can you give aN example, that would be great to see it work!

  7. #7
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    re: Find the best combination of scenarios

    Here is your example 1 with the solver. After opening, just go into the solver and hit solve. I set the constraints and optimized value up for you. After you see how it works, take a look at the sheet and see how easy it is to set up great optimization problems like yours.
    Attached Files Attached Files
    Last edited by davegugg; 08-19-2009 at 05:47 PM.

  8. #8
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find the best combination of scenarios

    Yep, I believe this is it!!! I will test this on my orignally data! thanks!!

  9. #9
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find the best combination of scenarios

    Ai..it is not working..theres too much data. Isn't there a macro that will do the same as the solver add-in?

  10. #10
    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: Find the best combination of scenarios

    Entia non sunt multiplicanda sine necessitate

  11. #11
    Registered User
    Join Date
    08-19-2009
    Location
    Amsterdam Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Find the best combination of scenarios

    Are there no VBA codes made that will do the same? Buying a license isn't exactly what I would like to do But if theres no other way..I would have to think of something.

  12. #12
    Registered User
    Join Date
    04-17-2014
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Find the best combination of scenarios

    The scenarios in the example were given randomly by you guys. Assume that we do not what are these scenarios.
    So: - How can we give out what are the most feasible scenarios ?

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Find the best combination of scenarios

    Ai..it is not working..theres too much data.
    Are you problem a linear one? The example give to you by davegugg is linear and excel solver limit is 100 - 200 constraints including binaries.

    I would recommend using "OpenSolver" much faster and better than the excel solver and no limitation on the number of constraints but the problem must be linear!

    You can upload the model (free of course) here:

    http://opensolver.org/

    Works flawlessly with excel and it's easy to install. For excel 2007/2010 user it looks like this after setup.

    Alf
    Attached Images Attached Images

+ 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