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

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 docs.google.com

@maabadi
 
Sort by date Sort by votes
This is Formula for payment Term as Days and All Numbers( Positive & Negative) . Input at AJ28 then Drag right & Down:
Excel Formula: 
=IFERROR(IF(SUM($AJ14:AJ14)=0,0,IF(AND(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28>=AJ$27+1,INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28-1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)),IF(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28>=AJ$27+1,0,INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28>0,0),0),1))))),"")

For Input As Month You after inputing as Month value at Column D change All $D28 at formula to $D28 * 30 then Drag right & Down:
Excel Formula: 
=IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,0,IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30-1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30>=AJ$27+1,0,ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")

For type MISMatch Try 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), x As Long
Set Sh = Sheets("Cost Details")
Application.EnableEvents = False
With Sh
Lr = .Range("N74").End(xlDown).Row
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
x = 1
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
If .Range("N" & i).Value <> "" Then
        N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
        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 To K - 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
                    L = (12 / N) * .Range("Q" & i).Value
                    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
If i = 30 * x - 7 Then
x = x + 1
i = i + 20
End If
Next i

End With
Application.EnableEvents = True
End Sub
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
maabadi said:
This is Formula for payment Term as Days and All Numbers( Positive & Negative) . Input at AJ28 then Drag right & Down:
Excel Formula: 
=IFERROR(IF(SUM($AJ14:AJ14)=0,0,IF(AND(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28>=AJ$27+1,INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28-1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)),IF(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0))+$D28>=AJ$27+1,0,INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28>0,0),0),1))))),"")

For Input As Month You after inputing as Month value at Column D change All $D28 at formula to $D28 * 30 then Drag right & Down:
Excel Formula: 
=IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,0,IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30-1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30>=AJ$27+1,0,ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")

For type MISMatch Try 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), x As Long
Set Sh = Sheets("Cost Details")
Application.EnableEvents = False
With Sh
Lr = .Range("N74").End(xlDown).Row
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
x = 1
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
If .Range("N" & i).Value <> "" Then
        N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)
        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 To K - 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
                    L = (12 / N) * .Range("Q" & i).Value
                    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
If i = 30 * x - 7 Then
x = x + 1
i = i + 20
End If
Next i

End With
Application.EnableEvents = True
End Sub
Click to expand...
Hi @maabadi

1/ Your VBA still does not work with my file. Still the same error.
I sent you in your personal email the last version of the file.
Could you check directly in that version and fix this issue in that excel file?

2/ Regarding the formula for payment terms, I still can not see if that works or not, I need to wait for your VBA code to work. (But I already put the formulas in the righ seet for now.. you will see)
 
Upvote 0
maabadi said:
Try This file. I changed payment terms from Days to Months also & add formula.
Also Add macro to view Tab. when press it macro runs.
Model for Maabadi (30.05.21) vF - Macro Enabled
Click to expand...
Hi @maabadi

Thank you so much. 2 issues remaining:
1/ The VBA code does not work in the following lines (tab "cost details"): 22 / 23 / 52 / 53 / 82 / 83
2/ The formula for Payment terms does not seem to work well. Could you please check and fix it?
Let's take line 14 as an example (line 14 is linked to 28, line 15 is linked to line 29, and so on).
If in line 14 we have: - 1 000 000 $ in June 21 and - 83 333 $ and so on .... AND if we have as payment terms + 2months selected (remember that payment term begin with + 0 month) at cell D28 ... then we should move these values 2 months after (august 21 for the - 1 000 000 $ / september 21 for the first - 83 333 $ / and so on). Same thing for everything in Payment terms.

Additional note: please be sure that we have no values at column AI (I saw some values there, but we are not supposed to begin at column AI ... we begin at column AJ only).

Could you apply these changes and make sure it's working?
Again thank you SO MUCH @maabadi
 
Upvote 0
1. VBA Works but your year at that rows is 2026. you should check that columns Not First columns.;):ROFLMAO::ROFLMAO:???
2.Change formula at AJ28 to this & Drag it right & down. then copy to the two other parts (Rows 58 to 67, & Rows 88 to 97):
Excel Formula: 
IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,0,IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30 + 2>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 2>=AJ$27+1,0,ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")

AND if you don't want to see any zero value Change formula to this:

