Using Excel for Business and Financial Modelling

Using Excel

for Business

and Financial

Modelling

Founded in 1807, John Wiley & Sons is the oldest independent publishing

company in the United States. With offices in North America, Europe, Australia

and Asia, Wiley is globally committed to developing and marketing print and

electronic products and services for our customers’ professional and personal

knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and

investment professionals as well as sophisticated individual investors and their

financial advisors. Book topics range from portfolio management to e-commerce,

risk management, financial engineering, valuation and financial instrument anal￾ysis, as well as much more.

For a list of available titles, visit our Web site at www.WileyFinance.com.

Using Excel

for Business

and Financial

Modelling

A practical guide

Third Edition

DANIELLE STEIN FAIRHURST

This third edition first published 2019

© 2019 John Wiley & Sons Ltd

Edition History

John Wiley & Sons Ltd (1e, 2012 and 2e, 2015)

Registered office

John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ,

United Kingdom

For details of our global editorial offices, for customer services and for information about how

to apply for permission to reuse the copyright material in this book please see our website at

www.wiley.com.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or

transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or

otherwise, except as permitted by the UK Copyright, Designs and Patents Act 1988, without the prior

permission of the publisher.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material

included with standard print versions of this book may not be included in e-books or in print-on￾demand. If this book refers to media such as a CD or DVD that is not included in the version you

purchased, you may download this material at http://booksupport.wiley.com. For more information

about Wiley products, visit www.wiley.com.

Designations used by companies to distinguish their products are often claimed as trademarks.

All brand names and product names used in this book are trade names, service marks, trademarks or

registered trademarks of their respective owners. The publisher is not associated with any product or

vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts

in preparing this book, they make no representations or warranties with respect to the accuracy or

completeness of the contents of this book and specifically disclaim any implied warranties of mer￾chantability or fitness for a particular purpose. It is sold on the understanding that the publisher is not

engaged in rendering professional services and neither the publisher nor the author shall be liable for

damages arising herefrom. If professional advice or other expert assistance is required, the services of

a competent professional should be sought.

Library of Congress Cataloging-in-Publication Data is Available

ISBN 978-1-119-52038-2 (paperback) ISBN 978-1-119-52037-5 (ePDF)

ISBN 978-1-119-52035-1 (epub) ISBN 978-1-119-52034-4 (Obook)

Cover Design: Wiley

Cover Image: © Mike Fairhurst

Set in 10/12pt TimesLTStd by SPi Global, Chennai, India

Printed in Great Britain by TJ International Ltd, Padstow, Cornwall, UK

10 9 8 7 6 5 4 3 2 1

For Mike

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

vii

viii CONTENTS

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

Contents ix

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

x CONTENTS

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

Preface

This book was written from course materials compiled over many years of train￾ing in analytical courses in Australia and globally—most frequently courses

such as Financial Modelling in Excel, Data Analysis & Reporting in Excel, and

Budgeting & Forecasting in Excel, both as face-to-face workshops and online

courses. The common theme is the use of Microsoft Excel, and I’ve refined the

content to suit the hundreds of participants and their questions over the years. This

content has been honed and refined by the many participants in these courses, who

are my intended readers. This book is aimed at you, the many people who seek

financial analysis training (either by attending a seminar or self-paced by reading

this book), because you are seeking to improve your skills to perform better in your

current role, or to get a new and better job. When I started financial modelling in

the early 1990s, it was not called financial modelling—it was just “using Excel

for business analysis”, which is what I called the first two editions of this book.

The title was edited in the third edition to “Using Excel for Business and Financial

Modelling”. It was only just after the new millennium that the term financial mod￾elling gained popularity in its own right and became a required skill often listed

on analytical job descriptions. This book spends quite a bit of time in Chapter 1

defining the meaning of a financial model, as it’s often thought to be something

that is far more complicated than it actually is. Many analysts and financial pro￾fessionals I’ve met are building financial models already without realising it, but

they do themselves a disservice by not calling their models, “models”!

However, those who are already building financial models are not necessarily

following good modelling practice as they do so. Chapter 3 is dedicated to the

principles of best modelling practice, which will save you a lot of time, effort, and

anguish in the long run. Many of the principles of best practice are for the purpose

of reducing the possibility of error in your model, and there is a whole section on

strategies for reducing error in Chapter 4.

The majority of Excel users are self-taught, and therefore many users will

often know highly advanced Excel tools, yet fail to understand how to use them

in the context of building a financial model. This book is very detailed, so feel

free to skip sections you already know. Because of the comprehensive nature of

the book, much of the detailed but less commonly used content, such as instruc￾tions for the older versions of Excel, has been moved to the companion website

at www.wiley.com/go/steinfairhurstrevised. There you will also find templates,

checklists, and other useful materials. References to the content on the website,

xi

xii PREFACE

and many cross-references to other sections of the book, can be found throughout

the book.

I’m passionate about supporting the financial modelling community, and

especially about encouraging more women into what I believe is a highly reward￾ing and in-demand profession. There is a whole world of financial modelling

awards, championships, standards, certifications, and meetup groups all over the

world you can tap into to learn, network, and progress your career. If this book

has piqued your interest, then I invite you to connect with me directly on LinkedIn

and other social media platforms to find out more about how to get more involved

in the wonderful world of financial modelling.

BOOK OVERVIEW

This book has 12 chapters, which can be grouped into three sections. Whilst they

