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
Solved

So, what's the margin?

Posted on 2011-03-07
9
211 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
We are taking giant steps in technological advances in the field of wireless telephony. At just 10 years since the advent of smartphones, it is crucial to examine the benefits and disadvantages that have been report to us.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

829 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