• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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
0
dlmille
Asked:
dlmille
  • 3
  • 3
  • 3
3 Solutions
 
Dave BrettCommented:
> 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
 
jimyXCommented:
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
 
jimyXCommented:
here is the formula:
Math.xls
0
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!

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

Featured Post

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.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now