Payment terms (formula) for P&L impact and Cash flow

[Adfinance]

Hi everyone, how are you?
Could I ask you to help me please?

I need to calculate automatically 2 things (see the link below - in the tab "Cost details"):
1/ The P&L impact (taking into account several variables - from cell L13 to cell S13
2/ the Cash position (cashout) based on payment terms, total cost expense, start month and date, etc.. The payment terms may change (from cell D28 to cell D37), so I need dynamic formulas.
Can anyone help me please?

All the details to help you are displayed in the Excel file, in the tab "Instructions". And your outcome will be in the tab "Cost details".
Thank you soooo much for your help, it would be really helpful to me.

Link to access the excel file here:

To send to Mr Excel - version 1.xlsx

docs.google.com

@maabadi

 

[Adfinance]

FORGET POST #90 and only base your work on this new post #91

Hi @maabadi
New file (with an update on tab "CapexOpex" only):

https://docs.google.com/spreadsheets/d/1vxctUdSiMluwvK5UTTgDMcXRVnEjbh2A/edit?usp=drive_web&ouid=114111419884827392378&rtpof=true

Maybe could you just please try this at tab "CapexOpex" only (do not touch the "Executive summary" tab, only work on "CapexOpex" tab, and then I will adjust myself on the tab "Executive summary". The formula will be based on either "BUY" selection or "LEASE" selection.

I - First formula to be put at tab "CapexOpex", from cell P7 to EE17:
> This is for "Capex - Software" at tab "Executive summary", line 28
1/ Let's begin with the formula from P7 to EE7 (same line) then drag and drop.
If "BUY" selection at cell R14 (tab "Cost details"), then the values displayed in "CapexOpex" tab (line 7) will be the following:
The total value from "total contracted amount" (which is the first value on the left side at tab "Cost details", from cell AJ14 to EY14) will be displayed at line 7 tab "CapexOpex" (from cell P7 to EE7).

NOTE THAT THE VALUES RELATED TO "EBIT IMPACT" WILL NOT BE DISPLAYED IN "CAPEX". BUT HERE IN THE EXAMPLE WITH "BUY" IT WILL BE DISPLAYED IN "OPEX" (line 21 in tab "CapexOpex")

The values that are related to "EBIT impact" will be (in this example wih "BUY" selection) all the values after the first one on the left.

The value(s) from the "total contracted amount" are here considered as CAPEX (if "BUY" selection) or are considered as OPEX (if "LEASE selection")
And ... the values from EBIT impact are considered as OPEX (always, no matter what .... it could be "BUY" or "LEASE" ... always OPEX).

Please try to have the right formula at line 7 (from P7 to EE7) of tab "CapexOpex", then we will drag and drop".

2/ If "LEASE" selection at cell R14 then all the values will be from "Total contracted amount", so they will be displayed (ALL OF THEM) in OPEX.


II - Second formula to be put at tab "CapexOpex", but here for "OPEX" from cell P21 to EE31:
This is another formula (in total we will have 2 formulas this one and the one above for Capex).
This formula is for OPEX.
1/ If "BUY" selection (for OPEX section line 21 tab "CapexOpex), the only values to be displayed in OPEX section (here at line 21 from tab "CapexOpex") will be the one related with "EBIT impact" values ....
And, the one with "total contracted amount" WILL NOT be displayed here in OPEX (they will only be displayed in CAPEX, nothing more).

2/ If "LEASE" selection (for OPEX section line 21 tab "CapexOpex) : ALL THE VALUES WILL BE DISPLAYED HERE.
Nothing will be displayed in the CAPEX section at line 7 tab "CapexOpex" ... that's right ... BUT IN THE OPEX section (line 21 tab "CapexOpex"), the values will be displayed, and ALL OF THEM.

Thank you so much @maabadi you are a life savior

Please do tell if my explanations are clear enough.

 

[maabadi]

you don't your idea about my previous work on formula assumptions tab.
1. if it is correct & then you don't want modification on that tab (Only I think I have one mistake, I work formula on rows 28 to 37 at CostDetails tab but should work on rows 1 to 23). don't consider this mistake only see formula is correct or not .
2. it has problems & need to be modified. if have problems, what are them.
3. how we know what is difference between line 28 & 30 or 33 & 35 at Executive Summary tab. ( how we know what rows belong to software & what rows belong to InfraStructure or ...).

 

[Adfinance]

Your previous post #88 has formulas that are wrong because they do not follow what I wanted to have (it does not give the right outcomes).
If "BUY" ... If "LEASE" ...
SO I decided, instead of explaining why it was wrong point by point, that is was better for me to help you with a new tab "CapexOpex" (with everything in it). The only thing is that you have to find the formula there (please follow the post #91, everything is really well explained ... and if there is something you do not understand please do tell).

1/ and 2/ formulas are not correct and don't give the right values, but it's better for you to follow the post #91 (instead of correcting everything) ... because you will need less time to do it. Please follow post #91.
3/ the difference between line 28 and line 30 of tab "Executive summary) is that one is for "CAPEX - software" and the other one is for "OPEX - Software" (same for line 33 and 35 but with INFRASTRUCTURE).
But actually you don't need it anymore. Now this is easier for you, please just see and follow post #91).:

You just have to find the 2 formulas at tab "CapexOpex" for "BUY" or "LEASE" selection (at cell P7 for CAPEX and at P21 for OPEX ... then drag and drop).

And I will use the same structure of the formula and will adapt it for cell P38 and P52.

Thank you so much for your help @maabadi ... this is the last thing we want to do in order for the file to work perfectly!

Best,

 

[maabadi]

Please Check file & Report Problems if see.

 

[Adfinance]

Please Check file & Report Problems if see.

Hi @maabadi thank you so much for your feedback!
This is not exactly that (almost but not exactly that).

The ONLY remaining error are the following:
1/ Let's take line 14 of tab "Cost details"
In total in tab "CapexOpex" we have to find the same total ! So, here the sum of all the values equals to - 2 000 000 $. OK.
So ... the first value of - 1 000 000 is displayed in march 2021 at cell AL14 "Cost details" (as a CAPEX value, because "BUY" selection).
But we have all the other values remaining from cell AM14 to AX14 in thi example at line 14 of tab "Cost details".
These values ARE the ones from "EBIT impact".
We said that (always), when "BUY selection", the first value is for CAPEX. And the next ones are for OPEX.
So technically, these values AFTER the first one - not the first one - (because "BUY" selection) have to appear on the OPEX section.

This is the only issue that has to be fixed, from what I have seen. This is just the formula to be changed in "OPEX" section.
What is displayed in "CAPEX" section is correct.

Thank you so much @maabadi !

 

[maabadi]

Check File Again.
I think this is formula that you wants for OPEX.

 

[Adfinance]

Check File Again.
I think this is formula that you wants for OPEX.

I think that's it! Thank you so much @maabadi !
You are a life savior! If any issue, I will tell you. But so far, everything is PERFECT! THANK YOU SO MUCH AGAIN!

 

[maabadi]

You're Welcome & very happy I can help.

 

[Adfinance]

Hi @maabadi

How are you
2 issues please ... link here of the new file : Google Sheets - create and edit spreadsheets online, for free.

1/ In the"FormulaAssumptions" tab, I don't know why the formulas you inserted lead to errors
2/ in the "CapexOpex" tab, I don't know why when we update the values from cell L14 to R14 (tab "Cost details"), the calculation for OPEX does not work.
Just test it and try it with new values at line 14 (tab "Cost details"), and see at tab "CapexOpex" that at line 21 (first line), nothing appears. It's wrong because it's not updating....

Do you think you can fix these issues please?

Again @maabadi thank you sooooo much!

 

[Adfinance]

Hi @maabadi

How are you
2 issues please ... link here of the new file : Google Sheets - create and edit spreadsheets online, for free.

1/ In the"FormulaAssumptions" tab, I don't know why the formulas you inserted lead to errors
2/ in the "CapexOpex" tab, I don't know why when we update the values from cell L14 to R14 (tab "Cost details"), the calculation for OPEX does not work.
Just test it and try it with new values at line 14 (tab "Cost details"), and see at tab "CapexOpex" that at line 21 (first line), nothing appears. It's wrong because it's not updating....

Do you think you can fix these issues please?

Again @maabadi thank you sooooo much!

Hi @maabadi do you think you can have a look on the post #99?

Thank you so much!