Solved

Sales Optimization Problem

Posted on 2011-09-25
11
414 Views
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".  

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
0
Comment
Question by:Bright01
  • 6
  • 5
11 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
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.
0
 

Author Comment

by:Bright01
ID: 36596412
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
 
LVL 37

Expert Comment

by:TommySzalapski
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).
0
 

Author Comment

by:Bright01
ID: 36598507
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
 
LVL 37

Expert Comment

by:TommySzalapski
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Bright01
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.

B.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36601838
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
 

Author Comment

by:Bright01
ID: 36708363
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
 
LVL 37

Accepted Solution

by:
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.
0
 
LVL 37

Expert Comment

by:TommySzalapski
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.
0
 

Author Closing Comment

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

Great thinking!

B
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VBA Code Mixed Combining Two User Forms 7 38
second highest value difference 11 24
TT Status Chang 3 32
TT Auto DashBoard 4 33
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now