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

 

[Fluff]

That file requires a sign-in, you need to mark the file for sharing & post the link you are given to the board.

 

[Adfinance]

Please go to post #3 for the link of the file.

Hi @maabadi

Please take into account my comments (sheet "New comments on options" in the above link).You will see that I put 8 different case scenarios.
Taking into account all of that will make the file ready to be used!

Looking forward to your answer, and very grateful for your help my dear @maabadi !
Thank you!

 

[Fluff]

Please go to post #3 for the link of the file.

That also requires a password. Please mark the file for sharing & post the share link you get.

 

[Adfinance]

Hi @maabadi

Please take into account my comments (sheet "New comments on options" in the above link post #3).
You will see that I put 8 different case scenarios.
Taking into account all of that will make the file ready to be used!

Looking forward to your answer, and very grateful for your help my dear @maabadi !
Thank you!

 

[Fluff]

@Adfinance You seem to be totally ignoring my comments. Those links require people to sign in. Post a link to the shared file that does not require a password.

 

[Adfinance]

@Adfinance You seem to be totally ignoring my comments. Those links require people to sign in. Post a link to the shared file that does not require a password.

Hi @Fluff how are you my dear ?
I am sorry, I didn't understood your comments when you asked a password
But now I understand that you want to have access to the file (i.e. #post 3). Yes, of course my dera @Fluff !!

Now, with this link you can access the file without any password : 2020 05 15 - Explications Mr Excel (Only for P&L).xlsx

Thank you so much again for your unconditional help @Fluff

 

[maabadi]

Two Questions about Description, Case 2,
1. You told Quarterly means each 4 month, I think it means 4 times at the year & it Means each 3 month. Which One I should Use? Your Suggestion or Mine?

2. At Case 3, Last two Line Means You Only want value for First month if Ebit is 0 ? or .....

 

[Adfinance]

Two Questions about Description, Case 2,
1. You told Quarterly means each 4 month, I think it means 4 times at the year & it Means each 3 month. Which One I should Use? Your Suggestion or Mine?

2. At Case 3, Last two Line Means You Only want value for First month if Ebit is 0 ? or .....

Hi @maabadi
1/ Yes you are right, it's a mistake. Quarterly means each 3 months. My mistake. This is for case n°2 (cases are just an example for you to produce the best macro that includes everything).
In this example (case 2), the calculation still remains the same: - 1 000 000 $ / 2 years / 4 quarters a year = - 20 833 $ (each quarter of 3 months).
2/ At case 3, this was just to show you that it's an exception. It's "prepaid" and it's also "BUY", so every thing from the "total contracted amount" is displayed in the "starting month & year of bills".
But we still have an EBIT impact.... And when we see the details ("contract duration" = 0 ; "# years for D&A impact" = 0)

So it means that the value of EBIT impact will be "0" everywhere. So at the end, yes, we will only have the following value - 1 000 000 $ (in July 2021).

Please ask me again if you need any further info or if it's not clear

Thank you SO MUCH for your help @maabadi

 

[maabadi]

One Other Question. At Case 2 you told at Lease manner, starting input data one month after starting month but at text you write same month.
Both is June (month 6). Is it right? Or started from July?

 

[maabadi]

I don't Understand Difference between Case 5 & Case 7.
Also Add this Macro with Instruction I give at Post #8

Try this works step to step to see what occurs:
1. At excel window, Press ALT+F11
2. At the windows appears (VBA Window), Press Insert, then Select Module.
3. At the windows open at the right side, paste Code at Below.
4. Close VBA window, & At excel window save file as Macro-Enabled Workbook (.xlsm)
5. After Input all data at source columns ( Payment Term, Cost Detail, Buy or Lease ,....) You can run macro. Until you don't run macro you don't see Results.
6. For this Purpose, Go to View Tab, Select Last Item Macros, Then View Macros ( Or Press ALT, W, M , V ) and Select CostDetails then Press Run.
7. Now if you Come back to excel window, you see results at cells you want.
8. If You want add shortcut to when Press it , Macro Runs, Do step 6 except run, after select macro name go to Option & at Shortcut section, you can specify one shortcut. for example. if you hold SHIFT & Press R, then you see your shortcut key is CTRL+SHIFT+R and when Press it Macro Runs.

Sub CostDetails()
Dim i As Long, j As Long, Target As Range, K As Long, Lr As Long, Lc As Long, Sh As Worksheet
Dim M As Double, L As Long, N As Long
Set Sh = Sheets("Cost Details")
Application.EnableEvents = False
With Sh
Lr = .Range("N" & Rows.Count).End(xlUp).Row
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
For i = 14 To Lr
Range(.Cells(i, 36), .Cells(i, Lc)).ClearContents
If .Range("N" & i).Value <> "" Then
    If .Range("L" & i).Value = "Prepaid" Then
        K = Application.WorksheetFunction.EoMonth(.Range("O" & i).Value, .Range("P" & i).Value) - 1
        K = Application.WorksheetFunction.Match(K, Range(.Cells(13, 1), .Cells(13, Lc)))
 
        If .Range("R" & i).Value = "BUY" Then
            If .Range("S" & i).Value = "YES" Then
                Select Case .Range("M" & i).Value
                    Case "Non applicable"
                     .Cells(i, K).Value = .Range("N" & i).Value * -1
                      If .Range("T" & i).Value = 0 Then
                       GoTo Step2
                      Else
                        L = 12 * .Range("T" & i).Value
                        N = 1
                      End If
                    Case "Monthly"
                        L = 12 * .Range("T" & i).Value
                        N = 1
                    Case "Quarterly"
                        L = 4 * .Range("T" & i).Value
                        N = 3
                    Case "Bi-Annually"
                        L = 2 * .Range("T" & i).Value
                        N = 6
                    Case "Annually"
                        L = .Range("T" & i).Value
                        N = 12
                End Select
                    M = (.Range("N" & i).Value / L) * -1
     
                For j = K + 1 To K + 12 * .Range("T" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
            ElseIf .Range("S" & i).Value = "NO" Then
              .Cells(i, K).Value = .Range("N" & i).Value * -1
            End If

        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
                Select Case .Range("M" & i).Value
                 Case "Non applicable"
                  If .Range("Q" & i).Value = 0 Then
                    GoTo Step2
                  Else
                    L = 12 * .Range("Q" & i).Value
                    N = 1
                  End If
                 Case "Monthly"
                  L = 12 * .Range("Q" & i).Value
                  N = 1
                 Case "Quarterly"
                  L = 4 * .Range("Q" & i).Value
                  N = 3
                 Case "Bi-Annually"
                  L = 2 * .Range("Q" & i).Value
                  N = 6
                 Case "Annually"
                  L = .Range("Q" & i).Value
                  N = 12
                End Select
                    M = (.Range("N" & i).Value / L) * -1
                For j = K To K - 1 + 12 * .Range("Q" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
            ElseIf .Range("S" & i).Value = "YES" Then
                .Cells(i, K).Value = .Range("N" & i).Value * -1
            End If
        End If

    End If

    If .Range("L" & i).Value = "Postpaid" Then
        K = Application.WorksheetFunction.EoMonth(.Range("O" & i).Value, .Range("P" & i).Value) - 1
        K = Application.WorksheetFunction.Match(K, Range(.Cells(13, 1), .Cells(13, Lc)))
     
        If .Range("R" & i).Value = "BUY" Then
           .Cells(i, K).Value = .Range("N" & i).Value * -1
            L = 12 * .Range("Q" & i).Value
            N = 1
            M = (.Range("N" & i).Value / L) * -1
            For j = K + 1 To K + 12 * .Range("T" & i).Value Step N
               .Cells(i, j).Value = M
            Next j
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
                Select Case .Range("M" & i).Value
                 Case "Monthly"
                  L = 12 * .Range("Q" & i).Value
                  N = 1
                 Case "Quarterly"
                  L = 4 * .Range("Q" & i).Value
                  N = 3
                 Case "Bi-Annually"
                  L = 2 * .Range("Q" & i).Value
                  N = 6
                 Case "Annually"
                  L = .Range("Q" & i).Value
                  N = 12
                End Select
                    M = (.Range("N" & i).Value / L) * -1
                For j = K To K - 1 + 12 * .Range("Q" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
            ElseIf .Range("S" & i).Value = "YES" Then
                .Cells(i, K).Value = .Range("N" & i).Value * -1
            End If
        End If
    End If
Step2:
 End If
Next i

End With
Application.EnableEvents = True
End Sub

After Running Macro, Check Results, if you see problems with each case, Please report it with Cell Rows & Case Number .

if Don't See Problem we Can do Next Step

We have Two Other Option.
1. WorkSheet Change Event: this one Automatically triggered when you add data at Columns N to T. at the first you only need to add code to sheet and Save it at Macro-Enabled Workbook(.xlsm). after add and Save each time you add data at column N to T it automatically triggered and you can see results.

2. UDF (User-Defined Function): If you use this file at only one computer. then you can add your macro to your Personal-Macro Workbook and then with save file as Normal Excel file you can Use UDF same as Excel function. I described more Later.