help with dynamic project planner

[BrutalLogiC]

Hi, hope someone can help please. I have a growing list of projects and each project is split into phases, usually five phases.

Each phase lasts a certain number of months and has a start date month and end date month for each phase (.....the start date of the first phase and the end date of the last phase are the start & end dates of the overall project..... start of month is always first day in month and end date is always last day in month so full months only no part months)

Phase 2 will start the month after phase 1 and so on (....there won't be any gap months between phases, not sure if this is helpful).

Each project has an overall value and within this, each phase has its own value (total value of all phases equals overall project value).

The value for each month in each phase is equal to the total phase value divided by the number of months in the phase.... so each month in each phase has the same value in a particular project

I'm trying to create a dynamic forecast/planner whereby there is a row showing months and below are the values for each month for each phase which automatically go into the right months so I don't have to keep manually linking each month every time there is a change......... the number of months in a phase keeps changing

My current attempt sheet is below for the first two projects up to Jan-23 but the full sheet will have 40 projects for 5 years so I want to enter a formula to all the cells in green to automatically calculate the values based on data above.

There are values in the cells in green already as I have manually linked them and copied them across based on how many months in a phase.... that's the result I am trying to recreate with a formula.

I entered new rows for each phase (e.g. rows 110-114) thinking this might be easier than a single row for each project so I can lookup based on phase number and project name in column C.

Row 108 shows all the months as 01-month-year but day in the month not important only the month/year (hopefully that makes sense)

headache.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
79	Development fees	enter full budget
80		TOTAL PROJECT BUDGET	Phase 1	Phase 2	Phase 3	Phase 4	Phase 5		Project Start	Project End	Total months	Total years		PROJECT FEES	Phase 1	Phase 2	Phase 3	Phase 4	Phase 5	TOTAL PROJECT FEES
81	Project A	400,000,000	80,000,000	80,000,000	80,000,000	80,000,000	80,000,000		Aug-21	Dec-22	17	1.4		1.0%	800,000	800,000	800,000	800,000	800,000	4,000,000
82	Project B	7,000,000,000	770,000,000	1,610,000,000	1,540,000,000	1,540,000,000	1,540,000,000		Oct-21	Sep-24	36	3.0		1.0%	7,700,000	16,100,000	15,400,000	15,400,000	15,400,000	#########
85														Total	20,200,000	35,600,000	31,400,000	31,400,000	31,400,000	#########
86			enter phasing estimated percentage
87		PHASING	Phase 1	Phase 2	Phase 3	Phase 4	Phase 5
88	Project A	100%	20%	20%	20%	20%	20%
89	Project B	100%	11%	23%	22%	22%	22%
92
93		enter number of months per phase
94		Phase 1	Phase 2	Phase 3	Phase 4	Phase 5	Total months	Total years
95	Project A	4	4	3	3	3	17	1.4
96	Project B	8	7	7	7	7	36	3.0
99
100		Phase 1		Phase 2		Phase 3		Phase 4		Phase 5
101		Start	End	Start	End	Start	End	Start	End	Start	End	Total months	Total years
102	Project A	Aug-21	Nov-21	Dec-21	Mar-22	Apr-22	Jun-22	Jul-22	Sep-22	Oct-22	Dec-22	17	1.4
103	Project B	Oct-21	May-22	Jun-22	Dec-22	Jan-23	Jul-23	Aug-23	Feb-24	Mar-24	Sep-24	36	3.0
106
107
108	Total Monthly Value	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21	01-Jan-22	01-Feb-22	01-Mar-22	01-Apr-22	01-May-22	01-Jun-22	01-Jul-22	01-Aug-22	01-Sep-22	01-Oct-22	01-Nov-22	01-Dec-22	01-Jan-23
109	Project A
110	Phase 1		200,000	200,000	200,000	200,000
111	Phase 2						200,000	200,000	200,000	200,000
112	Phase 3										266,667	266,667	266,667
113	Phase 4													266,667	266,667	266,667
114	Phase 5																266,667	266,667	266,667
115		0	200,000	200,000	200,000	200,000	200,000	200,000	200,000	200,000	266,667	266,667	266,667	266,667	266,667	266,667	266,667	266,667	266,667	0
116	Project B
117	Phase 1				962,500	962,500	962,500	962,500	962,500	962,500	962,500	962,500
118	Phase 2												2,300,000	2,300,000	2,300,000	2,300,000	2,300,000	2,300,000	2,300,000
119	Phase 3																			2,200,000
120	Phase 4
121	Phase 5
122		0	0	0	962,500	962,500	962,500	962,500	962,500	962,500	962,500	962,500	2,300,000	2,300,000	2,300,000	2,300,000	2,300,000	2,300,000	2,300,000	2,200,000
Monthly cashflow

Cell Formulas
Range	Formula
E81:I82	E81	=$D81*E88
L81:L82	L81	=M102
M81:M82	M81	=1+(DATEDIF(K81,L81,"M"))
O102:O103,J95:J96,N81:N82	N81	=M81/12
Q81:U82	Q81	=$P81*E81
I95:I96,V81:V82	V81	=SUM(Q81:U81)
Q85:V85	Q85	=SUM(Q81:Q84)
D88:D89	D88	=SUM(E88:I88)
D102:D103	D102	=K81
E102:E103	E102	=EOMONTH(K81,D95-1)
F102:F103,L102:L103,J102:J103,H102:H103	F102	=EOMONTH(E102,1)
G102:G103	G102	=EOMONTH(F102,E95-1)
I102:I103	I102	=EOMONTH(H102,F95-1)
K102:K103	K102	=EOMONTH(J102,G95-1)
M102:M103	M102	=EOMONTH(L102,H95-1)
N102:N103	N102	=1+(DATEDIF(K81,M102,"M"))
E110:H110	E110	=$P81*$E81/$D95
I111:L111	I111	=$P81*$F81/$E95
M112:O112	M112	=$P81*$G81/$F95
P113:R113	P113	=$P81*$H81/$G95
S114:U114	S114	=$P81*$I81/$H95
D122:V122,D115:V115	D115	=SUM(D110:D114)
G117:N117	G117	=$P82*$E82/$D96
O118:U118	O118	=$P82*$F82/$E96
V119	V119	=$P82*$G82/$F96