A hands-on guide to using Excel in the business context
First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away.
Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques.
- Provides the perfect mix of practice and theory
- Helps you become a DIY Excel modelling specialist
- Includes updates for Excel 2019/365 and Excel for Mac
- May be used as an accompaniment to the author’s online and face-to-face training courses
Many people are often overwhelmed by the hundreds of tools in Excel, and this book gives clarity to the ones you need to know in order to perform your job more efficiently. This book also demystifies the technical, design, logic and financial skills you need for business and financial modelling.
Table of Contents
Preface xi
Chapter 1 What is Financial Modelling? 1
What’s the Difference Between a Spreadsheet and a Financial Model? 3
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 23
Summary 27
Chapter 2 Building a Model 29
Model Design 29
The Golden Rules for Model Design 31
Design Issues 32
The Workbook Anatomy of a Model 33
Project Planning Your Model 36
Model Layout Flowcharting 37
Steps to Building a Model 39
Information Requests 47
Version-Control Documentation 49
Summary 50
Chapter 3 Best-Practice Principles of Modelling 51
Document Your Assumptions 51
Linking, Not Hardcoding 52
Enter Data Only Once 53
Avoid Bad Habits 53
Use Consistent Formulas 53
Format and Label Clearly 54
Methods and Tools of Assumptions Documentation 55
Linked Dynamic Text Assumptions Documentation 62
What Makes a Good Model? 65
Summary 67
Chapter 4 Financial Modelling Techniques 69
The Problem with Excel 69
Error Avoidance Strategies 71
How Long Should a Formula Be? 76
Linking to External Files 78
Building Error Checks 81
Circular References 85
Summary 90
Chapter 5 Using Excel in Financial Modelling 91
Formulas and Functions in Excel 91
Excel Versions 94
Handy Excel Shortcuts 100
Cell Referencing Best Practices 104
Named Ranges 107
Basic Excel Functions 110
Logical Functions 114
Nesting Logical Functions 117
Summary 125
Chapter 6 Functions for Financial Modelling 127
Aggregation Functions 127
LOOKUP Functions 139
Nesting Index and Match 150
OFFSET Function 153
Regression Analysis 158
Choose Function 164
Working with Dates 165
Financial Project Evaluation Functions 171
Loan Calculations 177
Summary 183
Chapter 7 Tools for Model Display 185
Basic Formatting 185
Custom Formatting 186
Conditional Formatting 191
Sparklines 195
Bulletproofing Your Model 199
Customising the Display Settings 203
Form Controls 210
Summary 226
Chapter 8 Tools for Financial Modelling 227
Hiding Sections of a Model 227
Grouping 233
Array Formulas 234
Goal Seeking 240
Structured Reference Tables 242
PivotTables 245
Macros 254
Summary 263
Chapter 9 Common Uses of Tools in Financial Modelling 265
Escalation Methods for Modelling 265
Understanding Nominal and Effective (Real) Rates 270
Calculating a Cumulative Sum (Running Totals) 274
How to Calculate a Payback Period 275
Weighted Average Cost of Capital (WACC) 278
Building a Tiering Table 282
Modelling Depreciation Methods 286
Break-Even Analysis 295
Summary 300
Chapter 10 Model Review 301
Rebuilding an Inherited Model 301
Improving Model Performance 312
Auditing a Financial Model 317
Summary 323
Appendix: QA Log 323
Chapter 11 Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 325
What are the Differences Between Scenario, Sensitivity, and What-If Analyses? 326
Overview of Scenario Analysis Tools and Methods 328
Advanced Conditional Formatting 337
Comparing Scenario Methods 340
Adding Probability to a Data Table 350
Summary 351
Chapter 12 Presenting Model Output 353
Preparing an Oral Presentation for Model Results 353
Preparing a Graphic or Written Presentation for Model Results 355
Chart Types 358
Working with Charts 367
Handy Charting Hints 374
Dynamic Named Ranges 376
Charting with Two Different Axes and Chart Types 382
Bubble Charts 384
Creating a Dynamic Chart 387
Waterfall Charts 391
Summary 395
About the Author 397
About the Website 399
Index 403