Excel Formula: 
IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,"",IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30 + 2>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 2>=AJ$27+1,"",ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")
 
Last edited:
Upvote 0
maabadi said:
1. VBA Works but your year at that rows is 2026. you should check that columns Not First columns.;):ROFLMAO::ROFLMAO:???
2.Change formula at AJ28 to this & Drag it right & down. then copy to the two other parts (Rows 58 to 67, & Rows 88 to 97):
Excel Formula: 
IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,0,IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30 + 2>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 2>=AJ$27+1,0,ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")

AND if you don't want to see any zero value Change formula to this:

Excel Formula: 
IFERROR(IF(ABS(SUM($AJ14:AJ14))=0,"",IF(AND(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28 * 30 + 2>=AJ$27+1,ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 1<=EOMONTH(AJ$27,0)),INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)),IF(ABS(INDEX($AJ$13:$EY$13,,MATCH(TRUE,INDEX($AJ14:$EY14<0,0),0)))+$D28*30 + 2>=AJ$27+1,"",ABS(INDEX($AJ14:$EY14,,MATCH(TRUE,INDEX($AJ14:$EY14>0,0),0)+COLUMNS($AJ28:AJ28)+1-IFERROR(MATCH(TRUE,INDEX($AI28:AI28<0,0),0),1)))))),"")
Click to expand...
1/ You are absolutely right ahahah, thank you @maabadi
2/ The formula still does not work. It takes into account just a part of the values, not all the values. Your formula only put the first value. It has to put all the value in the payment terms :) Would it be possible to fix this please? Thank you again.

As a reminder :
Let's take line 14 as an example (line 14 is linked to 28, line 15 is linked to line 29, and so on).
If in line 14 we have: - 1 000 000 $ in June 21 and - 83 333 $ and so on .... AND if we have as payment terms + 2months selected (remember that payment term begin with + 0 month) at cell D28 ... then we should move these values 2 months after (august 21 for the - 1 000 000 $ / september 21 for the first - 83 333 $ / and so on). Same thing for everything in Payment terms.

Thanks @maabadi
 
Upvote 0
I think this simple formula work very better for you. try it at AJ28 then drag it right &down:
Excel Formula: 
=IF(COLUMNS($AJ:AJ) - 1 >= $D28,OFFSET(AJ14,0, $D28*-1,1,1),"")
 
Upvote 0
maabadi said:
I think this simple formula work very better for you. try it at AJ28 then drag it right &down:
Excel Formula: 
=IF(COLUMNS($AJ:AJ) - 1 >= $D28,OFFSET(AJ14,0, $D28*-1,1,1),"")
Click to expand...
THANK YOU SO MUCH! Everything works perfectly!

Could I ask you something?
Do you think it would be possible to the same as the VBA code, but with formulas?

Have a nice day! @maabadi
 
Upvote 0
About above formula if you see very zero values and don't want them Try this at AJ28 then Drag it:
Excel Formula: 
=IF(COLUMNS($AJ:AJ) - 1 >= $D28,IF(ISBLANK(OFFSET(AJ14,0, $D28*-1,1,1)),"",OFFSET(AJ14,0, $D28*-1,1,1)),"")
What is Problem with VBA Code?
1. if you have problem with need to run after inputting data. you can use Worksheet change event Macro. this macro automatically run after input data as specific Cells. only you need save file as Macro-Enabled Workbook(.xlsm)
For this, at excel Window, Right click on sheet Cost Details & Select View Code, then Paste this code and save file as Macro-Enabled Workbook(.xlsm)
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, K As Long, Lr As Long, Lc As Long, Sh As Worksheet
Dim M As Double, L As Long, N As Long, P As Long, x As Long, arr(1 To 5, 1 To 2)
Set Sh = Sheets("Cost Details")
With Sh
If Intersect(Target, Union(.Range("L14:T23"), .Range("L44:T53"), .Range("L74:T83"))) Is Nothing Then Exit Sub
i = Target.Row
P = Application.WorksheetFunction.CountA(.Range("L" & i & ":T" & i))
If P < 9 Then Exit Sub
Application.EnableEvents = False
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
x = 1
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)
        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 To K - 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
                    L = (12 / N) * .Range("Q" & i).Value
                    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 With
Application.EnableEvents = True
End Sub
2. for formula, it maybe possible & if possible, it would be very long & Complex formula.
 
