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)
End Sub

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

About these ads

3 Responses to “DIY Scenarios”

  1. 1 Sebastien Labonne March 23, 2010 at 5:30 pm

    I’m glad to see I’m not the only one who don’t like the built-in Scenario Manager .

    When teaching Excel classes, I always teach that feature only to know it’s mere existence, but recommend not using it and instead use a more flexible and transparent approach.

    However, I use a slightly different approach that does not involve any macro. I simply use a table with one column for each scenario and a column with the “active” scenario calculated with an INDEX function from which my inputs in the model are linked.

    That way, it’s also easy to set up sensitivity tables relating the variables to some core results.

    I wrote a blog post on the approach some time ago.


  2. 2 Roy MacLean March 23, 2010 at 6:21 pm

    There are certainly different ways of doing scenarios, as your post illustrates.

    My ‘Set’ buttons were just an optional extra – there’s no need for any VBA (but I love it so much :-)).

    I like the INDIRECT function with multi-cell ranges, because you can move data around en bloc, provided you don’t mind array formulas. The Table version with [#This Row] just avoids an array formula, since these seem to scare some people.

    PS I like the dynamic tag cloud on your blog!


  3. 3 Sebastien Labonne March 24, 2010 at 12:59 pm

    The plugin of the tag cloud is WP-Cumulus.

    I don’t know if you can use it on or only on self-hosted WP.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

March 2010
« Feb   Apr »


Get every new post delivered to your Inbox.

%d bloggers like this: