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?

 

[CA_Punit]

Try this

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2
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		8000	0	0	0	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		SubTotal
9
10		END of Month		31-01-2021	28-02-2021	31-03-2021	30-04-2021	31-05-2021	30-06-2021	31-07-2021	31-08-2021	30-09-2021	31-10-2021	30-11-2021	31-12-2021	31-01-2022
11		Nature of Expense	Helper	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22		Payment Terms(s)
12		Expense No. 1	8000	-	8,000.00	-	-	-	-	-	-	-	-	-	-	-		40
13		Expense No. 2	4000	-	-	4,000.00	-	-	-	-	-	-	-	-	-	-		30.00
14		Expense No. 3	500	-	-	-	-	-	-	-	-	-	-	-	500.00	-		200
15
16
17
18
19			Cell marked in yellow is Helper Column
20			Assumption all expenses are expected to occur on the 1st day of the Month
21
22
23
Sheet1

Cell Formulas
Range	Formula
F10:R10	F10	=EOMONTH(F11,0)
E12:E14	E12	=SUM(F5:Q5)
F12:R14	F12	=IF(AND((LARGE(IF($F5:$Q5>0,$F$4:$Q$4),1)+$T12)>=F$11,(LARGE(IF($F5:$Q5>0,$F$4:$Q$4),1)+$T12)<=F$10),$E12,0)

But the above solution has too many probelm.
1. It assumes your Bill is on the 1st Day of the Month and it calculates the month in which it is due. Assuming you have received a bill on 30th jan and required to pay by 15 days the formula will show that the payment is required to be made in Jan whereas the payment is due on Feb

2. If you have multiple payment for Expenses 1 in the different month it wont calculate it.

So do you mind changing the scenario of your question or the Layout. If yes, then we can work on that. Also please quote does it anyway solves your requirment

 

[Adfinance]

Hi @maabadi how are you? Your formula work well as I told you before.
Nevertheless, I am facing a quite similar issue (same formula but for 10 years : From year 2020 to year 2029).
The issue here is that if we have, let's say, a cost at the end of 2020 with a Payment term that implies a cashout at 2021 ... the formula does not take it into consideration.

