Return a value to certain columns based on date AND project duration

[jessicarichards295]

Hello,

I am trying to create a revenue forecast with each month of the year in columns. The input data is updated weekly, so the forecast needs to be dynamic.

I can easily get the revenue amount to return to the first month of the project, but for projects that stretch over multiple months, I haven't been able to work out a way to get the revenue amount for subsequent months.

For example: for project 222XXX the first month of the project is displaying correctly in cell L3, but how can I get the remaining two months of revenue to show in M3:N3?

In the attached, the orange cells are the input data, the columns J:U is where I need help.

Thank you!

 

[kevin9999]

Welcome to the forum
Until a more elegant solution comes along, try the following:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Start Date		Duration		Forecast		Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
2	01/03/2024		3		$450,000		$0	$0	$150,000	$150,000	$150,000	$0	$0	$0	$0	$0	$0	$0
Sheet2

Cell Formulas
Range	Formula
J2:U2	J2	=IF(AND(J1>=EOMONTH($D2,-1)+1,SUM($I2:I2)<$H2),$H2/$F2,0)

 

[KRice]

I think some allocation rules need to be established. For example, if the start date begins near the end of one month and ends just days later at the beginning of the following month, that would probably be considered a "1 month" duration, the smallest resolution used in the table. But that duration also "touches" two months. So should the cost be entirely allocated to the 1st month where we find the start date, entirely allocated to the 2nd month where we find the end date, or divided somehow between those two months? In the example below using some of the ideas suggested by @kevin9999, I'm allocating the Forecast evenly across all months touched by the start and end dates. This means that the divisor for the monthly forecast amount is not necessarily the same as the "Duration", so I've added a computed End Date column and a Number of Months Touched (NMoT) column for convenient reference, Additionally, I'm recommending that the allocation logic use only the dates to determine if an allocation should be made, rather than evaluating whether the full sum of allocated costs equals the forecast amount. The reason is that, should a start date begin in a prior year and the activity spills into the current year, or if a start date begins in the current year and the activity spills into the following year, details of those pre/post years are not shown, and you won't be able to rely on a sum of allocated costs.

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Start Date	Duration	End Date	NmoT	Forecast		Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
2	2/15/2024	3	5/14/2024	4	$ 450,000		$ -	$ 112,500	$ 112,500	$ 112,500	$ 112,500	$ -	$ -	$ -	$ -	$ -	$ -	$ -
3	11/1/2023	5	3/31/2024	5	$ 500,000		$ 100,000	$ 100,000	$ 100,000	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
4	12/7/2023	9	9/6/2024	10	$ 100,000		$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ 10,000	$ -	$ -	$ -
5	11/1/2024	5	3/31/2025	5	$ 100,000		$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 20,000	$ 20,000
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=EDATE(D2-1,E2)
G2:G5	G2	=12*(YEAR(F2)-YEAR(D2))+MONTH(F2)-MONTH(D2)+1
J2:U5	J2	=IF(AND(J$1>=EOMONTH($D2,-1)+1,J$1<=EOMONTH($F2,0)),$H2/$G2,0)