Wiley
Wiley.com
Print this page Share

Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, + Website

ISBN: 978-1-118-13284-5
320 pages
September 2012
Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals, + Website (111813284X) cover image
A clear, concise, and easy-to-use guide to financial modelling suitable for practitioners at every level

Using a fundamental approach to financial modelling that's accessible to both new and experienced professionals, Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals + Website offers practical guidance for anyone looking to build financial models for business proposals, to evaluate opportunities, or to craft financial reports. Comprehensive in nature, the book covers the principles and best practices of financial modelling, including the Excel tools, formulas, and functions to master, and the techniques and strategies necessary to eliminate errors.

As well as explaining the essentials of financial modelling, Using Excel for Business Analysis is packed with exercises and case studies to help you practice and test your comprehension, and includes additional resources online.

  • Provides comprehensive coverage of the principles and best practices of financial modeling, including planning, how to structure a model, layout, the anatomy of a good model, rebuilding an inherited model, and much more
  • Demonstrates the technical Excel tools and techniques needed to build a good model successfully
  • Outlines the skills you need to learn in order to be a good financial modeller, such as technical, design, and business and industry knowledge
  • Illustrates successful best practice modeling techniques such as linking, formula consistency, formatting, and labeling
  • Describes strategies for reducing errors and how to build error checks and other methods to ensure accurate and robust models

A practical guide for professionals, including those who do not come from a financial background, Using Excel for Business Analysis is a fundamentals-rich approach to financial modeling.

See More

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 4

Tool Selection 5

What Skills Do You Need to Be a Good Financial Modeller? 10

The Ideal Financial Modeller 16

Summary 19

CHAPTER 2 Building a Model 21

Model Design 21

The Golden Rules for Model Design 22

Design Issues 24

The Workbook Anatomy of a Model 25

Project Planning Your Model 27

Model Layout Flow Charting 28

Steps to Building a Model 28

Information Requests 35

Version-Control Documentation 36

Summary 37

CHAPTER 3 Best Practice Principles of Modelling 39

Document Your Assumptions 39

Linking, Not Hard Coding 39

Only Enter Data Once 40

Avoid Bad Habits 41

Use Consistent Formulas 41

Format and Label Clearly 41

Methods and Tools of Assumptions Documentation 42

Linked Dynamic Text Assumptions Documentation 48

What Makes a Good Model? 51

Summary 52

CHAPTER 4 Financial Modelling Techniques 53

The Problem with Excel 53

Error Avoidance Strategies 54

How Long Should a Formula Be? 59

Linking to External Files 61

Building Error Checks 63

Avoid Error Displays in Formulas 66

Circular References 67

Summary 70

CHAPTER 5 Using Excel in Financial Modelling 71

Formulas and Functions in Excel 71

Excel Versions 73

Handy Excel Shortcuts 74

Basic Excel Functions 76

Logical Functions 82

Nesting: Combining Simple Functions to Create Complex Formulas 84

Cell Referencing Best Practices 86

Named Ranges 89

Summary 92

CHAPTER 6 Functions for Financial Modelling 93

Aggregation Functions 93

LOOKUP Formulas 100

Other Useful Functions 106

Working with Dates 115

Financial Project Evaluation Functions 121

Loan Calculations 126

Summary 131

CHAPTER 7 Tools for Model Display 133

Basic Formatting 133

Custom Formatting 133

Conditional Formatting 139

Sparklines 143

Bulletproofing Your Model 147

Customising the Display Settings 151

Form Controls 157

Summary 171

CHAPTER 8 Tools for Financial Modelling 173

Hiding Sections of a Model 173

Grouping 178

Array Formulas 179

Goal Seeking 184

Pivot Tables 186

Macros 190

User-Defined Functions (UDFs) 198

Summary 200

CHAPTER 9 Common Uses of Tools in Financial Modelling 201

Escalation Methods for Modelling 201

Understanding Nominal and Effective (Real) Rates 206

Calculating Cumulative Totals 209

How to Calculate a Payback Period 210

Weighted Average Cost of Capital (WACC) 214

Building a Tiering Table 216