Could you please give me your opinion. It would be amazing if you can find the formula for that (I think you just have to change a part of the actual formula, but I don't know which one).
The link to access the file (with year 2020 to year 2029) is the following: For Mr Excel error formula v7.xlsx


Thank you so much again!

 

[Adfinance]

Try this

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2
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		8000	0	0	0	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		SubTotal
9
10		END of Month		31-01-2021	28-02-2021	31-03-2021	30-04-2021	31-05-2021	30-06-2021	31-07-2021	31-08-2021	30-09-2021	31-10-2021	30-11-2021	31-12-2021	31-01-2022
11		Nature of Expense	Helper	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22		Payment Terms(s)
12		Expense No. 1	8000	-	8,000.00	-	-	-	-	-	-	-	-	-	-	-		40
13		Expense No. 2	4000	-	-	4,000.00	-	-	-	-	-	-	-	-	-	-		30.00
14		Expense No. 3	500	-	-	-	-	-	-	-	-	-	-	-	500.00	-		200
15
16
17
18
19			Cell marked in yellow is Helper Column
20			Assumption all expenses are expected to occur on the 1st day of the Month
21
22
23
Sheet1

Cell Formulas
Range	Formula
F10:R10	F10	=EOMONTH(F11,0)
E12:E14	E12	=SUM(F5:Q5)
F12:R14	F12	=IF(AND((LARGE(IF($F5:$Q5>0,$F$4:$Q$4),1)+$T12)>=F$11,(LARGE(IF($F5:$Q5>0,$F$4:$Q$4),1)+$T12)<=F$10),$E12,0)

But the above solution has too many probelm.
1. It assumes your Bill is on the 1st Day of the Month and it calculates the month in which it is due. Assuming you have received a bill on 30th jan and required to pay by 15 days the formula will show that the payment is required to be made in Jan whereas the payment is due on Feb

2. If you have multiple payment for Expenses 1 in the different month it wont calculate it.

So do you mind changing the scenario of your question or the Layout. If yes, then we can work on that. Also please quote does it anyway solves your requirment

Hi @CA_Punit thank you for your reply! You are absolutely right.
1. the issue that is linked with the exact day of the bill (let's say 25th of January 2020 ... with a payment term of 15 days for instance) is something that ideally needs to be fixed. But I don't know how.

2. Regarding your second issue, you are right too. Actually, this problem was solved by @maabadi in the post #28. I have added a new issue (if we have, not just 1 year, but 10 years ... please see the post #32 )

 

[Adfinance]

Hi @maabadi how are you? Your formula work well as I told you before.
Nevertheless, I am facing a quite similar issue (same formula but for 10 years : From year 2020 to year 2029).
The issue here is that if we have, let's say, a cost at the end of 2020 with a Payment term that implies a cashout at 2021 ... the formula does not take it into consideration.

Could you please give me your opinion. It would be amazing if you can find the formula for that (I think you just have to change a part of the actual formula, but I don't know which one).
The link to access the file (with year 2020 to year 2029) is the following: For Mr Excel error formula v7.xlsx


Thank you so much again!

@maabadi a new issue (if 10 years of projection instead of just 1 year)

 

[Adfinance]

@maabadi a new issue (if 10 years of projection instead of just 1 year)

@maabadi do you have any idea?

 

[maabadi]

@Adfinance I work on User define Formula for you. Maybe take time. Don't Hurry.

 

[Adfinance]

@Adfinance I work on User define Formula for you. Maybe take time. Don't Hurry.

@maabadi oh ok. Thank you so much

 

[maabadi]

This is Macro. I work on Function also.

Sub ArrangeData()
Dim Lr As Long, Lr2 As Long, i As Long, j As Long, Ws As Worksheet, Ws2 As Worksheet, a As Long, Lc As Long
Dim b As Long, c As Long, d As Long, Rng As Range, Fcell As Range, Lcell As Range, MCell As Range, col As Long
Dim CFCell As Range, CLCell As Range, CrCell As Range, e As Long, f As Long, g As Long, n As Long
Dim h As Long, k As Long, L As Long, m As Long, o As Long
Set MCell = Range("K13")
Lc = Cells(15, Columns.Count).End(xlToLeft).Column


For j = 13 To 150
For i = 28 To 37
n = Cells(i, j).Column Mod 14

If n = 11 Then
j = j + 2
ElseIf n = 12 Then
j = j + 1
End If
col = Cells(i, j).Column + 3
a = Int(col / 14) * 14 - 1

Set Fcell = Cells(i - 14, a)
Set Lcell = Cells(i - 14, a + 11)
Set CFCell = Cells(13, a)
Set CLCell = Cells(13, a + 11)
Set CrCell = Cells(i, 152)
Debug.Print Fcell.Address
Debug.Print Lcell.Address

b = Application.WorksheetFunction.Sum(Range(Cells(i - 14, a), Cells(i - 14, j)))
If b = 0 Then GoTo Resum4
For L = a To a + 11
If Cells(i - 14, L).Value = 0 Then
c = 0
Else
c = L - a + 1
GoTo Resum
End If
Next L
If c = 0 Then
Cells(i, j).Value = 0
GoTo Resum3
End If
Resum:
For L = a - 1 To j - 1
If Cells(i, L).Value = 0 Then
m = 0
Else
m = L - a + 1
GoTo Resum2
End If
Next L
Resum2:
d = Application.WorksheetFunction.Index(Range(CFCell, CLCell), 1, c)
e = Application.WorksheetFunction.EoMonth(Cells(27, j), 0)
f = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c)
If d + CrCell.Value >= Cells(27, j) + 1 Then GoTo Resum4
h = Cells(i, j).Column - Fcell.Column
k = Application.WorksheetFunction.IfError(m, 1)
o = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c + h + 1 - k)
'On Error GoTo ErrorHandler
Resum4:
If j = 29 And i = 33 Then
d = d
End If
If b = 0 Then
g = 0
ElseIf d + CrCell.Value >= Cells(27, j) + 1 And d + CrCell.Value <= e + 1 Then
g = f
ElseIf d + CrCell.Value >= Cells(27, j) + 1 Then
g = 0
Else
g = o
End If
'ErrorHandler:
'g = ""
Cells(i, j).Value = g
Resum3:
Next i
Next j
End Sub

 

[maabadi]

And This is function.
One Problem I have with this function. I use target cell row and column and cann't use it at function. thus you should change cell above target Cell as topresultCell.
For Example, if you want to see result at M28 select cell M27 or if you want to see result at P36 select P35.
For Payment Term you can Select Cell adress with fixed column address e.g. $EV28 or input number.
Insert New module and input this code. Save file as .xlsm , Then At excel window at result Cell input equal function name e.g.

=ArrangeData(M27,$EV28)

OR

=ArrangeData(M27,95)

Function ArrangeData(TopResultCell As Range, PaymentTerm As Variant) As Long
Dim Lr As Long, Lr2 As Long, i As Long, j As Long, Ws As Worksheet, Ws2 As Worksheet, a As Long, Lc As Long
Dim b As Long, c As Long, d As Long, Rng As Range, Fcell As Range, Lcell As Range, MCell As Range, col As Long
Dim CFCell As Range, CLCell As Range, CrCell As Range, e As Long, f As Long, g As Long, n As Long
Dim h As Long, k As Long, L As Long, m As Long, o As Long
Set MCell = Range("K13")
Lc = Cells(15, Columns.Count).End(xlToLeft).Column
i = TopResultCell.Row + 1
j = TopResultCell.Column

n = Cells(i, j).Column Mod 14

If n = 11 Then
j = j + 2
ElseIf n = 12 Then
j = j + 1
End If
col = Cells(i, j).Column + 3
a = Int(col / 14) * 14 - 1

Set Fcell = Cells(i - 14, a)
Set Lcell = Cells(i - 14, a + 11)
Set CFCell = Cells(13, a)
Set CLCell = Cells(13, a + 11)
Set CrCell = Cells(i, 152)
Debug.Print Fcell.Address
Debug.Print Lcell.Address

b = Application.WorksheetFunction.Sum(Range(Cells(i - 14, a), Cells(i - 14, j)))
If b = 0 Then GoTo Resum4
For L = a To a + 11
If Cells(i - 14, L).Value = 0 Then
c = 0
Else
c = L - a + 1
GoTo Resum
End If
Next L
If c = 0 Then
ArrangeData = 0
GoTo Resum3
End If
Resum:
For L = a - 1 To j - 1
If Cells(i, L).Value = 0 Then
m = 0
Else
m = L - a + 1
GoTo Resum2
End If
Next L
Resum2:
d = Application.WorksheetFunction.Index(Range(CFCell, CLCell), 1, c)
e = Application.WorksheetFunction.EoMonth(Cells(27, j), 0)
f = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c)
If d + CrCell.Value >= Cells(27, j) + 1 Then GoTo Resum4
h = Cells(i, j).Column - Fcell.Column
k = Application.WorksheetFunction.IfError(m, 1)
o = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c + h + 1 - k)
'On Error GoTo ErrorHandler
Resum4:
If j = 29 And i = 33 Then
d = d
End If
If b = 0 Then
g = 0
ElseIf d + CrCell.Value >= Cells(27, j) + 1 And d + CrCell.Value <= e + 1 Then
g = f
ElseIf d + CrCell.Value >= Cells(27, j) + 1 Then
g = 0
Else
g = o
End If
'ErrorHandler:
'g = ""
ArrangeData = g
Resum3:
End Function

 

