Solved

So, what's the margin?

Posted on 2011-03-07
9
210 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 41

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 41

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 41

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

772 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