Practical Financial Modelling, Second Edition: A guide to current practice

This second edition of Practical Financial Modelling is vital tool for all finance and management professionals whose work involves the production and development of complex spreadsheets and financial models. The author bridges the gap between the Excel manual and financial literature with a wealth of practical advise and useful tips. The book identifies good practice and highlights those areas which are prone to error and inconsistency resulting in a refreshingly simple approach to building and using financial models suitable for novice and experienced modellers. By using practical worked examples the most effective ways in which problems can be solved are explored. Key themes include: model structure, audit formulae and functions and model use. New to the second edition: Instructive information on Excel 2007 and its enhanced modelling functions and feature; Risk controls in developing and using financial models; Test-yourself modelling problems and applied examples in every chapter; Substantial information related to reporting and charting techniques and an appendix devoted to parallel comparison of how-to in Excel 2003 and 2007. . Instructive information on Excel 2007 and its enhanced modelling functions and features . Risk controls in developing and using financial models . Test-yourself modelling problems and applied examples in every chapter . Substantial information relating to reporting and charting techniques . An appendix devoted to a parallel comparison of how-to in Excel 2003 and Excel 2007 Front Cover......Page 1 Practical Financial Modelling: A Guide to Current Practice......Page 4 Copyright Page......Page 5 Table of Contents......Page 8 Preface to the First Edition......Page 12 Preface to the Second Edition......Page 14 About the Author......Page 16 Introduction......Page 18 Introduction......Page 30 Risk assessment and risk controls......Page 31 The model developer......Page 33 Two approaches......Page 34 Purpose......Page 35 Structure......Page 36 Workbook structure......Page 39 Inputs......Page 40 Workings......Page 41 Outputs......Page 43 Variations......Page 45 Documentation......Page 47 Reporting......Page 50 Reports......Page 55 Model development......Page 56 Navigation......Page 61 Exercises......Page 66 Introduction......Page 68 Taxonomies of error......Page 69 Audit tools......Page 72 Error values......Page 76 Audit sheet......Page 80 Structural checks......Page 82 Arithmetical checks......Page 95 Financial checks......Page 97 Model map......Page 102 Excel 2007 workbook tests......Page 104 Exercises......Page 105 Introduction......Page 108 Range names......Page 109 Define Names......Page 116 Additional name functionality......Page 127 Timing......Page 141 Changing time periods......Page 148 Circularities and iteration......Page 152 Coercion......Page 164 Array formulas......Page 165 R1C1......Page 168 Exercises......Page 169 Introduction......Page 172 Logical......Page 173 Revolving credit and the cash cascade......Page 183 Lookup......Page 186 Financial......Page 198 Dates......Page 201 Other useful functions......Page 203 Exercises......Page 208 Introduction......Page 210 Grouping and outlining......Page 211 Data inputs......Page 214 Conditional formatting......Page 220 Custom formatting......Page 227 Protection......Page 234 Charting......Page 238 Exercises......Page 240 Introduction......Page 242 Best practice......Page 243 Goal Seek......Page 244 Data tables......Page 245 Scenarios......Page 248 Risk......Page 260 Monte Carlo simulation......Page 262 Exercises......Page 263 Introduction......Page 266 Macro security settings......Page 267 Recorded macros......Page 268 Iteration macro......Page 269 Assigning macros......Page 273 Written macros......Page 276 Branching macros......Page 278 Quarterly/annual macro......Page 280 Error handling......Page 281 User-defined functions......Page 282 Deleting macros......Page 285 Exercises......Page 286 Appendix 1: Keyboard Shortcuts......Page 288 Appendix 2: Good Modelling Practice......Page 292 Appendix 3: Case Study......Page 298 Index......Page 300 "Practical Financial Modelling" is for all finance and management professionals whose work involves the development or use of complex spreadsheets and financial models. The second edition now covers Excel 2007. Chapter 1: Model structure now includes a review of non-spreadsheet modelling tools. Chapter 2: Quality control now contains a new section to include Excel's in-built audit functionality; and further audit checks. Chapter 3: Mainly formulae now includes a number of worked examples for eg cash cascades, cash sweeps, sinking funds etc. Chapter 5: Model use, has under gone a substantial revision, and now includes reporting and charting techniques. In order to accomodate both Excel 2003 users and new Excel 2007 users the book has an extensive appendix which shows "how to do it in Excel 2003 and how to do it in Excel 2007" as comparison. This book follows on from the previous very successful first edition by offering a refreshingly simple approach to building and using financial models. Most models in use today are as unique as the individuals who create them, and because of this they can be difficult to understand or use with confidence. This book offers a critical insight into current practice and presents a clear and robust modelling methodology with the specific aim of reducing unnecessary complexity in favour of a simpler and more reliable approach, which will be of interest to both experienced and novice modellers. This methodology has been used for some of the most modelling intensive transactions in the world, but is also equally applicably to reader creating simple cash flow forecasts."Practical Financial Modelling" bridges the gap between the Excel manual and the finance textbook. The principle is that once people have the ability to structure a model, they have a better starting point from which to address the financial calculations. Quality control is built in from the outset. The key themes of the book are model structure, audit, formulae and This second edition of Practical Financial Modelling is vital tool for all finance and management professionals whose work involves the production and development of complex spreadsheets and financial models. The author bridges the gap between the Excel manual and financial literature with a wealth of practical advise and useful tips.

The book identifies good practice and highlights those areas which are prone to error and inconsistency resulting in a refreshingly simple approach to building and using financial models suitable for novice and experienced modellers. By using practical worked examples the most effective ways in which problems can be solved are explored. Key themes include: model structure, audit formulae and functions and model use.

New to the second edition:

Instructive information on Excel 2007 and its enhanced modelling functions and feature; Risk controls in developing and using financial models; Test-yourself modelling problems and applied examples in every chapter; Substantial information related to reporting and charting techniques and an appendix devoted to parallel comparison of how-to in Excel 2003 and 2007.

• Instructive information on Excel 2007 and its enhanced modelling functions and features
• Risk controls in developing and using financial models
• Test-yourself modelling problems and applied examples in every chapter
• Substantial information relating to reporting and charting techniques
• An appendix devoted to a parallel comparison of how-to in Excel 2003 and Excel 2007 Functions, and model use, using basic Excel functionality throughout. The author draws on years of teaching and consultancy to identify the elements of good practice and to highlight those areas and techniques which are prone to error and inconsistency. He explores the different ways in which problems can be solved by using practical worked examples. The reader can adapt the ideas and techniques for their own purposes. It is updated to include how-to-do-it in the new Excel 2007. Now, it covers Excel's in-built audit functionality, and reporting and charting techniques. It shows how-to approach to financial modelling and budgeting, with numerous worked examples Provides practical advice and tips for producing and developing complex spreadsheets and financial models. Aims to bridge the gap between the Excel manual and financial literature. Includes: instructive information on the enhanced features and modelling functions of Excel 2007; risk controls in developing and using financial models; exercises and applied examples; substantial information relating to reporting and charting techniques; and, a parallel comparison of Excel 2003 and Excel 2007