Pivot table Calculated field for Projected Year-End based on most recent month value

[joieroxx]

Hello. I have a pivot table showing monthly revenue for fiscal year beginning Jul ending the following Jun. How would you add a calculated field to show projected year-end total based on Jul-Sep total, plus the projected revenue from Oct to Jun, based on the Sep value, assuming the Sep value would remain the same for the rest of the fiscal year? Thanks so much!

 

[jdellasala]

You should go to your profile and define what version of Excel you're using. For example I have no idea if this will work.
For MS 365, select your data (typically two columns like a Date and Values), select the data, then go to Data and click Forecast Sheet button. Select your options and click Options at the lower left to change the options, and then click the [Create] button. A new Worksheet will be created like this:

Book1
	A	B	C	D	E
1	date	value	Forecast(value)	Lower Confidence Bound(value)	Upper Confidence Bound(value)
2	1/1/2020	258.682
3	2/1/2020	259.007
4	3/1/2020	258.165
35	10/1/2022	298.062	298.062	298.06	298.06
36	11/1/2022		299.0797885	296.75	301.41
37	12/1/2022		300.3349557	296.13	304.54
38	1/1/2023		301.5901229	294.87	308.31
39	2/1/2023		302.84529	293.15	312.54
40	3/1/2023		304.1004572	291.05	317.16
41	4/1/2023		305.3556243	288.61	322.10
42	5/1/2023		306.6107915	285.87	327.35
43	6/1/2023		307.8659586	282.86	332.88
44	7/1/2023		309.1211258	279.58	338.67
Sheet2

Cell Formulas
Range	Formula
C36:C44	C36	=FORECAST.ETS(A36,$B$2:$B$35,$A$2:$A$35,1,1)
D36:D44	D36	=C36-FORECAST.ETS.CONFINT(A36,$B$2:$B$35,$A$2:$A$35,0.95,1,1)
E36:E44	E36	=C36+FORECAST.ETS.CONFINT(A36,$B$2:$B$35,$A$2:$A$35,0.95,1,1)

Note that I hid rows to reduce the size above. You can then use those values created. The formulas created may be help as well!