Financial Forecasting Model Simulation

"How much will next year's sales be?", "If costs rise 10%, how much will profits decrease?"... To answer these questions, you need financial forecasting models. But it requires statistical knowledge and creating complex formulas isn't easy.

With AI, you can easily create professional-level financial models. With just historical data, AI analyzes trends and predicts the future. It automatically creates scenario-based simulations and even performs sensitivity analysis.

In this post, we'll learn how to create financial forecasting models with AI. From sales forecasting to profit simulation and break-even point analysis, we'll build models you can apply directly at work, step by step.

1. Sales Forecasting Model

Linear Regression-Based Forecasting

AI Prompt:

"Based on 3 years of historical sales data,
create a model to forecast sales for the next 12 months.
Consider both trend line and seasonality."

AI Generated Formulas:

// Sheet: Sales Forecast

// Linear trend forecast
=FORECAST.LINEAR(A2, HistoricalSales!$B:$B, HistoricalSales!$A:$A)

// Or TREND function
=TREND(HistoricalSales!$B$2:$B$37, HistoricalSales!$A$2:$A$37, A2)

// Apply seasonality index
=C2*INDEX(SeasonalIndex!$B:$B, MATCH(MONTH(A2), SeasonalIndex!$A:$A, 0))

// Confidence interval (upper/lower bounds)
// Calculate standard error
=STEYX(HistoricalSales!$B$2:$B$37, HistoricalSales!$A$2:$A$37)

// 95% confidence interval upper bound
=D2+1.96*$StandardError

// 95% confidence interval lower bound
=D2-1.96*$StandardError

VBA for Auto-Forecasting:

Sub SalesForecastModel()
    Dim ws As Worksheet
    Dim forecastWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim forecastMonths As Integer

    Set ws = ThisWorkbook.Sheets("HistoricalSales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Create forecast sheet
    On Error Resume Next
    Set forecastWs = Sheets("SalesForecast")
    If forecastWs Is Nothing Then
        Set forecastWs = Sheets.Add(After:=ws)
        forecastWs.Name = "SalesForecast"
    Else
        forecastWs.Cells.Clear
    End If
    On Error GoTo 0

    ' Headers
    With forecastWs
        .Cells(1, 1).Value = "Forecast Month"
        .Cells(1, 2).Value = "Trend Forecast"
        .Cells(1, 3).Value = "Seasonal Adjustment"
        .Cells(1, 4).Value = "Final Forecast"
        .Cells(1, 5).Value = "Lower Bound (95%)"
        .Cells(1, 6).Value = "Upper Bound (95%)"
        .Cells(1, 7).Value = "Confidence"
    End With

    ' Calculate statistics
    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)

    ' Calculate seasonal indices
    Dim seasonalIndex(1 To 12) As Double
    Call CalculateSeasonalIndices(ws, seasonalIndex)

    ' Forecast (12 months)
    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

        ' Next month
        forecastDate = DateAdd("m", i, lastDate)
        month = Month(forecastDate)

        ' Trend forecast (linear)
        trendValue = slope * (lastRow + i) + intercept

        ' Seasonal adjustment
        seasonalValue = trendValue * seasonalIndex(month)

        ' Final forecast
        finalForecast = seasonalValue

        ' Confidence interval
        Dim margin As Double
        margin = 1.96 * stdError * Sqr(1 + 1 / lastRow)

        ' Enter results
        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%")

            ' Number formatting
            .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

    ' Create chart
    Call CreateForecastChart(ws, forecastWs, lastRow, forecastMonths)

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

    MsgBox "Sales forecast complete." & vbCrLf & _
           "R² = " & Format(rSquared, "0.00%") & vbCrLf & _
           "Next 12 months total forecast: " & _
           Format(Application.WorksheetFunction.Sum(forecastWs.Range("D2:D13")), "#,##0"), _
           vbInformation
End Sub

2. Profit & Loss Simulation

AI Prompt:

"Auto-generate profit and loss statements for various sales scenarios (best, base, worst)."

AI Generated VBA:

Sub ProfitLossSimulation()
    Dim ws As Worksheet
    Dim simWs As Worksheet

    ' Create simulation sheet
    On Error Resume Next
    Set simWs = Sheets("ProfitLossSimulation")
    If simWs Is Nothing Then
        Set simWs = Sheets.Add
        simWs.Name = "ProfitLossSimulation"
    Else
        simWs.Cells.Clear
    End If
    On Error GoTo 0

    ' Create layout
    With simWs
        ' Title
        .Cells(1, 1).Value = "Profit & Loss Simulation"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' Scenario input area
        .Cells(3, 1).Value = "Scenario"
        .Cells(3, 2).Value = "Worst"
        .Cells(3, 3).Value = "Base"
        .Cells(3, 4).Value = "Best"

        ' Input assumptions
        .Cells(5, 1).Value = "** Key Assumptions **"
        .Cells(5, 1).Font.Bold = True

        .Cells(6, 1).Value = "Monthly Revenue"
        .Cells(6, 2).Value = 80000000    ' Worst
        .Cells(6, 3).Value = 100000000   ' Base
        .Cells(6, 4).Value = 120000000   ' Best

        .Cells(7, 1).Value = "COGS Rate (%)"
        .Cells(7, 2).Value = 70
        .Cells(7, 3).Value = 60
        .Cells(7, 4).Value = 55

        .Cells(8, 1).Value = "Fixed SG&A"
        .Cells(8, 2).Value = 20000000
        .Cells(8, 3).Value = 20000000
        .Cells(8, 4).Value = 20000000

        .Cells(9, 1).Value = "Variable SG&A Rate (%)"
        .Cells(9, 2).Value = 15
        .Cells(9, 3).Value = 12
        .Cells(9, 4).Value = 10

        ' Income statement formulas...
        ' (Implementation continues with P&L calculations)

        .Columns("A:D").AutoFit

        ' Create chart
        Call CreateScenarioChart(simWs)
    End With

    MsgBox "Profit & loss simulation created.", vbInformation
