Forum Discussion
Excel Solver
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.
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
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
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
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.
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