As requested by my delegates at the seminar in Manila last week, here is a multiple worksheet Scenario Manager model.

What I have done is to take a basic budgeting question and set up a three scenario model (best, worst and most likely cases) that uses its input sheet as the basis for entering three scenario variable values. That works well although please note, I have not set up a three variable option for all 17 variables: just the first 8.

The difficult part of this exercise? It’s the fact that it doesn’t seem possible to keep the scenario as a multiple sheet model. That is, I have had to summarise the main points of each worksheet on the input/scenario input sheet, like this:

mult_scenario_1

You might decide that you need to be more elaborate than that and that is fine: it’s up to you how far to take such a summary.

The scenario summary itself, as in the Scenario Manager generated summary, forces us to create a relatively large number of range names so that the summary contains meaningful row headings. The setting up of this part is a pain and a compromise of sorts. A pain because each cell in the model summary and each cell in the input section needs its own range name: a total of 32 of them in this example. A compromise because I have used abbreviated names to keep the range names short.

mult_scenario_2

Overall, there is plenty of potential for this approach and I just hope that this simple model helps to demonstrate what might be possible.

Download the file: hayes_budgeting_model_trainer_scenario

Duncan Williamson

%d bloggers like this: