Payment term and Cash flow (formula linked with costs)

[Adfinance]

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?

 

[maabadi]

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)

=ArrangeData(M27,$EV28)

then you can drag it to total table or copy to other tables.

 

[maabadi]

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]

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:

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Again, thank you for your undonditionnal help!

 

[maabadi]

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.

 

[icn0006]

@maabadi

Hi there! I am actually looking at a similar issue within my organization. I know this thread is old but is there a way you may be able to provide the spreadsheet? Thanks!

 

[maabadi]

Welcome To MrExcel Message Board.
Please Ask your question as New Thread. & if you want to also I check your thread

 

[Adfinance]

hi @maabadi
ok perfect, I will ask in a new thread, thank you so much.

 

[ryzhaja]

Guys, any chance that someone could explain me what INDEX($B5:$M5>0,0) supposed to do? Please need your help.

 

[maabadi]

@ryzhaja
Welcome to MrExcel Message Board.
Please Ask your question as New Thread (Post Thread). At That situation, your question is unanswered has many visitor than now and you Take better answers.