Danielle Stein Fairhurstis a Sydney-based financial modeling consultant who helps her clients create meaningful financial models for business analysis. She is regularly engaged around Australia and globally as a speaker and course facilitator. She received the Microsoft MVP Award in 2021 in recognition of her technical expertise and contributions to the community.

Introduction 1

About This Book 1

Foolish Assumptions 2

Icons Used in This Book 2

Beyond the Book 3

Where to Go from Here 3

Part 1: Getting Started with Financial Modeling 5

Chapter 1: Introducing Financial Modeling 7

Defining Financial Modeling 7

What it is 8

Who uses it 9

Why it matters 10                                                              

Looking at Examples of Financial Models 10

Project finance models 11

Pricing models 12

Integrated financial statement models 12

Valuation models 12

Reporting models 13

Chapter 2: Getting Acquainted with Excel 15

Making Sense of the Different Versions of Excel 15

A rundown of recent Excel versions 16

Focusing on file formats 23

Defining Modern Excel 23

Recognizing the Dangers of Using Excel 25

Capacity 26

Lack of discipline 27

Errors 28

Looking at Alternatives and Supplements to Excel 31

Chapter 3: Planning and Designing Your Financial Model 35

Identifying the Problem That Your Financial Model Needs to Solve 35

Designing How the Problem’s Answer Will Look 39

Gathering Data to Put in Your Model 45

Documenting the Limitations of Your Model 46

Considering the Layout and Design of Your Model 47

Structuring your model: What goes where 49

Defining inputs, calculations, and output blocks 50

Determining your audience 51

Chapter 4: Building a Financial Model by the Rulebook 53

Document Your Assumptions 53

Create Dynamic Formulas Using Links 59

Only Enter Data Once 61

Model with Consistent Formulas 62

Build in Error Checks 64

Allowing tolerance for error 66

Applying conditional formatting to an error check 67

Format and Label for Clarity 68

Chapter 5: Using Someone Else’s Financial Model 71

Considering Templates for Building a Financial Model 72

Why templates can be appealing 72

What’s wrong with using templates 72

Why you should build your own model 74

Inheriting a File: What to Check For 75

Meeting a model for the first time 76

Inspecting the workbook 77

Using Audit Tools to Find and Correct Errors 84

Checking a model for accuracy 85

Making sense of the formulas 88

Sharing and Version Control 95

Part 2: Diving Deep into Excel 97

Chapter 6: Excel Tools and Techniques for Financial Modeling 99

Referencing Cells 100

Relative cell referencing 101

Absolute cell referencing 103

Mixed cell referencing 106

Naming Ranges 109

Understanding why you may want to use a named range 109

Creating a named range 110

Finding and using named ranges 111

Editing or deleting a named range 113

Dynamic Ranges 113

Linking in Excel 114

Internal links 115

External links 117

Using Shortcuts 120

Restricting and Validating Data 123

Restricting user data entry 124

Creating drop-down boxes with data validations 125

Protecting and locking cells 126

Goal Seeking 127

Limiting project costs with a goal seek 128

Calculating a break-even point with a goal seek 129

Chapter 7: Using Functions in Excel 131

Identifying the Difference between a Formula and a Function 131

Finding the Function You Need 132

Getting Familiar with the Most Important Functions 133

SUM 134

MAX and MIN 135

AVERAGE 139

COUNT and COUNTA 140

ROUND, ROUNDUP, and ROUNDDOWN 146

IF 153

COUNTIF and SUMIF 156

Reporting sales with SUMIF 158

VLOOKUP, HLOOKUP, and XLOOKUP 161

Being Aware of Advanced Functions and Functionality 170

Chapter 8: Applying Scenarios to Your Financial Model 173

Identifying the Differences among Types of Analysis 174

Building Drop-Down Scenarios 175

Using data validations to model profitability scenarios 175

Applying formulas to scenarios 178

Applying Sensitivity Analysis with Data Tables 181

Setting up the calculation 181

Building a data table with one input 182

Building a data table with two inputs 184

Applying probability weightings to your data table 186

Using Scenario Manager to Model Loan Calculations 189

Setting up the model 189

Applying Scenario Manager 191

Chapter 9: Charting and Presenting Model Output 195

Deciding Which Data to Display 196

Conveying Your Message by Charting Scenarios 198

Deciding Which Type of Chart to Use 200

Line charts 201

Bar charts 206

Combo charts 209

Pie charts 211

Charts in newer versions of Excel 214

Dynamic Charting 219

Building the chart on formula-driven data 219

Linking the chart titles to formulas 220

Creating dynamic text 221

Preparing a Presentation 225

Part 3: Building Your Financial Model 227

Chapter 10: Building an Integrated Financial Statements Model 229

Getting to Know the Case Study 230

Entering Assumptions 231

Revenue assumptions 232

Expense assumptions 233

Other assumptions 234

Calculating Revenue 234

Projecting sales volume 235

Projecting dollar sales 237

Calculating Expenses 238

Staff costs 238

Other costs 239

Depreciation and amortization 240

Building the Income Statement 243

Building the Cash Flow Statement 248

Building the Balance Sheet 252

Building Scenarios 258

Entering your scenario assumptions 258

Building a drop-down box 259

Building the scenario functionality 260

Chapter 11: Building a Discounted Cash Flow Valuation 263

Understanding How the Discounted Cash Flow Valuation Works 264

Step 1: Calculating Free Cash Flow to Firm 265

Step 2: Calculating Weighted Average Cost of Capital 268

Step 3: Finding the Terminal Value 269

Discounting Cash Flows and Valuation 270

Chapter 12: Budgeting for Capital Expenditure and Depreciation 273

Getting Started 274

Making a reusable budget model template 274

Creating dynamic titles 277

Output 1: Calculating Cash Required for Budgeted Asset Purchases 277

Output 2: Calculating Budgeted Depreciation 282

Useful life 283

Written-down date 284

The depreciation schedule for the current year 285

Depreciation in prior periods 287

Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet 288

Part 4: The Part of Tens 291

Chapter 13: Ten Strategies for Reducing Error 293

Using the Enter Key 293

Checking Your Work 294

Checking It Again 295

Getting Someone Else to Check Your Work 296

Documenting Assumptions 297

Documenting Methodology with a Flowchart 297

Stress-Testing with Sensitivity Analysis 298

Conducting a Scenario Analysis 299

Taking Note of Excel Error Values 300

Including Error Checks 302

Chapter 14: Ten Common Pitfalls to Avoid 303

The Numbers Don’t Add Up 303

You’re Getting #REF! Errors 304

You Have Circular References 304

The Model Has Too Much Detail 307

The File Size Is Out of Control 307

Your Model Is Full of “Spaghetti” Links 309

The Formulas Are Unnecessarily Long and Complicated 311

No One Is Paying Attention to the Model 312

You Don’t Want to Let Go 313

Someone Messes Up Your Model 313

Index 315

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.