margin calc

  • Thread starter Thread starter eggman
  • Start date Start date
E

eggman

This may be more of a math question than an excel question, but I am sure
many of you will be able to provide a little insight on this calculation.

I have a spreadsheet that has the following values/formulas in these cells
B5-B12 is current pricing/costs and various rebates/discounts are before
price change
C5-C12 is what pricing/costs and various rebates/discounts will be after
price change

B8: 2%
B10: 0%
B11: 0%
(List Price) C2: 718.69
(% discount of list before price change) C5: 78.46
C7: =((100-C5)*$C2)/100
C8: =C7*-$B8
C9: =SUM(C7:C8)
C10: =C9*-$B10
C11: =C10*-$B11
C12: =SUM(C9:C10)
(% discount of list after price change) E5: 69
E7: =((100-E5)*$C2)/100
E8: =E7*-$F8
E9: =SUM(E7:E8)
E10: =-E9*$F10
E11: =-E9*$F11
E12: =SUM(E9:E11)
E14: =($C12-E12)/$C12
F8: 1%
F10: 20%

Allright, if F11 is 0%, the margin calculation in E14 is -16.31%. If I
change F11 to 12.5%, E14 becomes 1.86%. F11 is a rebate % we are hoping to
receive, but it is not guaranteed. I am not a math wiz, so if I am losing
16.31% on a customer without a rebate, how am I making 1.86% with a 12.5%
rebate? Simple me expected it to be around -3%. I promise this is not a
homework assignment, and I believe it has to do with the aggregate effect a
percentages have on the total value. I know excel is calculating it
correctly, I would just like to be able to explain it better to my boss.

TIA to anyone that can help me.
 
Let's call colmn C, Current Scenario.
Let's call column E, New Scenario.

Current Revenue (C12) is $ 151
When New Discount% (F11) is 0%, New Revenue is $176
This means you make more money with the NewRevenue, therefore, in E14, you
should have a positive number: +16% instead of -16%
So your formula in E14 should be : =(E12-$C12)/$C12
instead of : =($C12-E12)/$C12

With this change in place, enter 12.5% in New Rebate (F11), you should make
less money.
Current Revenue is still : $ 151
and New Revenue becomes: $148 <-- which is less than prior $176
Now, you make less money with the New Revenue than with Current Revenue,
therefore the difference % (E14) should be a negative number... and you get :
-2%
This makes sense.

I hope this helps,
Sebastien
 
Thanks for the response sebastienm, but the 12.5% rebate would increase our
margin because it is a rebate to us from the vendor to support selling this
customer in a highly competitive market. Anyone else able to elaborate on
my initial question?

TIA
 
Back
Top