- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2007 August
- Help with formulae

Subject: | Help with formulae |

Posted by: | Pivot Man |

Date: | Wed, 15 Aug 2007 |

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

- Re: Help with formulae posted by JE McGimpsey on Wed, 15 Aug 2007