Automating Monte Carlo simulation reports in Excel with VBA

Automating Monte Carlo simulation reports in Excel with VBA allows you to quickly generate multiple simulation results and produce analytical reports based on those results. Below is a detailed guide on how to create a Monte Carlo simulation for a project or investment, generate random results, and create a report using VBA in Excel.

Steps in a Monte Carlo Simulation

1. Define input variables (model parameters):
   • For example, if you are simulating a project, you might have variables such as initial cost, expected revenue, operational costs, growth rates, etc.
2. Generate random numbers for the inputs:
   • Use appropriate statistical distributions to generate random values (e.g., normal distribution for returns).
3. Run the simulation over many iterations:
   • For example, running 10,000 iterations to obtain a distribution of results.
4. Analyze the results:
   • Calculate metrics such as the mean, median, percentiles, etc.
5. Automate report generation:
   • Create a report in a new Excel sheet with charts, tables, and analytical metrics.

VBA Code to Automate Monte Carlo Simulation

Below is a detailed example of VBA code for a Monte Carlo simulation:

1. Setup Inputs

This code assumes a simple model with three variables:

• Initial Cost (which follows a normal distribution),
• Annual Revenue (which follows a uniform distribution),
• Project Lifetime (in years).

2. VBA Code for Simulation

Sub MonteCarloSimulation()
    ' Define simulation paraeters
    Dim numIterations As Long
    numIterations = 10000 ' Number of iterations   
    Dim i As Lo
    Dim initialCost As Double
    Dim annualRevenue As Double
    Dim projectLifetime As Long
    Dim cashFlow As Double
    Dim finalResult As Double
    Dim totalResult As Double   
    ' Create an array to store results
    Dim results() As Double
    ReDim results(1 To numIterations)   
    ' Setup result sheet
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Monte Carlo Report"
    With Sheets("Monte Carlo Report")
        .Cells(1, 1).Value = "Iteration"
        .Cells(1, 2).Value = "Cash Flow"
    End With   
    ' Run Monte Carlo simulation
    For i = 1 To numIterations
        ' Generate random variables for each iteration
        initialCost = Application.WorksheetFunction.NormInv(Rnd(), 100000, 20000) ' Initial cost with normal distribution
        annualRevenue = Application.WorksheetFunction.RandBetween(5000, 20000) ' Annual revenue with uniform distribution
        projectLifetime = Application.WorksheetFunction.RandBetween(5, 15) ' Project lifetime between 5 and 15 years       
        ' Calculate cash flow (revenue - costs over the lifetime)
        cashFlow = annualRevenue * projectLifetime - initialCost       
        ' Store the result in the array
        results(i) = cashFlow       
        ' Record the results in the Excel sheet
        Sheets("Monte Carlo Report").Cells(i + 1, 1).Value = i
        Sheets("Monte Carlo Report").Cells(i + 1, 2).Value = cashFlow       
        ' Add to total result for calculating average later
        totalResult = totalResult + cashFlow
    Next i
    ' Calculate statistics for the results
    Dim mean As Double
    Dim stdDev As Double
    Dim minResult As Double
    Dim maxResult As Double   
    mean = totalResult / numIterations
    stdDev = Application.WorksheetFunction.StDev(results)
    minResult = Application.WorksheetFunction.Min(results)
    maxResult = Application.WorksheetFunction.Max(results)   
    ' Add simulation statistics to the report
    With Sheets("Monte Carlo Report")
        .Cells(numIterations + 3, 1).Value = "Mean"
        .Cells(numIterations + 3, 2).Value = mean
        .Cells(numIterations + 4, 1).Value = "Standard Deviation"
        .Cells(numIterations + 4, 2).Value = stdDev
        .Cells(numIterations + 5, 1).Value = "Min"
        .Cells(numIterations + 5, 2).Value = minResult
        .Cells(numIterations + 6, 1).Value = "Max"
        .Cells(numIterations + 6, 2).Value = maxResult
    End With   
    ' Create a chart to visualize the distribution of results
    Dim chartObj As ChartObject
    Set chartObj = Sheets("Monte Carlo Report").ChartObjects.Add(Left:=200, Width:=400, Top:=50, Height:=300)
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=Sheets("Monte Carlo Report").Range("B2:B" & numIterations + 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Monte Carlo Simulation Results Distribution"  
    MsgBox "Monte Carlo Simulation Complete!", vbInformation
End Sub

Explanation of the Code

1. Defining Parameters:
   • numIterations: Number of iterations for the simulation (default is 10,000).
   • initialCost: The initial cost generated using a normal distribution.
   • annualRevenue: The annual revenue generated using a uniform distribution.
   • projectLifetime: The project lifetime generated between 5 and 15 years with a uniform distribution.
2. Simulation Loop:
   • The loop For i = 1 To numIterations generates random variables for each iteration, performs the cash flow calculation, and stores the results in an array.
3. Calculating Statistics:
   • After all iterations are completed, the code calculates the mean, standard deviation, minimum, and maximum of the results.
4. Report Generation:
   • The results of each iteration are recorded in a new sheet called « Monte Carlo Report ».
   • Statistical metrics are calculated and added to the sheet below the results.
5. Chart Creation:
   • A bar chart is created to visualize the distribution of results.

Expected Output in Excel

• A new sheet named « Monte Carlo Report » is created, showing the simulation results.
• Each iteration’s cash flow is listed.
• At the bottom of the sheet, statistics such as the mean, standard deviation, minimum, and maximum are displayed.
• A chart showing the distribution of results is added to the sheet.

Customization

• Variable Distributions: You can adjust the distribution types for each parameter as needed (e.g., using triangular, log-normal distributions, etc.).
• Model: You can replace the simple cash flow model with more complex calculations depending on your project.
• Report: The report format can be customized further (e.g., adding colors, annotations, or additional metrics).