Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Sales Optimization Problem

Posted on 2011-09-25
Medium Priority
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
  • 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).
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Industry Leaders: 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!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

564 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