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