Formula to recover cost

[Caly]

Hi I have items that had cost increases but how do you add in the cost increase to the resulting price?

Example, Current price is $23.18, current margin is 12.90% but old margin was 24.89% and the Goal is to keep the same margin rate but include the cost increases.

—Old cost was $17.41
—new cost is $20.19

For the margin neutral price I am doing this formula
=round(new cost 20.19/(1-old margin 24.89%),2)
Answer = $26.88



But how can the cost increase be factored in?

Would it be a margin delta using this formula
=(new margin 12.90% - old margin 24.89%)/new margin 12.90%
Answer= -93.06%

Then doing this formula
=round(price $23.18*(1+abs(margin delta 93.06%)),2)
Answer = $44.75

 

[StephenCrump]

But how can the cost increase be factored in?

I'm not clear what your question is?

You have factored in the cost increase - a new sell price of $26.88 retains your required 24.89% margin on sell price:

	A	B	C	D
1		Cost	Margin	Sell
2	Previous	17.41	24.89%	23.18
3	Cost increase	20.19	12.90%	23.18
4	New sell price	20.19	24.89%	26.88
Sheet1

Cell Formulas
Range	Formula
D2,D4	D2	=ROUND(B2/(1-C2),2)
C3	C3	=1-B3/D3

 

[Caly]

Sorry I was thinking because the old cost was $17.41 and new cost is now $20.19 and because of the cost increase Erne margin changed from 24.89 to 12.90, and I was thinking that I had to take the old margin rate difference of 93.96% and apply that to the price.

Price $23.18
Cost today $20.19
Margin today 12.90%
Formula: =(price-cost)/price

Old margin 24.89%
Margin neutral price $26.88
Formula: =(cost today/(1-old margin)

Margin change -92.96
Formula: =(margin today-old margin)/margin today

Final price with cost recovery =$44.73
Formula: =(round(price*(1+abs(margin change)),2)

 

[StephenCrump]

I don't know for sure what answer you want. But if your cost is 20.19, and you sell for 26.88, your margin is 24.89% of sell price, i.e. (26.88 - 20.19) / 26.88, which I think is what is required?

It looks like you're getting muddled thinking about the margin change when the cost increases. Setting aside the dodgy arithmetic, if your cost is 20.19, and you sell for 44.73, will this meet your margin requirement?

 

[Caly]

Thank you. That makes sense, yes the resulting price would be too high and the price already captures the cost increase. Thank you for outlining and providing the formulas… that helped tremendously to see the desired outcome