DIY Scenarios

I’ve always thought that the built-in Scenario Manager is a bit feeble. In particular, the constituent values of a scenario should be visible on a worksheet, not squirrelled away in the SM. It’s really quite straightforward to do it yourself, especially with 2007 Tables.

Here’s a very simple example. This is a ‘Model’ worksheet, showing two different scenarios:

There are three values in our scenarios, which are dropped into D2, D3, D4. These cells are named A, B, C respectively. Output (F2) is a suitably complicated formula that uses A, B and C – obviously, there could be lots of other formulas dependent on the scenario values, making up a complex model. B1 is named Scenario.

Each scenario is in a Table on a separate worksheet, with the Tables named Scenario1, Scenario2, etc:

Back on the Model worksheet, D2, D3, D4 each contain the formula:

=INDIRECT(“Scenario” & Scenario &
“[[#This Row],[value]]”)

The [#This Row] accessor requires the formula to be on the same row as the corresponding row in the scenario tables (even though on different worksheets). If this is an issue, then D2:D4 could contain the array formula:

{=INDIRECT(“Scenario” & Scenario & “[value]“)}

since this works anywhere with respect to the scenario tables.

In fact, you don’t really need tables – you could just use named ranges on the scenario worksheets – but it saves (re)defining names manually.

The Set buttons on the scenario worksheets are just a convenience, so you can inspect a scenario and then make it the current one, without having to remember which number it is. Here’s the button code for the Scenario1 button:

Private Sub SetCommand1_Click()
    Worksheets("Model").Range("Scenario").Value = _
                          Right(Me.Name, 1)
    Worksheets("Model").Activate
End Sub

And similarly for the other buttons. Note that the button names have to be unique within the workbook.

About these ads