Using Excel for Business and Financial Modelling A Practical Guide

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.

DANIELLE STEIN FAIRHURST is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. Her extensive experience as a financial analyst enables her to help her clients create meaningful financial models. She is regularly engaged as a speaker, course facilitator, financial modelling consultant, and analyst.

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

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.