Using Excel for business analysis

Using Excel

for Business

Analysis

The Wiley Finance series contains books written specifi cally for fi nance and

investment professionals as well as sophisticated individual investors and

their fi nancial advisors. Book topics range from portfolio management to

e-commerce, risk management, fi nancial engineering, valuation and fi nan￾cial instrument analysis, as well as much more. For a list of available titles,

visit our Web site at www.WileyFinance.com.

Founded in 1807, John Wiley & Sons is the oldest independent publish￾ing company in the United States. With offi ces in North America, Europe,

Australia, and Asia, Wiley is globally committed to developing and market￾ing print and electronic products and services for our customers’ profes￾sional and personal knowledge and understanding.

Using Excel

for Business

Analysis

A Guide to Financial

Modelling Fundamentals

Revised Edition

DANIELLE STEIN FAIRHURST

Cover image: ©Leontura/iStockphoto.com and ©solarseven/iStockphoto.com

Cover design: Wiley

Copyright © 2015 by John Wiley & Sons Singapore Pte. Ltd.

Published by John Wiley & Sons Singapore Pte. Ltd.

1 Fusionopolis Walk, #07-01, Solaris South Tower, Singapore 138628

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, scanning, or

otherwise, except as expressly permitted by law, without either the prior written permission

of the Publisher, or authorization through payment of the appropriate photocopy fee to the

Copyright Clearance Center. Requests for permission should be addressed to the Publisher,

John Wiley & Sons Singapore Pte. Ltd., 1 Fusionopolis Walk, #07-01, Solaris South Tower,

Singapore 138628, tel: 65-6643-8000, fax: 65-6643-8008, e-mail: [email protected].

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 specifi cally disclaim any implied

warranties of merchantability or fi tness for a particular purpose. No warranty may be created or

extended by sales representatives or written sales materials. The advice and strategies contained

herein may not be suitable for your situation. You should consult with a professional where

appropriate. Neither the publisher nor the author shall be liable for any damages arising herefrom.

Other Wiley Editorial Offi ces

John Wiley & Sons, 111 River Street, Hoboken, NJ 07030, USA

John Wiley & Sons, The Atrium, Southern Gate, Chichester, West Sussex, P019 8SQ, United

Kingdom

John Wiley& Sons (Canada) Ltd., 5353 Dundas Street West, Suite 400, Toronto, Ontario,

M9B 6HB, Canada

John Wiley& Sons Australia Ltd., 42 McDougall Street, Milton, Queensland 4064, Australia

Wiley-VCH, Boschstrasse 12, D-69469 Weinheim, Germany

ISBN 978-1-119-06246-2 (Paperback)

ISBN 978-1-119-06245-5 (ePDF)

ISBN 978-1-119-06244-8 (ePub)

ISBN 978-1-119-07001-6 (o-Book)

Typeset in 10/12 pt, Sabon LT Std Roman by Aptara Inc, New Delhi, India

Printed in Singapore by C.O.S. Printers Pte Ltd.

10 9 8 7 6 5 4 3 2 1

v

Contents

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

vi CONTENTS

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

Contents vii

CHAPTER 7

Tools for Model Display 187

Basic Formatting 187

Custom Formatting 187

Conditional Formatting 193

Sparklines 200

Bulletproofi ng 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

viii CONTENTS

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

ix

T

his book was written from course materials compiled over many years of

training 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

refi ned the content to suit the hundreds of participants and their questions over

the years. This content has been honed and refi ned by the many participants

in these courses, who are my intended readers. This book is aimed at you, the

many people who seek fi nancial 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 fi nancial modelling in the early nineties, it was not called

fi nancial modelling—it was just “using Excel for business analysis,” and this is

what I’ve called this book. It was only just after the new millennium that the

term fi nancial modelling gained popularity in its own right and became a re￾quired skill often listed on analytical job descriptions. This book spends quite a

bit of time in Chapter 1 defi ning the meaning of a fi nancial model, as it’s often

thought to be something that is far more complicated than it actually is. Many

analysts I’ve met are building fi nancial models already without realising it, but

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

