Using Excel for business analysis : a guide to financial modelling fundamentals

443 Pages·2015·131.4 MB·English

by  Fairhurst, Danielle Stein

Using Excel
for Business
Analysis
The Wiley Finance series contains books written specif cally for f nance and
investment professionals as well as sop histicated individual investors and
their f nancial advisors. Book topics range from portfolio management to
e-commerce, risk management, f nancial engineering, valuation and f 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 off 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: [phone], fax: [phone], e-mail:
Contents
Preface ix
CHAPTER 1
What Is Financial Modelling? 1
What’s the Difference between a
Spreadshet 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
Sumary 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
Sumary 53
CHAPTER 3
Best Practice Principles of Modeling 5
Document Your Asumptions 5
Linking, Not Hard Coding 56
Enter Data Only Once 57
Avoid Bad Habits 57
v
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
Sumary 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 Eror Checks 86
Sumary 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
Regresion 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 Formating 187
Custom Formatting 187
Conditional Formatting 193
Sparklines 20
Bulletproof 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
Aray Formulas 240
Goal Seking 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 35
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