Solved

So, what's the margin?

Posted on 2011-03-07
9
214 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 400 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 400 total points
ID: 35065098
here is the formula:
Math.xls
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 100 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

696 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