Scenario Analysis Tool

Sub ScenarioAnalysisTool()
    Dim ws As Worksheet
    Dim scenarioWs As Worksheet
    Dim baseValue As Double
    Dim scenarios As Variant
    Dim i As Long

    Set ws = ActiveSheet
    Set scenarioWs = Worksheets.Add
    scenarioWs.Name = "Scenario_Analysis"

    ' Get base case value
    baseValue = ws.Range("B1").Value ' Adjust cell reference as needed

    ' Define scenarios (percentage changes)
    scenarios = Array(-0.2, -0.1, 0, 0.1, 0.2, 0.3) ' -20% to +30%

    ' Create scenario table
    scenarioWs.Range("A1").Value = "Scenario Analysis"
    scenarioWs.Range("A2").Value = "Change %"
    scenarioWs.Range("B2").Value = "New Value"
    scenarioWs.Range("C2").Value = "Impact"

    For i = 0 To UBound(scenarios)
        scenarioWs.Cells(i + 3, 1).Value = Format(scenarios(i), "0%")
        scenarioWs.Cells(i + 3, 2).Value = baseValue * (1 + scenarios(i))
        scenarioWs.Cells(i + 3, 3).Value = (baseValue * (1 + scenarios(i))) - baseValue
    Next i

    ' Format the table
    With scenarioWs.Range("A2:C" & UBound(scenarios) + 3)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 10
    End With

    scenarioWs.Range("A2:C2").Font.Bold = True

    MsgBox "Scenario analysis table created!"
End Sub