Example Application for Working with Scenarios in Excel VBA

Let us demonstrate how the Scenario object works with a simple example.

We will create an expense table for LLC “Megatop” for January and forecast the expenses for the next month.

Our forecast will be based on the assumption that the relative structure of expenses in February will remain the same, while the absolute value will increase due to inflation.

We assume the inflation rate in February will be:

• best case: 1%
• worst case: 7%
• most likely: 3%

Setting up the analysis as follows Work with Scenarios

We will organize the scenario:

• Place a list box on the worksheet and, in the Properties window, set its Name property to lstScenarios.

When the workbook is opened, the Workbook_Open event procedure will automatically fill the list box with the names of possible inflation scenarios for the following month.

Clicking on a list item will:

1. • enter the chosen inflation rate into cell E2;
   • recalculate the projected expenses for the next month.

• In cell B7, enter a formula to calculate the total expenses:

=SUM(B2:B6)

• Select the range C2:C7 and enter the following array formula:

=B2:B7*(1+E2)

To confirm, press Ctrl+Shift+Enter (since this is an array formula).
This formula allows you to calculate the expected expenses for the entire range of values at once.

VBA Code Example

In code, such scenarios are implemented:.

Expense Scenarios Based on the Scenario Object. ThisWorkbook Module

Private Sub Workbook_Open()
    Dim sc As Scenario
    Dim i As Integer
    Dim V As Variant
    ' Delete existing scenarios
    For Each sc In Worksheets("January").Scenarios
        sc.Delete
    Next
    With Worksheets("January")
        ' Add new scenarios from values in column D (rows 10–12)
        For i = 10 To 12
            V = .Cells(i, 4).Value
            .Scenarios.Add Name:=.Cells(i, 3).Value, _
                ChangingCells:=.Range("E2"), Values:=V
        Next
        ' Configure the list box
        With .lstScenarios
            .ColumnCount = 2
            .ListFillRange = "C10:D12"
            .BoundColumn = 2
            .ListIndex = 0
        End With
    End With
End Sub

Expense Scenarios Based on the Scenario Object. Worksheet January Module

Private Sub lstScenarios_Click()
    Worksheets("January").Scenarios(lstScenarios.Text).Show
End Sub

This example demonstrates how to:

• Define different inflation scenarios;
• Use the Scenario Manager programmatically with VBA;
• Automatically update expenses when a scenario is selected from a list box.