VBA for Scenario Analysis in Project Finance Modelling

By Kenny Chew on August 25 2010

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?

Our Approach

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.

Advantages

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

Disadvantages

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!