If statement or something better?

[shestheceo]

I have monthly figures (actuals) by month and I can't figure out the best formula to do the following:

Create a monthly forecast assuming a 1% annual increase in units and a 3% annual increase in costs starting in April.

I started with an If statement but it's getting a bit long... any better formula ideas?

 

[6StringJazzer]

Are you increases one-time annual increases, or allocated monthly (i.e., 1/12% per month for units and 1/4% per month costs)?

 

[shestheceo]

They are allocated monthly.

 

[6StringJazzer]

There are several ways to do forecasting. From what you've described, you could simply use your increase figures to forecast a month based on units/cost of 12 months prior. So your October 2023 forecast will be (October 2022 units x 1.00083) and (October 2022 cost x 1.0025). So you simply apply your increase percentage year-over-year.

But to give you a formula that would work for your file I would have to see how your data is set up. Unfortunately you cannot attach a file here but do you have access to a cloud service (Google Docs, Dropbox, etc.) where you could share a link to a sample file? Also the site provides an Excel add-in to copy a range and paste it into a post.

Another way is to smooth out the actuals using a rolling average instead of just one months of actuals.

Another way is to determine a trend line for the actual data and project that into the future linearly. If your business is seasonal, then that would have to be taken into account.

Here is an example using my first method.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Actuals													Forecast
2		9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023
3	Units	13,509	13,866	13,148	13,292	11,523	13,248	10,312	14,001	13,901	10,551	13,220	10,445	13,321	13,878	13,159	13,303
4	Costs	$193,448.88	$196,758.54	$156,987.12	$157,908.96	$149,914.23	$183,749.76	$139,830.72	$198,674.19	$179,044.88	$144,021.15	$186,798.60	$129,622.45	$169,043.49	$197,250.44	$157,379.59	$158,303.73
5
6	Escalation
7	Annual	Monthly
8	1%	0.083%
9	3%	0.250%
Monthly

Cell Formulas
Range	Formula
C2:Q2	C2	=EOMONTH(B2,0)+1
O3:Q3	O3	=C3*(1+$B$8)
O4:Q4	O4	=C4*(1+$B$9)
B8:B9	B8	=A8/12