Apply different price increases over time

[Caly]

Hi. I am trying to stage out the amount of times to apply price changes that will be increases due to cost increases that have resulted in lost margin. Want to recover the loss and build it into the price over a period of time so that I am not recovering the total amount all at once to avoid sticker shock.

For instance, I may need to stage out the price updates over 3 months but may change it to 2 or 5 for different items.



How can I do a formula that will show the price applying the price increases for this recover over the next 3 months? I need basically 3 columns that will show the price in month 1, month 2, then month 3 that will show the price and margin and recovery to recover the full amount of the cost increase.



Below is what I was thinking but is there a simpler approach?

I looked at the amount of the price change percent % compared to the original price and then divided that out by the amount of times to stage out the price increase.

A	B	C	D	E	F	G	H	I	J	K	L
Base cost	Base price	Base margin	New cost	Margin - base price at new cost	What new price would need to be to regain lost margin	Change % from new price to old price	Amount of months to apply price increase	% increase to apply to get full update	Price 1	Price 2	Price 3
$10	$25	60% =(base price $25 - base cost $10)/base price $25	$35	-40% =(base price $25 - new cost $35)/base price $25	$87.50 =round(new cost $35/(1-base margin 60%),2)	250% =(resulting price recovery $87.50 - base price $25)/resulting price recovery $87.50	3	83.33% =change % 250%/amount of time 3	$45.83 =base price $25 * (1+% increase over time 83.33%),2)	$66.67. =round(base price $25 * (1+ (2 * % increase over time 83.33%),2)	$87.50. =round(base price $25 * (1+ (3 * % increase over time 83.33%),2)

 

[KRice]

I'm not sure how to answer your question about a simpler approach. That may depend on how much information you want to see...which intermediate results are meaningful to you. The upper table is very similar to what you show above. The lower table opts out of displaying the margins and price objectives, instead computing those internally such that the final Price achieved after x markup periods is the price that would be necessary to maintain the same initial Margin, but considering the higher Cost1 of the product. This is intended as a solution for Excel 365 as the SEQUENCE function is used to automatically populate the Price headings and spill the price increases over time.

MrExcel_20220610.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Product	Base Cost0	Price0	Margin0	Cost1	Margin 1wrt0	Price1 to maintain Margin0	Price Change P1wrtP0	Straight Margin Recovery over x Pds	Price 1	Price 2	Price 3	Price 4	Price 5
2	A	$ 10.00	$25.00	60%	$ 35.00	-40%	$ 87.50	250%	3	$ 45.83	$ 66.67	$ 87.50
3	B	$ 12.00	$28.00	57%	$ 18.00	36%	$ 42.00	50%	4	$ 31.50	$ 35.00	$ 38.50	$ 42.00
4	C	$ 10.00	$25.00	60%	$ 15.00	40%	$ 37.50	50%	3	$ 29.17	$ 33.33	$ 37.50
5	D	$ 15.35	$31.99	52%	$ 22.50	30%	$ 46.89	47%	5	$ 34.97	$ 37.95	$ 40.93	$ 43.91	$ 46.89
6
7
8
9
10	Product	Base Cost0	Price0	Cost1	Straight Margin Recovery over x Pds	Price 1	Price 2	Price 3	Price 4	Price 5
11	A	$ 10.00	$25.00	$ 35.00	3	$ 45.83	$ 66.67	$ 87.50
12	B	$ 12.00	$28.00	$ 18.00	4	$ 31.50	$ 35.00	$ 38.50	$ 42.00
13	C	$ 10.00	$25.00	$ 15.00	3	$ 29.17	$ 33.33	$ 37.50
14	D	$ 15.35	$31.99	$ 22.50	5	$ 34.97	$ 37.95	$ 40.93	$ 43.91	$ 46.89
15
16
Caly

Cell Formulas
Range	Formula
F2:F5	F2	=(C2-E2)/C2
G2:G5	G2	=E2/(1-D2)
H2:H5	H2	=(G2-C2)/C2
D2:D5	D2	=(C2-B2)/C2
J1:N1	J1	="Price " & SEQUENCE(1,MAX($I$2:$I$7),1)
J2:L2,J5:N5,J4:L4,J3:M3	J2	=ROUND(SEQUENCE(1,$I2,$C2+($G2-$C2)/$I2,($G2-$C2)/$I2),2)
F10:J10	F10	="Price " & SEQUENCE(1,MAX($E$11:$E$16),1)
F11:H11,F14:J14,F13:H13,F12:I12	F11	=ROUND(SEQUENCE(1,$E11,$C11+$C11*($D11-$B11)/($B11*$E11),$C11*($D11-$B11)/($B11*$E11)),2)
Dynamic array formulas.

 

[Caly]

Thank you this worked fantastic

 

[KRice]

You're welcome...I'm happy to help.