Payment term and Cash flow (formula linked with costs)

Adfinance

New Member
Joined
Jan 1, 2021
Messages
26
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: 39

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
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
26
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,551
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,126,897
Messages
5,621,498
Members
415,845
Latest member
marej123

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