Financial Modeling in Excel For Dummies

Description

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 strtegies 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! Title Page Copyright Page Table of Contents Introduction About This Book Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here Part 1 Getting Started with Financial Modeling Chapter 1 Introducing Financial Modeling Defining Financial Modeling What it is Who uses it Why it matters Looking at Examples of Financial Models Project finance models Pricing models Integrated financial statement models Valuation models Reporting models Chapter 2 Getting Acquainted with Excel Making Sense of the Different Versions of Excel A rundown of recent Excel versions Focusing on file formats Defining Modern Excel Recognizing the Dangers of Using Excel Capacity Lack of discipline Errors Looking at Alternatives and Supplements to Excel Chapter 3 Planning and Designing Your Financial Model Identifying the Problem That Your Financial Model Needs to Solve Designing How the Problem’s Answer Will Look Gathering Data to Put in Your Model Documenting the Limitations of Your Model Considering the Layout and Design of Your Model Structuring your model: What goes where Defining inputs, calculations, and output blocks Determining your audience Chapter 4 Building a Financial Model by the Rulebook Document Your Assumptions Create Dynamic Formulas Using Links Only Enter Data Once Model with Consistent Formulas Build in Error Checks Allowing tolerance for error Applying conditional formatting to an error check Format and Label for Clarity Chapter 5 Using Someone Else’s Financial Model Considering Templates for Building a Financial Model Why templates can be appealing What’s wrong with using templates Why you should build your own model Inheriting a File: What to Check For Meeting a model for the first time Inspecting the workbook Using Audit Tools to Find and Correct Errors Checking a model for accuracy Making sense of the formulas Sharing and Version Control Part 2 Diving Deep into Excel Chapter 6 Excel Tools and Techniques for Financial Modeling Referencing Cells Relative cell referencing Absolute cell referencing Mixed cell referencing Naming Ranges Understanding why you may want to use a named range Creating a named range Finding and using named ranges Editing or deleting a named range Dynamic Ranges Linking in Excel Internal links External links Using Shortcuts Restricting and Validating Data Restricting user data entry Creating drop-down boxes with data validations Protecting and locking cells Goal Seeking Limiting project costs with a goal seek Calculating a break-even point with a goal seek Chapter 7 Using Functions in Excel Identifying the Difference between a Formula and a Function Finding the Function You Need Getting Familiar with the Most Important Functions SUM MAX and MIN AVERAGE COUNT and COUNTA ROUND, ROUNDUP, and ROUNDDOWN IF COUNTIF and SUMIF Reporting sales with SUMIF VLOOKUP, HLOOKUP, and XLOOKUP Being Aware of Advanced Functions and Functionality Chapter 8 Applying Scenarios to Your Financial Model Identifying the Differences among Types of Analysis Building Drop-Down Scenarios Using data validations to model profitability scenarios Applying formulas to scenarios Applying Sensitivity Analysis with Data Tables Setting up the calculation Building a data table with one input Building a data table with two inputs Applying probability weightings to your data table Using Scenario Manager to Model Loan Calculations Setting up the model Applying Scenario Manager Chapter 9 Charting and Presenting Model Output Deciding Which Data to Display Conveying Your Message by Charting Scenarios Deciding Which Type of Chart to Use Line charts Bar charts Combo charts Pie charts Charts in newer versions of Excel Dynamic Charting Building the chart on formula-driven data Linking the chart titles to formulas Creating dynamic text Preparing a Presentation Part 3 Building Your Financial Model Chapter 10 Building an Integrated Financial Statements Model Getting to Know the Case Study Entering Assumptions Revenue assumptions Expense assumptions Other assumptions Calculating Revenue Projecting sales volume Projecting dollar sales Calculating Expenses Staff costs Other costs Depreciation and amortization Building the Income Statement Building the Cash Flow Statement Building the Balance Sheet Building Scenarios Entering your scenario assumptions Building a drop-down box Building the scenario functionality Chapter 11 Building a Discounted Cash Flow Valuation Understanding How the Discounted Cash Flow Valuation Works Step 1: Calculating Free Cash Flow to Firm Step 2: Calculating Weighted Average Cost of Capital Step 3: Finding the Terminal Value Discounting Cash Flows and Valuation Chapter 12 Budgeting for Capital Expenditure and Depreciation Getting Started Making a reusable budget model template Creating dynamic titles Output 1: Calculating Cash Required for Budgeted Asset Purchases Output 2: Calculating Budgeted Depreciation Useful life Written-down date The depreciation schedule for the current year Depreciation in prior periods Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet Part 4 The Part of Tens Chapter 13 Ten Strategies for Reducing Error Using the Enter Key Checking Your Work Checking It Again Getting Someone Else to Check Your Work Documenting Assumptions Documenting Methodology with a Flowchart Stress-Testing with Sensitivity Analysis Conducting a Scenario Analysis Taking Note of Excel Error Values Including Error Checks Chapter 14 Ten Common Pitfalls to Avoid The Numbers Don’t Add Up You’re Getting #REF! Errors You Have Circular References The Model Has Too Much Detail The File Size Is Out of Control Your Model Is Full of “Spaghetti” Links The Formulas Are Unnecessarily Long and Complicated No One Is Paying Attention to the Model You Don’t Want to Let Go Someone Messes Up Your Model Index EULA