Principles of Financial Modelling (eBook)

Model Design and Best Practices Using Excel and VBA

Michael Rees (Autor)

eBook Download: EPUB

2018
John Wiley & Sons (Verlag)
978-1-118-90400-8 (ISBN)

Infos

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.

Autor

MICHAEL REES, D.PHIL., MBA, operates globally to help senior executives to solve their most complex problems in the areas of decision support, business strategy, value-creation, risk assessment, and optimisation. He combines practical experience from top firms with an exceptional analytic record, and is among the world's leading authors and instructors in the field of financial and risk modelling. His special interest is in cases where issues in strategy, business economics, and valuation are best addressed using practical advanced quantitative approaches. He has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark. He has approximately 30 years' business and finance experience, in many sectors, including oil, gas, energy and resources, private equity, health care, biotechnology, chemicals, construction, engineering, and insurance.

Inhalt

Preface xxv

About the Author xxvii

About the Website xxix

PART ONE Introduction to Modelling, Core Themes and Best Practices 1

CHAPTER 1 Models of Models 3

CHAPTER 2 Using Models in Decision Support 7

CHAPTER 3 Core Competencies and Best Practices: Meta-themes 15

PART TWO Model Design and Planning 23

CHAPTER 4 Defining Sensitivity and Flexibility Requirements 25

CHAPTER 5 Database Versus Formulae-driven Approaches 37

CHAPTER 6 Designing the Workbook Structure 47

PART THREE Model Building, Testing and Auditing 57

CHAPTER 7 Creating Transparency: Formula Structure, Flow and Format 59

CHAPTER 8 Building Robust and Transparent Formulae 79

CHAPTER 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105

CHAPTER 10 Dealing with Circularity 117

CHAPTER 11 Model Review, Auditing and Validation 143

PART FOUR Sensitivity and Scenario Analysis, Simulation and Optimisation 153

CHAPTER 12 Sensitivity and Scenario Analysis: Core Techniques 155

CHAPTER 13 Using GoalSeek and Solver 163

CHAPTER 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171

CHAPTER 15 Introduction to Simulation and Optimisation 177

CHAPTER 16 The Modelling of Risk and Uncertainty, and Using Simulation 187

PART FIVE Excel Functions and Functionality 199

CHAPTER 17 Core Arithmetic and Logical Functions 201

CHAPTER 18 Array Functions and Formulae 217

CHAPTER 19 Mathematical Functions 229

CHAPTER 20 Financial Functions 243

CHAPTER 21 Statistical Functions 257

CHAPTER 22 Information Functions 299

CHAPTER 23 Date and Time Functions 307

CHAPTER 24 Text Functions and Functionality 313

CHAPTER 25 Lookup and Reference Functions 325

CHAPTER 26 Filters, Database Functions and PivotTables 355

CHAPTER 27 Selected Short-cuts and Other Features 387

PART SIX Foundations of VBA and Macros 395

CHAPTER 28 Getting Started 397

CHAPTER 29 Working with Objects and Ranges 413

CHAPTER 30 Controlling Execution 425

CHAPTER 31 Writing Robust Code 441

CHAPTER 32 Manipulation and Analysis of Data Sets with VBA 455

CHAPTER 33 User-defined Functions 473

Index 493

Textauszug

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 insights can often be generated early in the overall process, whereas numerical values tend to be of most use later on.

Capturing Influencing Factors and Relationships

The process of building a model should force a consideration of which factors influence the situation, including which are most important. Whilst such reflections may be of an intuitive or qualitative nature (at the early stages), much insight can be gained through the use of a quantitative process. The quantification of the relationships requires one to consider the nature of the relationships in a very precise way (e.g. whether a change in one would impact another and by how much, whether such a change is linear or non-linear, whether other variables are also affected, or whether there are (partially) common causal factors between variables, and so on).

Generating Insight and Forming Hypotheses

The modelling process should highlight areas where one's knowledge is incomplete, what further actions could be taken to improve this, as well as what data is needed. This can be valuable in its own right. In fact, a model is effectively an explicit record of the assumptions and of the (hypothesised) relationships between items (which may change as further knowledge is developed). The process therefore provides a structured approach to develop a better understanding. It often uncovers many assumptions that are being made implicitly (and which may be imprecisely understood or incorrect), as well as identifying the assumptions that are required and appropriate. As such, both the qualitative and the quantitative aspects of the process should provide new insights and identify issues for further exploration.

The overlooking or underestimation of these exploratory aspects is one of the main inefficiencies in many modelling processes, which are often delegated to junior staff who are competent in “doing the numbers”, but who may not have the experience, or lack sufficient project exposure, authority, or the credibility to identify and report many of the key insights, especially those that may challenge current assumptions. Thus, many possible insights are either lost or are simply never generated in the first place. Where a model produces results that are not readily explained intuitively, there are two generic cases:

• It is over-simplified, highly inaccurate or wrong in some important way. For example, key variables may have been left out, dependencies not correctly captured, or the assumptions used for the values of variables may be wrong or poorly estimated.
• It is essentially correct, but provides results which are not intuitive. In such situations, the modelling process can be used to adapt, explore and generate new insights, so that ultimately both the intuition and the model's outputs become aligned. This can be a value-added process, particularly if it highlights areas where one's initial intuition may be lacking.

