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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
I view again your file.
I didn't see how we know you select which Payment term for each row? for example at row 14, how we know what is payment term? it is D28, D29 , D30 or ....
then add formula to cells based on at that row?
 
Upvote 0
maabadi said:
I view again your file.
I didn't see how we know you select which Payment term for each row? for example at row 14, how we know what is payment term? it is D28, D29 , D30 or ....
then add formula to cells based on at that row?
Click to expand...

Hi @maabadi
Thank you for your unconditionnal help. Please forget the first file ... I have made it simple for you here with another file :

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).
drive.google.com

Let's forget about "Payment terms" for now :)
 
Upvote 0
Try This Macro. I can change it to worksheet change event later to when you input data at specific row or columns then Macro trigger:
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 "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
            .Cells(i, K).Value = .Range("N" & i).Value * -1


        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
        
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "YES" 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)
                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 = "NO" Then
                .Cells(i, K).Value = .Range("N" & i).Value
            End If
        End If
    End If
 End If
Next i

End With
Application.EnableEvents = True
End Sub
 
Upvote 0
maabadi said:
Try This Macro. I can change it to worksheet change event later to when you input data at specific row or columns then Macro trigger:
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 "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
            .Cells(i, K).Value = .Range("N" & i).Value * -1


        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
       
        ElseIf .Range("R" & i).Value = "LEASE" Then
            If .Range("S" & i).Value = "YES" 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)
                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 = "NO" Then
                .Cells(i, K).Value = .Range("N" & i).Value
            End If
        End If
    End If
 End If
Next i

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

this is First file , Test all Possible situations ( Prepaid-Buy, Prepaid-Lease, Postpaid-Buy, Postpaid-Lease, .... ) and report problem you see with details.
Then I change macro based it,
And if you want, I Cab change macro to triggered when specify data at one or multi column at one row filled. this is last step.

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

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

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

this is First file , Test all Possible situations ( Prepaid-Buy, Prepaid-Lease, Postpaid-Buy, Postpaid-Lease, .... ) and report problem you see with details.
Then I change macro based it,
And if you want, I Cab change macro to triggered when specify data at one or multi column at one row filled. this is last step.

For Now Focus on Problem to resolve them.
Click to expand...

Hi @maabadi
Link for the file here: Google Sheets - create and edit spreadsheets online, for free.

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

Looking forward to your answer, and very grateful for your help my dear @maabadi !
Thank you!
 
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
237
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,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

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