Using Excel for business and financial modelling : a practical guide

Content: Preface xiChapter 1 What is Financial Modelling? 1What's the Difference Between a Spreadsheet and a Financial Model? 3Types and Purposes of Financial Models 5Tool Selection 6What Skills Do You Need to Be a Good Financial Modeller? 17The "Ideal" Financial Modeller 23Summary 27Chapter 2 Building a Model 29Model Design 29The Golden Rules for Model Design 31Design Issues 32The Workbook Anatomy of a Model 33Project Planning Your Model 36Model Layout Flowcharting 37Steps to Building a Model 39Information Requests 47Version-Control Documentation 49Summary 50Chapter 3 Best-Practice Principles of Modelling 51Document Your Assumptions 51Linking, Not Hardcoding 52Enter Data Only Once 53Avoid Bad Habits 53Use Consistent Formulas 53Format and Label Clearly 54Methods and Tools of Assumptions Documentation 55Linked Dynamic Text Assumptions Documentation 62What Makes a Good Model? 65Summary 67Chapter 4 Financial Modelling Techniques 69The Problem with Excel 69Error Avoidance Strategies 71How Long Should a Formula Be? 76Linking to External Files 78Building Error Checks 81Circular References 85Summary 90Chapter 5 Using Excel in Financial Modelling 91Formulas and Functions in Excel 91Excel Versions 94Handy Excel Shortcuts 100Cell Referencing Best Practices 104Named Ranges 107Basic Excel Functions 110Logical Functions 114Nesting Logical Functions 117Summary 125Chapter 6 Functions for Financial Modelling 127Aggregation Functions 127LOOKUP Functions 139Nesting Index and Match 150OFFSET Function 153Regression Analysis 158Choose Function 164Working with Dates 165Financial Project Evaluation Functions 171Loan Calculations 177Summary 183Chapter 7 Tools for Model Display 185Basic Formatting 185Custom Formatting 186Conditional Formatting 191Sparklines 195Bulletproofing Your Model 199Customising the Display Settings 203Form Controls 210Summary 226Chapter 8 Tools for Financial Modelling 227Hiding Sections of a Model 227Grouping 233Array Formulas 234Goal Seeking 240Structured Reference Tables 242PivotTables 245Macros 254Summary 263Chapter 9 Common Uses of Tools in Financial Modelling 265Escalation Methods for Modelling 265Understanding Nominal and Effective (Real) Rates 270Calculating a Cumulative Sum (Running Totals) 274How to Calculate a Payback Period 275Weighted Average Cost of Capital (WACC) 278Building a Tiering Table 282Modelling Depreciation Methods 286Break-Even Analysis 295Summary 300Chapter 10 Model Review 301Rebuilding an Inherited Model 301Improving Model Performance 312Auditing a Financial Model 317Summary 323Appendix: QA Log 323Chapter 11 Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 325What are the Differences Between Scenario, Sensitivity, and What-If Analyses? 326Overview of Scenario Analysis Tools and Methods 328Advanced Conditional Formatting 337Comparing Scenario Methods 340Adding Probability to a Data Table 350Summary 351Chapter 12 Presenting Model Output 353Preparing an Oral Presentation for Model Results 353Preparing a Graphic or Written Presentation for Model Results 355Chart Types 358Working with Charts 367Handy Charting Hints 374Dynamic Named Ranges 376Charting with Two Different Axes and Chart Types 382Bubble Charts 384Creating a Dynamic Chart 387Waterfall Charts 391Summary 395About the Author 397About the Website 399Index 403