SOLVED

Excel Solver

Copper Contributor

Hi guys,


I just joined this community and I am hoping to get some knowledge regarding Excel Solver - I have a specific problem that I am hoping someone can provide some useful insights.

 

This is the scenario:

 

Let's say that I am in advertising and that I get an order of 100 000 US. I can distribute that money however I want on ten different arenas where the add will show. All of these arenas have a different percentage regarding how much money they will get from showing the add (COGS%), and all of these arenas also have something called "minimum guarantee", meaning that we have to pay them this amount if (Revenue * COGS%) < Minimum Guarantee (please see the formulas in order to understand the problem better). If we choose to spend 0 US on an arena, we still have to pay the minimum guarantee.

 

First of all, is this a Simplex LP problem or is it GRG Nonlinear or Evolutionary?

Second, I have tried to set the changing variables to the percentage figures in column K, the constraint is of course that the sum of these percentages should equal to 100%.

 

Third, the objective is to maximize the gross profit in cell Q16.

 

I tried to run Solver (GRG Nonlinear) with "Use Automatic Scaling" ticked and then I ran it again having it unticked - I got different results, can someone explain whether it should be ticked or not?

I also tried to change the changing variables to column L, instead of changing the percentages it should change the Revenue amount (I thought it would provide the same answer but it didn't), with the constraint that the SUM of "Revenue" should be = 100 000 US.

Lastly, could someone please explain to me how I find the optimal solution for this example if it is even possible?

Kind Regards,

 

Robin

 

 

9 Replies

@Robin_Lindstrom

 

1st of all your problem is non-linear hence, if is not convex there is no global solution. But since the number of decision variables is 10, if you run GRG Non linear with "Multistart" option checked use a population of 100,  you should get a solution which is near optimal. 

After than you can run Evolutionary to see if you might find a better point. Just remember to bound the percentages in the arenas between 0 and 1 (two additional constraints).

 

I noticed your problem seems to have an optimal solution of 62,000. but you also have multiple optimal values, so there exist a number of values that gives the same optimal solution. Add extra constraints if you wish to get the most desired one.

 

hope this is useful

 

Hi @Ramiz_Assaf 

Thank you so much for answering!

This might be a stupid question, but how do you know if a problem is linear or non-linear, and what does convex mean? Also, I have no idea what "multistart" does and why you set the population to 100 :)

My other question is this, instead of using the column K as changing variables, let's decide to use column L instead (100 000 should be divided between these 10 arenas instead of using percentages). Why does Solver come up with another solution, even though the objective is the same and the only change I have done is for it to use amounts instead of percentages. I do get 62 000 when I run it by percentages, but if I switch I get 54 206?

Thanks again for taking the time and sorry for my lack of knowledge, I have been trying to search for information regarding Solver and what everything means, I just can't seem to find this stuf.

 

Kind regards,

 

Robin

 

 

@Robin_Lindstrom 

 

It is convex when it has one summit, and we know there is only one. 

For example we know that the highest summit on planet earth is mount Everest. Now if you visit some other plant, and you find a very high summit, can you be sure it is the highest summit?? The answer is no. Convex (actually Concave in the case of maximization) that you know that your planet has only one summit and if you find it, it is the highest point (does this make sense?)

 

Now, if your function is not convex, you need to visit every mountain you see. if you start from one area you will only see few mountains. Multistart lets you start in different locations so you will be seeing more mountains and evaluate their heights (most probably one of them will be the max) population 100 is just a number, you can play with it, the larger the number of variables you need a bigger number obviously.

 

Why your formula is not linear, because of the max function I guess. It is ok, to keep non linear functions since you have limited number of variables.

 

Your final point why you have many solutions. if you have limited constraints, there will be multiple points satisfying the optimal solution. if I ask you for two number making the number 4. you have many different answers all of them are correct. But if I say the two numbers should be different, the options decreases.. and so on.

 

hope this makes sense

@Ramiz_Assaf 

 

I yet again really appreciate your explanations, and it makes things a lot more clear.

 

I did however run into a problem. I have attached a new file with more arenas, some of them have a guarantee and most of them do not. I created this example for me to be able to by myself figure out what the optimum solution is, even before running solver. You can find the optimum allocation of revenue in column "I", this gives me a gross profit of 9 323 810:- and a Gross Margin of 42.38%.

 

However, I have tried running solver with so many different options ticked/unticked, but I never get to that number. However, if I run Solver multiple times (always saving the "new numbers" it spits out as input when I run Solver again) it eventually after 8-12 times gets pretty close, but not all the way :)

I would really appreciate if you could have I look and provide an answer, if possible, because using this in my organization, how can I ever be sure that I get the optimal solution? And also, do I really have tu run it multiple times in order to get "close enough" instead of changing the parameters in someway and get to the answer faster?

Lastly, if Excel is limited regarding this and Solver cannot "fix it" do you suggest any other program that can?

Once again, thank you so much for taking the time.

 

Br,

 

Robin

best response confirmed by Robin_Lindstrom (Copper Contributor)
Solution

@Robin_Lindstrom 

 

Ok so now I understood your problem and the good news, it can be linearized 

remove the max(Revenue * CoGS, Minimum guarantee )  only revenue * CoGS

 

then add a new constraint 

revenue * CoGS >= minimum guarantee

 

it makes your problem linear and you will always get an global optimal solution.

I think you obtained your solution with correct thinking, trying to revenue more than the minimum guarantee then getting more revenue from areas with lower CoGS rate.

 

Hope this is useful

 

Finally, the problem is not with the software, if the problem is not convex or concave, no one can guarantee an optimal solution.

@Ramiz_Assaf 

 

Hi again!

I did exactly what you said, and that it awesome, it works! 

Does this mean I should run "Simplex LP" instead of "GRG Nonlinear" ? I tried both, both provided the same result so does it even matter?

Br,

 

Robin

@Robin_Lindstrom 

 

If it linear, use simplex, I am not sure if GRG will always give an optimal solution if the problem is linear. I have to read about that. But why to take chances, use Simplex.

 

Best of luck

@Ramiz_Assaf 

 

Thanks for all your help, legend!

I really learned a lot!

Best Regards,

 

Robin

@Robin_Lindstrom 

 

Thanks for the interesting problem

1 best response

Accepted Solutions
best response confirmed by Robin_Lindstrom (Copper Contributor)
Solution

@Robin_Lindstrom 

 

Ok so now I understood your problem and the good news, it can be linearized 

remove the max(Revenue * CoGS, Minimum guarantee )  only revenue * CoGS

 

then add a new constraint 

revenue * CoGS >= minimum guarantee

 

it makes your problem linear and you will always get an global optimal solution.

I think you obtained your solution with correct thinking, trying to revenue more than the minimum guarantee then getting more revenue from areas with lower CoGS rate.

 

Hope this is useful

 

Finally, the problem is not with the software, if the problem is not convex or concave, no one can guarantee an optimal solution.

View solution in original post