View More Content
SHARE

VBA for scenario analysis in project finance modelling

VBA for scenario analysis in project finance modelling

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. 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 Corality Financial Group, 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 Mastering Excel with VBA training course.

Pros and cons

Like everything else, running scenarios using a macro has its advantages and its disadvantages.

Advantages

  • May increase calculation speed – multiple data tables may cause speed issues, especially in Excel 2007
  • 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 opening the spreadsheet (unless specifically coded)

So should you use VBA or run multiple models for your scenario analysis? The choice is yours!