Smooth monthly trend to achieve assumed average YoY growth

[TelecomBeast]

I am trying to create a formula that will populate 2024 values and show a smooth trend that ends up with the 2024 average being 3% higher than the 2023 average. When I average all of the months for 2024, it needs to come out to $71.71.The 3% assumption would be an input cell.

Is there a way to do this with formulas? I do not want to use any kind of goal seek, as the formula has to be duplicated for many other metrics.

 

[Cubist]

Just multiply each month by 1.03.

 

[TelecomBeast]

Just multiply each month by 1.03.

Sorry, I should have specified that January 2024 can't be lower than December 2023. The trend needs to "jump-off" from December 2023.

 

[Cubist]

-

 

[Cubist]

Try this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Last Year Average	69.63		12/23/2023	1/23/2024	2/23/2024	3/23/2024	4/23/2024	5/23/2024	6/23/2024	7/23/2024	8/23/2024	9/23/2024	10/23/2024	11/23/2024	12/23/2024		Average
2	Rate of Increase	3%		71.00	71.17	71.34	71.51	71.68	71.85	72.02	72.19	72.37	72.54	72.71	72.89	73.06		72.11
3	Target	71.7189
4	Monthly Rate	-99%
5
Sheet1

Cell Formulas
Range	Formula
E1:P1	E1	=EDATE(D1,SEQUENCE(,12))
E2:P2	E2	=D2*(1+$B$4)*100
R2	R2	=AVERAGE(E2:P2)
B3	B3	=B1*(1+B2)
B4	B4	=RATE(12,D2,0,-B3,,)
Dynamic array formulas.

 

[TelecomBeast]

Try this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Last Year Average	69.63		12/23/2023	1/23/2024	2/23/2024	3/23/2024	4/23/2024	5/23/2024	6/23/2024	7/23/2024	8/23/2024	9/23/2024	10/23/2024	11/23/2024	12/23/2024		Average
2	Rate of Increase	3%		71.00	71.17	71.34	71.51	71.68	71.85	72.02	72.19	72.37	72.54	72.71	72.89	73.06		72.11
3	Target	71.7189
4	Monthly Rate	-99%
5
Sheet1

Cell Formulas
Range	Formula
E1:P1	E1	=EDATE(D1,SEQUENCE(,12))
E2:P2	E2	=D2*(1+$B$4)*100
R2	R2	=AVERAGE(E2:P2)
B3	B3	=B1*(1+B2)
B4	B4	=RATE(12,D2,0,-B3,,)
Dynamic array formulas.

Unfortunately it looks like the 2024 average is not coming out to $71.71 when using this method

 

[Cubist]

We're dealing with relatively small numbers and with each step the calculations get rounded so it won't be exact. Maybe someone else can offer you something better. Good luck.