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
OK. With this discription, Only Tell me
When you have Lease you want
1. show Total value at first month
OR
2. Only splitted values.
Please be clarify. I don't know what exact you want & Then cannot help.
Tell you want option 1 or 2 only.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
With headers Name I think that Ebit Impact for Lease always equal NO.
At Lease Situation, if We have Ebit Impact Equal Yes what is differenece with Ebit Equal No.
 
Upvote 0
With all of this description try this code and report issues:
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 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
Next i

End With
Application.EnableEvents = True
End Sub
 
Upvote 0
maabadi said:
With headers Name I think that Ebit Impact for Lease always equal NO.
At Lease Situation, if We have Ebit Impact Equal Yes what is differenece with Ebit Equal No.
Click to expand...
When we select "LEASE", automatically (whether we have Prepaid or Postpaid) ... EBIT Impact will be NO (ebit impact is only YES if we select "BUY").
And even if we manually select "YES" for EBIT impact (when "LEASE" selection) ... it will be considered as NO (not taken into consideration)
 
Upvote 0
Hi @maabadi!

Link to the NEW AND LAST file (don't take any other file into consideration please): Google Sheets - create and edit spreadsheets online, for free.

1st thing: Could you please make your last Macro work in this "Cost details" tab?
I want it to work from:
A/ Line 14 to 23
B/ Line 44 to 53
C/ Line 74 to 83

2nd thing: For the "Cash flow impact" parts (line 28 to 37 / line 58 to 67 / line 88 to 97), we put a formula in the past (from column AJ to EY).

We want the same formula that takes into consideration the payment term in column D. BUT, the only thing that will change here will be that a month will ALWAYS be 30 days. ID we have a payment term of 60 days, it will be : + 2 months ... and so on

If not clear please do tell :)
Thank you so much! @maabadi
 
Upvote 0
Adfinance said:
We want the same formula that takes into consideration the payment term in column D. BUT, the only thing that will change here will be that a month will ALWAYS be 30 days. ID we have a payment term of 60 days, it will be : + 2 months ... and so on
Click to expand...
Please Give me Example with Desired Result, for Example if Rhytm of Invoice is Monthly, then Payment Term should be 30, if Quarterly, should be 90,.....
 
Upvote 0
This code Works for Part 1 Request:
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("N74").End(xlDown).Row
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
j = 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
    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 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 * j - 7 Then
j = j + 1
i = i + 20
End If
Next i

End With
Application.EnableEvents = True
End Sub
 
Upvote 0
maabadi said:
This code Works for Part 1 Request:
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("N74").End(xlDown).Row
Lc = .Cells(13, Columns.Count).End(xlToLeft).Column
j = 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
    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 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 * j - 7 Then
j = j + 1
i = i + 20
End If
Next i

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

> Part 1 Request :
"Run-time error 1004 ... Unable to get the EoMonth property of the worksheetFunction class"
Could you help me with that issue in the VBA code please?

> Part 2 Request (with Cash flow impact) :
You worked on this in the past (the formulas from cells AJ28 to EY37, from AJ58 to EY67 and from AJ88 to EY97 were yours).
How it worked in the past ? simple ....
For instance the line 14 and 28 come together, and so on.
If we selected at cell "D28" a value of 60 days, it meant that your formulas at line 28 (from AJ28 to EY28) took that 60 days into consideration to put the value from line 14 at line 28, BUT with +60 days after. It worked really well! Thank you!

BUT now, we just want to just a small thing on the formulas ... we want to have a general assumption :
+ 30 days is +1 month (even if we know that each month is different in term of days)
+ 60 days is +2 months, etc.

Example A/ For instance, if we have a value of -500 000 $ at cell AJ14, a "Starting Month & Year" which is 01/05/2021 and a payment term of +60 days at cell AJ28 ... at the end the value of - 500 000 $ will be displayed in 2 months (at cell AL28).
Example B/ ALSO, if we have a value of - 250 000 $ at cell AJ15, a "Starting Month & Year" which is 22/05/2021 and a payment term of +90 days at cell AJ29 ... at the end the value of - 250 000 $ will be displayed in 3 months (at cell AL29).

Is that clear enough?
Thank you SO MUCH @maabadi
 
Upvote 0
Part 1:
Make sure all values at columns N, P, Q & T are Numbers and don't have text. also your date is correct date. I check your last file updated & see same error. I want to Change 1,5 year(s) to another value but dropdown menu don't work for me. I want to change it manually but see data validation error. But code work on previous files when I select value from dropdown menu.
Part 2:
1. I understand your description. Also I realize this formula don't work wit negative number & because your all values is negative, we should change formula also.
2. About Change payment terms from 30, 60 , ... to 1, 2, ... No Problem we can change it at formula also.
3. If you want we can omit formula from that cells and add this part also to Macro.

Part 3:
I have low times these days maybe take times to modify code and sent answers, please be patients & Sorry.
if you have another idea also tell to I consider them also.
 
Upvote 0
maabadi said:
Part 1:
Make sure all values at columns N, P, Q & T are Numbers and don't have text. also your date is correct date. I check your last file updated & see same error. I want to Change 1,5 year(s) to another value but dropdown menu don't work for me. I want to change it manually but see data validation error. But code work on previous files when I select value from dropdown menu.
Part 2:
1. I understand your description. Also I realize this formula don't work wit negative number & because your all values is negative, we should change formula also.
2. About Change payment terms from 30, 60 , ... to 1, 2, ... No Problem we can change it at formula also.
3. If you want we can omit formula from that cells and add this part also to Macro.

Part 3:
I have low times these days maybe take times to modify code and sent answers, please be patients & Sorry.
if you have another idea also tell to I consider them also.
Click to expand...
1/ with what you told me I made sure we had numbers in columns N, P, Q and T.
BUT, we still have another problem, which is the following: " Run-time error '13' ... TYPE MISMATCH "
Could you help me fix this issue (the macro does not work so far because of this error)

2/ If you can change the formula it would be perfect (I prefer to have it as a formula please).
If you don't succeed, please add it in the Macro (but for me, better to have it directly in the formula to be honest).

3/ Yes @maabadi I understand :) Thank you very much for you time!
 
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,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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