ExcelVBA高效办公应用-第七章-VBA财务报表分析-Part3（现金流量表分析）

Public Sub 现金流量表()
    '计算“经营活动现金流入合计”
    Cells(9, 3) = "=sum(C5:C8)"
    '计算“经营活动现金流出合计”
    Cells(15, 3) = "=sum(C10:C14)"
    '计算“经营活动产生的现金流量净额”
    Cells(16, 3) = Cells(9, 3) - Cells(15, 3)
    '计算“投资活动现金流入合计”
    Cells(23, 3) = "=sum(C18:C22)"
    '计算“投资活动现金流出合计”
    Cells(8, 6) = "=sum(F4:F7)"
    '计算“投资活动产生的现金流量净额”
    Cells(9, 6) = Cells(23, 3) - Cells(8, 6)
    '计算“筹资活动现金流入合计”
    Cells(15, 6) = "=sum(F11:F14)"
    '计算“筹资活动现金流出合计”
    Cells(22, 6) = "=sum(F16:F21)"
    '计算“筹资活动产生的现金流量净额”
    Cells(23, 6) = Cells(15, 6) - Cells(22, 6)
    '计算“现金及现金等价物净增加值”
    Cells(24, 6) = Cells(16, 3) + Cells(9, 6) + Cells(23, 6)
End Sub

Public Sub 现金流量表分析()
'定义一个保存记录列数的整型变量
Dim iCount As Integer
    '把工作表的记录列数赋予iCount
    iCount = Sheets("现金流量表分析").[A3].CurrentRegion.Columns.Count
    '选择“损益统计总汇”工作表
    Sheets("现金流量表分析").Select
    With Worksheets("现金流量表")
        '把“现金流量表”工作表中的值复制到“现金流量表分析”工作表中
        Cells(2, iCount + 1) = .Cells(2, 2).Value
        Cells(3, iCount + 1) = .Cells(9, 3).Value
        Cells(4, iCount + 1) = .Cells(15, 3).Value
        Cells(5, iCount + 1) = .Cells(16, 3).Value
        Cells(6, iCount + 1) = .Cells(23, 3).Value
        Cells(7, iCount + 1) = .Cells(8, 6).Value
        Cells(8, iCount + 1) = .Cells(9, 6).Value
        Cells(9, iCount + 1) = .Cells(15, 6).Value
        Cells(10, iCount + 1) = .Cells(22, 6).Value
        Cells(11, iCount + 1) = .Cells(23, 6).Value
        Cells(12, iCount + 1) = .Cells(24, 6).Value
        '计算“现金流动负债比” (现金及现金等价物净增加值/流动负债合计)
        Cells(14, iCount + 1) = .Cells(24, 6) / Worksheets("资产负债表").Cells(15, 6)
        '计算“现金债务总额比” (现金及现金等价物净增加值/(流动负债合计+ 长期负债合计))
        Cells(15, iCount + 1) = .Cells(24, 6) / (Worksheets("资产负债表").Cells(15, 6) _
                + Worksheets("资产负债表").Cells(22, 6))
        '计算“销售现金比率” (现金及现金等价物净增加值/主营业务收入)
        Cells(16, iCount + 1) = .Cells(24, 6) / Worksheets("损益表").Cells(5, 3)
        '计算“资产现金回收率” (现金及现金等价物净增加值/负债及股东权益总计)
        Cells(17, iCount + 1) = .Cells(24, 6) / Worksheets("资产负债表").Cells(30, 3)
    End With
End Sub