Details

<p>Background, Objectives and Approach xv</p> <p>About the Author xix</p> <p>Acknowledgements xxi</p> <p><b>1 Building Blocks: Selected Excel Functions and Tools 1</b></p> <p>Core Functions for Financial Modelling 1</p> <p>Arithmetic Operations 1</p> <p>Logical Operations 3</p> <p>Financial Calculations 6</p> <p>Database Functions, Features and Pivot Tables 12</p> <p>Statistical Functions 16</p> <p>Lookup and Reference Functions 20</p> <p>Text Functions 29</p> <p>Information Functions 33</p> <p>Array Functions, Formulae and Matrix Calculations 35</p> <p>GoalSeek and Solver 40</p> <p>The Analysis ToolPak and Other Add-ins 43</p> <p>The Analysis ToolPak 44</p> <p>Selected Excel Short-cuts 47</p> <p><b>2 Principles of Modelling 49</b></p> <p>What is a Good Model? 49</p> <p>Model Design 50</p> <p>Selection of Model Variables and their Dependencies 50</p> <p>Hyper Sensitivity to a Variable 55</p> <p>Level of Detail or Aggregation 55</p> <p>Model Structure and Planning 60</p> <p>Logical Flow 60</p> <p>Compactness 62</p> <p>Named Ranges 64</p> <p>Circular References 69</p> <p>Model Building 74</p> <p>Formatting and Comments 74</p> <p>Creating Robust Formulae 78</p> <p>Results Presentation and Other Uses of Sensitivity Analysis 85</p> <p>General Remarks on Presentation 86</p> <p>Using Data Tables to Conduct Sensitivity Analysis 86</p> <p>Hiding and Protecting Models 92</p> <p>Model Auditing 94</p> <p><b>3 Financial Statement, Cash Flow and Valuation Modelling 99</b></p> <p>Financial Statement Modelling: Core Points and Example 99</p> <p>General Comments 100</p> <p>Income Statement Forecasting 102</p> <p>Balance Sheet Forecasting 107</p> <p>Cash Flow Statement Forecasting 110</p> <p>Error Checks and Feasibility Checks 111</p> <p>General Error Checking Tools 111</p> <p>Feasibility Checking and Ratio Analysis 113</p> <p>Adding Generality 116</p> <p>Cash Flow Valuation 120</p> <p>Calculation of Free Cash Flow 121</p> <p>Discounting Free Cash Flow 121</p> <p>Terminal Value Calculations 124</p> <p>Further Adjustments 128</p> <p>Sensitivity Analysis 129</p> <p><b>4 Risk Modelling 133</b></p> <p>Benefits and Challenges of Risk Modelling 133</p> <p>The Risk Modelling Process 135</p> <p>An Introduction to Simulation Techniques 136</p> <p>The Language of Probability Distributions 137</p> <p>Quick Guide to using @RISK 142</p> <p>Types of Dependency Relationships in Risk Models 144</p> <p>The Selection and Use of Distributions 147</p> <p>Pragmatic Approaches and Distributions 148</p> <p>Data-Driven Approaches and Distributions 153</p> <p>Scientific Approaches and Distributions: The Basics 155</p> <p>Further Aspects of the Science of Distributions 163</p> <p>Further Example Models 167</p> <p><b>5 Introduction to Options and Real Options Modelling 181</b></p> <p>Financial Market Derivatives: An Introduction 181</p> <p>Real Options Modelling 188</p> <p>Uses and Relationships to Other Types of Analysis 189</p> <p><b>6 VBA for Financial Modelling 203</b></p> <p>Introduction 203</p> <p>The Bare Essentials 204</p> <p>Simple Examples 208</p> <p>Building Blocks 212</p> <p>Working with Ranges 212</p> <p>Writing Robust Code 220</p> <p>Further Topics 224</p> <p>Object Orientation: An Introduction 224</p> <p>Controlling Execution and Related Topics 227</p> <p>Working with Functions 230</p> <p>Checking and Debugging Code 232</p> <p>Examples: Recording Macros and Related Topics 235</p> <p>Introduction 235</p> <p>Using GoalSeek and Solver 237</p> <p>Examples: Simulation Modelling 240</p> <p>Introduction 240</p> <p>Examples: User-defined Functions 243</p> <p>Creating Functions 243</p> <p>Examples 244</p> <p>Structure and Organisation: Further Topics 248</p> <p>Further Reading 253</p> <p>Index 255</p>

<b>Michael Rees</b> gained a BA with First Class Honours and a Doctorate in Mathematics from Oxford University in 1985 and 1988 respectively. In 1992 he gained an MBA with Distinction from INSEAD, and in 2003 graduated in first position on the Certificate in Quantitative Finance program, also winning the Wilmott award.<br /> Since 2002 Michael has worked independently as a consultant and trainer in financial modelling. Prior to this he worked as a strategy consultant with Braxton Associates and Mercer Management Consulting, and also as an analyst at J.P. Morgan.<br /> Michael lives in Richmond, UK. He was born in Canada, has lived in several countries, and is fluent in French and German.

<i>Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level</i> is a practical, comprehensive and in-depth guide to financial modelling designed to cover the modelling issues that are relevant to facilitate the construction of robust and readily understandable models. <p>Based on the authors extensive experience of building models in business and finance, and of training others how to do so this book starts with a review of Excel functions that are generally most relevant for building intermediate and advanced level models (such as Lookup functions, database and statistical functions and so on). It then discusses the principles involved in designing, structuring and building relevant, accurate and readily understandable models (including the use of sensitivity analysis techniques) before covering key application areas, such as the modelling of financial statements, of cash flow valuation, risk analysis, options and real options. Finally, the topic of financial modelling using VBA is treated. Practical examples are used throughout and model examples are included in the attached CD-ROM.</p> <p>Aimed at intermediate and advanced level modellers in Excel who wish to extend and consolidate their knowledge, this book is focused, practical, and application-driven, facilitating knowledge to build or audit a much wider range of financial models.</p> <p>"An excellent book which presents advanced financial modelling tools and simulations, and applies them to modern aspects of financial management. As a renowned expert in modelling, Michael Rees develops efficient techniques for simulation and sensitivity analysis within an Excel and Excel add-on framework using many useful and transparent applications in the context of company valuation, derivative business and risk management, enabling the reader to develop good models themselves. A unique book which is highly instructive and motivating."<br /> <b>—Professor Dr Dieter Gramlich, University of Cooperative Education, Heidenheim, Germany</b></p> <p>"Mike Rees's book fills an important gap in the literature on how to model financial data. It not only provides a whole host of useful suggestions on how to design, structure, build and analyse models; including tips on how use some of the more advanced functionality in Excel, but also in a clear and concise way explains how to include uncertainty in to these models. During the last few years many business's environment have changed, creating the need to explicitly include uncertainty into their decision making rather than hide behind simple (and often flawed) assumptions of what the future may hold. Mike clearly understands the importance of this area and includes several sections which provide an excellent introduction to anyone starting to apply these types of techniques in their financial models for the first time. It is the combination of best practice modelling techniques, plenty of examples and the basics of some of the more advanced approaches that make this book a useful addition to anyone building financial models."<br /> <b>—</b><b>Andrea Dickens,</b> <b>Decision Analysis Group Leader, Finance Excellence Unilever</b></p>

GB