Payment term and Cash flow (formula linked with costs)

Adfinance

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • Excel issue.PNG
    Excel issue.PNG
    51.6 KB · Views: 36

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Try this

Book1
CDEFGHIJKLMNOPQRSTU
2
3
4Nature of ExpenseJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
5Expense No. 1800000000000000
6Expense No. 2004000000000000
7Expense No. 300000500000000
8SubTotal
9
10END of Month31-01-202128-02-202131-03-202130-04-202131-05-202130-06-202131-07-202131-08-202130-09-202131-10-202130-11-202131-12-202131-01-2022
11Nature of ExpenseHelperJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Payment Terms(s)
12Expense No. 18000-8,000.00-----------40
13Expense No. 24000--4,000.00----------30.00
14Expense No. 3500-----------500.00-200
15
16
17
18
19Cell marked in yellow is Helper Column
20Assumption all expenses are expected to occur on the 1st day of the Month
21
22
23
Sheet1
Cell Formulas
RangeFormula
F10:R10F10=EOMONTH(F11,0)
E12:E14E12=SUM(F5:Q5)
F12:R14F12=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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Adfinance

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Try this

Book1
CDEFGHIJKLMNOPQRSTU
2
3
4Nature of ExpenseJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
5Expense No. 1800000000000000
6Expense No. 2004000000000000
7Expense No. 300000500000000
8SubTotal
9
10END of Month31-01-202128-02-202131-03-202130-04-202131-05-202130-06-202131-07-202131-08-202130-09-202131-10-202130-11-202131-12-202131-01-2022
11Nature of ExpenseHelperJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Payment Terms(s)
12Expense No. 18000-8,000.00-----------40
13Expense No. 24000--4,000.00----------30.00
14Expense No. 3500-----------500.00-200
15
16
17
18
19Cell marked in yellow is Helper Column
20Assumption all expenses are expected to occur on the 1st day of the Month
21
22
23
Sheet1
Cell Formulas
RangeFormula
F10:R10F10=EOMONTH(F11,0)
E12:E14E12=SUM(F5:Q5)
F12:R14F12=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

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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) :)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,363
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@Adfinance I work on User define Formula for you. Maybe take time. Don't Hurry.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,363
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is Macro. I work on Function also.
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,363
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
Excel Formula:
=ArrangeData(M27,$EV28)
OR
Excel Formula:
=ArrangeData(M27,95)
VBA Code:
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

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=ArrangeData(M27,$EV28)
OR
Excel Formula:
=ArrangeData(M27,95)
VBA Code:
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.
Excel Formula:
=ArrangeData(M27,$EV28)
OR
Excel Formula:
=ArrangeData(M27,95)
VBA Code:
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,060
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top