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]

Welcome to MrExcel Message Board.
Please give more examples and upload your file with XL2BB ADDIN (preferable) or at Free hosting site and then input link here. e.g. www.dropbox.com
Examples if your Payment less than 40 result is 8000 and ....
Are Result different for each month.
Is your starting date is 1/1/2021.
and ....

 

[Adfinance]

Hi maabadi.
Example, if for your first expense you have a cost of 8000 in January 2021, and a payment term of 40 days, it means that you will have a cashout (in the second table) of 8000 as well (but in February, not in January ... so the value will be automatically displayed in January because it's a payment term of 40 days).

With the same example, if the payment term was 200 days, the value "8000" in january 2021 (in the P&L section, for costs) would have been displayed in july 2021 for the cash outflow.

 

[Adfinance]

Hi maabadi.
Example, if for your first expense you have a cost of 8000 in January 2021, and a payment term of 40 days, it means that you will have a cashout (in the second table) of 8000 as well (but in February, not in January ... so the value will be automatically displayed in January because it's a payment term of 40 days).

With the same example, if the payment term was 200 days, the value "8000" in january 2021 (in the P&L section, for costs) would have been displayed in july 2021 for the cash outflow.

The same is the payment term is less than 40 days (let's say : 23 days), the cash outflow would be in the same month (here in January). If the payment term exceeds the number of days for the month (for instance, january you have a total of 31 days), then the cash out will be displayed in February, not in January.

 

[maabadi]

And What about if your payment Day is in less than of month that displayed First Value. For Example if payment if less than 40 days and first Value is in March , Minimum 60 days,Are Value comes in March or Feb. (row 2 & 3 of your Data).
And Please upload Example file.

 

[maabadi]

Try This:

Redditexcel.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		Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
5		Expense No. 1		8,000	-	-	-	-	-	-	-	-	-	-	-
6		Expense No. 2		-	-	4,000	-	-	-	-	-	-	-	-	-
7		Expense No. 3		-	-	-	-	-	500	-	-	-	-	-	-
8
9
10
11
12
13
14
15		SubTotal		8,000	-	4,000	-	-	500	-	-	-	-	-	-
16
17
18		Nature of Expense		Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21		Payment Terms(s)
19		Expense No. 1		-	8,000	-	-	-	-	-	-	-	-	-	-		+ 40 Day(s)
20		Expense No. 2		-	-	4,000	-	-	-	-	-	-	-	-	-		+ 15 Day(s)
21		Expense No. 3		-	-	-	-	-	-	-	-	-	-	-	500		+ 200 Day(s)
22				-	-	-	-	-	-	-	-	-	-	-	-
23				-	-	-	-	-	-	-	-	-	-	-	-
24				-	-	-	-	-	-	-	-	-	-	-	-
25				-	-	-	-	-	-	-	-	-	-	-	-
26				-	-	-	-	-	-	-	-	-	-	-	-
27				-	-	-	-	-	-	-	-	-	-	-	-
28				-	-	-	-	-	-	-	-	-	-	-	-
29		SubTotal		-	8,000	4,000	-	-	-	-	-	-	-	-	500
30
Sheet1

Cell Formulas
Range	Formula
F15	F15	=SUBTOTAL(109,[Jan-21])
G15	G15	=SUBTOTAL(109,[Feb-21])
H15	H15	=SUBTOTAL(109,[Mar-21])
I15	I15	=SUBTOTAL(109,[Apr-21])
J15	J15	=SUBTOTAL(109,[May-21])
K15	K15	=SUBTOTAL(109,[Jun-21])
L15	L15	=SUBTOTAL(109,[Jul-21])
M15	M15	=SUBTOTAL(109,[Aug-21])
N15	N15	=SUBTOTAL(109,[Sep-21])
O15	O15	=SUBTOTAL(109,[Oct-21])
P15	P15	=SUBTOTAL(109,[Nov-21])
Q15	Q15	=SUBTOTAL(109,[Dec-21])
G18:Q18	G18	=EOMONTH(F18,0)+1
F19:Q28	F19	=IF(SUM($F5:F5)=0,0,IF(AND(F$18-INDEX($F$18:$Q$18,1,MATCH( 0,$F5:$Q5,-1))<$S19,EOMONTH(F$18,0)-INDEX($F$18:$Q$18,1,MATCH( 0,$F5:$Q5,-1))>$S19),SUM($F5:F5),0))
F29	F29	=SUBTOTAL(109,Sheet1!$F$19:$F$28)
G29	G29	=SUBTOTAL(109,Sheet1!$G$19:$G$28)
H29	H29	=SUBTOTAL(109,Sheet1!$H$19:$H$28)
I29	I29	=SUBTOTAL(109,Sheet1!$I$19:$I$28)
J29	J29	=SUBTOTAL(109,Sheet1!$J$19:$J$28)
K29	K29	=SUBTOTAL(109,Sheet1!$K$19:$K$28)
L29	L29	=SUBTOTAL(109,Sheet1!$L$19:$L$28)
M29	M29	=SUBTOTAL(109,Sheet1!$M$19:$M$28)
N29	N29	=SUBTOTAL(109,Sheet1!$N$19:$N$28)
O29	O29	=SUBTOTAL(109,Sheet1!$O$19:$O$28)
P29	P29	=SUBTOTAL(109,Sheet1!$P$19:$P$28)
Q29	Q29	=SUBTOTAL(109,Sheet1!$Q$19:$Q$28)

 

[Adfinance]

Hi maabadi, could you please send me your email? I will send you the file there, because it does not work here unfortunately.

Regardind the formula, the idea is to have pretty much the "same" formula everywhere in the below table .... and if I decide to change many times one or several Payment terms (just to see the outcome), or one of several costs values, the table with the cash has to change accordingy (and automatically).
Thus, the formulas with a "sumtotal" won't work. Because if in cell S19 (payment term for the all the values for cost n°1 (from F5 to Q5)) I decide to put let's say 200 days instead of the actual value which is 40 days .... then if we have:
a) 8 000 in cell F5 (with payment term term of 200 days: cell S19)
b) 10 000 in cell K5 (with payment term term of 200 days: cell S19)

The value 8 000 (with the correct formula) will be displayed automatically in the cell L19 ... for the cash flow impact
The value 10 000 (with the correct formula) will be displayed automatically in the cell Q19 ... for the cash flow impact

Note that: Cost values are added manually and they could be different each month if wanted. Each cost value is displayed "once" in the "cash flow impacct Table". We could have many cost values for Expense n°1 (for instance one in January, one in February, one in July, etc.)

The same for each Expense from line 5 to 14.

 

[Adfinance]

In your different cells formulas, the only formula I am looking for here is you formula in cell F19 (that I will copy past in all the table as wellfrom cell F19 to Q28).
Unfortunately you formula in F19 does not work. Please send me your email so that I can share with you my file.

 

[maabadi]

Please upload your file at Free hosting site and then input link here. e.g. www.dropbox.com or Google Drive (sign in with your google account) or OneDrive (sign in with Microsoft account) and Insert Link Here

 

[Adfinance]

For Mr Excel.xlsx

drive.google.com