Upvote 0
This is UDF ( User-Defined Function) Method. if you don't want Worksheet Change Method first delete it. (at excel Window, Right click on sheet Cost Details & Select View Code, then delete code previously pasted)
Then add This Code as Normal macro method to your workbook and save file as Macro-Enabled Workbook(.xlsm)
After that you can write at destination cells =COSTDTL(PaidMethod, Amount) Example at AJ14 :
Excel Formula: 
=CostDTL($L14,$N14)
Then Drag it.
This is UDF Code:
VBA Code: 
Function CostDTL(PaidMethod As Variant, Amount As Double) As Variant
Dim i As Long, j As Long, P As Double, 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), x As Long
Set Sh = Sheets("Cost Details")
Application.EnableEvents = False
With Sh
  Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
    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
 Application.Volatile
   With Application.Caller.Parent
   i = Application.Caller.Row
    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)))
    N = 12 / arr(Application.Match(Range("M" & i).Value, Application.Index(arr, 0, 1), 0), 2)

    If PaidMethod = "Prepaid" Then
        If .Range("R" & i).Value = "BUY" Then
            If .Range("S" & i).Value = "YES" Then
              If Application.Caller.Column = K Then
                P = Amount * -1
                GoTo LastS
              End If
                 If .Range("T" & i).Value = 0 Then
                       GoTo LastS
                 Else
                 L = (12 / N) * .Range("T" & i).Value
                 M = (Amount / L) * -1
               For j = K + 1 To K + 12 * .Range("T" & i).Value Step N
                  If Application.Caller.Column = j Then
                  P = M
                  GoTo LastS
                  Else
                  End If
                Next j
          
                End If
            ElseIf .Range("S" & i).Value = "NO" Then
              If Application.Caller.Column = K Then
                P = Amount * -1
                GoTo LastS
              End If
            End If
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "NO" Then
              If .Range("Q" & i).Value = 0 Then
                    GoTo LastS
              Else
                L = (12 / N) * .Range("Q" & i).Value
                M = (Amount / L) * -1
                For j = K To K - 1 + 12 * .Range("Q" & i).Value Step N
                  If Application.Caller.Column = j Then
                  P = M
                  GoTo LastS
                  Else
                  End If
                Next j
              End If
            ElseIf .Range("S" & i).Value = "YES" Then
              If Application.Caller.Column = K Then
                P = Amount * -1
                GoTo LastS
              End If
            End If
        End If

    End If

    If PaidMethod = "Postpaid" Then
        If .Range("R" & i).Value = "BUY" Then
           If Application.Caller.Column = K Then
                P = Amount * -1
                GoTo LastS
           End If
            If .Range("S" & i).Value = "YES" Then

                 If .Range("T" & i).Value = 0 Then
                       GoTo LastS
                 Else
                    L = (12 / N) * .Range("T" & i).Value
                    M = (Amount / L) * -1

                For j = K + N To K + N - 1 + 12 * .Range("T" & i).Value Step N
                  If Application.Caller.Column = j Then
                  P = M
                  GoTo LastS
                  Else
                  End If
                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 = (Amount / L) * -1
                For j = K To K - 1 + 12 * .Range("Q" & i).Value Step N
                  If Application.Caller.Column = j Then
                  P = M
                  GoTo LastS
                  Else
                  End If
                Next j
            ElseIf .Range("S" & i).Value = "YES" Then
              If Application.Caller.Column = K Then
                P = Amount * -1
                GoTo LastS
              End If
            End If
        End If
    End If
 


LastS:
If P <> 0 Then
CostDTL = P
Else
CostDTL = ""
End If
End With
End With
Application.EnableEvents = True
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question
Payment terms (formula) for P&L impact and Cash flow - HELP
Replies
1
Views
3K
Fluff
Fluff
A
  • 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
248
DRSteele
DRSteele
A
  • Question
Payment term and Cash flow (formula linked with costs)
3 4 5
Replies
48
Views
14K
maabadi
M
G
  • Question
Payment term and cash flow (formula linked with costs) - weekly cash flow
Replies
1
Views
1K
jorismoerings
J

Forum statistics

Threads
1,215,463
Messages
6,124,965
Members
449,201
Latest member
Jamil ahmed

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