Rolling Forward Lookup formulas (financials reporting)

[mark9988]

Hello,

Hoping to get some much-needed help with some financial reporting automation. Table 1 is basically an input table. Upon receipt of quarterly financial statements, I enter revenue and EBITDA for the corresponding reporting period.

Table 2 is my output summary.....this summary will continue to roll forward indefinitely. I need this output summary to automatically update based on the values entered in table 1. For example, since I haven't received any financials for 09/30/18, my summary page would reflect the latest financial information available, which in this case would be 06/30/18.

Therefore, 5 reporting periods would update automatically.

1) LTM 06/30/18 (most recent four quarters)

2) YTD 06/30/18 (year to date for 2018- in this instance 1Q18 and 2Q18)

3) YTD 06/30/17 (year to date for 2017 - in this instance 1Q17 and 2Q17)

4) FYE 12/31/17 (four quarters for 2017)

5) FYE 12/31/16 (four quarters for 2016)

I hope this makes sense and any help would greatly be appreciated.

Thanks!
Mark9988

Excel 2013/2016
	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Table 1
3					FYE				FYE				FYE
4	Quarter	1Q	2Q	3Q	4Q	1Q	2Q	3Q	4Q	1Q	2Q	3Q	4Q
5	Date	3/31/2016	6/30/2016	9/30/2016	12/31/2016	3/31/2017	6/30/2017	9/30/2017	12/31/2017	3/31/2018	6/30/2018	9/30/2018	12/31/2018
6
7	Quarterly Revenue	$ 45	$ 40	$ 50	$ 30	$ 40	$ 50	$ 45	$ 60	$ 65	$ 70
8	Quarterly EBITDA	$ 10	$ 10	$ 15	$ 12	$ 8	$ 15	$ 10	$ 20	$ 25	$ 30
9
10
11	Table 2
12
13		FYE	FYE		YTD	YTD		last 12 months
14		12/31/2016	12/31/2017		6/30/2017	6/30/2018		6/30/2018
15	Revenue	$ 165	$ 195		$ 90	$ 135		$ 240
16	EBITDA	$ 47	$ 53		$ 23	$ 55		$ 85
17
18
Sheet1

Cell Formulas
Range	Formula
D15	=SUM(D7:G7)
D16	=SUM(D8:G8)
E15	=SUM(H7:K7)
E16	=SUM(H8:K8)
G15	=SUM(H7:I7)
G16	=SUM(H8:I8)
H15	=SUM(L7:M7)
H16	=SUM(L8:M8)
J14	=M5
J15	=SUM(J7:M7)
J16	=SUM(J8:M8)