재무 예측 모델 시뮬레이션

"내년 매출이 얼마나 될까?", "원가가 10% 오르면 이익은 얼마나 줄어들까?"... 이런 질문에 답하려면 재무 예측 모델이 필요해요. 하지만 통계 지식도 필요하고, 복잡한 수식도 만들어야 해서 쉽지 않죠.

AI를 활용하면 전문가 수준의 재무 모델을 쉽게 만들 수 있습니다. 과거 데이터만 있으면 AI가 추세를 분석하고 미래를 예측해줘요. 시나리오별 시뮬레이션도 자동으로 만들어주고, 민감도 분석까지 해줍니다.

이번 글에서는 AI로 재무 예측 모델을 만드는 방법을 알아봅니다. 매출 예측부터 손익 시뮬레이션, 손익분기점 분석까지 실무에서 바로 쓸 수 있는 모델들을 단계별로 구축해볼게요.

1. 매출 예측 모델

선형 회귀 기반 예측

AI 프롬프트:

"과거 3년 매출 데이터를 기반으로
향후 12개월 매출을 예측하는 모델을 만들어주세요.
추세선과 계절성을 모두 고려하세요."

AI 생성 수식:

// Sheet: 매출예측

// 선형 추세 예측
=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

Sub 계절지수계산(ws As Worksheet, ByRef seasonalIndex() As Double)
    Dim lastRow As Long
    Dim monthlySum(1 To 12) As Double
    Dim monthlyCount(1 To 12) As Integer
    Dim i As Long, month As Integer
    Dim totalAvg As Double

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 월별 평균 계산
    For i = 2 To lastRow
        month = Month(ws.Cells(i, 1).Value)
        monthlySum(month) = monthlySum(month) + ws.Cells(i, 2).Value
        monthlyCount(month) = monthlyCount(month) + 1
    Next i

    ' 전체 평균
    totalAvg = Application.WorksheetFunction.Average(ws.Range("B2:B" & lastRow))

    ' 계절 지수 계산 (평균 대비 비율)
    For i = 1 To 12
        If monthlyCount(i) > 0 Then
            seasonalIndex(i) = (monthlySum(i) / monthlyCount(i)) / totalAvg
        Else
            seasonalIndex(i) = 1
        End If
    Next i
End Sub

Sub 예측차트생성(historyWs As Worksheet, forecastWs As Worksheet, _
                    historyRows As Long, forecastRows As Integer)
    Dim chartObj As ChartObject

    Set chartObj = forecastWs.ChartObjects.Add( _
        Left:=forecastWs.Range("I2").Left, _
        Top:=forecastWs.Range("I2").Top, _
        Width:=500, _
        Height:=300)

    With chartObj.Chart
        .ChartType = xlLineMarkers

        ' 과거 실적
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .Name = "과거 실적"
            .XValues = historyWs.Range("A2:A" & historyRows)
            .Values = historyWs.Range("B2:B" & historyRows)
            .Format.Line.ForeColor.RGB = RGB(79, 129, 189)
            .MarkerStyle = xlMarkerStyleCircle
        End With

        ' 예측
        .SeriesCollection.NewSeries
        With .SeriesCollection(2)
            .Name = "예측"
            .XValues = forecastWs.Range("A2:A" & forecastRows + 1)
            .Values = forecastWs.Range("D2:D" & forecastRows + 1)
            .Format.Line.ForeColor.RGB = RGB(192, 80, 77)
            .Format.Line.DashStyle = msoLineDash
            .MarkerStyle = xlMarkerStyleDiamond
        End With

        ' 신뢰구간 상한
        .SeriesCollection.NewSeries
        With .SeriesCollection(3)
            .Name = "상한"
            .XValues = forecastWs.Range("A2:A" & forecastRows + 1)
            .Values = forecastWs.Range("F2:F" & forecastRows + 1)
            .Format.Line.ForeColor.RGB = RGB(192, 192, 192)
            .Format.Line.Weight = 1
            .MarkerStyle = xlMarkerStyleNone
        End With

        ' 신뢰구간 하한
        .SeriesCollection.NewSeries
        With .SeriesCollection(4)
            .Name = "하한"
            .XValues = forecastWs.Range("A2:A" & forecastRows + 1)
            .Values = forecastWs.Range("E2:E" & forecastRows + 1)
            .Format.Line.ForeColor.RGB = RGB(192, 192, 192)
            .Format.Line.Weight = 1
            .MarkerStyle = xlMarkerStyleNone
        End With

        .HasTitle = True
        .ChartTitle.Text = "매출 예측 (95% 신뢰구간)"
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
    End With
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

        ' 수식 입력
        Dim col As Integer
        For col = 2 To 4 ' 최악, 기본, 최선
            ' 매출액
            .Cells(12, col).Formula = "=B6"

            ' 매출원가
            .Cells(13, col).Formula = "=B12*B7/100"

            ' 매출총이익
            .Cells(14, col).Formula = "=B12-B13"

            ' 매출총이익률
            .Cells(15, col).Formula = "=B14/B12*100"

            ' 고정비
            .Cells(17, col).Formula = "=B8"

            ' 변동비
            .Cells(18, col).Formula = "=B12*B9/100"

            ' 판관비 합계
            .Cells(16, col).Formula = "=B17+B18"

            ' 영업이익
            .Cells(19, col).Formula = "=B14-B16"

            ' 영업이익률
            .Cells(20, col).Formula = "=B19/B12*100"

            ' 열 참조 업데이트
            .Cells(12, col).Formula = Replace(.Cells(12, col).Formula, "B", Chr(64 + col))
            .Cells(13, col).Formula = Replace(.Cells(13, col).Formula, "B", Chr(64 + col))
            .Cells(14, col).Formula = Replace(.Cells(14, col).Formula, "B", Chr(64 + col))
            .Cells(15, col).Formula = Replace(.Cells(15, col).Formula, "B", Chr(64 + col))
            .Cells(16, col).Formula = Replace(.Cells(16, col).Formula, "B", Chr(64 + col))
            .Cells(17, col).Formula = Replace(.Cells(17, col).Formula, "B", Chr(64 + col))
            .Cells(18, col).Formula = Replace(.Cells(18, col).Formula, "B", Chr(64 + col))
            .Cells(19, col).Formula = Replace(.Cells(19, col).Formula, "B", Chr(64 + col))
            .Cells(20, col).Formula = Replace(.Cells(20, col).Formula, "B", Chr(64 + col))
        Next col

        ' 서식 적용
        .Range("B6:D20").NumberFormat = "#,##0"
        .Range("B15:D15, B20:D20").NumberFormat = "0.0"

        ' 조건부 서식 (영업이익)
        With .Range("B19:D19")
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0"
            .FormatConditions(1).Interior.Color = RGB(198, 224, 180)

            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
            .FormatConditions(2).Interior.Color = RGB(255, 199, 206)
        End With

        .Columns("A:D").AutoFit

        ' 차트 생성
        Call 시나리오차트생성(simWs)
    End With

    MsgBox "손익 시뮬레이션이 생성되었습니다.", vbInformation