Modelling Depreciation Methods 219

Break-Even Analysis 227

Summary 233

CHAPTER 10 Model Review 235

Rebuilding an Inherited Model 235

Auditing a Financial Model 245

Appendix 10.1: QA Log 250

Summary 250

CHAPTER 11 Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 251

What’s the Difference between Scenario, Sensitivity, and What-If Analysis? 252

Overview of Scenario Analysis Tools and Methods 253

Advanced Conditional Formatting 261

Comparing Scenario Methods 264

Summary 273

CHAPTER 12 Presenting Model Output 275

Preparing an Oral Presentation for Model Results 275

Preparing a Graphic or Written Presentation for Model Results 276

Chart Types 279

Working with Charts 291

Handy Charting Hints 295

Dynamic Range Names 297

Charting with Two Different Axes and Chart Types 300

Bubble Charts 302

Waterfall Charts 307

Summary 316

About the Author 317

About the Website 319

Index 321

See More

DANIELLE STEIN FAIRHURST is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. With many years' experience as a financial analyst, she helps her clients create meaningful financial models. She has hands-on experience in a number of industry sectors, including telecommunications, information systems, manufacturing, and financial services. Stein Fairhurst is regularly engaged as a speaker, course facilitator, financial modelling consultant, and analyst around the world, and has taught management accounting subjects at Sydney University.

See More
July 23, 2012
New Book for Professionals Using Excel® Helps to Save Time and Effort in Creating Financial Models for Business Analysis

The majority of Excel® users is self-taught. They often know some of Excel®’s advance features such as macros, lookup functions, array formulas, pivot tables, and data management, yet fail to understand how to use them in the context of performing business analysis. Some do not even realise they are already building their own financial models for managing risk, planning projects, preparing business proposals, or evaluating opportunities. In a new book by Danielle Stein Fairhurst, ‘Using Excel® for Business Analysis: A Guide to Financial Modelling Fundamentals’ is dedicated to the principles of best modelling practice to help them save a lot of time, effort and anguish in the long run.

‘Using Excel® for Business Analysis: A Guide to Financial Modelling Fundamentals’  is designed for professionals of all backgrounds and levels of experience looking to become familiar with financial modelling, whether they are seeking to improve their skills to perform better in their current role, or getting a new and better job.

“When I started financial modelling in the early nineties, it was not called ‘financial modelling’—it was just “Using Excel for Business Analysis”, and this is what I’ve called this book,” says Stein Fairhurst, a Sydney-based consultant specialising in financial modelling and analysis. “It was only after the new millennium that the term ‘financial modelling’ gained popularity and became a required skill often listed on analytical job descriptions,” she said.

‘Using Excel® for Business Analysis: A Guide to Financial Modelling Fundamentals’ presents the key strategies for minimising errors, rebuilding an inherited model and offers other techniques for ensuring accurate robust models. It also covers the often-neglected task of presenting model output—the area where many modellers spend days or weeks on the calculations and functionality—to judge the usefulness of the model.

‘Using Excel® for Business Analysis: A Guide to Financial Modelling Fundamentals’ is a concise, step-by-step guide that presents how a spreadsheet can fulfill a variety of purposes, for example, setting out a business case, evaluating potential opportunities, or building reports and models using Excel. It covers both the essential concepts behind financial modelling and the practicalities of performing complex analysis.

The book is bundled with a companion site (www.wiley.com/go/steinfairhurst) containing additional materials to supplement the information in the book. More updated materials in the companion site will be available at Plum Solutions website at: www.plumsolutions.com.au/using-excel-business-analysis.

It is now available in soft cover and e-book formats. For details to purchase online at Amazon China and Japan, Kinokuniya, MPHOnline.com or Wiley.com, visit www.wiley.com/buy/9781118132845.

Stein Fairhurst also conducts training seminars and online Financial Modelling and analysis courses in Excel® that correspond to the book. For more information, visit: www.plumsolutions.com.au.

‘Using Excel® for Business Analysis’ free sample chapter is available for download at:
http://bit.ly/MBUah0 

See More
Back to Top