A common way of running scenario analysis in financial models is by using Excel’s in-built Data Table function (see tutorial Excel Data Tables). However, this becomes a challenge when the model contains macros that need to be run for every input change.
A perfect example demonstrating this problem involves models with debt sizing macros. Every time a new scenario (new set of inputs) is run, the debt size will need to be re-calculated (macro needs to be re-run). A standard Excel Data Table will not do the trick, being only able to calculate the scenarios based on the initial debt size. So how can we run scenarios on a model like this without resorting to running ten different versions of the model?
Think of it this way – running scenarios is essentially inputting different sets of assumptions and retrieving the corresponding outputs for comparison. So, which tool in Excel will allow you to repeat such actions with ease? Undoubtedly, it is Visual Basic for Application (VBA)!
At Navigator Project Finance, we have developed a VBA macro to solve the challenge of being unable to run macros for every scenario. I will not go into the exact coding here but rather outline the steps required to achieve our goal
- Set up a scenario manager (refer to tutorial Scenario Manager for Project Finance)
- Select the first scenario of interest
- Run the debt sizing macro
- Copy key outputs required
- Paste results in a table
- Repeat the steps above for all scenarios
The exact coding and more useful tools are covered in our VBA for Cashflow Models training course.
Pros and Cons
As you can imagine, like everything else, running scenarios using a macro has its pros and cons.
- May increase calculation speed – multiple data tables may cause speed issues, especially in Excel 07
- Quickly calculate scenarios involving a macro which needs to be run
- Some banks do not accept models with macros
- Macros are unfamiliar to some people – there is a lack of transparency since users will only see the hard coded results
- The VBA macro needs to be run to update the scenario table – unlike the Data Table, there is no automatic updating when saving or upon spreadsheet open (unless specifically coded)
The choice is yours whether to use VBA or run multiple models for your scenario analysis!