Implement Advanced Sensitivity Analysis Techniques With Excel VBA

1. Introduction to Sensitivity Analysis in Excel

Sensitivity analysis is used to understand how different values of an independent variable (input) can affect a dependent variable (output). It is commonly used in financial modeling, risk analysis, and engineering to understand the impact of uncertainty.

There are several methods for performing sensitivity analysis in Excel:

• Scenario Analysis: This involves creating different « scenarios » (sets of assumptions) and observing how changes affect the output.
• Data Tables: These allow you to create a table of results by varying one or two input values.
• Monte Carlo Simulation: This technique uses random sampling to model the uncertainty in a system.

2. Scenario Analysis in Excel VBA

Scenario Analysis involves manually defining several scenarios and then evaluating the output for each. In VBA, we can automate the creation and evaluation of scenarios.

Example:

Assume you have a financial model with three variables:

• Revenue Growth Rate (cell B2)
• Cost of Goods Sold (cell B3)
• Discount Rate (cell B4)

The formula for the output (let’s say the net present value or NPV) is in cell B5.

Step-by-Step VBA Code for Scenario Analysis:

Sub ScenarioAnalysis()
    ' Define variables
    Dim growthRate As Double
    Dim costRate As Double
    Dim discountRate As Double
    Dim npv As Double
    Dim scenarios As Variant
    Dim i As Integer
    ' Define different scenarios for input variables
    ' Each row in the array represents a different scenario with growth rate, cost rate, and discount rate
    scenarios = Array( _
        Array(0.05, 0.30, 0.10), ' Scenario 1: Growth 5%, Cost 30%, Discount 10%
        Array(0.07, 0.28, 0.09), ' Scenario 2: Growth 7%, Cost 28%, Discount 9%
        Array(0.10, 0.35, 0.12)  ' Scenario 3: Growth 10%, Cost 35%, Discount 12%
    ' Loop through scenarios
    For i = 0 To UBound(scenarios)
        growthRate = scenarios(i)(0)
        costRate = scenarios(i)(1)
        discountRate = scenarios(i)(2)
        ' Set the values of the inputs based on the current scenario
        Range("B2").Value = growthRate
        Range("B3").Value = costRate
        Range("B4").Value = discountRate
        ' Calculate the NPV (this is just an example, use your own formula here)
        npv = CalculateNPV(growthRate, costRate, discountRate
        ' Output the results in the sheet (or log them somewhere)
        Range("D" & (i + 2)).Value = "Scenario " & (i + 1)
        Range("E" & (i + 2)).Value = npv
    Next i
    MsgBox "Scenario Analysis Completed"
End Sub
' Example NPV calculation function (replace with your actual model)
Function CalculateNPV(growthRate As Double, costRate As Double, discountRate As Double) As Double
    ' Assume we have some financial model to calculate NPV
    ' For simplicity, let's return a basic formula
    CalculateNPV = (growthRate * 10000) - (costRate * 5000) / (1 + discountRate)
End Function

Explanation of the Code:

• We define a few scenarios using an array of input values for the variables (growthRate, costRate, discountRate).
• The For loop iterates through these scenarios, updating the input cells (B2, B3, B4) accordingly.
• A function CalculateNPV computes the output based on the given scenario.
• Finally, the results (NPV) for each scenario are written to the sheet.

3. Data Table Analysis in Excel VBA

Data Tables are a built-in Excel feature that allow you to perform sensitivity analysis by systematically varying one or two input values. You can automate this process using VBA.

Example:

Let’s say you want to see how the NPV changes when varying both the Growth Rate and the Discount Rate.

Step-by-Step VBA Code for Data Table:

Sub DataTableAnalysis()
    ' Define variables
    Dim growthRate As Double
    Dim discountRate As Double
    Dim npv As Double 
    ' Set the range where the data table will be created (use your own model ranges)
    Dim dataRange As Range
    Set dataRange = Range("A1:C6")
    ' Set row and column values for the data table
    ' First column (A2:A6) will represent different growth rates
    ' First row (B1:F1) will represent different discount rates
    dataRange.Cells(1, 1).Value = "Growth Rate\Discount Rate"
    ' Populate the growth rate values (A2:A6)
    For i = 2 To 6
        dataRange.Cells(i, 1).Value = 0.05 + (i - 2) * 0.01
    Next i
    ' Populate the discount rate values (B1:F1)
    For i = 2 To 6
        dataRange.Cells(1, i).Value = 0.08 + (i - 2) * 0.02
    Next i   
    ' Fill the data table with NPV values based on growth rate and discount rate
    For i = 2 To 6
        For j = 2 To 6
            growthRate = dataRange.Cells(i, 1).Value
            discountRate = dataRange.Cells(1, j).Value
            npv = CalculateNPV(growthRate, 0.3, discountRate) ' Assume cost rate = 0.3
            dataRange.Cells(i, j).Value = npv
        Next j
    Next i
    MsgBox "Data Table Analysis Completed"
End Sub

Explanation of the Code:

• A range (dataRange) is defined where the data table will be populated.
• The first column and row are populated with varying growth rates and discount rates.
• The nested For loops iterate through all the combinations of growth and discount rates, calculating the corresponding NPV and filling the table.
• CalculateNPV is the same function used in the previous example.

4. Monte Carlo Simulation in Excel VBA

Monte Carlo Simulation is a more advanced technique that uses random sampling to model the uncertainty of input variables. It helps in assessing the probability distribution of the output.

Example:

Let’s simulate how NPV varies with random inputs for Growth Rate and Discount Rate over 1000 trials.

Step-by-Step VBA Code for Monte Carlo Simulation:

Sub MonteCarloSimulation()
    ' Define variables
    Dim growthRate As Double
    Dim discountRate As Double
    Dim npv As Double
    Dim trials As Integer
    Dim i As Integer
    ' Set the number of simulation trials
    trials = 1000
    Dim npvResults() As Double
    ReDim npvResults(1 To trials)
    ' Run the simulation
    For i = 1 To trials
        ' Generate random values for growth and discount rate within given ranges
        growthRate = Rnd() * (0.15 - 0.05) + 0.05 ' Random value between 5% and 15%
        discountRate = Rnd() * (0.12 - 0.08) + 0.08 ' Random value between 8% and 12%
        ' Calculate the NPV
        npv = CalculateNPV(growthRate, 0.3, discountRate)
        ' Store the result
        npvResults(i) = npv
    Next i
    ' Output results (mean, min, max)
    Dim meanNPV As Double
    Dim minNPV As Double
    Dim maxNPV As Double
    meanNPV = Application.WorksheetFunction.Average(npvResults)
    minNPV = Application.WorksheetFunction.Min(npvResults)
    maxNPV = Application.WorksheetFunction.Max(npvResults)
    ' Output to sheet
    Range("E1").Value = "Mean NPV"
    Range("E2").Value = meanNPV
    Range("F1").Value = "Min NPV"
    Range("F2").Value = minNPV
    Range("G1").Value = "Max NPV"
    Range("G2").Value = maxNPV
    MsgBox "Monte Carlo Simulation Completed"
End Sub

Explanation of the Code:

• The Rnd() function generates random numbers between 0 and 1. We scale these values to fall within specific ranges for growth rate and discount rate.
• The simulation runs for trials iterations, calculating the NPV for each random combination of input values.
• Finally, we output the mean, minimum, and maximum NPVs to the worksheet.

Conclusion

Using Excel VBA for sensitivity analysis techniques like Scenario Analysis, Data Tables, and Monte Carlo Simulation can automate the process of understanding how changes in inputs affect outputs in your model. These methods give you powerful tools to perform both deterministic and stochastic sensitivity analysis, enabling you to make more informed decisions based on your model’s behavior.