At the inaugural Financial Modelling Innovation Awards announced at the recent Financial Modelling Virtual Summit, the winner of the “Analysis Accelerators” category was an Excel model using data tables to perform dynamic scenario analysis. Hear from the creator of this winning entry, financial modeller Thomas Paul as he explains how this simple and elegant tool works, and how you can apply it to your own scenario models. This live and interactive virtual meetup is hosted and moderated by Danielle Stein Fairhurst, who was on the judging panel for the awards.
Financial modelling often requires the comparing of different options or sets of assumptions, otherwise known as scenario testing. Traditionally this analysis has involved either multiple copies of the model to be saved with different assumptions entered into each or VBA coding to automatically change assumptions from a defined list. Neither of these solutions are particularly adaptable, either requiring changes to be made in multiple copies of a model or adjustments in the code to account for new variables or outputs. A better way is needed which gives confidence as to the integrity of the results and which can quickly produce results when a deadline is looming.
Data tables have previously been identified as a solution for scenario testing, but only for one or two variables at a time. The Dynamic Scenario Manager overcomes this limitation by allowing the testing of a virtually unlimited number of variables. This is achieved by using each scenario’s unique number as the key variable to be tested by the Data Table. The tool can use a single set of calculations to produce outputs for dozens of scenarios dynamically which means if the calculation methodology or a universal assumption changes, all outputs are recalculated live without the user needing to do anything.