[Adfinance]

And This is function.
One Problem I have with this function. I use target cell row and column and cann't use it at function. thus you should change cell above target Cell as topresultCell.
For Example, if you want to see result at M28 select cell M27 or if you want to see result at P36 select P35.
For Payment Term you can Select Cell adress with fixed column address e.g. $EV28 or input number.
Insert New module and input this code. Save file as .xlsm , Then At excel window at result Cell input equal function name e.g.

=ArrangeData(M27,$EV28)

OR

=ArrangeData(M27,95)

Function ArrangeData(TopResultCell As Range, PaymentTerm As Variant) As Long
Dim Lr As Long, Lr2 As Long, i As Long, j As Long, Ws As Worksheet, Ws2 As Worksheet, a As Long, Lc As Long
Dim b As Long, c As Long, d As Long, Rng As Range, Fcell As Range, Lcell As Range, MCell As Range, col As Long
Dim CFCell As Range, CLCell As Range, CrCell As Range, e As Long, f As Long, g As Long, n As Long
Dim h As Long, k As Long, L As Long, m As Long, o As Long
Set MCell = Range("K13")
Lc = Cells(15, Columns.Count).End(xlToLeft).Column
i = TopResultCell.Row + 1
j = TopResultCell.Column

n = Cells(i, j).Column Mod 14

If n = 11 Then
j = j + 2
ElseIf n = 12 Then
j = j + 1
End If
col = Cells(i, j).Column + 3
a = Int(col / 14) * 14 - 1

Set Fcell = Cells(i - 14, a)
Set Lcell = Cells(i - 14, a + 11)
Set CFCell = Cells(13, a)
Set CLCell = Cells(13, a + 11)
Set CrCell = Cells(i, 152)
Debug.Print Fcell.Address
Debug.Print Lcell.Address

