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!
hi its musa in Uganda why should someone learn financial modeling, and what does he become after
Hi Musa,
Many finance related roles today require the use of some sort of financial model, most commonly in the form of a spreadsheet. Financial modelling is a skill area that combines accounting, tax and business knowledge with a form of computer programming. In the past financial modelling was seen as a secondary skill of an accountant/banker/consultant (e.g. an accountant who could do a bit of programming).
However, financial modelling is beginning to be seen as a primary skill area in its own right as the level of sophistication of financial models increases. This attitude varies quite significantly in different parts of the world. There is currently no association or institute of financial modellers (at least not that I know of) that provides certification or qualifications like many accounting institutes do. There are people in the field who would like to see that happen, though.
Most people I know of who describe themselves as primarily financial modellers have an accounting or finance background and qualification and their financial modelling skills have been largely self taught and gained through on-the-job experience.
Many people gain some financial modelling experience as a stepping stone to other finance related roles, but an increasing number of people are choosing to make financial modelling their career, either as model developers or model auditors, or both.
Thanks, Gavin – a great answer!
How would you simply summarise the main differnce between sensitivity and senario analysis when using spreadsheets in the budget process?
Hi Annette,
I have attempted to answer this question here:
http://www.plumsolutions.com.au/articles/scenarios-sensitivities-what-if-analysis-%E2%80%93-what%E2%80%99s-difference
Using financial models in the budget process, as with any model, you could test the sensitivity of your budgeted P&L, cash flow etc. to fluctuations in inputs, such as interest rates, customer demand etc by tweaking a single input. If you want to see a conservative scenario, however for example, you would use one of the scenario tools outlined in the video above to create a scenario that includes changes in several inputs or calculation methods.
Please could anyone assist.
I am trying to develop a financial model. I have put all my assumptions e.g. inflation, margin ets on Sheet1. I have then projected revenues on costs for the various business units on differnt worksheets e.g. Sheet2, 3 and 4. On Sheet 5, I have done a Profit & Loss Account, Balance sheet on sheet 6 and cashflow and ratios on sheet 7.
I would like to view the impact changes in my assumptions (stress-testing) of Sheet 1 have on the ratios on Sheet 7.
Thank you
Hi Charisi,
You could use the first method of scenario analysis (manual scenario selection) outlined in the video in a couple of different ways. The simplest would be to cut and paste your input variables to Sheet 1, and then as you change them, you can see the impact changes have to the ratios.
If you prefer to keep all your assumptions on one page (which is better practice) you could use the manual selection using a drop-down box.
1. Create a table with all your scenarios and changes in inputs on Sheet 1.
2. Create a drop-down box on Sheet 7.
3. Replace your inputs on Sheet 1 with a formula such as a VLOOKUP or IF that will change according to the value in the drop-down box, and the values in your scenario table created in Step 1.
4. As you change the drop-down box on Sheet 7, the outputs will change too.
I hope this helps. We use this form of scenario analysis in the Online Financial Modelling courses http://www.plumsolutions.com.au/elearning. The participants build their own model which contain a Base, Best and Worst case scenario using the method I have outlined above.
[...] and do a scenario analysis to find out how increasing marketing by 10% affects the bottom line? A good scenario analysis tool built into a financial model is really what makes a model really useful, as you can easily see what [...]
This was a great short video Danielle, excellent work.
Hi Danielle. How do I link the Scenarios in the Manual Data Validation drop down menu. I mean I have now understood how to create the drop down but how do I link it so when I change the drop down say from moderate to base it changes the values automatically. Would appreciate a quick reply.
Thanks a lot
Hi Victor,
Sure, you need to link the inputs for your financial model to the cell that contains the drop-down. For example, if your scenario inputs for, say growth rates, are as follows:
Optimistic: 20%
Likely: 10%
Pessimistic: 5%
You could insert a LOOKUP formula (or many other formulas would also do the trick!) in your growth input cell so that if the drop-down contains the word “Optimistic”, the growth amount is 20% and so forth.
I hope that helps! If you want to get some practice, we build a scenario like this together in the online courses: http://www.plumsolutions.com.au/elearning
Hi,
first of all I wanted to thank all of you for the video and all the articles (very usefull!).
Just one more question: how can I performe a sensitivity analysis (e.g. using a scenario manager) on capex and interest rate if the financial structure is optimised with a “copy and paste” macro (I have a cap on equity so I have to adjust the debt funding)?
Thank you very much in advance,
Elisa
Hi Elisa,
By selecting a scenario from the scenario manager you are simply changing some hard-coded input numbers and unless that interferes with the macro somehow, these should work fine together.
Danielle.