Preface ix
CHAPTER 1 What Is Financial Modelling? 1
What’s the Difference between a
Spreadsheet and a Financial Model? 4
Types and Purposes of Financial Models 5
Tool Selection 6
What Skills Do You Need to Be a Good Financial Modeller? 17
The Ideal Financial Modeller 24
Summary 28
CHAPTER 2 Building a Model 31
Model Design 31
The Golden Rules for Model Design 33
Design Issues 35
The Workbook Anatomy of a Model 36
Project Planning Your Model 38
Model Layout Flow Charting 41
Steps to Building a Model 41
Information Requests 50
Version-Control Documentation 51
Summary 53
CHAPTER 3 Best Practice Principles of Modelling 55
Document Your Assumptions 55
Linking, Not Hard Coding 56
Enter Data Only Once 57
Avoid Bad Habits 57
Use Consistent Formulas 57
Format and Label Clearly 58
Methods and Tools of Assumptions Documentation 59
Linked Dynamic Text Assumptions Documentation 67
What Makes a Good Model? 70
Summary 72
CHAPTER 4 Financial Modelling Techniques 73
The Problem with Excel 73
Error Avoidance Strategies 75
How Long Should a Formula Be? 81
Linking to External Files 83
Building Error Checks 86
Summary 96
CHAPTER 5 Using Excel in Financial Modelling 97
Formulas and Functions in Excel 97
Excel Versions 101
Handy Excel Shortcuts 103
Basic Excel Functions 109
Logical Functions 112
Nesting: Combining Simple Functions to
Create Complex Formulas 115
Cell Referencing Best Practices 119
Named Ranges 122
Summary 126
CHAPTER 6 Functions for Financial Modelling 127
Aggregation Functions 127
LOOKUP Formulas 140
Nesting INDEX and MATCH 153
OFFSET Function 157
Regression Analysis 161
CHOOSE Function 164
Working with Dates 166
Financial Project Evaluation Functions 174
Loan Calculations 180
Summary 186
CHAPTER 7 Tools for Model Display 187
Basic Formatting 187
Custom Formatting 187
Conditional Formatting 193
Sparklines 200
Bulletproofing Your Model 204
Customising the Display Settings 208
Form Controls 216
Summary 232
CHAPTER 8 Tools for Financial Modelling 233
Hiding Sections of a Model 233
Grouping 238
Array Formulas 240
Goal Seeking 247
Structured Reference Tables 249
PivotTables 251
Macros 262
Summary 272
CHAPTER 9 Common Uses of Tools in Financial Modelling 273
Escalation Methods for Modelling 273
Understanding Nominal and Effective (Real) Rates 278
Calculating Cumulative Totals 283
How to Calculate a Payback Period 284
Weighted Average Cost of Capital (WACC) 288
Building a Tiering Table 293
Modelling Depreciation Methods 296
Break-Even Analysis 307
Summary 313
CHAPTER 10 Model Review 315
Rebuilding an Inherited Model 315
Improving Model Performance 323
Auditing a Financial Model 328
Summary 335
Appendix 10.1: QA Log 336
CHAPTER 11 Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 337
What Are the Differences between Scenario, Sensitivity, and What-If Analyses? 338
Overview of Scenario Analysis Tools and Methods 340
Advanced Conditional Formatting 349
Comparing Scenario Methods 353
Summary 365
CHAPTER 12 Presenting Model Output 367
Preparing an Oral Presentation for Model Results 367
Preparing a Graphic or Written Presentation for Model Results 369
Chart Types 372
Working with Charts 380
Handy Charting Hints 386
Dynamic Named Ranges 388
Charting with Two Different Axes and Chart Types 394
Bubble Charts 400
Creating a Dynamic Chart 402
Waterfall Charts 407
Summary 420
About the Author 421
About the Website 423
Index 425
