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:
Delete Previous code & Try this:
maabadi said:
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
Click to expand...

[/CODE]
Click to expand...
The code does not work. A message or "ERROR" is displayed. >>>>> "COMPILER ERROR : Expected End Sub"
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I forgot to take last line and paste.
After last Line (End With)
Press Enter & Write
End Sub
 
Upvote 0
This is Complete Code:
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
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Also Test This Smaller Code:
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, arr(1 To 5, 1 To 2)
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
    arr(1, 1) = "Annually": arr(2, 1) = "Bi-annually": arr(3, 1) = "Quarterly"
    arr(4, 1) = "Monthly": arr(5, 1) = "Non applicable": arr(1, 2) = 1
    arr(2, 2) = 2: arr(3, 2) = 4: arr(4, 2) = 12: arr(5, 2) = 12
    N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
If .Range("N" & i).Value <> "" 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("L" & i).Value = "Prepaid" Then
        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
                 L = (12 / N) * .Range("T" & i).Value
                 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
                L = (12 / N) * .Range("Q" & i).Value
                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
        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
                    L = (12 / N) * .Range("T" & i).Value
                    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
            End If
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
                    L = (12 / N) * .Range("Q" & i).Value
                    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
Application.EnableEvents = True
End Sub
 
Upvote 0
maabadi said:
Also Test This Smaller Code:
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, arr(1 To 5, 1 To 2)
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
    arr(1, 1) = "Annually": arr(2, 1) = "Bi-annually": arr(3, 1) = "Quarterly"
    arr(4, 1) = "Monthly": arr(5, 1) = "Non applicable": arr(1, 2) = 1
    arr(2, 2) = 2: arr(3, 2) = 4: arr(4, 2) = 12: arr(5, 2) = 12
    N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
If .Range("N" & i).Value <> "" 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("L" & i).Value = "Prepaid" Then
        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
                 L = (12 / N) * .Range("T" & i).Value
                 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
                L = (12 / N) * .Range("Q" & i).Value
                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
        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
                    L = (12 / N) * .Range("T" & i).Value
                    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
            End If
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
                    L = (12 / N) * .Range("Q" & i).Value
                    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
