Financial & Valuation Modeling

Financial Statement Modeling

Review key Excel skills for financial modeling

• Basic Excel techniques and keyboard shortcuts
• The most accurate way to perform Excel’s basic functions without the mouse
• Customize Excel’s default settings for effective modeling

Learn to format and structure financial models to limit errors and simplify auditing

• Standard modeling structures and formulas
• Best practices in modeling
• Learn the correct way to check models for errors, accuracy and integrity
• Check and fix circular reference and iteration problems (#REF)
• Tools for formula checking and auditing

Learn how to present financial statement model

• Create a ratios page
• Prepare a model for distribution
• Learn summary and sensitivity analysis using data tables

Financial statement model development

• Learn to build projection drivers
• Learn to integrate assumptions into financial models – learn what makes a good assumption
• Build core statements:
  • Cash flow statement
  • Balance sheet
  • Income statement
• Build supporting schedules:
  • Debt schedule
  • Working capital
  • Interest schedule
  • Retained earnings
  • PP&E analysis
• Understand the interconnectivity of the core financial statements and learn how the cash flow statement, income statement, and balance sheet are linked.
• Learn to apply automatic “balance checks”
• Understand treatment of non-recurring charges and  tax implications on financial models
• Understand implications of new FASB regulations on financial models used by investment banks and other financial institutions.

DCF Modeling

Valuation methodologies deconstructed

• Learn market-based valuation, including comparable company analysis and comparable transaction analysis using appropriate equity and enterprise value multiples (PE, EV/EBITDA, etc.)
• Intrinsic valuation, including DCF and LBO (floor) valuation
• Presentation of valuation results using sensitivity analysis and data tables

Enterprise value relationships

• Participants learn to identify and analyze the elements of the financial statement that are key to the value of an enterprise, including:
  • Market capitalization
  • Enterprise value
  • Options
  • Preferred stock
  • Minority interests
  • Debt
  • Cash and marketable securities
  • Treasury Stock Method of Calculating Diluted Shares Outstanding

Calculate free cash flows using the unlevered valuation approach

• Use previously built model to derive unlevered earnings and unlevered free cash flow
• Handle working capital items, deferred taxes and long-term accruals 

Discount the cash flows

• Learn how to derive a discount rate by deriving the cost of debt and the cost of equity
• Derive the cost of capital using CAPM
• Select the correct discount rates

Calculate the value

• Estimating terminal value
• Calculating enterprise value
• Using the enterprise value to determine implied share prices

Sensitivity analysis

• Analyze key input assumptions at various sensitivities

M&A Modeling

Building a robust merger model

• Setting up a control area for assumptions
• Inputting deal assumptions (% cash vs. stock considerations, purchase premium, asset write-ups, advisory fees, financing fees, and severance fees)
• Calculating shares outstanding using the treasury stock method
• Appropriate treatment of convertible securities
• Allocating purchase price and calculating goodwill
• Preparing the pushed-down balance sheet
• Making pro forma balance sheet adjustments
• Calculating sources & uses of funds
• Inputting operating & synergy projections
• Calculating the stub year period
• Building a pro forma income statement and making appropriate deal-related adjustments to arrive at accretion/dilution per share
• Error-checking a merger model and inserting circuit breaker switches where appropriate
• Sensitivity analysis: EPS accretion/dilution in stock vs. cash deal; interest rate assumptions, premium paid
• Pre-tax synergies required to break-even, and break-even PE analysis
• Revenue, EBITDA, and Net Income contribution analysis

Advanced merger accounting

• Understanding the differences between asset sales (including 338h10 elections) and stock sales
• Taxation issues, including deferred taxes created by the step-up of asset values
• Legal considerations in acquisitions