I hope there is someone out here that likes solving math problems. I have a math problem where I am attempting to build a "Optimization Model".
I have two products (A and B). There is a ratio of value between the two products (i.e. for each dollar unit sale of B results in 3 dollar units of A. I have two types of Transactions (Large and Small). Each type has different margins (i.e. 25% for A and 85% for B). There are 4 scenarios.
1.) Product A can be sold by itself
2.) Product B can be sold by itself
3.) Both Product A and Product B can be sold together
4.) Product B can be sold by a Channel Partner but if so, there is no sale of Product A (i.e. the Channel Partner sells a substitute product in each case they sell a Product B.)
There are odds associated with each type of transaction.
Now for the optimization question;
At what price point (Cells F5 and I5), is it better to sell a deal (due to optimizing Gross Margin) in both Large and Small Deals, by letting a Partner sell it instead of selling it direct (In Red)?
I'm using excel so the variables can change and the optimization price point should change.
Any help or insight into this optimization problem is appreciated. Thank you in advance.