[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

So, what's the margin?

Posted on 2011-03-07
9
Medium Priority
?
231 Views
Last Modified: 2012-06-27
I have some knowns and unknowns and can't seem to do the calculus.  So, I'm going to take a break and in the meantime see if I can't get some help.

I have the following data:  Volume, Total Margin, % Sold thru Channel A, and % Sold thru Channel B.  I need to determine unit and total margin for both Channels. I also know that my $/unit margin for Channel A is twice what it is for Channel B.

E.g., Volume = 100 units, %A = 50% @ $.50/unit, %B = 50% @ $.25/unit.  So 50 units sold Channel A @ $25 and 50 units sold Channel B @ 12.5, for a total Margin of $37.5

Now, if I have Volume = 100 units and %A = 75%, %B = 25% and I know my total margin is $100, what is my margin for Channel A and Channel B on a per unit and total basis?

Can someone create a formula based on %A and %B sold for X Total Volume and Y Total Margin, what would be the unit and total margin for Channel A and for Channel B?  What if the margin relationship changes and that relationship is Z?  E.,g., now unit margin A = 2 * unit Margin B

Thanks -

Dave
0
Comment
Question by:dlmille
[X]
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
  • 3
  • 3
  • 3
9 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35064061
> Now, if I have Volume = 100 units and %A = 75%, %B = 25% and I know my total margin is $100, what is my margin for Channel A and Channel B on a per unit and total basis?

It's not solvable for two unknowns. There are an infinite amount fo solutions

Until you fix say A or B margin, then you can't calculate the other

Cheers
Dave
0
 
LVL 24

Accepted Solution

by:
jimyX earned 1600 total points
ID: 35064841
Since there is a relation it's possible and solvable:

Volume = 100 units -> let's assume it's n
%A = 50% @ $.50/unit -> let's assume x = $/unit A
%B = 50% @ $.25/unit -> let's assume y = $/unit B

x * (n * %A) + y * (n * %B)
0.5 * (100 * 50%) + 0.25 (100 * 50%)
25 + 12.5
= 37.5

Total Margin is $37.5

Volume = 100 units -> n
%A = 75% @ x $/unit
%B = 25% @ y $/unit
Total Margin = $100
(x * (n * 75%) + (y * (n * 25%)) = 100
The relationship between x and y is: x = y + y
((y+y) * (n * 75%)) + (y * (n * 25%)) = 100
((y+y) * (100 * 75%)) + (y * (n * 25%)) = 100
((y+y) * 75) + (y * 25) = 100
75y + 75y + 25y = 100
y(75+75+25) = 100
y = 100 / 175
y = 0.57142857142857142857142857142857
Then y + y = x = 1.1428571428571428571428571428571

Now let's apply this on the first example:
Volume = 100 units -> let's assume it's n
%A = 50% @ $.50/unit -> let's assume x = $/unit A
%B = 50% @ $.25/unit -> let's assume y = $/unit B

(x * (n * 50%) + (y * (n * 50%)) = 37.5
x = y + y
((y+y) * (100 * 50%) + (y * (100 * 50%)) = 37.5
((y+y) * 50) + (y * 50) = 37.5
50y + 50y + 50y = 37.5
y(150) = 37.5
y = 0.25
x = 0.25+0.25
Then (x=0.5) (y=0.25)
0
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 1600 total points
ID: 35065098
here is the formula:
Math.xls
0
Independent Software Vendors: 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!

 
LVL 42

Author Comment

by:dlmille
ID: 35065360
Thanks - it looks right but let me validate tomorrow when I get back to work and apply "Real" numbers :)  Brain is fried tonite!!!

Much Appreciated - will try to close out or ask questions, soonest!

Would you kindly modify the formula if the relationship between A and B unit margin is X?

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35065947
> I also know that my $/unit margin for Channel A is twice what it is for Channel B.

Had missed this rather important line.
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 400 total points
ID: 35066033
For a generica solution:

A*Margin1+B*Margin2 = C
which in your case
1) A*Margin1+(1-A)*Margin2 = C
2) MArgin1 = X*Margin2

so putting 2 in 1

A*Margin1+(1-A)*Margin1/X = C

rearranging gives your generic solution for Margin1
Margin1 = C/(A+(1-A)*1/X)

so for example 1
Margin1 = 100/(75%+(1-75%)*1/2)
             = 1.14
Then using (2)
Margin2 =1/X * Margin 1
            = 1/2 *1.14
            = 0.57


Dave
0
 
LVL 42

Author Comment

by:dlmille
ID: 35066059
@jimyX - Sorry, I only just now looked at your written formulas.  Appears the relationship between A & B margin is readily apparent in your formulas.

Will revert tomorrow.


If A margin is a multiple of B margin (written AmultB), and A sales volume is written Avolume, B sales volume is wrtten Bvolume then
A unit margin = B unit margin X AmultB

B unit margin in the spreadsheet = =B30/(((B24*B25%)*AmultB)+(B24*B26%))

So... B unit margin = Total Margin / ((Avolume * AmultB) + (BVolume))

Correct?

Dave
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35070521
Exactly.
0
 
LVL 42

Author Closing Comment

by:dlmille
ID: 35071248
JimyX was first and complete with spreadsheet.  brettdj helped see the same approach in a different light.  Excellent collaboration and thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

649 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