do follow on from each other with the most basic concepts at the beginning, feel

free to jump directly to any of the chapters. The first section—Chapters 1 to

3—addresses the least technical topics about financial modelling in general, such

as tool selection, model design, and best practice.

The second section—Chapters 4 to 8—is extremely practical and hands on.

Here I have outlined all of the tools, techniques, and functions in Excel that are

commonly used in financial modelling. Of course, it does not cover everything

Excel can do, but it covers the “must know” tools.

The third section—Chapters 9 to 12—is the most important in my view.

This covers the use of Excel in financial modelling and analysis. This is really

where the book differs from other “how to” Excel books. Chapter 9 covers some

commonly used techniques in modelling, such as escalation, tiering tables, and

depreciation—how to actually use Excel tools for something useful! Chapter

11 covers the several different methods of performing scenarios and sensitivity

analysis (basically the whole point of financial modelling to my mind). Lastly,

Chapter 12 covers the often-neglected task of presenting model output. Many

modellers spend days or weeks on the calculations and functionality, but fail

to spend just a few minutes or hours on charts, formatting, and layout at the

end of the process, even though this is what the user will see, interact with, and

eventually use to judge the usefulness of the model.

ACKNOWLEDGEMENTS

This book would not have been written had it not been for the many people who

have attended my training sessions, participated in online courses, and contributed

to the forums. Your continual feedback and enthusiasm for the subject inspired me

Preface xiii

to write this book and it was because of you that I realised how much a book like

this was needed.

The continued support of my family and network made this project possi￾ble. In particular, Mike, my husband, for his unconditional commitment and to

whom this book is dedicated; my children who give me so much joy; as well as

my remarkable parents and siblings, who have always inspired and encouraged me

without question. I would like to give special thanks to my ever-patient assistant

Susan Wilkin for her continuing dedication and diligence, as always. I could not

do it without you all.

I hope you find the book both useful and enjoyable. Happy modelling!

Using Excel

for Business

and Financial

Modelling

CHAPTER 1

What is Financial Modelling?

There are all sorts of complicated definitions of financial modelling, and in my

experience there is quite a bit of confusion around what a financial model is

exactly. A few years ago, we put together a Plum Solutions survey about the atti￾tudes, trends, and uses of financial modelling, asking respondents “What do you

think a financial model is?” Participants were asked to put down the first thing that

came to mind, without any research or too much thinking about it.

I found the responses interesting, amusing, and sometimes rather disturbing.

Some answers were overly complicated and highly technical:

■ “Representation of behaviour/real-world observations through mathematical

approach designed to anticipate range of outcomes.”

■ “A set of structured calculations, written in a spreadsheet, used to analyse the

operational and financial characteristics of a business and/or its activities.”

■ “Tool(s) used to set and manage a suite of variable assumptions in order to

predict the financial outcomes of an opportunity.”

■ “A construct that encodes business rules, assumptions, and calculations

enabling information, analysis, and insight to be drawn out and supported by

quantitative facts.”

■ “A system of spreadsheets and formulas to achieve the level of record keeping

and reporting required to be informed, up-to-date, and able to track finances

accurately and plan for the future.”

Some philosophical:

■ “A numerical story.”

Some incorrect:

■ “Forecasting wealth by putting money away now/investing.”

■ “It is all about putting data into a nice format.”

1

Using Excel for Business and Financial Modelling: A practical guide, Third Edition.

Danielle Stein Fairhurst.

© 2019 John Wiley & Sons Ltd. Published 2019 by John Wiley & Sons Ltd.

2 USING EXCEL FOR BUSINESS AND FINANCIAL MODELLING

■ “It is just a mega huge spreadsheet with fancy formulas that are streamlined

to make your life easier.”

Some ridiculous:

■ “Something to do with money and fashion?”

Some honest:

■ “I really have no idea.”

And some downright profound:

■ “A complex spreadsheet.”

There are many (often very complicated and long-winded) definitions avail￾able from different sources, but I actually prefer the last, very broad, but accurate

description: “a complex spreadsheet”. Whilst it does need some definition, my

definition of a financial model is pretty broad.

As long as a spreadsheet has financial inputs and outputs, and is dynamic

and flexible, I’m happy to call it a financial model! Pretty much the whole point

of financial modelling is that you change the inputs and the outputs. This is the

major premise behind scenario and sensitivity analysis; this is what Excel, with

its algebraic logic, was made for. Most of the time, a model will contain financial

information and serve the purpose of making a financial decision, but not always.

Quite often it will contain a full set of financial statements: profit and loss, cash

flow, and balance sheet; but not always.

According to the more staid or traditional definitions of financial modelling,

the following items would all most certainly be classified as financial models:

■ A business case that determines whether to go ahead with a project.

■ A five-year forecast showing profit and loss, cash flow, and balance sheet.

■ Pricing calculations to determine how much to bid for a new tender.

■ Investment analysis for a joint venture.

But what about other pieces of analysis that we perform as part of our roles?

Can these also be called financial models? What if something does not contain

financial information at all? Consider if you were to produce a spreadsheet for the

following purposes:

■ An actual versus budget monthly variance analysis that does not contain

scenarios and for which there are no real assumptions listed.

■ A risk assessment, where you enter the risk, assign a likelihood to that risk,

and calculate the overall risk of the project using probability calculations. This

does not contain any financial outputs at all.