Payment term and Cash flow (formula linked with costs)

[Adfinance]

Hi everyone, I need to calculate automatically the cash position (cashout) based on payment terms and costs.
The payment terms may change, so I need a dynamic formula.
Attached you will see that there is a section with "P&L impact", with the listing of all the costs and their value month by month (if any).

Also, you will see in the "Cash flow impact" section that I need to find a formula (in cells S19 to Q28) which depends on 2 things:
1/ Cost value (from cells F5 to Q14)
2/ Payment term in days (cells S19 to S28)

For instance, if I put the value 8000 in cell F5, and Payment term in cell S19 is 40 days, thanks to the formula we will have automatically the value 8000 in cell G19.

Can anyone help me please?

 

[maabadi]

First Test this formula at F19:

=IF(SUM($F5:F5)=0,0,IF(AND(F$18-INDEX($F$18:$Q$18,1,MATCH(0,$F5:F5,-1))<$S19,EOMONTH(F$18,0)-INDEX($F$18:$Q$18,1,MATCH(0,$F5:F5,-1))>$S19),SUM($F5:F5)-SUM(E19:$F19),0))

 

[maabadi]

Why you changed Dates from Day 1 of month to Day 22.
1. Change days to first day of Month.
2. AND Change S19 to S28 Number format to Custom and Write at Types

+ ##0 " Day(s)"

With this format you only need input number others Inserted automatically.

And Post #11 formula works correctly.

 

[Adfinance]

1/ What do you mean by changing dates ? which cells are you talking about ? Cells D2 and E2 ? Or cells F4 to Q4 ?

2/ Your format does not work. Is it ok if I put this format instead ? _(+# ##0" day(s)"
This format works.

 

[Adfinance]

Your formula does not work ....

 

[Adfinance]

Your formula does not work ....

Haveyou got the solution @maabadi ? Thank you so much again.

 

[maabadi]

I work on it. & find.

 

[Adfinance]

I work on it. & find.

Thank you so much! Looking forward to your outcome

 

[Adfinance]

Thank you so much! Looking forward to your outcome

Hi @maabadi
Have you found something? It appears to be a difficult formula, it's a really interesting case!

 

[maabadi]

Try this:

Book11.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	Start Date	1/1/2021
3
4		Nature of Expense		1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec
5		Expense No. 1		8000	6000	2000	4000	0	0	0	0	0	0	0	0
6		Expense No. 2		0	0	4000	0	0	0	0	0	0	0	0	0
7		Expense No. 3		0	0	0	0	0	500	0	0	0	0	0	0
8				0	0	0	100	0	0	0	0	0	0	0	0
9								0	0	500
10								100	0	0
11							6000
12									2000
13											3000
14										1000
15
16
17
18		Nature of Expense		1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec		Payment Terms(s)
19		Expense No. 1		0	8000	6000	2000	4000	0	0	0	0	0	0	0		+ 40 Day(s)
20		Expense No. 2		0	0	0	0	0	0	0	0	4000	0	0	0		+ 200 Day(s)
21		Expense No. 3		0	0	0	0	0	0	0	0	0	0	0	500		+ 190 Day(s)
22				0	0	0	0	0	0	0	0	0	100	0	0		+ 200 Day(s)
23				0	0	0	0	0	0	0	0	0	500	0	0		+ 100 Day(s)
24				0	0	0	0	0	0	0	100	0	0	0	0		+ 100 Day(s)
25				0	0	0	0	0	0	6000	0	0	0	0	0		+ 100 Day(s)
26				0	0	0	0	0	0	0	0	2000	0	0	0		+ 100 Day(s)
27				0	0	0	0	0	0	0	0	0	0	3000	0		+ 100 Day(s)
28				0	0	0	0	0	0	0	0	0	1000	0	0		+ 100 Day(s)
29		SubTotal
Sheet2

Cell Formulas
Range	Formula
F19:Q28	F19	=IFERROR(IF(SUM($F5:F5)=0,0,IF(AND(INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19>F$18,INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19<EOMONTH(F$18,0)),INDEX($F5:$Q5,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0)),IF(INDEX($F$4:$Q$4,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0))+$S19>F$18,0,INDEX($F5:$Q5,,MATCH(TRUE,INDEX($F5:$Q5>0,0),0)+COLUMNS($F19:F19)+1-IFERROR(MATCH(TRUE,INDEX($E19:E19>0,0),0),1))))),"")

 

[Adfinance]

Hi @maabadi thank you very much. Unfortunately when I insert the formula in my excel file, it does not work (mayne a problem of "coma" or something like that?).

It seems to be perfectly working with your example above ... or maybe could you just send me your file with the formula (directly in the sheet, to be sure it works)? Thank you so much again.