Payment term and Cash flow (formula linked with costs)

Adfinance

Board Regular
Joined
Jan 1, 2021
Messages
80
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: 654
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))
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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.
 
Upvote 0
Your formula does not work ....
 

Attachments

  • 1609581049846.png
    1609581049846.png
    76.6 KB · Views: 13
Upvote 0
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: 16
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top