# So, what's the margin?

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
LVL 42
###### Who is Participating?

Commented:
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

> 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

Commented:
here is the formula:
Math.xls
0

Author Commented:
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

> 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

For a generica solution:

A*Margin1+B*Margin2 = C
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

Author Commented:
@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

Commented:
Exactly.
0

Author Commented:
JimyX was first and complete with spreadsheet.  brettdj helped see the same approach in a different light.  Excellent collaboration and thanks!
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.