In this video clip, I demonstrate a technique to develop dynamic scenario analysis for Excel, in order to understand the impact on model result/s of changes to multiple input cells under multiple scenarios. The technique overcomes the limitations of the Scenario Manager which is found under the heading of "What-If Analysis" on the Excel Data Tab. The limitations of the standard Excel Scenario Manager are:
1) The routine exists outside of the Excel cells and it is a cumbersome process to change to change input cells under different scenarios in order to evaluate the impact on the model result for each scenario.
2) There is a limitation on the number of input variables (or "changing cells" as they are referred to in the Scenario Manager), which can be reached quite easily on more complex models.
By using a combination of the HLOOKUP function, Data Validation (list type) and Data Tables, this technique overcomes these limitations.
In order to clearly demonstrate the technique, the example I have used is deliberately simple (one would probably not use this for a simple model as the same result could be achieved by a grid structure with results at the end of each column). I have used the technique successfully for a number of more complex applications such as business valuations, project viabilities and financial budgets. In such applications, the final model result depends upon input from a number of other worksheets.
The technique can also be used to assess multiple results for each scenario, with multiple input variables. For example, I have developed a financial model with 6 scenarios, 20 input variables and three results per scenario (e.g. NPV, IRR and Profitability Index). I have tested the results against separate static models for each scenario and I get the same results, so I am confident in the integrity of the technique. There are a few basic rules to follow:
1) The input variables must be values.
2) The scenario table, the data table and the model result formulae need to be located on the same worksheet. It is fine to link the input variables to other worksheets.
The technique can be used for virtually any Excel model and has multiple applications. Although I believe that is preferable to commence model development with the specification of the scenario analysis, as this structures the thought process in terms of selection of key input variables and model result/s, the technique can be easily retro-fitted to existing models.