Principles of Financial Modelling

Model Design and Best Practices Using Excel and VBA

Michael Rees

• Publisher: Wiley
• Year: 2018
• Print ISBN: 9781118904015
• eBook ISBN: 9781118904008
• Edition: 1
• Topic: Business
• Subtopic: Finance
• Index: Business

The comprehensive, broadly-applicable, real-world guide to financial modelling

Principles of Financial Modelling – Model Design and Best Practices Using Excel and VBAcovers the full spectrum of financial modelling tools and techniques in order to provide practical skills that are grounded in real-world applications. Based on rigorously-tested materials created for consulting projects and for training courses, this book demonstrates how to plan, design and build financial models that are flexible, robust, transparent, and highly applicable to a wide range of planning, forecasting and decision-support contexts. This book integrates theory and practice to provide a high-value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. Highlights of its content include extensive coverage of:

• Model design and best practices, including the optimisation of data structures and layout, maximising transparency, balancing complexity with flexibility, dealing with circularity, model audit and error-checking
• Sensitivity and scenario analysis, simulation, and optimisation
• Data manipulation and analysis
• The use and choice of Excel functions and functionality, including advanced functions and those from all categories, as well as of VBA and its key areas of application within financial modelling

The companion website provides approximately 235 Excel files (screen-clips of most of which are shown in the text), which demonstrate key principles in modelling, as well as providing many examples of the use of Excel functions and VBA macros. These facilitate learning and have a strong emphasis on practical solutions and direct real-world application.

For practical instruction, robust technique and clear presentation, Principles of Financial Modelling is the premier guide to real-world financial modelling from the ground up. It provides clear instruction applicable across sectors, settings and countries, and is presented in a well-structured and highly-developed format that is accessible to people with different backgrounds.

PART One
Introduction to Modelling, Core Themes and Best Practices

CHAPTER 1
Models of Models

INTRODUCTION

This chapter provides an overview of financial modelling, including its objectives, stages and processes. The discussion sets the context and frameworks that are used in much of the subsequent text.

CONTEXT AND OBJECTIVES

A model is a numerical or mathematical representation of a real-life situation. A financial model is one which relates to business and finance contexts. The typical objectives of financial modelling include to support decisions relating to business plans and forecasts, to the design, evaluation and selection of projects, to resource allocation and portfolio optimisation, to value corporations, assets, contracts and financial instruments, and to support financing decisions.

In fact, there is no generally accepted (standardised) definition of financial modelling. For some, it is a highly pragmatic set of activities, essentially consisting of the building of Excel worksheets. For others, it is a mainly conceptual activity, whose focus is on the use of mathematical equations to express the relationships between the variables in a system, and for which the platform (e.g. Excel) that is used to solve such equations is not of relevance. In this text, we aim to integrate theory and practice as much as possible.

THE STAGES OF MODELLING

The modelling process can be considered as consisting of several stages, as shown in Figure 1.1.

FIGURE 1.1 A Generic Framework for Stages of the Modelling Process

The key characteristics of each stage include:

• Specification: This involves describing the real-life situation, either qualitatively or as a set of equations. In any case, at this stage one should also consider the overall objectives and decision-making needs, and capture the core elements of the behaviour of the real-world situation. One should also address issues relating to the desired scope of model validity, the level of accuracy required and the trade-offs that are acceptable to avoid excessive complexity whilst providing an adequate basis for decision support.
• Implementation: This is the process to translate the specification into numerical values, by conducting calculations based on assumed input values. For the purposes of this text, the calculations are assumed to be in Excel, perhaps also using additional compatible functionality (such as VBA macros, Excel add-ins, optimisation algorithms, links to external databases and so on).
• Decision support: A model should appropriately support the decision. However, as a simplification of the real-life situation, a model by itself is almost never sufficient. A key challenge in building and using models to greatest effect is to ensure that the process and outputs provide a value-added decision-support guide (not least by providing insight, reducing biases or correcting invalid assumptions that may be inherent in less-rigorous decision processes), whilst recognising the limitations of the model and the modelling process.

