Using Excel for Business and Financial Modelling

A hands-on guide to using Excel in the business context First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away. Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques. Provides the perfect mix of practice and theory Helps you become a DIY Excel modelling specialist Includes updates for Excel 2019/365 and Excel for Mac May be used as an accompaniment to the author's online and face-to-face training courses Many people are often overwhelmed by the hundreds of tools in Excel, and this book gives clarity to the ones you need to know in order to perform your job more efficiently. This book also demystifies the technical, design, logic and financial skills you need for business and financial modelling.

Autorentext

DANIELLE STEIN FAIRHURST is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. Her extensive experience as a financial analyst enables her to help her clients create meaningful financial models. She is regularly engaged as a speaker, course facilitator, financial modelling consultant, and analyst.

Klappentext

Praise for Using Excel for Business and Financial Modelling

Third Edition

"Highly recommended. I wish I had this book when I was learning Excel! This handy guide covers everything you need to know about modeling business problems and analyzing data."
Purna Chandra Rao Duggirala (Chandoo), Microsoft Excel MVP; Founder, Chandoo.org

"Danielle's expertise and experience shines through in a very readable book, introducing the key concepts and considerations inherent in financial modelling. From novice to advanced modeller alike, readers are presented with many useful tips, tools, techniques, and pragmatic examples to refer to time and time again. Readily digestible, this book plugs a gaping academic hole that illustrates the risks and rewards of taking a spreadsheet by the throat and converting it into a robust, transparent, and flexible decision-making device. It will help you to 'Excel'."
Liam Bastick, Managing Director, SumProduct, and Excel MVP

"Danielle has done an incredible job of explaining all the component parts of financial modelling and demystifying what is often seen as a complex and difficult area. Her book contains useful insights of benefit to everyone performing business analysis in Excel - regardless of their level of experience. It's the book I wish had been available when I started my career."
Kenny Whitelaw-Jones, Partner, Gridlines

Zusammenfassung

A hands-on guide to using Excel in the business context

First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away.

Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques.

• Provides the perfect mix of practice and theory

• Helps you become a DIY Excel modelling specialist

• Includes updates for Excel 2019/365 and Excel for Mac

• May be used as an accompaniment to the author's online and face-to-face training courses

Many people are often overwhelmed by the hundreds of tools in Excel, and this book gives clarity to the ones you need to know in order to perform your job more efficiently. This book also demystifies the technical, design, logic and financial skills you need for business and financial modelling.

Inhalt

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

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

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 Method...