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

443 Pages·2015·131.4 MB·English

by  Danielle Stein

Using Excel
for Business
AAnnaallyyssiiss
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
AAnnaallyyssiiss
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: [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
CCoonntteennttss
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
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
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