b = Application.WorksheetFunction.Sum(Range(Cells(i - 14, a), Cells(i - 14, j)))
If b = 0 Then GoTo Resum4
For L = a To a + 11
If Cells(i - 14, L).Value = 0 Then
c = 0
Else
c = L - a + 1
GoTo Resum
End If
Next L
If c = 0 Then
ArrangeData = 0
GoTo Resum3
End If
Resum:
For L = a - 1 To j - 1
If Cells(i, L).Value = 0 Then
m = 0
Else
m = L - a + 1
GoTo Resum2
End If
Next L
Resum2:
d = Application.WorksheetFunction.Index(Range(CFCell, CLCell), 1, c)
e = Application.WorksheetFunction.EoMonth(Cells(27, j), 0)
f = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c)
If d + CrCell.Value >= Cells(27, j) + 1 Then GoTo Resum4
h = Cells(i, j).Column - Fcell.Column
k = Application.WorksheetFunction.IfError(m, 1)
o = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c + h + 1 - k)
'On Error GoTo ErrorHandler
Resum4:
If j = 29 And i = 33 Then
d = d
End If
If b = 0 Then
g = 0
ElseIf d + CrCell.Value >= Cells(27, j) + 1 And d + CrCell.Value <= e + 1 Then
g = f
ElseIf d + CrCell.Value >= Cells(27, j) + 1 Then
g = 0
Else
g = o
End If
'ErrorHandler:
'g = ""
ArrangeData = g
Resum3:
End Function

Hi @maabadi, thank you so much for your output. To be honest I don't understand anything about Macro in Excel. Thus, I don't know how to put your Macro above in m Excel file. Could you help me with that? Is it impossible just to change our last formula in post #

And This is function.
One Problem I have with this function. I use target cell row and column and cann't use it at function. thus you should change cell above target Cell as topresultCell.
For Example, if you want to see result at M28 select cell M27 or if you want to see result at P36 select P35.
For Payment Term you can Select Cell adress with fixed column address e.g. $EV28 or input number.
Insert New module and input this code. Save file as .xlsm , Then At excel window at result Cell input equal function name e.g.

=ArrangeData(M27,$EV28)

OR

=ArrangeData(M27,95)

Function ArrangeData(TopResultCell As Range, PaymentTerm As Variant) As Long
Dim Lr As Long, Lr2 As Long, i As Long, j As Long, Ws As Worksheet, Ws2 As Worksheet, a As Long, Lc As Long
Dim b As Long, c As Long, d As Long, Rng As Range, Fcell As Range, Lcell As Range, MCell As Range, col As Long
Dim CFCell As Range, CLCell As Range, CrCell As Range, e As Long, f As Long, g As Long, n As Long
Dim h As Long, k As Long, L As Long, m As Long, o As Long
Set MCell = Range("K13")
Lc = Cells(15, Columns.Count).End(xlToLeft).Column
i = TopResultCell.Row + 1
j = TopResultCell.Column

n = Cells(i, j).Column Mod 14

If n = 11 Then
j = j + 2
ElseIf n = 12 Then
j = j + 1
End If
col = Cells(i, j).Column + 3
a = Int(col / 14) * 14 - 1

Set Fcell = Cells(i - 14, a)
Set Lcell = Cells(i - 14, a + 11)
Set CFCell = Cells(13, a)
Set CLCell = Cells(13, a + 11)
Set CrCell = Cells(i, 152)
Debug.Print Fcell.Address
Debug.Print Lcell.Address

b = Application.WorksheetFunction.Sum(Range(Cells(i - 14, a), Cells(i - 14, j)))
If b = 0 Then GoTo Resum4
For L = a To a + 11
If Cells(i - 14, L).Value = 0 Then
c = 0
Else
c = L - a + 1
GoTo Resum
End If
Next L
If c = 0 Then
ArrangeData = 0
GoTo Resum3
End If
Resum:
For L = a - 1 To j - 1
If Cells(i, L).Value = 0 Then
m = 0
Else
m = L - a + 1
GoTo Resum2
End If
Next L
Resum2:
d = Application.WorksheetFunction.Index(Range(CFCell, CLCell), 1, c)
e = Application.WorksheetFunction.EoMonth(Cells(27, j), 0)
f = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c)
If d + CrCell.Value >= Cells(27, j) + 1 Then GoTo Resum4
h = Cells(i, j).Column - Fcell.Column
k = Application.WorksheetFunction.IfError(m, 1)
o = Application.WorksheetFunction.Index(Range(Fcell, Lcell), 1, c + h + 1 - k)
'On Error GoTo ErrorHandler
Resum4:
If j = 29 And i = 33 Then
d = d
End If
If b = 0 Then
g = 0
ElseIf d + CrCell.Value >= Cells(27, j) + 1 And d + CrCell.Value <= e + 1 Then
g = f
ElseIf d + CrCell.Value >= Cells(27, j) + 1 Then
g = 0
Else
g = o
End If
'ErrorHandler:
'g = ""
ArrangeData = g
Resum3:
End Function

Hi @maabadi, thank you so much for your output. To be honest I don't understand anything about Macro in Excel. Thus, I don't know how to put your Macro above in my Excel file. Could you help me with that? Is it impossible just to change our last formula in post #28 ? It would be (maybe) easier, no?

Thank you for your unconditionnal support again