Home Spun Scenarios

Most Excel users are content with the incredible power that the basic spreadsheet model provides: named ranges, linked formulas, auto-fill, and so on.  When more in-depth analysis is required, power users often turn to one of the other significant Excel features that are often overlooked, such as Goal Seek, Pivot Tables, Outlining, and the Scenario Manager.

Today's post will discuss the Scenario Manger, and why it is better (and possibly easier) to avoid this feature and build your own scenario control logic.

The Scenario Manager provides a framework, allowing the average Excel user to define a set of input cells in a model, and specify a variety of possible inputs that can be substituted into those cells in order to perform "what-if?" analysis.  What if sales were 20% higher?  What if the price of Oil reaches $200 per barrel?  Any number of distinct scenarios can be defined, and the input data for each can be applied to your model to quickly compare different inputs.

Detailed instructions on using Excel's built-in scenario manager, with examples, can be found here on Microsoft's web site.  This feature is quite easy to use, but I personally believe that Excel's Scenario functionality is just too basic for most power users, and has one glaring disadvantage: you cannot see what scenarios have been created without drilling down into the Scenario Manger dialog, clicking the Edit button, and inspecting the substitute values (and obscure $A$1-style cell references) that have been defined.

A better approach can be achieved easily with a bit of consideration to how your worksheet is laid out, some in-cell validation, and the Offset function.

The goal is to have an easy way to quickly substitute a new set of data for our model's inputs.  This example drives a real-estate calculator (click to enlarge):

In this worksheet, the model's inputs are in column D.  Columns F through J contain alternate input sets - the scenarios.  These alternate sets of data can be substituted into the model by linking the input cells in column D to the scenario cells in F to J, using the Offset function.

All input cells in column D contain the formula: =Offset(0,1+ScenarioOffset), rather than actual data.  The ScenarioOffset reference is a named range pointing to a cell that evaluates which Scenario we have selected in the drop-down in cell D4, using the Match function like this: =Match(D4, ScenarioList, 0)  The ScenarioList reference points to the range of scenario names, which reside in cells F4 through J4.  To make this easy to use (and foolproof), I have added Data Validation to cell D4, so the user must select a value from a list, and the list coices come from... you guessed it!,  ScenarioList - the names of our scenarios.

To use this, the operator selects a value from the drop-down in cell D4: Low, Medium, High, etc.  Our Match function converts this choice to a number, so if we pick "Medium", we get 2 in the cell called ScenarioOffset.  This causes the Offset functions in cells D5 through D17 to change, selecting data from 3 columns to the right (ScenarioOffset + 1, to account for the empty column E.)  Changes to the inputs cause an automatic recalculation of the rest of the model.

Depending on how complex things are, you could write code in the worksheet's Change event to perform additional calculations, regenerate reports, and so on.  The key here is: by changing a single cell (the selected scenario), this changes the data captured by the set of input cells.

Using this approach has some distinct advantages over Excel's Scenario Manager:  It is very easy to add new scenarios by copying values from existing ones and changing the details that are different.  Also, each scenario's data is clearly visible, and sits nicely in-line with the input names (in column B.)  Managing scenarios becomes a simple matter of managing worksheet cells, rather than jerking around with a set of Excel dialogs and modifying data buried in the Scenario Manager's settings.  And if your model has dozens or hundreds of inputs, you will quickly see that the Scenario Manager is simply not viable.

Adding Scenario analysis vastly extends the usefullness of your worksheet models, giving your operators a powerful tool to compare and analyze any number of different possible parameters.  By building it yourself, you maintain control over the input and scenario data, and achieve the exact functionality you need rather than settling for the "good enough" built-in framework that Excel provides.  That's how Power Users roll.

Dynamic Names

One of Excel's most powerful features is the ability to define Names. A Name can refer to a single cell, a range, or a calculated value. You can even define a dynamic Name that refers to a changing range of cells.

You might need to refer to a list of items in your code, but you don't know how big the list will get or where someone might move it. To automatically sync the named range to the data, define a dynamic Name using the Insert -> Name -> Define menu, and this dialog:

The Refers To: field contains a couple of formulae instead of a cell reference, rephrased:
=OFFSET($B$2,1,0,COUNTA($B$2:$B$1000)-1,1)

The Offset formula defines a corner cell, how many rows away, columns away, how many rows to include, and columns to include.  

For the corner cell use the table header, not the first data item, because somebody might delete that cell. The CountA function in the How Many Rows parameter counts non-blank cells in the column.  The heading cell is included in the count, so subtract 1 from it's result to give the actual number of rows in the range.  This example sets an arbitrary limit of 998 items - your knowledge of the likely dataset and any use of the worksheet below the data table are factors in specifying this value.

Blank rows within the data would be a problem, so if that's a possibility, you can nest a few more functions to detect the range of cells you want... but it does get messy and there's no function help when editing names in the dialog, so be warned.

Anyway, now you can use that Named Range in code...

Dim rng as range
For Each rng in Range("Customer_List")
... some code
Next

And you're not putting any actual cell addresses in there so the code stays clean and safe.

An important factor when defining a Name this way is that your Reference formula needs to be safeguarded against possible edits to the worksheet. If a user deletes a cell which is part of the Name's RefersTo property, that Name could break, resolving to a #REF error for ever more. Good Excel programmers anticipate this possibility and set up Names (and Formula references) for maximum durability, helping workbooks live longer.