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: 27

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
First Test this formula at F19:
Excel Formula:
=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))
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Excel Formula:
+ ##0 " Day(s)"
With this format you only need input number others Inserted automatically.

And Post #11 formula works correctly.
 

Adfinance

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

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Your formula does not work ....
 

Attachments

  • 1609581049846.png
    1609581049846.png
    76.6 KB · Views: 3

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I work on it. & find.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Book11.xlsx
CDEFGHIJKLMNOPQRS
1
2Start Date1/1/2021
3
4Nature of Expense1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-Dec
5Expense No. 1800060002000400000000000
6Expense No. 2004000000000000
7Expense No. 300000500000000
800010000000000
900500
1010000
116000
122000
133000
141000
15
16
17
18Nature of Expense1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-DecPayment Terms(s)
19Expense No. 1080006000200040000000000+ 40 Day(s)
20Expense No. 2000000004000000+ 200 Day(s)
21Expense No. 300000000000500+ 190 Day(s)
2200000000010000+ 200 Day(s)
2300000000050000+ 100 Day(s)
2400000001000000+ 100 Day(s)
25000000600000000+ 100 Day(s)
26000000002000000+ 100 Day(s)
27000000000030000+ 100 Day(s)
28000000000100000+ 100 Day(s)
29SubTotal
Sheet2
Cell Formulas
RangeFormula
F19:Q28F19=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))))),"")
 

Attachments

  • 222333.jpg
    222333.jpg
    152.2 KB · Views: 1

Adfinance

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

Watch MrExcel Video

Forum statistics

Threads
1,119,023
Messages
5,575,620
Members
412,679
Latest member
TSpan
Top