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.
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
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