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

A

Adfinance

Board Regular
Joined
Jan 1, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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:
docs.google.com

To send to Mr Excel - version 1.xlsx

docs.google.com docs.google.com

@maabadi
 
Sort by date Sort by votes
maabadi said:
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?
Click to expand...
Hi @maabadi
For case 2, it's "prepaid" and "lease", so because it's prepaid (whether it's LEASE or BUY) we should have the first expense at the "starting month & year" (june 2021).
REMEMBER that because it's LEASE, the "total contracted amount" will be split into 2 years (in that example) and QUARTERLY (also in that example). And when we split it it's : - 1 000 000 $ / 2 years / 4 quarters a year =- 125 000 $ (per quarter, for 2 years ... to finally have a total of 1 000 000 $).

Note : when Prepaid, the first expense always start at the "starting month & year" ... BUT 2 options are possible :
1/ If we have a "BUY", then first month we put the "total contracted" amount, AND next months we will have the EBIT impact taken into consideration.
2/ OR, second option : "LEASE" selection, so we DO NOT have any EBIT impact (only "total contracted amount" that will be split depending on "rythm of invoice" and "contract duration"

Any doubt please do tell :)
Thank you again for your help @maabadi !
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
maabadi said:
I don't Understand Difference between Case 5 & Case 7.
Also Add this Macro with Instruction I give at Post #8


VBA Code: 
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
Click to expand...
@maabadi
as the file will be used by other people (not just one computer), maybe let's choose option 1?
 
Upvote 0
maabadi said:
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
Click to expand...
Do I have to erase the first VBA code and put this one? Or do I have to keep the last one and also put this new one? Thanks @maabadi
 
Upvote 0
Delete previous. Don't need it.
After Running macro, See result & Report problems, With Case No.
For Option 2 we also can save file as .xlsm and use at all PC.
 
Upvote 0
maabadi said:
Delete previous. Don't need it.
After Running macro, See result & Report problems, With Case No.
For Option 2 we also can save file as .xlsm and use at all PC.
Click to expand...
Hi @maabadi

I hope this finds you well.
I reported ALL the problems in a new tab named "New issues to be fixed".
Do you think you can correct everything, to be sure the VBA code works well?
Thank you so much.
Link of the shared file: 2020 05 15 - Explications Mr Excel (Only for P&L).xlsx
 
Upvote 0
1. Please Tell Problems within Post to I don't obligate download file each time to only see your suggestions.

2. One Question You told Before at The file:
At PostPaid: REMEMBER that if we select "BUY" in cell R14, the total value will be displayed once (and not partially). It does not matter what we have for "Rythm of Invoice" (cell M14) or "Contract duration" (cell Q14) … the value will be displayed totally.

And at the last file (Case 5 & 7 : Row 18 & 20) you want Display it partially?! I confused.
 
Last edited:
Upvote 0
maabadi said:
1. Please Tell Problems within Post to I don't obligate download file each time to only see your suggestions.

2. One Question You told Before at The file:
At PostPaid: REMEMBER that if we select "BUY" in cell R14, the total value will be displayed once (and not partially). It does not matter what we have for "Rythm of Invoice" (cell M14) or "Contract duration" (cell Q14) … the value will be displayed totally.

And at the last file (Case 5 & 7 : Row 18 & 20) you want Display it partially?! I confused.
Click to expand...
Ok @maabadi
2/ What will be displayed but splited will be the "EBIT impact" (depends on "rythm of invoice", "contract duration", etc.
And "total contracted amount" will be displayed totally, once . For postpaid it will be displayed at: "starting month & year" + "if "how many months after starting date" (example: starting date is 22/05/21 and "how many months ..." is +3 months ... so it will be displayed in August 2021.

I dont' understand why we tell me that I want to display it partially. I said "not partially, but totally" ahah. Please be more specific, in order to answer :)

1/ We have many issues remaining, so I decided to put it in the excel file in a new tab "New issues to be fixed", it was more simple this way. Is it fine for you?


Thank you again @maabadi
 
Upvote 0
OK. One example for clarifying. At Postpaid & Ebit equal YES, If Starting month is 2021/05/21 and 4 month after if Rhytm of invoice is Quarterly Then you want to Display
1. Total amount at September And after that
2. Total amount at each 3 month until end of Contract Duration.

Or
2. Partial amount after September each 3 month.
 
Upvote 0
maabadi said:
OK. One example for clarifying. At Postpaid & Ebit equal YES, If Starting month is 2021/05/21 and 4 month after if Rhytm of invoice is Quarterly Then you want to Display
1. Total amount at September And after that
2. Total amount at each 3 month until end of Contract Duration.

Or
2. Partial amount after September each 3 month.
Click to expand...
For you example in post 28 ...
1/ First the "total contracted amount" will be displayed in September 21 yes ("starting month & year" + 4 months. ("total contract amount" in the case is
- 500 000 $).
2/ Then we also have the EBIT impact here.
> If "quartelry" it starts in december 21 (+3 month after "total contracted amount"), and then each 3 months we have the same value, until getting in total as well the same as "total contracted value"). Here for instance the contract duration is 1 year so : - 125 000 $ in december 21, - 125 000 $ in march 22 (+3 months), - 125 000 $ in june 21 (+3 months) AND - 125 000 $ in September 21 (+3 months)

Thanks @maabadi
 
Upvote 0
Delete Previous code & Try this:
Report problem with Case No. & Rhytm of invoice.
If you want show them at excel file upload it with XL2BB Addin (very good addin).
VBA Code: 
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
                .Cells(i, K).Value = .Range("N" & i).Value * -1
                 If .Range("T" & i).Value = 0 Then
                       GoTo Step2
                 Else
                 Select Case .Range("M" & i).Value
                    Case "Non applicable"
                        L = 12 * .Range("T" & i).Value
                        N = 1
                    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 + N To K + N - 1 + 12 * .Range("T" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
                End If
            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
              If .Range("Q" & i).Value = 0 Then
                    GoTo Step2
              Else
                Select Case .Range("M" & i).Value
                 Case "Non applicable"
                    L = 12 * .Range("Q" & i).Value
                    N = 1
                 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 + N - 1 To K + N - 2 + 12 * .Range("Q" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
              End If
            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
            If .Range("S" & i).Value = "YES" Then
                
                 If .Range("T" & i).Value = 0 Then
                       GoTo Step2
                 Else
                 Select Case .Range("M" & i).Value
                     Case "Non applicable"
                        L = 12 * .Range("T" & i).Value
                        N = 1
                    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 + N + 1 To K + N + 12 * .Range("T" & i).Value Step N
                  .Cells(i, j).Value = M
                Next j
                End If
            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"
                  L = 12 * .Range("Q" & i).Value
                  N = 1
                 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 + N - 1 To K + N - 2 + 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
Payment terms (formula) for P&L impact and Cash flow - HELP
Replies
1
Views
3K
Fluff
Fluff
A
  • Question Question
P&L impact and Cash flow impact (for expenses) - with payment terms
Replies
1
Views
1K
Adfinance
A
ricafonyat
  • Solved
Loans Payments Consolidated Cash Flow
Replies
1
Views
381
DRSteele
DRSteele
A
  • Question Question
Payment term and Cash flow (formula linked with costs)
3 4 5
Replies
48
Views
16K
maabadi
M
G
  • Question Question
Payment term and cash flow (formula linked with costs) - weekly cash flow
Replies
1
Views
2K
jorismoerings
J

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top