Help with IF AND formula again

[BrutalLogiC]

I'm trying to write a formula to work out future potential monthly revenue based on contract start dates and their associated payment terms and value... hopefully someone can assist

The input cells would be:
Column V from cell V12 is the contract monthly value
Column W from cell W12 is the contract start date which is always start of month
Column X is the end date of the contract which is always end of month
Column AA is the first month the contract will be invoiced... typically either "1" or "3"
Column AB is the number of months which will be invoiced every time an invoice is raised (the billing frequency)... typically either "1" or "3" or "12"
Column AC is the month the first invoice will actually be paid... typically either "2" or "3"
Row 11 is the forecast months... first month is "May-19" in cell AE11

example 1:
V12 = $1,000 (the monthly contract value)
W12 = 01 May 2019 (the contract start date)
AA12 = 1 (i.e. the first invoice will be raised in the first month of the contract which would be May in this example)
AB12 = 3 (i.e. each invoice raised will be for 3 months' value, AB12*V12)
AC12 = 3 (i.e. each invoice raised will be paid in month 3... with month 1 being the month the invoice is raised.. so in this example it would be July19)

So in the forecast I would expect to see
May = 0 (cell AE12)
June = 0 (cell AF12)
July = $3000
Aug = 0
Sept = 0
Oct = $3000
Nov = 0
Dec = 0
Jan = $3000

example 2:
V12 = $1,000 (the monthly contract value)
W12 = May 2019 (the contract start date)
AA12 = 1 (i.e. the first invoice will be raised in the first month of the contract which would be May in this example)
AB12 = 1 (i.e. each invoice raised will be for 1 month's value, AB12*V12)
AC12 = 2 (i.e. the first invoice will be paid in month 2 and then every month thereafter)

So in the forecast I would expect to see
May = 0
June = $1000
July = $1000
Aug = $1000
Sept = $1000
Oct = $1000

example 3:
V12 = $1,000 (the monthly contract value)
W12 = 01 May 2019 (the contract start date)
AA12 = 3 (i.e. the first invoice will be raised in the third month of the contract which would be July)
AB12 = 3 (i.e. each invoice raised will be for 3 months' value, AB12*V12)
AC12 = 4 (i.e. the first invoice will be paid in month 4.. then 7...10...etc.)

So in the forecast I would expect to see
May = 0
June = 0
July = 0
Aug = $3000
Sept = 0
Oct = 0
Nov = 0
Dec = $3000
Jan = 0

I haven't factored the end date into any of these examples but if the current month was post the contract end date then there would be no further invoices raised or income received.

 

[AlanY]

try this

Book1
	V	W	X	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
11								May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20
12	1000	01/05/2019	31/01/2020	1	3	3		0	0	3000	0	0	3000	0	0	3000	0
13	1000	01/05/2019		1	1	2		0	1000	1000	1000	1000	1000	1000	1000	1000	1000
14	1000	01/05/2019		3	3	4		0	0	0	3000	0	0	3000	0	0	3000
15	1000	01/06/2019	31/01/2020	1	3	2		0	0	3000	0	0	3000	0	0	3000	0
Sheet3

Cell Formulas
Range	Formula
AE12	=IF(AND(AE$11>=EDATE($W12,$AC12-1),MOD(MONTH(AE$11)-MONTH(EDATE($W12,$AC12-1)),$AB12)=0),$V12*$AB12,0)

 

[BrutalLogiC]

Thanks again Alan! I can't get that to work, if the end date in your example is 31/01/20 and I drag the cells further across there is still income appearing, in Apr-20, July-20 etc, is it the same for you?

It should be
May = 0
June = 0
July-19 = 3,000
Aug = 0
Sept =0
Oct = 3,000
Nov = 0
Dec = 0
Jan-20 = 3,000
then 0 for every month thereafter as contract ended in Jan-20

I just realised that if the end date is 29-02-20 (contract duration 10 months) and 3 months are billed at a time then final month (Feb 20) will need to be a single month.. that sounds a bit complex?

 

[AlanY]

Well, I haven’t make use of the contract end date in the formula. You need to add an additional condition in the AND() function to cover that.

 

[BrutalLogiC]

I will attempt Thanks!

 

[AlanY]

modified for the last month of the contract to invoice the outstanding

Book1
	V	W	X	AA	AB	AC	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
11							May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20
12	1000	01/05/2019	31/03/2020	1	3	3	0	0	3000	0	0	3000	0	0	3000	0	2000	0	0
13	1000	01/05/2019	31/03/2020	1	1	2	0	1000	1000	1000	1000	1000	1000	1000	1000	1000	2000	0	0
14	1000	01/05/2019	31/03/2020	3	3	4	0	0	0	3000	0	0	3000	0	0	3000	2000	0	0
15	1000	01/07/2019	30/04/2020	1	3	4	0	0	0	0	0	3000	0	0	3000	0	0	4000	0
16	1000	01/07/2019	30/04/2020	1	3	3	0	0	0	0	3000	0	0	3000	0	0	3000	1000	0
17	1000	01/08/2019	30/04/2020	1	3	4	0	0	0	0	0	0	3000	0	0	3000	0	3000	0
Sheet2

Cell Formulas
Range	Formula
AE12	=IF(AE$11<$X12,IF(MONTH(EDATE(AE$11,0))=MONTH(EDATE($X12,0)), MONTH($X12-$W12)*$V12-SUM(AD12:$AE12), IF(AND(AE$11>=EDATE($W12,$AC12-1),MOD(MONTH(AE$11)- MONTH(EDATE($W12,$AC12-1)),$AB12)=0),$V12*$AB12,0)),0)

 

[BrutalLogiC]

Hi Alan. Thanks again for your reply. I'm trying to use the formula with
W12 start date 01-06-19
X12 end date 01-06-22
row 11 starting at column AE with Mar-19 (instead of May-19)

but I just can't seem to get it to work, as the wrong value 12,000 appears against June19 (AF12) then -15,000 against June2020 (AT12)

I can't quite figure out what's going on.. help please

 

[AlanY]

think i might finally cracked it, the problem was i assumed the contract end the end of the month and that been fixed now.

Book1
	V	W	X	AA	AB	AC	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	BP
11							May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Jun-22
12	1000	01/05/2019	31/03/2020	1	3	3			3000			3000			3000
13	1000	01/05/2019	31/03/2020	1	1	2		1000	1000	1000	1000	1000	1000	1000	1000	1000	2000
14	1000	01/05/2019	31/03/2020	3	3	4				3000			3000			3000
15	1000	01/07/2019	31/07/2020	1	3	4						3000			3000			3000			4000
16	1000	01/07/2019	30/04/2020	1	3	3					3000			3000			3000
17	1000	01/08/2019	30/04/2020	1	3	4							3000			3000
18	1000	01/06/2019	30/06/2022	1	3	4					3000			3000			3000			3000			4000
Sheet2

Cell Formulas
Range	Formula
AE12	=IF(AND(AE$11>=EDATE($W20,$AC20-1),AE$11<=$X20),IF(MOD(MONTH($W12)+$AC12-1,$AB12)= MOD(MONTH(AE$11),$AB12),IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)), IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),(DATEDIF($W12,$X12,"m")+1)* $V12-SUM(AD12:$AE12),0),IF(AND(AE$11>=EDATE($W20,$AC20-1),AE$11<=$X20, MOD(MONTH($W12)+$AC12-1,$AB12)=MOD(MONTH(AE$11),$AB12)),$V12*$AB12,"")),""),"")

 

[BrutalLogiC]

Cheers but what are the references to row 20 doing? I'm not sure how to adjust on my own data that goes from rows 12 to 82.

 

[AlanY]

typo, it's the working rows.

=IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12),IF(MOD(MONTH($W12)+$AC12-1,$AB12)=
MOD(MONTH(AE$11),$AB12),IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),
IF(AND($X12>=AE$11,$X12<=EOMONTH(AE$11,0)),(DATEDIF($W12,$X12,"m")+1)*
$V12-SUM(AD12:$AE12),0),IF(AND(AE$11>=EDATE($W12,$AC12-1),AE$11<=$X12,
MOD(MONTH($W12)+$AC12-1,$AB12)=MOD(MONTH(AE$11),$AB12)),$V12*$AB12,"")),""),"")