Sales Optimization Problem

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

Description:

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

B.

 






Decision-Optimization-for-Sales-.xlsm
Bright01Asked:
Who is Participating?
 
TommySzalapskiCommented:
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.
0
 
TommySzalapskiCommented:
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.
0
 
Bright01Author Commented:
Tommy,

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,

B.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TommySzalapskiCommented:
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).
0
 
Bright01Author Commented:
Tommy,

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.

B.
0
 
TommySzalapskiCommented:
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.
0
 
Bright01Author Commented:

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.

B.
0
 
TommySzalapskiCommented:
Well, the cutoff point when you should sell with a partner is when profitPartner > profitA + profitB.
But
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.
0
 
Bright01Author Commented:
Tommy,

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

B.
Decision-Optimization-for-Channe.xlsm
0
 
TommySzalapskiCommented:
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.
0
 
Bright01Author Commented:
Thank you! Sorry for the delay....extensive travel schedule.

Great thinking!

B
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.