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

Instructions >> To fill in (in tab "Cost details") From line 13 to 24: this is for Profit and loss impact From line 26 to 38 : this is for Cash flow impact Hi everyone, I need to calculate automatically 2 things (in the tab "Cost details"): 1/ The P&L impact (taking into account several variabl...

docs.google.com

@maabadi

 

[maabadi]

Try this at AJ28:

=IF(COLUMNS($AJ:AJ)-1>=$D28,IF($R14="LEASE",IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),IF(ABS(OFFSET(AJ14,0,$D28*-1,1,1))=$N14,IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),"")),"")

 

[Adfinance]

Try this at AJ28:

=IF(COLUMNS($AJ:AJ)-1>=$D28,IF($R14="LEASE",IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),IF(ABS(OFFSET(AJ14,0,$D28*-1,1,1))=$N14,IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),"")),"")

@maabadi There is an error, which is the following : "#VALUE".

Could you fix it please? I tried the function "IFERROR" but I am not sure if that's correct
Thank you!


Also, could you give me this change in the VBA code ? (the one with the ADVANCED macro)? Thank you so much.

 

[maabadi]

1. Please test it at my last updated file.
2. This part not in VBA Macro, because you want formula for this.

 

[maabadi]

1. About #value Error, Try this:

=IF(COLUMNS($AJ:AJ)-1>=$D28,IF($R14="LEASE",IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),IF(OFFSET(AJ14,0,$D28*-1,1,1)=$N14*-1,IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),"")),"")

2.this formula don't have in Macro.
3. Are ADVANCED Macro = Worksheet Change Event Macro (Macro that automatically run)??

 

[Adfinance]

1. About #value Error, Try this:

=IF(COLUMNS($AJ:AJ)-1>=$D28,IF($R14="LEASE",IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),IF(OFFSET(AJ14,0,$D28*-1,1,1)=$N14*-1,IF(OFFSET(AJ14,0,$D28*-1,1,1)="","",OFFSET(AJ14,0,$D28*-1,1,1)),"")),"")

2.this formula don't have in Macro.
3. Are ADVANCED Macro = Worksheet Change Event Macro (Macro that automatically run)??

Hi @maabadi
1/ Thank you, it works perfectly
2/ Yes, I know that the formula does not have any macro ... I was talking about the worksheet change event macro
3/ Yes exactly. Do you think you can provide me with this also?

 

[Adfinance]

Hi @maabadi
1/ Thank you, it works perfectly
2/ Yes, I know that the formula does not have any macro ... I was talking about the worksheet change event macro
3/ Yes exactly. Do you think you can provide me with this also?

Oh no sorry. You are absolutely right. The CASH FLOW impact sectio, is a formula. It is not part of VBA excel.
Thank you. Let's forget it

 

[Adfinance]

Oh no sorry. You are absolutely right. The CASH FLOW impact sectio, is a formula. It is not part of VBA excel.
Thank you. Let's forget it

Hi @maabadi

2 things remaining and then everything will be perfect.

1/ In the "cost details" tab (cell AJ14), I dont' know why the formula does not update when we change the value at columns L to R ... or when we change the starting date only at cell AJ13 (for example .... march 2023 or ... june 2026).
Could you make sure that all the values from AJ14 to EY23 will update automatically, when we modify inputs ? (in the formula + in the Worksheet Change Event Code)

2/ In the "Executive summary" tab, from line 28 to 36, this is divided into 2 parts : "CAPEX" and "OPEX".

