Financial modeling in Excel for Dummies
- ISBN: 9781119844518
- Editorial: For Dummies
- Fecha de la edición: 2022
- Lugar de la edición: Hoboken (NJ). Estados Unidos de Norteamérica
- Edición número: 2nd ed.
- Colección: For dummies
- Encuadernación: Rústica
- Medidas: 24 cm
- Nº Pág.: 352
- Idiomas: Inglés
Turn your financial data into insightful decisions with this straightforward guide to financial modeling with Excel
Interested in learning how to build practical financial models and forecasts but concerned that you don't have the math skills or technical know-how? We've got you covered! Financial decision-making has never been easier than with Financial Modeling in Excel For Dummies. Whether you work at a mom-and-pop retail store or a multinational corporation, you can learn how to build budgets, project your profits into the future, model capital depreciation, value your assets, and more.
You'll learn by doing as this book walks you through practical, hands-on exercises to help you build powerful models using just a regular version of Excel, which you've probably already got on your PC. You'll also:
Master the tools and strategies that help you draw insights from numbers and data you've already got
Build a successful financial model from scratch, or work with and modify an existing one to your liking
Create new and unexpected business strategies with the ideas and conclusions you generate with scenario analysis
Don't go buying specialized software or hiring that expensive consultant when you don't need either one. If you've got this book and a working version of Microsoft Excel, you've got all the tools you need to build sophisticated and useful financial models in no time!
TABLE OF CONTENTS
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
