This video looks at including scenarios in your financial model, using different methods in Excel. From a simple manual drop down selection, to using ‘Scenario Manager’ within Excel, to data tables (one of the more complex tools). As a financial modeller – you need to know all of the tools that are available. A full transcript of the 3-minute long video is also supplied for your convenience.
Transcript from Video
Once you’ve decided that you need to include some scenarios in your financial model and lets face it pretty much any financial model worth it’s salt should include at least a couple of scenarios. Once you’ve decided that you need to put in your scenarios you’ve got several options.
Excel will give you a couple of different ways of creating scenarios from a technical perspective.
So you’ve really got three options when it comes to Excel as to what sort of Excel tool you want to use to create your scenario.
First is probably the simplest and most commonly used form of scenario analysis and that is a manual drop down selection. Let me show you what I mean. So you’ve got your basic drop down here is a data validation type of drop down, you’ve got your base case, best case, worst case. You change the case and all the numbers will change as a result. Ok let’s take a look at the combo box. Now this mechanically is a very similar concept, you’ve got your drop down box, you’ve got best case, base case, worst case. From a user perspective it’s probably better because you can see straight away what you need to do, that you need to actually select and make a selection. Ok so that’s your manual scenario selection – you show one scenario at a time. Probably the most commonly used sort of scenario analysis in Excel.
Let’s talk about scenario manager now, it is a tool within excel which is specifically for showing different scenarios. Let’s take a look at that now. Now I’m not going to go into too much detail I just want to show you what it is. We can go into it in a bit more detail if you take that component of the course. Just to show you what it is, if you go into scenario manager and then you just say I want to look at the best case and then ‘show’, and that will change, base case, ‘show’, worst case, ‘show’ so you can see that the numbers are changing. To my mind not a particularly useful tool, but just to give you an overview that that is available in excel.
Now data tables are a completely different kettle of fish. They will actually show multiple results for a scenario in one table. Let me show you what I mean. So this is example of a data table where you have, this is a scenario of having an interest rate of 6%, 6.5%, 7% and showing the multiple results of what your monthly repayment would be under all of these different interest rates. So instead of having to change the interest rate here, you can see the results simultaneously in a data table. So needless to say data tables are probably one of the more complex tools in excel modelling. I just wanted to give you a really brief overview of all of the different types of tools that are available to you in scenario analysis. I guess in order to call yourself an expert financial modeller you need to know all of the tools that are available.
Much appreciated.
I think your use of data table is more of a sensitivity analysis.
From what I have seen on the models done by NavigatorPF (and Corality?), their use of data table is more reflective of a “Scenario Manager”.
And the difference between sensitivity analysis and scenario analysis? I think you (Plum) has explained it before already (maybe worth adding here?)
Yes, the data table really is just testing the sensitivity of the outcome to changes in inputs. Bit of a fine line there and yes, I have attempted to distinguish the difference between scenarios, sensitivity and what-if analysis in this post: http://www.plumsolutions.com.au/articles/scenarios-sensitivities-what-if-analysis-%E2%80%93-what%E2%80%99s-difference
One of the limitations of data tables that I didn’t mention was that it’s hard to show changes in more than two input variables at a time, whereas using drop-down scenarios you do get a lot more flexibility.
J and Danielle,
I believe that what J is referring to is the trick covered in the financial modelling courses of Navigator Project Finance where you link up a Data Table to a Scenario manager. This allows you to change numerous assumptions at the same time using a one-dimensional Data Table. It is hard to explain but the example on Navigator Project Finance’s website gives a very good example.
http://www.navigatorpf.com/training/tutorials/tutorial-scenario-manager
There is nothing complicated about this techniques but it is extremely powerful for scenario analysis.
If you wanted to be fancy and introduce Data Tables in other sheets that where the underlying assumptions (in this case the Scenario Manager) then the following article could be of interest.
http://corality.com/training/tutorials/excel-data-tables-in-any-sheet
Since being on the Navigator course in London Scenario analysis have been complete turned around. Using DataTable with the Scenario Management means all scenarios all of the time as the trainer said. I didn’t think it was possible but now I do not know about modelling without this way. On later course I learned how build and unbuild of them with a button so that they don’t be slowing the model down.
Hi J,
We use them for both Scenario Analysis and Sensitivity Analysis. The power of a data-tables are fully harnessed in a Navigator Project Finance model, everything from debt sizing, automated geared versus ungeared analysis through to liquidated damages and advanced cost over run analysis. It can every be used in place of goal-seeking when there isn’t scope to buil din ‘buttons to do things’…
I will write up an article on advanced (PF) applications of Data Tables in the coming weeks.
Nick
Nick, I am aware of NavigatorPF’s use of data table for both sensitivity and scenario analyses, but like Rebecca said, its in the use of Scenario Manager where it is more “original” so to speak.
Am looking forward to your article.
Perhaps you can also write about the typical selection of variables that are used for sensitivities testing in a project finance model?
Hi J,
I think you have sent me into a circular loop…. I will have a crack at putting my thoughts down in an article with the top 5 most common scenarios in PF (and the easy mistakes!)….These days I think of the ‘Model’ rather than the ‘base case’ being special – one persons base case is anothers disaster case!
Nick
good explanation of one of the most important aspects of financial modelling! great video too!