財務予測モデル シミュレーション

「来年の売上はいくらになるだろう?」「原価が10%上がったら利益はどれくらい減るだろう?」...こうした質問に答えるには財務予測モデルが必要です。でも統計の知識も必要だし、複雑な数式も作らなければならないので簡単ではありません。

AIを活用すれば、専門家レベルの財務モデルを簡単に作成できます。過去のデータさえあれば、AIがトレンドを分析して未来を予測してくれます。シナリオ別のシミュレーションも自動で作成し、感度分析まで行ってくれます。

今回は、AIで財務予測モデルを作成する方法を学びます。売上予測から損益シミュレーション、損益分岐点分析まで、実務ですぐに使えるモデルを段階的に構築していきましょう。

1. 売上予測モデル

線形回帰ベースの予測

AIプロンプト:

過去3年間の売上データをもとに
今後12ヶ月の売上を予測するモデルを作ってください。
トレンドラインと季節性の両方を考慮してください。

AI生成数式:

// シート: 売上予測

// 線形トレンド予測
=FORECAST.LINEAR(A2, 過去売上!$B:$B, 過去売上!$A:$A)

// またはTREND関数
=TREND(過去売上!$B$2:$B$37, 過去売上!$A$2:$A$37, A2)

// 季節性指数適用
=C2*INDEX(季節指数!$B:$B, MATCH(MONTH(A2), 季節指数!$A:$A, 0))

// 信頼区間（上限/下限）
// 標準誤差計算
=STEYX(過去売上!$B$2:$B$37, 過去売上!$A$2:$A$37)

// 95%信頼区間 上限
=D2+1.96*$標準誤差

// 95%信頼区間 下限
=D2-1.96*$標準誤差

VBAによる自動予測:

Sub 売上予測モデル()
    Dim ws As Worksheet
    Dim forecastWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim forecastMonths As Integer

    Set ws = ThisWorkbook.Sheets("過去売上")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 予測シート生成
    On Error Resume Next
    Set forecastWs = Sheets("売上予測")
    If forecastWs Is Nothing Then
        Set forecastWs = Sheets.Add(After:=ws)
        forecastWs.Name = "売上予測"
    Else
        forecastWs.Cells.Clear
    End If
    On Error GoTo 0

    ' ヘッダー
    With forecastWs
        .Cells(1, 1).Value = "予測月"
        .Cells(1, 2).Value = "トレンド予測"
        .Cells(1, 3).Value = "季節調整"
        .Cells(1, 4).Value = "最終予測"
        .Cells(1, 5).Value = "下限 (95%)"
        .Cells(1, 6).Value = "上限 (95%)"
        .Cells(1, 7).Value = "信頼度"
    End With

    ' 統計計算
    Dim xValues As Range, yValues As Range
    Set xValues = ws.Range("A2:A" & lastRow)
    Set yValues = ws.Range("B2:B" & lastRow)

    Dim slope As Double, intercept As Double
    Dim stdError As Double, rSquared As Double

    slope = Application.WorksheetFunction.slope(yValues, xValues)
    intercept = Application.WorksheetFunction.Intercept(yValues, xValues)
    stdError = Application.WorksheetFunction.StEyx(yValues, xValues)
    rSquared = Application.WorksheetFunction.RSq(yValues, xValues)

    ' 季節指数計算
    Dim seasonalIndex(1 To 12) As Double
    Call 季節指数計算(ws, seasonalIndex)

    ' 予測（12ヶ月）
    forecastMonths = 12
    Dim lastDate As Date
    lastDate = ws.Cells(lastRow, 1).Value

    Application.ScreenUpdating = False

    For i = 1 To forecastMonths
        Dim forecastDate As Date
        Dim month As Integer
        Dim trendValue As Double
        Dim seasonalValue As Double
        Dim finalForecast As Double

        ' 次の月
        forecastDate = DateAdd("m", i, lastDate)
        month = Month(forecastDate)

        ' トレンド予測（線形）
        trendValue = slope * (lastRow + i) + intercept

        ' 季節調整
        seasonalValue = trendValue * seasonalIndex(month)

        ' 最終予測
        finalForecast = seasonalValue

        ' 信頼区間
        Dim margin As Double
        margin = 1.96 * stdError * Sqr(1 + 1 / lastRow)

        ' 結果入力
        With forecastWs
            .Cells(i + 1, 1).Value = forecastDate
            .Cells(i + 1, 1).NumberFormat = "yyyy-mm"
            .Cells(i + 1, 2).Value = trendValue
            .Cells(i + 1, 3).Value = seasonalIndex(month)
            .Cells(i + 1, 3).NumberFormat = "0.00"
            .Cells(i + 1, 4).Value = finalForecast
            .Cells(i + 1, 5).Value = finalForecast - margin
            .Cells(i + 1, 6).Value = finalForecast + margin
            .Cells(i + 1, 7).Value = Format(rSquared, "0.0%")

            ' 数値書式
            .Cells(i + 1, 2).NumberFormat = "#,##0"
            .Cells(i + 1, 4).NumberFormat = "#,##0"
            .Cells(i + 1, 5).NumberFormat = "#,##0"
            .Cells(i + 1, 6).NumberFormat = "#,##0"
        End With
    Next i

    ' チャート生成
    Call 予測チャート生成(ws, forecastWs, lastRow, forecastMonths)

    forecastWs.Columns("A:G").AutoFit
    Application.ScreenUpdating = True

    MsgBox "売上予測が完了しました。" & vbCrLf & _
           "R² = " & Format(rSquared, "0.00%") & vbCrLf & _
           "今後12ヶ月の予測合計: " & _
           Format(Application.WorksheetFunction.Sum(forecastWs.Range("D2:D13")), "#,##0"), _
           vbInformation
