Sales Optimization Problem

Posted on 2011-09-25
Last Modified: 2012-05-12
EE Professionals,

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.



Question by:Bright01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 37

Expert Comment

ID: 36596401
How's your calculus? If you can get a function where the profit is the output of the function and all your inputs are variables, then all you need to do is get the derivative of the function and check all places where it equals 0. The max value where the derivative is 0 is your answer.

Author Comment

ID: 36596412

Thank you very much for the reply.  Unfortunately Calculus wasn't one of my stronger subjects.  The model that I provided has profit as a formula (i.e. Gross Rev. X Gross Margin) --- (my example says Net Profit but it should read Gross Profit).  On challenge I see is how to treat "the odds".  Do I simply multiply by the odds to get a value that can then be compared?  Can you take a look at the simple model I put together and see if it is calculus able?

Much thanks,

LVL 37

Expert Comment

ID: 36596890
Yes, the value of a scenario is the probability times the profit.
So all the numbers in the sheet are constants except for the Gross Revenue? And the Gross Revenue for A is always thrice that of B? Okay. I get that part.

I'm not super clear on the scenarios and how they work. Are we mostly concerned with rows 10 and 12? I don't get what the penalties associated with option 4 are.

Oh, you shouldn't need calculus. I misread the main question. You aren't trying to maximize a function, you are trying to find the point and which one function becomes greater than another. For that, all you need is algebra. Just built the two functions and solve for the intersection(s). These are the points at which they cross (or just touch if the example is contrived enough but you can check for that easily).
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36598507

Thanks again for the direction here.  

The numbers that are variable include; Gross Revenue of Option B, all of the margin assumptions, and all of the "Odds" on each sceneario.  I've done this because I want to model different scenerios.  So I'm trying to solve for the intersection point (where it crosses) between the 4 options with the key point being the 4th; where the value of "Using a Partner", instead of a Direct Sales person is higher (optimized).  That's the decision point.

Is that clearer?  It sounds like I'm going to have to blow off the dust on my algebra and my calculus books..... aaaggghhhhhh.

LVL 37

Expert Comment

ID: 36599800
Yes. What is still not clear is what the differences are between using a partner and selling directly.

Anyway, all you will need is simple algebra. If you can solve an equations, then you are good.

Here's what you do. Set up functions like

profitDirect = GrossRevLargeB*LargeMarginB*DirectOdds + whatever the other stuff does
profitPartner = GrossRevLargeB*LargeMarginB*PartnerOdds + whatever

Then the price point when you need to switch is when profitDirect = profitPartner for the same price.
So set the two equations as equal and solve for GrossRevLargeB. If there is no solution or the solution comes back negative, then one option is always better.

I would set the equations up right now, but I still don't understand exactly what all the scenarios mean.

Author Comment

ID: 36601485

Good question and I should have pointed it out stronger.  If you look at the spreadsheet you will see;

Important Note:  If a Partner sells Product B, there is no Revenue for Product A because the Partner receives it directly from Client

That is the difference. And that's why it's so important to look at the margins.  Because Product B has such high margins compared to Product A, there is a point at which, be it large or small deal size, where letting the Partner sell B and give up the Rev./Margins on A, makes sense.  I just don't know how/where to put the formula in for that in order to "play" with the Revenue, Margins and Odds in order to see where the optimization point is.

Thanks for sticking with me on this.  Any other questions are welcome.

LVL 37

Expert Comment

ID: 36601838
Well, the cutoff point when you should sell with a partner is when profitPartner > profitA + profitB.
profitPartner = priceB * marginB * oddsPartnerB

profitA = priceA * marginA * oddsDirectA
profitB = priceB * marginB * oddsDirectB

but priceA = 3 * priceB so

profitA = 3*priceB * marginA * oddsDirectA

So we sell with a partner when profitPartner > profitA + profitB.or when

priceB * marginB * oddsPartnerB > 3*priceB * marginA * oddsDirectA + priceB * marginB * oddsDirectB

A little algebra rearranging gives

priceB * (marginB * oddsPartnerB) > priceB * ( 3*marginA * oddsDirectA + marginB * oddsDirectB)

So you can divide both sides by priceB since it is positive and > 0 and you get that you should sell by a partner when
oddsPartnerB >  3*marginA * oddsDirectA + marginB * oddsDirectB

So the price doesn't actually matter at all as long as priceA is a function of priceB. Only the margins and odds matter.

Author Comment

ID: 36708363

"Brilliant" --- You are truely mathmatically "gifted".  But I have a related question and do not want to lose your talent here.  I am happy to accept this solution but I must ask you, is there a simple way to solve for oddsPartnerB, marginsPartnerB and ratioAtoB?  

In other words, the math that you have provided provides me with a "True" or "False"; however, what I was looking for is what is the actual, numerical number where given the margins and odds, at what odds point should a Partner be used? In other words, once you plug in all of the assumptions, what is the formula(s) that show what odds, margin and ratio are the turning points for the decision to go with Partner over Direct?  I've attached a new spreadsheet with your formula embedded.  I hope you too can use this model for something in the future.... it's just what I needed to get to some critical decision points.

Hopefully this is a simple rearrangement of the math/logic you have already provided.  Let me know if you want me to close out this question and ask a related one.

Very thankful for your thoughts on this......again "brilliant".

LVL 37

Accepted Solution

TommySzalapski earned 500 total points
ID: 36710358
I lost a variable when I copy/pasted. That equation should be
marginB * oddsPartnerB > 3*marginA * oddsDirectA + marginB * oddsDirectB

The cutoff point will be when
marginB * oddsPartnerB = 3*marginA * oddsDirectA + marginB * oddsDirectB

Since any change will push one option above the other. So if that equation comes out to be true, then either option is exactly as profitable as the other.

There isn't just one set of numbers that make that work. There are an infinite number of solutions to the equation. You need to just use the equation as a guide.
Just plug in numbers for the variables in the equation and then it will tell you what the other numbers would need to be to match. For example, if you knew the margins were 33.33% for A and 50% for B then you would have
.5 * oddsPartnerB = 3*.3333 * oddsDirectA + .5 * oddsDirectB
oddsPartnerB/2 = oddsDirectA + oddsDirectB/2

So that would give the cutoff point and you would sell with a partner if
oddsPartnerB/2 > oddsDirectA + oddsDirectB/2

Since you know all the variables are non-zero and positive, then you can add/subtract/multiply/divide both sides of any of the equations by anything to move variables around.
LVL 37

Expert Comment

ID: 36710379
By the way, after you accept a solution a link appears on that question that says 'ask a related question.' Any time you click that, it alerts all the experts in the original question about the new question so you won't lose anyone by closing out a question. It's a really good feature of the site.

Author Closing Comment

ID: 36902639
Thank you! Sorry for the delay....extensive travel schedule.

Great thinking!


Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question