Need help creating a dynamic period row in a cash flow statement

[exceldreams]

Hi,

I have difficulty in conceptualizing how to do this in excel and require some help.

My goal is to be able to have a dynamic contract row in the "Cash Flow" tab. That means if I choose 5 from contract term in the "Inputs" tab, it will show 0, 1 (in F3), 2 (G3), 3 (H3), 4 (I3), 5 (J3) in the period counter and nothing after in the "Cash Flow" tab. If I were to choose 10 from the "Inputs" tab, then it would be like above, but continue to 10, i.e dynamically.

The additional requirement is that in the operating months row in the "Cash Flow" tab, it shows the partial month in period 1, full months in between the first and last year, and the remaining partial months in last year. That means if there is a 5 year contract, with a launch in "March", the "Cash Flow" tab would have 0, 1 (in F3), 2 (G3), 3 (H3), 4 (I3), 5 (J3) in the period counter and nothing after, with operating month row the number 10 (in F5), 12 (G5), 12 (GH), 12 (I5), 2 (J5).

Attached the excel sheet here.

I'd really appreciate some guidance with this.

Thanks for your help in advance!

 

[vbanoob0807]

Hi exceldreams,

try this

Sub Period_Counter()

Dim lr As Integer
Dim lc As Integer
Dim Inputs_tab As Worksheet
Dim CF_tab As Worksheet
Dim Inp_arry(3, 1) As Variant
Dim CF_arry(2, 1) As Variant

Set Inputs_tab = ThisWorkbook.Worksheets("Inputs")
Set CF_tab = ThisWorkbook.Worksheets("Cash Flow")

CF_arry(0, 0) = pd_ctr
CF_arry(0, 1) = "Period counter (Contract term)"
CF_arry(1, 0) = yr
CF_arry(1, 1) = "Year"
CF_arry(2, 0) = op_mths
CF_arry(2, 1) = "Operating months"

Inp_arry(0, 0) = launch_mth
Inp_arry(0, 1) = "Launch month (beginning of month)"
Inp_arry(1, 0) = launch_yr
Inp_arry(1, 1) = "Launch year"
Inp_arry(2, 0) = mths_op_1_year
Inp_arry(2, 1) = "Months operational in the first year"
Inp_arry(3, 0) = c_term
Inp_arry(3, 1) = "Contract Term"

lr = CF_tab.UsedRange.Rows.Count
lc = CF_tab.Cells(lr, Columns.Count).End(xlToLeft).Column

For CF = LBound(CF_arry) To UBound(CF_arry)

    Set CF_arry(CF, 0) = CF_tab.Cells.Find(what:=CF_arry(CF, 1), _
                                    After:=Range("A1"), _
                                    Lookat:=xlPart, _
                                    LookIn:=xlValues, _
                                    searchorder:=xlByRows, _
                                    searchdirection:=xlNext, _
                                    MatchCase:=False).Offset(0, 3)

Next CF

If Not lc <= 3 Then
    CF_tab.Activate
    CF_tab.Range(Cells(CF_arry(0, 0).Row, CF_arry(0, 0).Column), Cells(lr, lc)).ClearContents
End If

For i = LBound(Inp_arry) To UBound(Inp_arry)

    Set Inp_arry(i, 0) = Inputs_tab.Cells.Find(what:=Inp_arry(i, 1), _
                                    After:=Range("A1"), _
                                    Lookat:=xlPart, _
                                    LookIn:=xlValues, _
                                    searchorder:=xlByRows, _
                                    searchdirection:=xlNext, _
                                    MatchCase:=False).Offset(0, 2)
    
Next i
                                     
For cnt = 0 To Inp_arry(3, 0).Value
    
    If cnt = 0 Then

        CF_arry(0, 0).Value = cnt
        CF_arry(1, 0).Value = cnt
        CF_arry(2, 0).Value = cnt
    
    ElseIf cnt = 1 Then
    
        CF_arry(0, 0).Offset(0, cnt).Value = cnt
        CF_arry(1, 0).Offset(0, cnt).Value = Inp_arry(1, 0).Value + cnt - 1     'CF_arry(1, 0) refers to Year's row on CF tab to be input with Launch year[Inp_arry(1, 0)]
        CF_arry(2, 0).Offset(0, cnt).Value = Inp_arry(2, 0).Value               'Cf_arry(2, 0) refers to Operating months' row on CF tab to be input with
                                                                                'Months operational values[Inp_arry(2, 0)]
    ElseIf cnt <> Inp_arry(3, 0).Value Then
    
        CF_arry(0, 0).Offset(0, cnt).Value = cnt
        CF_arry(1, 0).Offset(0, cnt).Value = Inp_arry(1, 0).Value + cnt - 1
        CF_arry(2, 0).Offset(0, cnt).Value = 12
    
    Else
    
        CF_arry(0, 0).Offset(0, cnt).Value = cnt
        CF_arry(1, 0).Offset(0, cnt).Value = Inp_arry(1, 0).Value + cnt - 1
        CF_arry(2, 0).Offset(0, cnt).Value = 12 - Inp_arry(2, 0).Value
     
    End If
 
Next cnt

CF_tab.Activate

Erase Inp_arry
Erase CF_arry

End Sub