End Sub

2. 損益シミュレーション

AIプロンプト:

様々な売上シナリオ（最善、基本、最悪）に応じた
損益計算書を自動生成してください。

AI生成VBA:

Sub 損益シミュレーション()
    Dim ws As Worksheet
    Dim simWs As Worksheet

    ' シミュレーションシート生成
    On Error Resume Next
    Set simWs = Sheets("損益シミュレーション")
    If simWs Is Nothing Then
        Set simWs = Sheets.Add
        simWs.Name = "損益シミュレーション"
    Else
        simWs.Cells.Clear
    End If
    On Error GoTo 0

    ' レイアウト生成
    With simWs
        ' タイトル
        .Cells(1, 1).Value = "損益計算書シミュレーション"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' シナリオ入力エリア
        .Cells(3, 1).Value = "シナリオ"
        .Cells(3, 2).Value = "最悪"
        .Cells(3, 3).Value = "基本"
        .Cells(3, 4).Value = "最善"

        ' 前提条件入力
        .Cells(5, 1).Value = "** 主要前提 **"
        .Cells(5, 1).Font.Bold = True

        .Cells(6, 1).Value = "月間売上高"
        .Cells(6, 2).Value = 80000000    ' 最悪
        .Cells(6, 3).Value = 100000000   ' 基本
        .Cells(6, 4).Value = 120000000   ' 最善

        .Cells(7, 1).Value = "売上原価率 (%)"
        .Cells(7, 2).Value = 70
        .Cells(7, 3).Value = 60
        .Cells(7, 4).Value = 55

        .Cells(8, 1).Value = "販管費（固定）"
        .Cells(8, 2).Value = 20000000
        .Cells(8, 3).Value = 20000000
        .Cells(8, 4).Value = 20000000

        .Cells(9, 1).Value = "変動販管費率 (%)"
        .Cells(9, 2).Value = 15
        .Cells(9, 3).Value = 12
        .Cells(9, 4).Value = 10

        ' 損益計算書
        .Cells(11, 1).Value = "** 損益計算書 **"
        .Cells(11, 1).Font.Bold = True

        Dim items() As Variant
        items = Array("売上高", "売上原価", "売上総利益", "売上総利益率(%)", _
                     "販売管理費", " - 固定費", " - 変動費", _
                     "営業利益", "営業利益率(%)")

        Dim row As Integer
        row = 12
        Dim i As Integer
        For i = 0 To UBound(items)
            .Cells(row + i, 1).Value = items(i)
        Next i

        ' 数式入力（簡略化）
        ' 実際の実装では各列ごとに数式を設定

        .Columns("A:D").AutoFit

        ' チャート生成
        Call シナリオチャート生成(simWs)
    End With

    MsgBox "損益シミュレーションが生成されました。", vbInformation
