margin calc

[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.

 

[sebastienm]

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

 

[eggman]

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