Principles of Financial Modelling
Model Design and Best Practices Using Excel and VBA
Michael Rees
- English
- ePUB (disponibile sull'app)
- Disponibile su iOS e Android
Principles of Financial Modelling
Model Design and Best Practices Using Excel and VBA
Michael Rees
Informazioni sul libro
The comprehensive, broadly-applicable, real-world guide to financial modelling
Principles of Financial Modelling – Model Design and Best Practices Using Excel and VBA covers 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.
Domande frequenti
PART One
Introduction to Modelling, Core Themes and Best Practices
CHAPTER 1
Models of Models
INTRODUCTION
CONTEXT AND OBJECTIVES
THE STAGES OF MODELLING
- 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.
BACKWARD THINKING AND FORWARD CALCULATION PROCESSES
- 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.
- 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.
- 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.