End Sub

Sub 시나리오차트생성(ws As Worksheet)
    Dim chartObj As ChartObject

    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("F3").Left, _
        Top:=ws.Range("F3").Top, _
        Width:=400, _
        Height:=300)

    With chartObj.Chart
        .ChartType = xlColumnClustered

        ' 매출액 시리즈
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .Name = "매출액"
            .XValues = ws.Range("B3:D3")
            .Values = ws.Range("B12:D12")
            .Format.Fill.ForeColor.RGB = RGB(79, 129, 189)
        End With

        ' 영업이익 시리즈
        .SeriesCollection.NewSeries
        With .SeriesCollection(2)
            .Name = "영업이익"
            .XValues = ws.Range("B3:D3")
            .Values = ws.Range("B19:D19")
            .Format.Fill.ForeColor.RGB = RGB(155, 187, 89)
        End With

        .HasTitle = True
        .ChartTitle.Text = "시나리오별 손익 비교"
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
    End With
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

Sub 민감도차트생성(ws As Worksheet)
    Dim chartObj As ChartObject

    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("H3").Left, _
        Top:=ws.Range("H3").Top, _
        Width:=400, _
        Height:=300)

    With chartObj.Chart
        .ChartType = xlSurface
        .SetSourceData Source:=ws.Range("A3:F8")
        .HasTitle = True
        .ChartTitle.Text = "민감도 분석 (3D)"
    End With
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이면 Skip):", 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

        ' 시뮬레이션 테이블
        .Cells(15, 1).Value = "** 판매 시뮬레이션 **"
        .Cells(15, 1).Font.Bold = True
        .Cells(16, 1).Value = "수량"
        .Cells(16, 2).Value = "매출액"
        .Cells(16, 3).Value = "변동비"
        .Cells(16, 4).Value = "공헌이익"
        .Cells(16, 5).Value = "영업이익"

        Dim row As Integer
        row = 17
        Dim qty As Double

        For qty = bepQuantity * 0.5 To bepQuantity * 1.5 Step bepQuantity * 0.1
            .Cells(row, 1).Value = qty
            .Cells(row, 2).Value = qty * price
            .Cells(row, 3).Value = qty * variableCost
            .Cells(row, 4).Value = qty * contributionMargin
            .Cells(row, 5).Value = qty * contributionMargin - fixedCost

            ' 색상
            If .Cells(row, 5).Value >= 0 Then
                .Cells(row, 5).Interior.Color = RGB(198, 224, 180)
            Else
                .Cells(row, 5).Interior.Color = RGB(255, 199, 206)
            End If

            row = row + 1
        Next qty

        .Range("B4:B13").NumberFormat = "#,##0"
        .Range("A17:E" & row - 1).NumberFormat = "#,##0"
        .Columns("A:E").AutoFit

        ' 차트
        Call BEP차트생성(ws, row - 1, fixedCost, price, variableCost)
    End With

    MsgBox "손익분기점 분석이 완료되었습니다." & vbCrLf & _
           "BEP 수량: " & Format(bepQuantity, "#,##0") & vbCrLf & _
           "BEP 매출: " & Format(bepSales, "#,##0") & "원", _
           vbInformation
