Help with formulae

[Guest]

I would really appreciate the assistance with this...i am more than a little
frustrated.

Q1 Actuals are in column C
Revenue 1,355,000
COST 832,000
Gross Profit 523,000
Gross Margin 38.6%

Q2 Actuals are in Column I
Revenue 2,090,000
COST 1,162,000
Gross Profit 928,000
Gross Margin 44.4%

Total Change Qtr over Qtr Column J
Revenue 735,000
COST 330,000
Gross Profit 405,000
Gross Margin 5.8%

Column E, F and G represent the changes in results, quarter over quarter
reltaed to Prices Changes (ASP), Unit cost changes (Cogs) and Volume changes
quarter over quarter.

ASP Column E
Revenue 220,000
COST 0
Gross Profit 220,000
Gross Margin 100%

Cogs Column F
Revenue 0
COST -40,000
Gross Profit 40,000
Gross Margin

Column Vol G
Revenue 515,000
COST 370,000
Gross Profit 145,000
Gross Margin 28.2%

column C+E+F+G = Column I

Total Gross margin in Q2 = 44.4% which is 5.8% of a change from Q1 at
38.6%.

Here is the question...How much of the 5.8% relates to each of the columns
E, F and G. That is, how much of the change in Gross Margin is related to
each of ASP, Cogs, and Volume?

X + Y + Z =5.8%

I have a simple spreadsheet to send..

Any assistance would be greatly appreciated.

Thanks

 

[JE McGimpsey]

One way:

X: =E3/$J$3 * $J$4
Y: =F3/$J$3 * $J$4
Z: =G3/$J$3 * $J$4

 

[Guest]

Hi there and thanks for the reply. Yes, weighting the Gross profit is
certainly one way.

I was looking for a more "mathematic" approach that would use either the
Gross Margin, or Profit dollars from each relative to the total...or
somehting like that...I do appreciate the response.

Mathematically, I just cant figure out why I can't back into the 5.8% from
the available information.

Again...appreciate the assistance.

 

[JE McGimpsey]

That's exactly what my suggested formula does - calculates the gross
profit "from each relative to the total" gross profit.

The sum of the three weighted gross margins is the total gross margin
(e.g., in this case 5.8%).

Not sure what you mean by "a more 'mathematic' approach". There's no
need to back into anything when you can calculate it straightforwardly.