Determine the best combination of products

Hi all,

I've spent some time searching how to achieve what I'm working on to no avail.

Essentially I'm researching life insurance products. I'm able to purchase policies from multiple providers but I need to achieve a set value across 3 variables. For example variable A = 3,000,000 B = 3,050,000 and C = 400,000.

In researching providers there are very few that provide a single product to cover all 3 and therefore they attract a premium. It became quickly apparent that it was cheaper to insure say A = 3,000,000 & B = 2,950,000 with one provider, B = 50,000 with another provider and C = 400,000 with a 3rd provider.

I'd like to put into a sheet a list of say 40 products with varying values for A, B & C then determine what combination of products will provide the best value.

Any ideas on how I should achieve this? I've attached a dummy sheet as an example.

Why don't you try the Excel Add-In Solver?

Solver is not installed by default when you install excel so if you do not find "Solver" under the "Data" tab you need to install it.

I've set up a small model based on your uploaded file that you can test and added some pictures as well.

Alf

Originally Posted by Alf

Why don't you try the Excel Add-In Solver?

Solver is not installed by default when you install excel so if you do not find "Solver" under the "Data" tab you need to install it.

I've set up a small model based on your uploaded file that you can test and added some pictures as well.

Alf

You sir are a genius! I had come across Solver but couldn't quite grasp how to set it properly.

Thank you so much!

Thanks for feedback! Since you are building a bigger solver model perhaps I should add some solver comments.

In the small model I build the range C4:C10 is set to "Binary". For the model you build I think you should set the constraint to "Integer" as this will allow solver to chose several products from the same provider since a "Binary" setting only gives the solver the choice between 0 or 1.

For the sum of values A, B and C you got 3 choices (range D11:G11). Equal to or less then 3000000, 3050000, and 400000 in this case a not acceptable choice. You can also set it equal to the 3 values but then solver may not find the cheapest solution or perhaps no solution at all. Setting the sum of A, B and C to equal or greater than 3000000, 3050000, 400000 is what I recommend.

Even if you should be insured for more than you planned as long as the premium cost you get is the lowest why worry. Oh course if one has greedy heirs who would love to help one along to a "better" (?) world this could be a problem but this is outside the scope of solver.

For getting a better understanding of solver and its possibilities I would recommend this link.
http://www.solver.com/excel-solver-help
As this seems to fix your problem could you pleas mark thread “Solved” as pr forum rules.

Alf Ps

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved