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
With the Last code Copy all of it .
At Excel window Press ALT+F11 to go VBA window. at above panel go to Insert then Module and Paste the code at right window that appeared.
Then close vba Window and at Excel window Save as your file as Macro-Enabled workbook (.xlsm). Now you can use this function same as excel function.
input at first cell (M28)
Excel Formula:
=ArrangeData(M27,$EV28)
then you can drag it to total table or copy to other tables.
 

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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is your file with Function. Only Need to write Formula same as Excel formula.
I copy your Test2 sheet and at Test2(2), I test function for all cells.

ArrangDataFunction
 

Adfinance

New Member
Joined
Jan 1, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
This is your file with Function. Only Need to write Formula same as Excel formula.
I copy your Test2 sheet and at Test2(2), I test function for all cells.

ArrangDataFunction
Hi @maabadi I will try to insert the Macro excel code right now (in the file ... with the steps you mentioned above).
Just in case, do you know if it is possible to re-arrange the formula (without VBA code ... just the initial formula) to make it functionnal (cash flow working from one year to another one)? Or is it impossible?
Link of the file here:

Again, thank you for your undonditionnal help!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,089
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If your final file is this , I add function to it to you can use it. if not, Please upload your main file to I do.
For that formula it is very long & complicated and at wide range, Make slow running of your file.
 

Watch MrExcel Video

Forum statistics

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