Financial Modeling in Excel for Dummies

GETTING STARTED WITH FINANCIAL MODELING.

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.

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 .

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.

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 .

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.

 

DIVING DEEP INTO EXCEL.

Excel Tools and Techniques for Financial Modeling.

Referencing Cells. 91 Relative cell referencing. Absolute cell referencing. Mixed cell referencing. Naming Ranges. 100 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. 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.

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. VLOOKUP and HLOOKUP. Being Aware of Advanced Functions and Functionality.

Applying Scenarios to Your Financial Model.

dentifying the Differences between 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.

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. 205 Creating dynamic text.  Preparing a Presentation.

 

BUILDING YOUR FINANCIAL MODEL.

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.

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. 253 Discounting Cash Flows and Valuation .

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.

 

THE PART OF TENS.

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.

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. 296 Someone Messes Up Your Model.