In this context, the following well-known quotes come to mind:

• “Plans are useless, but planning is everything” (Eisenhower).
• “Every model is wrong, some are useful” (Box).
• “Perfection is the enemy of the good” (Voltaire).

Decision Levers, Scenarios, Uncertainties, Optimisation, Risk Mitigation and Project Design

When conducted rigorously, the modelling process distinguishes factors which are controllable from those which are not. It may also highlight that some items are partially controllable, but require further actions that may not (currently) be reflected in the planning nor in the model (e.g. the introduction of risk mitigation actions). Ultimately, controllable items correspond to potential decisions that should be taken in an optimal way, and non-controllable items are those which are risky or subject to uncertainty. The use of sensitivity, scenario and risk techniques can also provide insight into the extent of possible exposure if a decision were to proceed as planned, lead to modifications to the project or decision design, and allow one to find an optimal decision or project structure.

Improving Working Processes, Enhanced Communications and Precise Data Requirements

A model provides a structured framework to take information from subject matter specialists or experts. It can help to define precisely the information requirements, which improves the effectiveness of the research and collection process to obtain such information. The overall process and results should also help to improve communications, due to the insights and transparency generated, as well as creating a clear structure for common working and co-ordination.

CHALLENGES IN USING MODELS

This section highlights the key challenges faced when using models in decision support.

The Nature of Model Error

Models are, by nature, simplifications of (and approximations to) the real-world. Errors can be introduced at each stage (as presented in Figure 1.1):

• Specification error. This is the difference between the behaviour of the real-world situation and that captured within the specification or intentions of the model (sometimes this individual part is referred to as “model risk” or “model error”). Although one may often be able to provide a reasonable intuitive assessment of the nature of some such errors, it is extremely challenging to provide a robust quantification, simply because the nature of the real world is not fully known. (By definition, the ability to precisely define and calculate model error would only arise if such error were fully understood, in which case, it could essentially be captured in a revised model, with error then having been eliminated.) Further, whilst one may be aware of some simplifications that the model contains compared to the real-life situation, there are almost certainly possible behaviours of the real-life situation that are not known about. In a sense, one must essentially “hope” that the model is a sufficiently accurate representation for the purposes at hand. Of course, a good intuition, repeated empirical observations and large data sets can increase the likelihood that a conceptual model is correct (and improve one's confidence in it), but ultimately there will be some residual uncertainty (“black swans” or “unknown unknowns”, for example).
• Implementation error. This is the difference between the specified model (as conceived or intended) and the model as implemented. Such errors could result by mistake (calculation error) or due to subtler issues, such as the use of a discrete time axis in Excel (when events in fact materialise in continuous time), or of a finite time axis (instead of an unlimited one). Errors also arise frequently in which a model calculates correctly in the base case, but not in other cases (due to mistakes, or overlooking key aspects of the behaviour of the situation).
• Decision error. This is the idea that a decision that is made based on the results of a model could be inappropriate. It captures the (lack of) effectiveness of the decision-making process, including a lack of understanding of a model and its limitations. Note that a poor outcome following a decision does not necessarily imply that the decision was poor, nor does a good outcome imply that the decision was the correct choice.

Some types of model error relate to multiple process stages (rather than a single one), including where insufficient attention is given to scenarios, risk and uncertainties.

Inherent Ambiguity and Circularity of Reasoning

The modelling process is inherently ambiguous: in order to specify or build a model, one must already understand the situation reasonably well. However, the model and modelling process can provide benefit only to the extent that the initial understanding is imperfect. (By definition, were a perfect understanding to exist even before a model is built, then no model would be required, since there would be no way to improve the understanding further!)

This ambiguity also creates potentially uncertainty around the meaning of...

Produkt-Details

• Erscheint lt. Verlag: 19.3.2018
• Reihe/Serie: The Wiley Finance Series
• Reihe/Serie: Wiley Finance Series
• Reihe/Serie: Wiley Finance Series
• Sprache: englisch
• Themenwelt: Recht / Steuern ► Wirtschaftsrecht
• Themenwelt: Wirtschaft ► Betriebswirtschaft / Management ► Finanzierung
• Schlagworte: comprehensive financial modeling guide • comprehensive financial modelling guide • Excel modeling • Excel modelling • Finance & Investments • financial modeling guide • Financial Modeling in Practice: A Concise Guide to Using Excel and VBA for Intermediate and Advanced Level, Second Edition • financial modeling instruction • financial modeling reference • financial modeling resources • financial modeling techniques • financial modeling tools • financial modeling training • financial modelling guide • Financial Modelling in Practice: A Concise Guide to Using Excel and VBA for Intermediate and Advanced Level, Second Edition • financial modelling instruction • financial modelling reference • financial modelling resources • financial modelling techniques • financial modelling tools • financial modelling training • Finanztechnik • Finanz- u. Anlagewesen • intermediate financial modeling • intermediate financial modelling • learning financial modeling • learning financial modelling • master financial modeling • master financial modelling • Michael Rees • practical financial modeling • practical financial modelling • real-world financial modeling • real-world financial modelling • VBA financial modeling • VBA financial modelling
• ISBN-10: 1-118-90400-1 / 1118904001
• ISBN-13: 978-1-118-90400-8 / 9781118904008