End Sub

Sub BEP차트생성(ws As Worksheet, lastRow As Long, _
                 fixedCost As Double, price As Double, variableCost As Double)
    Dim chartObj As ChartObject

    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("G3").Left, _
        Top:=ws.Range("G3").Top, _
        Width:=450, _
        Height:=350)

    With chartObj.Chart
        .ChartType = xlXYScatterLines

        ' 매출선
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .Name = "매출"
            .XValues = ws.Range("A17:A" & lastRow)
            .Values = ws.Range("B17:B" & lastRow)
            .Format.Line.ForeColor.RGB = RGB(79, 129, 189)
            .Format.Line.Weight = 2
        End With

        ' 총비용선
        .SeriesCollection.NewSeries
        With .SeriesCollection(2)
            .Name = "총비용"
            .XValues = ws.Range("A17:A" & lastRow)

            ' 총비용 = 고정비 + 변동비
            Dim totalCostRange As String
            totalCostRange = "="&ws.Name&"!$C$17:$C$" & lastRow
            Dim i As Long
            For i = 17 To lastRow
                ws.Range("F" & i).Formula = "=" & fixedCost & "+C" & i
            Next i
            .Values = ws.Range("F17:F" & lastRow)

            .Format.Line.ForeColor.RGB = RGB(192, 80, 77)
            .Format.Line.Weight = 2
        End With

        ' 고정비선
        .SeriesCollection.NewSeries
        With .SeriesCollection(3)
            .Name = "고정비"
            Dim fixedArray() As Double
            ReDim fixedArray(1 To lastRow - 16)
            For i = 1 To lastRow - 16
                fixedArray(i) = fixedCost
            Next i

            .XValues = ws.Range("A17:A" & lastRow)
            .Values = fixedArray
            .Format.Line.ForeColor.RGB = RGB(128, 128, 128)
            .Format.Line.DashStyle = msoLineDash
        End With

        .HasTitle = True
        .ChartTitle.Text = "손익분기점 차트 (BEP)"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "판매수량"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "금액 (원)"
        .HasLegend = True
        .Legend.Position = xlLegendPositionTop
    End With
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

Sub 히스토그램생성(ws As Worksheet, lastRow As Long)
    ' 히스토그램용 구간 설정
    Dim minValue As Double, maxValue As Double
    Dim binWidth As Double
    Dim binCount As Integer
    Dim i As Long

    minValue = Application.WorksheetFunction.Min(ws.Range("D4:D" & lastRow))
    maxValue = Application.WorksheetFunction.Max(ws.Range("D4:D" & lastRow))
    binCount = 20
    binWidth = (maxValue - minValue) / binCount

    ' 구간 생성
    ws.Cells(3, 9).Value = "구간"
    ws.Cells(3, 10).Value = "빈도"

    For i = 0 To binCount
        ws.Cells(4 + i, 9).Value = minValue + i * binWidth
    Next i

    ' 빈도 계산 (FREQUENCY 배열 함수)
    ws.Range("J4:J" & 4 + binCount).FormulaArray = _
        "=FREQUENCY(D4:D" & lastRow & ",I4:I" & 4 + binCount & ")"

    ' 차트 생성
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("L3").Left, _
        Top:=ws.Range("L3").Top, _
        Width:=450, _
        Height:=300)

    With chartObj.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=ws.Range("I3:J" & 4 + binCount)
        .HasTitle = True
        .ChartTitle.Text = "영업이익 분포 (히스토그램)"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "영업이익 (원)"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "빈도"
        .HasLegend = False
    End With
End Sub

마무리

AI로 재무 예측 모델 구축:

• ✅ 시계열 데이터 기반 매출 예측
• ✅ 다양한 시나리오 손익 시뮬레이션
• ✅ 민감도 분석 및 손익분기점 계산
• ✅ 몬테카를로 방식의 확률적 예측

다음 글에서는 AI와 엑셀을 직접 연동하는 플러그인 활용법을 알아봅니다.