Hi everyone, I need to calculate automatically the cash position (cashout) based on payment terms and costs.
The payment terms or the values for expenses may change (inserted manually), so I need a dynamic formula for the cashout position (from cell F19 to Q28).
Attached you will see that there is a section with "P&L impact", with the listing of all the costs and their value from January 2021 to December 2021, 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 5000 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.
If in the same line we have another value for Costs (let’s say 8000 in cell K5), we will consider the same payment term (in cell S19) for the entire line 5.
The same idea with line 6, linked with Payment term in cell S20, etc.
Can anyone help me please?
Link to access the file is the following:
The payment terms or the values for expenses may change (inserted manually), so I need a dynamic formula for the cashout position (from cell F19 to Q28).
Attached you will see that there is a section with "P&L impact", with the listing of all the costs and their value from January 2021 to December 2021, 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 5000 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.
If in the same line we have another value for Costs (let’s say 8000 in cell K5), we will consider the same payment term (in cell S19) for the entire line 5.
The same idea with line 6, linked with Payment term in cell S20, etc.
Can anyone help me please?
Link to access the file is the following:
For Mr Excel.xlsx
drive.google.com