"Opex" (from line 33 to 36 of "Executive summary" tab) is supposed to follow the following rules:
> We select "OPEX" in "Cost details" tab (column D)
> The actual formula SUMIF is partly correct ... IF "OPEX" selected .. take the total values (per year) from columns Y to AH .. OK .. but the only issue here is that EVRY VALUE FROM EBIT IMPACT (from line 14 to 23, from line 44 to 53 and from line 74 to 83) will be considered as part of OPEX (and not Capex, even if we select here "Capex" at column D. Do you think you can find the right formula here for that (formula to be put in the "Executive summary, line 33 to 36, to begin with cell E33)

To be more straightforward ...
If "OPEX" and "BUY" selection : only take OUT the first value on the left which is the value from the "total contracted amount" (this is CAPEX) ... and let all the other values after that .. which are values from EBIT impact (they are OPEX values ... EBIT impact is OPEX, always).
If "OPEX" and "LEASE" selection : let all the values in the sum (in tab "Exec summary", line 33 to 36).

"Capex" (from line 27 to 31 of "Executive summary" tab) is supposed to follow the following rules:
> We select "CAPEX" in "Cost details" tab (column D)
> All the values displayed from column AJ to EY are not all part of CAPEX (line 27 to 31, tab "Executive summary"). See below ...

To be more straightforward ...
If "CAPEX" and "BUY" selection : only let the first value on the left which is the value from the "total contracted amount" (this is CAPEX) ... and TAKE OUT all the other values after that .. which are values from EBIT impact (they are OPEX values ... EBIT impact is OPEX, always).
If "CAPEX" and "LEASE" selection : TAKE OUT all the values in the sum (in tab "Exec summary", line 33 to 36). Final value in CApex will be "0" (in tab "exec summary", from line 27 to 31.

Thank you so much @maabadi
Link of the final file to take into consideration for the work to do here:

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

 

[maabadi]

For Part1:
Formula for K at FormulaAssumption Sheet was changed and show error. I correct it.
For Part 2: Give me Example with numbers for all 4 situations please.

 

[Adfinance]

For Part1:
Formula for K at FormulaAssumption Sheet was changed and show error. I correct it.
For Part 2: Give me Example with numbers for all 4 situations please.

1/ Thank you
2/ Ok, let's have some examples ... From line 14 to 17 at tab "Cost details":

Google Sheets: Sign-in

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

docs.google.com

Note that lines 28 to 36 are divided into 4 sections BUT only 2 sections are interesting for us (lines 28 / 30 / 33 / 35) ... SOFTWARE and INFRASTRUCTURE (do not touch the other ones please):
(i) SOFTWARE (see cell C10 at tab "Cost details" and also lines 28 / 30)
(ii) INFRASTUCTURE (see cell C40 at tab "Cost details" and also lines 33 / 35)

Examples below ... (Ohhh ... let's forget about the column D, this won't be taken into consideration OK?)

Line 14 (tab "cost details): "BUY" selection > The Cost details table is "divided" into 2 parts ... the "total contracted amount" which is the first value on the left side (from colum AJ to EY) AND the values that are related to "EBIT impact" (these values are the next ones, all the next one in the example).

The value(s) from the "total contracted amount" are here considered as CAPEX value(s) (in the tab "Executive summary") and the values from "EBIT impact" are ALWAYS considered as OPEX values (in the tab "Executive summary").
> First value of - 1 000 000 $ is the "total contracted amount" (always the first value to appear on the left side of the line), so it's CAPEX in tab "Executive summary" (because "BUY" selection in tab "Cost details").
> The next values are from "EBIT impact" so they will be counted as part of OPEX (in tab "Executive summary).

Line 15 (tab "cost details): "LEASE" selection > The Cost details table is "divided" into 1 part only here ... LEASE means we only have "total contracted amount" (NO EBIT impact!) but this total value is split into many small values ... all of them are considered as OPEX (always, in every situation) in the tab "Executive summary", because we do not BUY, we only LEASE (so, NO "CAPEX" investment here, BUT ONLY "OPEX" values).

Please tell me if it's clear enough

Thank you so much @maabadi

 

[Adfinance]

1/ Thank you
2/ Ok, let's have some examples ... From line 14 to 17 at tab "Cost details":

Google Sheets: Sign-in

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

docs.google.com

Note that lines 28 to 36 are divided into 4 sections BUT only 2 sections are interesting for us (lines 28 / 30 / 33 / 35) ... SOFTWARE and INFRASTRUCTURE (do not touch the other ones please):
(i) SOFTWARE (see cell C10 at tab "Cost details" and also lines 28 / 30)
(ii) INFRASTUCTURE (see cell C40 at tab "Cost details" and also lines 33 / 35)

Examples below ... (Ohhh ... let's forget about the column D, this won't be taken into consideration OK?)

Line 14 (tab "cost details): "BUY" selection > The Cost details table is "divided" into 2 parts ... the "total contracted amount" which is the first value on the left side (from colum AJ to EY) AND the values that are related to "EBIT impact" (these values are the next ones, all the next one in the example).

The value(s) from the "total contracted amount" are here considered as CAPEX value(s) (in the tab "Executive summary") and the values from "EBIT impact" are ALWAYS considered as OPEX values (in the tab "Executive summary").
> First value of - 1 000 000 $ is the "total contracted amount" (always the first value to appear on the left side of the line), so it's CAPEX in tab "Executive summary" (because "BUY" selection in tab "Cost details").
> The next values are from "EBIT impact" so they will be counted as part of OPEX (in tab "Executive summary).

Line 15 (tab "cost details): "LEASE" selection > The Cost details table is "divided" into 1 part only here ... LEASE means we only have "total contracted amount" (NO EBIT impact!) but this total value is split into many small values ... all of them are considered as OPEX (always, in every situation) in the tab "Executive summary", because we do not BUY, we only LEASE (so, NO "CAPEX" investment here, BUT ONLY "OPEX" values).

Please tell me if it's clear enough

Thank you so much @maabadi

@maabadi ?