Application.EnableEvents = True
End Sub
Click to expand...
Hi @maabadi
Please correct the last issues (everything is written in details, in the sheet "2. Last issues to be fixes".
Thank you so much for your help!
 
Upvote 0
For more Clarifying.
Please for Case 15. Give me Result with Number. For E.g. if amount is 1000000 and We have Prepaid & Lease for rhytm of annually, which month & with which amount we should put number.
Also for quarterly.
 
Upvote 0
maabadi said:
For more Clarifying.
Please for Case 15. Give me Result with Number. For E.g. if amount is 1000000 and We have Prepaid & Lease for rhytm of annually, which month & with which amount we should put number.
Also for quarterly.
Click to expand...
Ok @maabadi . For Case in Line 15, if we select what you said (amount is 1000000 and We have Prepaid & Lease for rythm of annually ... But with contract duration of 2 years AND starting date is 22/06/21).
1/ For "Annually" : - 1 000 000 $ in June 21 ... - 500 000 $ in May 2022 ... - 500 000 $ in May 2023
2/ For "Quarterly" : - 1 000 000 $ in June 21 ... - 125 000 $ in August 21 (+ 3 months including first month of "starting month & year of bills") ... - 125 000 $ in November 21 ... - 125 000 $ in February 21 ... - 125 000 $ in May 22 ...- 125 000 $ in August 22 ...- 125 000 $ in November 22 ...- 125 000 $ in February 23 ...- 125 000 $ in May 23
 
Upvote 0
You told for Case 15, Monthly & Non applicable is Perfect Means at that manners you don't want total contract amount at first month. but for others want !!!!!!!!
At this situation, I don't see difference between Prepaid & PostPaid Also Lease & Buy. Only Use value from different column. But at your data this different column have same values :unsure: :unsure:
if this macro work correct and your data at column Contract Duration with Year for D & A Imact always same, we can use simpler code than this:
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, arr(1 To 5, 1 To 2)
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
    arr(1, 1) = "Annually": arr(2, 1) = "Bi-annually": arr(3, 1) = "Quarterly"
    arr(4, 1) = "Monthly": arr(5, 1) = "Non applicable": arr(1, 2) = 1
    arr(2, 2) = 2: arr(3, 2) = 4: arr(4, 2) = 12: arr(5, 2) = 12
    N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
If .Range("N" & i).Value <> "" 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("L" & i).Value = "Prepaid" Then
        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
                 L = (12 / N) * .Range("T" & i).Value
                 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
              .Cells(i, K).Value = .Range("N" & i).Value * -1
              If .Range("Q" & i).Value = 0 Then
                    GoTo Step2
              Else
                L = (12 / N) * .Range("Q" & i).Value
                M = (.Range("N" & i).Value / L) * -1
                For j = K + N To K + N - 1 + 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
        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
                    L = (12 / N) * .Range("T" & i).Value
                    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
            End If
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
              .Cells(i, K).Value = .Range("N" & i).Value * -1
                    L = (12 / N) * .Range("Q" & i).Value
                    M = (.Range("N" & i).Value / L) * -1
                For j = K + N To K + N - 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
 
Upvote 0
maabadi said:
You told for Case 15, Monthly & Non applicable is Perfect Means at that manners you don't want total contract amount at first month. but for others want !!!!!!!!
At this situation, I don't see difference between Prepaid & PostPaid Also Lease & Buy. Only Use value from different column. But at your data this different column have same values :unsure: :unsure:
if this macro work correct and your data at column Contract Duration with Year for D & A Imact always same, we can use simpler code than this:
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, arr(1 To 5, 1 To 2)
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
    arr(1, 1) = "Annually": arr(2, 1) = "Bi-annually": arr(3, 1) = "Quarterly"
    arr(4, 1) = "Monthly": arr(5, 1) = "Non applicable": arr(1, 2) = 1
    arr(2, 2) = 2: arr(3, 2) = 4: arr(4, 2) = 12: arr(5, 2) = 12
    N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
If .Range("N" & i).Value <> "" 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("L" & i).Value = "Prepaid" Then
        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
                 L = (12 / N) * .Range("T" & i).Value
                 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
              .Cells(i, K).Value = .Range("N" & i).Value * -1
              If .Range("Q" & i).Value = 0 Then
                    GoTo Step2
              Else
                L = (12 / N) * .Range("Q" & i).Value
                M = (.Range("N" & i).Value / L) * -1
                For j = K + N To K + N - 1 + 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
        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
                    L = (12 / N) * .Range("T" & i).Value
                    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
            End If
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
              .Cells(i, K).Value = .Range("N" & i).Value * -1
                    L = (12 / N) * .Range("Q" & i).Value
                    M = (.Range("N" & i).Value / L) * -1
                For j = K + N To K + N - 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...
Hi @maabadi
To be more "straightforward":

1/ When "Prepaid" and BUY >> we have displayed both (i) the "Total contracted amount" at starting month & year ... AND the EBIT impact (see tab "2. Last issues to be fixed" to understand the details of EBIT impact)
2/ When "Prepaid" and LEASE >> here, just display the "Total contracted amount" : this total value will be split into many values (see tab "2. Last issues to be fixed" to understand the details of EBIT impact)
3/ When "Postpaid" and BUY >> Same as Prepaid, BUT the "total contract amount" displayed at "STARTING MONTH & YEAR" + "HOW MANY MONTHS ..." ... For the EBIT impact, please see tab "2. Last issues to be fixed" to understand the details of EBIT impact)
4/ When "Postpaid" and LEASE >> Same as Prepaid but
(i) The "total contract amount" is displayed at "STARTING MONTH & YEAR" + "HOW MANY MONTHS ..."
(i) The EBIT impact, please see tab "2. Last issues to be fixed" to understand the details of EBIT impact
 
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