However, those who are already building fi nancial models are not nec￾essarily following good modelling practice as they do so. Chapter 3is dedi￾cated 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 mod￾el, 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 fi nancial 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 instructions for the older Excel 2003 users, has been moved to the

companion website at www.wiley.com/go/steinfairhurstrevised . References

Preface

x PREFACE

to the content on the website, and many cross‐references to other sections of

the book, can be found throughout the book.

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 be￾ginning, feel free to jump directly to any of the chapters. The fi rst section—

Chapters 1to 3—addresses the least technical topics about fi nancial model￾ling in general, such as tool selection, model design, and best practice.

The second section—Chapters 4to 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 fi nancial modelling. Of course it does not cover

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

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

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

where the book differs from other “how‐two” Excel books. Chapter 9cov￾ers some commonly used techniques in modelling, such as escalation, tiering

tables, and depreciation—how to actually use Excel tools for something use￾ful! Chapter 11covers the several different methods of performing scenarios

and sensitivity analysis (basically the whole point of fi nancial modelling to my

mind). Last, Chapter 12covers the often‐neglected task of presenting model

output. Many modellers spend days or weeks on the calculations and func￾tionality, 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.

ACKNOWLEDGMENTS

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 to write this book and it was because of you that I real￾ized how much a book like this was needed.

The continued support of my family and network made this project

possible. In particular, Mike, my husband, for his unconditional commit￾ment 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 a special

thanks to my ever‐patient assistant Susan Wilkin for her continuing dedica￾tion and diligence, as always. I could not do it without you all.

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

1

Chapter 1

What Is Financial Modelling?

T

here 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

attitudes, 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 mathe￾matical 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 calcula￾tions 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.”

■ “It is just a mega‐huge spreadsheet with fancy formulas that are stream￾lined to make your life easier.”

2 Using Excel for Business Analysis

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

available from different sources, but I actually prefer the last, very broad,

but accurate description: “a complex spreadsheet.” Whilst it does need some

definition, a financial model can pretty much be whatever you need it to be.

As long as a spreadsheet has 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 or not 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.

What Is Financial Modelling? 3

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

risk, and calculate the overall risk of the project using probability calcu￾lations. This does not contain any financial outputs at all.

■ A dashboard report showing a balance scorecard type of metrics report￾ing like headcount, quality, customer numbers, call volume, and so on.

Again, there are few or no financial outputs.

See the section, “Types and Purposes of Financial Models,” later in this

chapter for greater detail on financial models that don’t actually contain

financial information.

Don’t get hung up on whether you’re actually building something that

meets the definition of a financial model or not. As long as you’ve got inputs

and outputs that change flexibly and dynamically, you can call it a financial

model. If you’re using Excel to any extent whereby you are linking cells

together, chances are you’re already building a financial model—whether

you realise it or not. The most important thing is that you are building the

model (or whatever it’s called!) in a robust way, following the principles of

best practice, which this book will teach you.

Generally, a model consists of one or more input variables along

with data and formulas that are used to perform calculations, make pre￾dictions, or perform any number of solutions to business (or nonbusi￾ness) requirements. By changing the values of the input variables, you

can do sensitivity testing and build scenarios to see what happens when

the inputs change.

Sometimes managers treat models as though they are able to produce

the answer to all business decisions and solve all business problems. Whilst a

good model can aid significantly, it’s important to remember that models are

only as good as the data they contain, and the answers they produce should

not necessarily be taken at face value.

“The reliability of a spreadsheet is essentially the accuracy of the

data that it produces, and is compromised by the errors found in approx￾imately 94 percent of spreadsheets.”1 When presented with a model, the

savvy manager will query all the assumptions, and the way it has been

built. Someone who has had some experience in building models will

realise that they must be treated with caution. Models should be used

as one tool in the decision‐making process, rather than the definitive

solution.

1 Ruth McKeever, Kevin McDaid, and Brian Bishop, “An Exploratory Analysis of

the Impact of Named Ranges on the Debugging Performance of Novice Users”

(presented at the annual conference of the European Spreadsheet Risks Interest

Group, Paris, France, July 2–3, 2009). Available at arxiv.org/abs/0908.0935.