Principles of Financial Modelling

CHAPTER 14Using VBA Macros to Conduct Sensitivity and Scenario Analyses

INTRODUCTION

This chapter discusses the use of VBA macros to run sensitivity and scenario analysis. This is needed in cases where any form of additional intervention or procedure is required between the changing of an input value and the calculation of the output. Examples of where such approaches may be required include:

• Where a model contains circular references that are resolved using a macro (as described in Chapter 10).
• If, after changing input values, GoalSeek or Solver needs to be run before the final output can be calculated.
• If other macros are needed to be run for any reason if input values change. For example, each scenario may require external data to be queried before the model is recalculated.

(Note that readers who are not familiar with VBA should nevertheless be able to follow the core principles of this chapter; otherwise they may choose to selectively study Part VI first.)

When using a VBA macro to run sensitivity analysis, generally two sets of procedures require automation:

• The first steps through the values to be used for the input(s) in the sensitivity or scenario analysis. This forms the outer loop of the overall process, and is a general step that essentially applies to all such approaches.
• The second applies the additional procedure that is to be performed at each step of the first loop (e.g. resolving circularities, running GoalSeek, querying the external data etc.). This forms ...