Note that in many practical cases, no explicit specification step is conducted; rather, knowledge of a situation is used to build an Excel workbook directly. Since Excel does not calculate incorrectly, such a model can never truly be “(externally) validated”: the model specification is the model itself (i.e. as captured within the formulae used in Excel). Although such “self-validation” is in principle a significant weakness of these pragmatic approaches, the use of a highly formalised specification stage is often not practical (especially if one is working under tight deadlines, or one believes that the situation is generally well-understood). Some of the techniques discussed in this text (such as sensitivity-driven model design and the following of other best practices) are particularly important to support robust modelling processes, even where little or no documented specification has taken place or is practically possible.

BACKWARD THINKING AND FORWARD CALCULATION PROCESSES

The modelling process is essentially two-directional (see Figure 1.2):

FIGURE 1.2 Modelling as a Combination of a Backward Thought Process and a Forward Calculation Process

• A “backward thought process”, in which one considers a variable of interest (the model output) and defines its underlying, or causal, factors. This is a qualitative process, corresponding to reading Figure 1.2 from left to right. For example, cash flow may be represented as being determined from revenue and cost, each of which may be determined by their own causal factors (e.g. revenue is determined by price and volume). As a qualitative process, at this stage, the precise the nature of the relationships may not yet be made clear: only that the relationships exist.
• A “forward-calculation process”, in which one which starts with the assumed values of the final set of causal factors (the “model inputs”) and builds the required calculations to determine the values of the intermediate variables and final outputs. This is a numerical process corresponding to reading Figure 1.2 from right to left. It involves defining the nature of the relationships sufficiently precisely that they can be implemented in quantitative formulae. That is, inputs are used to calculate the intermediate variables, which are used to calculate the outputs. For example, revenue would be calculated (from an assumed price and volume), and cost (based on fixed and variable costs and volume), with the cash flow as the final output.

Note that the process is likely to contain several iterations: items that may initially be numerical inputs may be chosen to be replaced by calculations (which are determined from new numerical inputs), thus creating a model with more input variables and detail. For example, rather than being a single figure, volume could be split by product group. In principle, one may continue the process indefinitely (i.e. repeatedly replacing hard-coded numerical inputs with intermediate calculations). Of course, the potential process of creating more and more detail must stop at some point:

• For the simple reason of practicality.
• To ensure accuracy. Although the creation of more detail would lead one to expect to have a more accurate model, this is not always the case: a detailed model will require more information to calibrate correctly (for example, to estimate the values of all the inputs). Further, the capturing of the relationships between these inputs will become progressively more complex as more detail is added.

The “optimal” level of detail at which a model should be built is not a trivial question, but is discussed further in Chapter 4.

It may be of interest to note that this framework is slightly simplified (albeit covering the large majority of cases in typical Excel contexts):

• In some applications (notably sequential optimisation of a time series, and decision trees), the calculations are required to be conducted both forward and backward, as the optimal behaviour at an earlier time depends on considering all the future consequences of each potential decision.
• In econometrics, some equations may be of an equilibrium nature, i.e. they contain the same variable(s) on both sides of an equation(s). In such cases, the logic flow is not directional, and will potentially give rise to circular references in the implemented models.

CHAPTER 2
Using Models in Decision Support

INTRODUCTION

This chapter summarises the main benefits and challenges of using models in decision support. Where significant amounts of money are at stake, or the choice of the most appropriate decision option is important for some other reason, it is often taken as a given that the building of a model would be useful. However, it is important to understand the specific sources of benefits, and the challenges and potential weaknesses of modelling processes. Doing so will help to support a more robust basis for decision-making, and reduce the likelihood that the outputs are misinterpreted, misused, or assumed to apply to a context for which the model was not designed.

BENEFITS OF USING MODELS

This section highlights the key benefits potentially achievable by the use of models.

Providing Numerical Information

A model calculates the possible values of variables that are considered important in the context of the decision at hand. Of course, this information is often of paramount importance, especially when committing resources, budgeting and so on.

Nevertheless, the calculation of the numerical values of key variables is not the only reason to build models; the modelling process often has an important exploratory and insight-generating aspect (see later in this section). In fact, many ...

Table of contents

• Cover
• Title Page
• Table of Contents
• Preface
• About the Author
• About the Website
• PART One: Introduction to Modelling, Core Themes and Best Practices
• PART Two: Model Design and Planning
• PART Three: Model Building, Testing and Auditing
• PART Four: Sensitivity and Scenario Analysis, Simulation and Optimisation
• PART Five: Excel Functions and Functionality
• PART Six: Foundations of VBA and Macros
• Index
• End User License Agreement