Cost recovery basis

[Caly]

Hi I have to provide pricing that will bake in any cost impacts either up or down. For instance if my cost increased since last year and my margin was 57% but today it is 55%, I need to bake in the lost margin into a new price. How do I do that in excel?

would this be correct
=55% - 57% is a -2%
To recover that in a new price would it be the new price*(1-abs(-2%)

 

[mrshl9898]

Not sure of your goal here, and not sure what bake in means. Can you answer the below?

Say your cost was $100, at 57% margin your sell price was $232.56 and profit was $132.56.

Now your margin is 55% on the sell price, because your costs are now $104.56. And your profit is $128.00.

What sell price do you now want? Are you maintaining margin % or profit $?

 

[Caly]

Hi thank you. The goal is to recover the amount of the change in the margin. So not maintaining the old margin rate but using the current cost that results in the new margin and including the amount of the cost change into the final sell price so that the amount of the increase will be factored in.

in the example above what would the formula be to include the amount of the margin loss?

 

[Caly]

Hi thank you. The goal is to recover the amount of the change in the margin. So not maintaining the old margin rate but using the current cost that results in the new margin and including the amount of the cost change into the final sell price so that the amount of the increase will be factored in.

in the example above what would the formula be to include the amount of the margin loss?

I’m not sure if this is correct but would the margin loss be to take the new margin of 55% minus the old margin of 57%?

or would it be this =(new margin 57% - old margin 55%)/old margin 55%

 

[Caly]

I’m not sure if this is correct but would the margin loss be to take the new margin of 55% minus the old margin of 57%?

or would it be this =(new margin 57% - old margin 55%)/old margin 55%

What would the best way be to do the following:
1. Have the sell price include the lost margin? Would it be the new margin minus old margin which would be -2% and then add that into the sell price? Or would it be this =(the new margin 57% - old margin 55%)/old margin 55%

2. what is the best way to maintain profit $ and the margin %?

 

[mrshl9898]

Hi thank you. The goal is to recover the amount of the change in the margin. So not maintaining the old margin rate but using the current cost that results in the new margin and including the amount of the cost change into the final sell price so that the amount of the increase will be factored in.

in the example above what would the formula be to include the amount of the margin loss?

Ok, so cost is now $104.56, margin is 55% but will change when we refigure the sell price and sell price becomes $232.56 + (104.56-100)?

Original Cost	New Cost	Original Margin	New Margin	Original Sell Price	New Sell Price
100​	104.56​	57%​	55.9%​	$ 232.56	$ 237.12

D2 =(F2-B2)/F2
E2 =A2/(1-C2)
F2 =B2-A2+E2

 

[Toadstool]

Please excuse me if I'm missing something or if I'm just confusing things further but here's what I understand.
Caly had increased costs which took the margin from 57% to 55% and they want to know what price change is needed to maintain profit?

Row 2 is just putting in some dummy numbers to show 57% profit.
Row 3 is calculating how the Costs must have changed to reduce the Margin to 55%.
Row 4 calculates what Revenue would be needed to restore the original Profit.
Row 5 calculates the new Price needed to achieve that Revenue.

Caly.xlsx
	E	F	G	H	I	J	K	L
1	Step	Retail Sales	Price	Revenue	Cost	Margin	Profit	Change
2	Original	2000	6	12000	5160	57.00%	6840
3	What Cost gives 55% Margin?	2000	6	12000	5400	55.00%	6600	55%
4	What Revenue restores Profit?			12240	5400	55.88%	6840	6840
5	What new price?	2000	6.12	12240	5400	55.88%	6840	12240
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=(H2-I2)/H2
K2:K5	K2	=H2-I2
I3	I3	=H3-(L3*H3)
H5,H2:H3	H2	=G2*F2
H4	H4	=I4+L4
G5	G5	=L5/F5

 

[Caly]

Thank you so much. I’m curious and I may be incorrect. But for the margin delta is it correct to take the New margin percent minus the old margin percent so 55% - 57% which is -2%.
Or should it be The new margin minus old margin divided by old margin which is this =(55%-57%)/57% which equals -3.64%?

I need to recover the lost margin due to the cost increase so what I’m doing is taking the old price and applying the lost margin to factor in the cost recovery to the price and I’m doing this formula
=old price*(1+abs((old margin 55% - new margin 57%))

but should it instead be
=old price*(1+abs((new margin 57% - old margin 55%)/old margin 55%))

what is the difference? I know doing the old minus new divided by old is a higher rate than just the old minus new but what is the proper way to explain to someone?

 

[johnnyL]

If you are referring to profit margin...

YourCostOfProduct/(1-DesiredProfitMargin) = SellingPriceToAchieveDesiredProfitMargin

Example to get 57% Profit Margin:
YourCostOfProduct/(1-.57) = SellingPriceToAchieveDesiredProfitMargin

 

[johnnyL]

If you are referring to Markup

YourCostOfProduct + (YourCostOfProduct x DesiredMarkUp) = SellingPriceToAchieveDesiredMarkUp

Example to get 57% MarkUp:
YourCostOfProduct + (YourCostOfProduct x .57) = SellingPriceToAchieveDesiredMarkUp