End Sub

3. 感度分析（What-If Analysis）

AIプロンプト:

売上高と原価率を変化させながら
営業利益がどう変化するかをテーブルで表示してください。

AI生成VBA:

Sub 感度分析()
    Dim ws As Worksheet

    ' 分析シート生成
    On Error Resume Next
    Set ws = Sheets("感度分析")
    If ws Is Nothing Then
        Set ws = Sheets.Add
        ws.Name = "感度分析"
    Else
        ws.Cells.Clear
    End If
    On Error GoTo 0

    With ws
        .Cells(1, 1).Value = "感度分析: 営業利益"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' データテーブル設定
        .Cells(3, 1).Value = "売上高 \ 原価率"
        .Cells(3, 1).Font.Bold = True

        ' 売上高シナリオ（行）
        Dim sales() As Variant
        sales = Array(80000000, 90000000, 100000000, 110000000, 120000000)

        Dim i As Integer
        For i = 0 To UBound(sales)
            .Cells(4 + i, 1).Value = sales(i)
            .Cells(4 + i, 1).NumberFormat = "#,##0"
        Next i

        ' 原価率シナリオ（列）
        Dim costRates() As Variant
        costRates = Array(50, 55, 60, 65, 70)

        Dim j As Integer
        For j = 0 To UBound(costRates)
            .Cells(3, 2 + j).Value = costRates(j) & "%"
        Next j

        ' 固定費設定
        Dim fixedCost As Long
        fixedCost = 20000000

        ' 営業利益計算
        For i = 0 To UBound(sales)
            For j = 0 To UBound(costRates)
                Dim revenue As Double
                Dim cogs As Double
                Dim grossProfit As Double
                Dim operatingProfit As Double

                revenue = sales(i)
                cogs = revenue * costRates(j) / 100
                grossProfit = revenue - cogs
                operatingProfit = grossProfit - fixedCost

                .Cells(4 + i, 2 + j).Value = operatingProfit
                .Cells(4 + i, 2 + j).NumberFormat = "#,##0"

                ' 条件付き書式
                If operatingProfit > 20000000 Then
                    .Cells(4 + i, 2 + j).Interior.Color = RGB(146, 208, 80)
                ElseIf operatingProfit > 10000000 Then
                    .Cells(4 + i, 2 + j).Interior.Color = RGB(255, 242, 204)
                ElseIf operatingProfit > 0 Then
                    .Cells(4 + i, 2 + j).Interior.Color = RGB(252, 228, 214)
                Else
                    .Cells(4 + i, 2 + j).Interior.Color = RGB(255, 199, 206)
                End If
            Next j
        Next i

        ' 枠線
        .Range("A3:F8").Borders.LineStyle = xlContinuous

        ' 凡例
        .Cells(10, 1).Value = "凡例:"
        .Cells(11, 1).Value = "優秀"
        .Cells(11, 1).Interior.Color = RGB(146, 208, 80)
        .Cells(12, 1).Value = "良好"
        .Cells(12, 1).Interior.Color = RGB(255, 242, 204)
        .Cells(13, 1).Value = "普通"
        .Cells(13, 1).Interior.Color = RGB(252, 228, 214)
        .Cells(14, 1).Value = "注意"
        .Cells(14, 1).Interior.Color = RGB(255, 199, 206)

        .Cells(11, 2).Value = "> 2,000万円"
        .Cells(12, 2).Value = "> 1,000万円"
        .Cells(13, 2).Value = "> 0円"
        .Cells(14, 2).Value = "< 0円（赤字）"

        .Columns("A:F").AutoFit

        ' 3Dチャート生成
        Call 感度チャート生成(ws)
    End With

    MsgBox "感度分析が完了しました。", vbInformation