End Sub

3. Sensitivity Analysis (What-If Analysis)

AI Prompt:

"Show how operating profit changes as you vary
sales amount and cost rate in a table."

AI Generated VBA:

Sub SensitivityAnalysis()
    Dim ws As Worksheet

    ' Create analysis sheet
    On Error Resume Next
    Set ws = Sheets("SensitivityAnalysis")
    If ws Is Nothing Then
        Set ws = Sheets.Add
        ws.Name = "SensitivityAnalysis"
    Else
        ws.Cells.Clear
    End If
    On Error GoTo 0

    With ws
        .Cells(1, 1).Value = "Sensitivity Analysis: Operating Profit"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' Data table setup
        .Cells(3, 1).Value = "Revenue \ Cost Rate"
        .Cells(3, 1).Font.Bold = True

        ' Revenue scenarios (rows)
        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

        ' Cost rate scenarios (columns)
        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

        ' Fixed cost setting
        Dim fixedCost As Long
        fixedCost = 20000000

        ' Calculate operating profit
        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"

                ' Conditional formatting
                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

        .Columns("A:F").AutoFit
    End With

    MsgBox "Sensitivity analysis complete.", vbInformation
End Sub

4. Break-Even Point Analysis

AI Prompt:

"Calculate break-even quantity and revenue
when fixed costs, variable costs, and selling price are entered."

AI Generated Formulas and VBA:

// Break-even quantity (BEP_Q)
=FixedCost/(SellingPrice-UnitVariableCost)

// Break-even revenue (BEP_S)
=BEP_Q*SellingPrice

// Or direct calculation
=FixedCost/(1-VariableCostRate)

// Target profit achievement quantity
=(FixedCost+TargetProfit)/(SellingPrice-UnitVariableCost)

5. Monte Carlo Simulation

AI Prompt:

"Considering uncertain future,
generate 1,000 random scenarios and
show probability distribution of each result."

AI Generated VBA:

Sub MonteCarloSimulation()
    Dim ws As Worksheet
    Dim iterations As Long
    Dim i As Long

    iterations = 1000

    ' Simulation sheet
    On Error Resume Next
    Set ws = Sheets("MonteCarlo")
    If ws Is Nothing Then
        Set ws = Sheets.Add
        ws.Name = "MonteCarlo"
    Else
        ws.Cells.Clear
    End If
    On Error GoTo 0

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ws
        .Cells(1, 1).Value = "Monte Carlo Simulation"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        ' Headers
        .Cells(3, 1).Value = "Trial"
        .Cells(3, 2).Value = "Revenue"
        .Cells(3, 3).Value = "Cost Rate(%)"
        .Cells(3, 4).Value = "Operating Profit"

        ' Run simulation
        Dim salesMean As Double, salesStdDev As Double
        Dim costRateMean As Double, costRateStdDev As Double
        Dim fixedCost As Double

        ' Set assumptions
        salesMean = 100000000      ' Average revenue
        salesStdDev = 20000000     ' Standard deviation
        costRateMean = 60          ' Average cost rate
        costRateStdDev = 5         ' Standard deviation
        fixedCost = 20000000       ' Fixed cost

        For i = 1 To iterations
            ' Generate normal distribution random numbers
            Dim sales As Double, costRate As Double
            Dim operatingProfit As Double

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

            ' Prevent negative values
            If sales < 0 Then sales = 0
            If costRate < 0 Then costRate = 0
            If costRate > 100 Then costRate = 100

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

            ' Record results
            .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 = "Simulation in progress... " & _
                    Format(i / iterations, "0%")
            End If
        Next i

        ' Statistical summary...
        ' (Implementation continues with statistics)
    End With

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

    MsgBox "Monte Carlo simulation complete.", vbInformation
End Sub

Conclusion

Building financial forecasting models with AI:

• ✅ Sales forecasting based on time series data
• ✅ Profit & loss simulation for various scenarios
• ✅ Sensitivity analysis and break-even point calculation
• ✅ Probabilistic forecasting with Monte Carlo method

In the next post, we'll learn how to use plugins that directly integrate AI with Excel.