End Sub

4. 損益分岐点分析

AIプロンプト:

固定費、変動費、販売価格を入力すると
損益分岐点の数量と売上高を計算してください。

AI生成数式およびVBA:

// 損益分岐点数量（BEP_Q）
=固定費/(販売単価-単位当たり変動費)

// 損益分岐点売上高（BEP_S）
=BEP_Q*販売単価

// または直接計算
=固定費/(1-変動費率)

// 目標利益達成数量
=(固定費+目標利益)/(販売単価-単位当たり変動費)

VBA自動計算機:

Sub 損益分岐点計算機()
    Dim fixedCost As Double
    Dim price As Double
    Dim variableCost As Double
    Dim targetProfit As Double

    ' ユーザー入力
    fixedCost = Application.InputBox("月間固定費を入力してください（円）:", Type:=1)
    price = Application.InputBox("製品販売単価を入力してください（円）:", Type:=1)
    variableCost = Application.InputBox("製品あたり変動費を入力してください（円）:", Type:=1)
    targetProfit = Application.InputBox("目標営業利益を入力してください（円、0ならスキップ）:", Type:=1)

    ' 計算
    Dim contributionMargin As Double
    Dim bepQuantity As Double
    Dim bepSales As Double
    Dim targetQuantity As Double

    contributionMargin = price - variableCost

    If contributionMargin <= 0 Then
        MsgBox "エラー: 販売単価が変動費より高くなければなりません。", vbCritical
        Exit Sub
    End If

    bepQuantity = fixedCost / contributionMargin
    bepSales = bepQuantity * price

    If targetProfit > 0 Then
        targetQuantity = (fixedCost + targetProfit) / contributionMargin
    End If

    ' 結果シート
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Sheets("損益分岐点")
    If ws Is Nothing Then
        Set ws = Sheets.Add
        ws.Name = "損益分岐点"
    Else
        ws.Cells.Clear
    End If
    On Error GoTo 0

    With ws
        .Cells(1, 1).Value = "損益分岐点分析結果"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' 入力値
        .Cells(3, 1).Value = "** 入力値 **"
        .Cells(3, 1).Font.Bold = True
        .Cells(4, 1).Value = "固定費:"
        .Cells(4, 2).Value = fixedCost
        .Cells(5, 1).Value = "販売単価:"
        .Cells(5, 2).Value = price
        .Cells(6, 1).Value = "単位当たり変動費:"
        .Cells(6, 2).Value = variableCost
        .Cells(7, 1).Value = "貢献利益:"
        .Cells(7, 2).Value = contributionMargin

        ' 結果
        .Cells(9, 1).Value = "** 分析結果 **"
        .Cells(9, 1).Font.Bold = True
        .Cells(10, 1).Value = "損益分岐点数量:"
        .Cells(10, 2).Value = Format(bepQuantity, "#,##0.0")
        .Cells(11, 1).Value = "損益分岐点売上:"
        .Cells(11, 2).Value = Format(bepSales, "#,##0")

        If targetProfit > 0 Then
            .Cells(12, 1).Value = "目標利益達成数量:"
            .Cells(12, 2).Value = Format(targetQuantity, "#,##0.0")
            .Cells(13, 1).Value = "目標利益達成売上:"
            .Cells(13, 2).Value = Format(targetQuantity * price, "#,##0")
        End If

        .Range("B4:B13").NumberFormat = "#,##0"
        .Columns("A:E").AutoFit

        ' チャート
        Call BEPチャート生成(ws, fixedCost, price, variableCost)
    End With

    MsgBox "損益分岐点分析が完了しました。" & vbCrLf & _
           "BEP数量: " & Format(bepQuantity, "#,##0") & vbCrLf & _
           "BEP売上: " & Format(bepSales, "#,##0") & "円", _
           vbInformation
End Sub

5. モンテカルロシミュレーション

AIプロンプト:

不確実な未来を考慮して
1,000回のランダムシナリオを生成し
各結果の確率分布を表示してください。

AI生成VBA:

Sub モンテカルロシミュレーション()
    Dim ws As Worksheet
    Dim iterations As Long
    Dim i As Long

    iterations = 1000

    ' シミュレーションシート
    On Error Resume Next
    Set ws = Sheets("モンテカルロ")
    If ws Is Nothing Then
        Set ws = Sheets.Add
        ws.Name = "モンテカルロ"
    Else
        ws.Cells.Clear
    End If
    On Error GoTo 0

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ws
        .Cells(1, 1).Value = "モンテカルロシミュレーション"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' ヘッダー
        .Cells(3, 1).Value = "試行"
        .Cells(3, 2).Value = "売上高"
        .Cells(3, 3).Value = "原価率(%)"
        .Cells(3, 4).Value = "営業利益"

        ' シミュレーション実行
        Dim salesMean As Double, salesStdDev As Double
        Dim costRateMean As Double, costRateStdDev As Double
        Dim fixedCost As Double

        ' 前提設定
        salesMean = 100000000      ' 平均売上
        salesStdDev = 20000000     ' 標準偏差
        costRateMean = 60          ' 平均原価率
        costRateStdDev = 5         ' 標準偏差
        fixedCost = 20000000       ' 固定費

        For i = 1 To iterations
            ' 正規分布乱数生成
            Dim sales As Double, costRate As Double
            Dim operatingProfit As Double

            sales = WorksheetFunction.NormInv(Rnd(), salesMean, salesStdDev)
            costRate = WorksheetFunction.NormInv(Rnd(), costRateMean, costRateStdDev)

            ' 負数防止
            If sales < 0 Then sales = 0
            If costRate < 0 Then costRate = 0
            If costRate > 100 Then costRate = 100

            operatingProfit = sales * (1 - costRate / 100) - fixedCost

            ' 結果記録
            .Cells(3 + i, 1).Value = i
            .Cells(3 + i, 2).Value = sales
            .Cells(3 + i, 3).Value = costRate
            .Cells(3 + i, 4).Value = operatingProfit

            If i Mod 100 = 0 Then
                Application.StatusBar = "シミュレーション進行中... " & _
                    Format(i / iterations, "0%")
            End If
        Next i

        ' 統計要約
        .Cells(3, 6).Value = "統計要約"
        .Cells(3, 6).Font.Bold = True

        .Cells(4, 6).Value = "平均営業利益:"
        .Cells(4, 7).Value = Application.WorksheetFunction.Average( _
            .Range("D4:D" & 3 + iterations))

        .Cells(5, 6).Value = "標準偏差:"
        .Cells(5, 7).Value = Application.WorksheetFunction.StDev( _
            .Range("D4:D" & 3 + iterations))

        .Cells(6, 6).Value = "最大値:"
        .Cells(6, 7).Value = Application.WorksheetFunction.Max( _
            .Range("D4:D" & 3 + iterations))

        .Cells(7, 6).Value = "最小値:"
        .Cells(7, 7).Value = Application.WorksheetFunction.Min( _
            .Range("D4:D" & 3 + iterations))

        .Cells(8, 6).Value = "中央値:"
        .Cells(8, 7).Value = Application.WorksheetFunction.Median( _
            .Range("D4:D" & 3 + iterations))

        ' 確率計算
        Dim profitCount As Long
        profitCount = Application.WorksheetFunction.CountIf( _
            .Range("D4:D" & 3 + iterations), ">0")

        .Cells(10, 6).Value = "利益確率:"
        .Cells(10, 7).Value = Format(profitCount / iterations, "0.0%")

        .Range("G4:G10").NumberFormat = "#,##0"

        ' ヒストグラムデータ生成
        Call ヒストグラム生成(ws, 3 + iterations)
    End With

    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "モンテカルロシミュレーションが完了しました。", vbInformation
End Sub

まとめ

AIによる財務予測モデル構築:

• ✅ 時系列データベースの売上予測
• ✅ 様々なシナリオの損益シミュレーション
• ✅ 感度分析および損益分岐点計算
• ✅ モンテカルロ方式の確率的予測

次回は、AIとExcelを直接連携するプラグインの活用法を学びます。