Premium Package

Introduction

• Learn to integrate assumptions and drivers into financial models
• Master Excel techniques and shortcuts for financial modeling
• Learn formatting and structural best practices

Projecting Financial Statements

• Build a fully dynamic and integrated three statement model
• Use equity research, management guidance and industry forecasting conventions to forecast the income statement, balance sheet and cash flow statement
• Normalize historical results to exclude non-recurring items and reconcile GAAP to Non-GAAP presentations of financials
• Build supporting balance sheet schedules using a consistent roll-forward approach: Working Capital, Deferred Taxes, PP&E /
• Depreciation, Intangible Assets, Retained Earnings , and Debt & Interest

Error Proofing

• Learn best practices for check models for errors, accuracy and integrity
• Check and fix circular reference and iteration problems (#REF)
• Insert circularity toggles and learn other tools for formula auditing
• Customize Excel for effective modeling using macro shortcuts
• Balancing the model and inserting automated crosschecks

Presentation and Scenarios

• Perform sensitivity analysis using data tables
• Perform scenario analysis using data validation
• Integrating drop down menus and other “bells & whistles”

DCF modeling overview

Building on the knowledge gained from the financial statement modeling course, you will be introduced to valuation analysis in general, and the DCF model in particular. You will learn step-by-step DCF modeling, using the free cash flows already projected in the financial statement modeling case study.

Along the way, you will learn how to estimate the weighted average cost of capital (WACC) in the real world, and build several commonly used approaches to calculating terminal value. Finally, we will use data tables to analyze a broad range of scenarios given different assumptions.

Modeling free cash flows (FCF)

• Unlevered vs. levered FCF
• Capitalizing inappropriately classified expenses
• Handling working capital items, deferred taxes and long-term accruals
• Handling stub periods and midyear adjustments
• Integrating synergy assumptions into the DCF model
• Common pitfalls – enterprise vs. equity value, unlevered vs. levered FCF

Discounting the cash flows

• Deriving the cost of debt
• Derive the cost of equity using CAPM
• Estimating the weighted average cost of capital (WACC)
• Delevering and relvering beta
• Determining the optimal WACC
• Avoiding inconsistency between the FCF and discount rate

Estimating terminal value

• Perpetuity approach
• EBITDA multiple approach
• Common pitfalls

From enterprise value to equity value per share

• Calculating net debt and treatment of debt equivalents such as preferred stock, convertible securities , capital leases , and minority interest
• Calculating options and convertible securities using both the standard and treasury stock methods

Sensitivity analysis

• Using data tables to analyze a broad range of scenarios given different assumptions

Trading comparables overview

“Comps” analysis is the quickest, most widely used valuation methodology, and fundamental part of the core valuation skill set of investment bankers and finance professionals. In this course, trainees learn how to select and “scrub” comparables, pick the right multiples and build dynamic comps models in Excel from scratch, using real case studies, industry best practices, and sensitivity analyses.

• Learn to select appropriate comparable companies by evaluating operational, financial, size, and other similarities
• Set evaluation benchmarks & select comparable companies
• Gather appropriate financial history and projections
• Normalizing operating results and calculating LTM operating results to reflect nonrecurring charges and stock option expenses
• Standardize various expense classifications including FIFO to LIFO inventory accounting
• Calculate shares outstanding using the treasury stock method
• Input financial data & calculate and interpret financial and market ratios
• Presenting trading comps by structuring output schedule
• Selecting key valuation multiples using the VLOOKUP function and generating multiple tables

Transaction comparables overview

Transaction comps (aka deal or transaction comps) is a variant of comparables valuation. Value of the firm is derived by assessing the value of comparable companies recently acquired. In this step-by-step, real case study course, you’ll learn how to select comparable acquisitions, find important disclosures in filings, spread the data and build a complete transaction comps model in Excel.

• You will learn to screen for comparable transactions using SIC and NAICS codes, source SEC filings and press releases
• Learn the pitfalls of relying exclusively on data services like CIQ, Factset, SNL, etc for deal data
• Once a comparable transaction universe is sleected, you will learn to find and gather appropriate financial details
• Scrubbing data is critical for comparability; you will learn best practices for standardizing information and selecting the most relevant multiples and calculating purchase premiums
• Best practices for incorporating synergy assumptions and appropriately calculating unaffected pre-deal share prices
• Build a full dynamic transaction comps output page

M&A Modeling Overview

Build an Accretion/Dilution (M&A) Model to reflect the pro forma impact of various Merger & Acquisition scenarios. Using a real case study, you will model out the acquisition of one company by another company, correctly incorporating pro forma balance sheet and income statement adjustments.

Key topics covered include a quick test of accretion / dilution in all-stock deals, pricing structures (exchange ratios/collars/”walk-away” rights), purchase accounting, and the step-by-step allocation of purchase price.

Understanding M&A

• Introduction to the M&A landscape
• Typical buy side and sell side processes and deal timelines
• A review of common deal structures
• Understanding acquisition accounting & purchase price allocation
• Understanding deferred taxes created and lost in M&A
• Dealing with convertible securities and options in M&A
• Performing a back-of-the-envelope accretion-dilution analysis

Accretion/Dilution and M&A modeling

• Building a complete accretion dilution model
• Forecasting pro forma financial statements, and learning to make correct adjusting entries for goodwill and fair market value write ups, advisory fees and financing fees and debt refinancing
• Calculating pre-tax synergies to break-even
• Sensitivity analysis using data tables to determine accretion/dilution to target shareholders in various stock/cash scenarios
• Error-check a merger model
• Sales, EBITDA, and ownership contribution analysis

Advanced topics in M&A

• Understand the asset vs. stock purchase decision
• Address situations in which amortization may be tax-deductible
• A discussion of 338(h)(10) election and wholly owned subsidiary acquisitions
• Legal considerations in M&A

LBO modeling overview

Leveraged buyout (LBO) modeling is used widely by investment banks and private equity firms and is often part of a finance interview. In this course, you will learn how to build a real, complex LBO model from scratch. You will start by learning about basic LBO concepts, typical deal structures, and current industry dynamics. You will then begin the step-by-step modeling, covering the most common and challenging issues that emerge when modeling buyouts. The course concludes with a sensitivity and scenario analysis, as well as how to build output tables that are key to any professional-grade LBO analysis.

Introduction to LBOs and recaps

• Understanding concepts: Working through an LBO on a cocktail napkin
• What is the current financing and valuation environment?
• What are key industry benchmarks?
• LBO capital structure: TLs, Bonds, Mezz, Preferred, Bridge Loans and Equity

LBO model development

• Setting up the initial valuation and general assumptions
• Calculating diluted shares
• Building the Sources & Uses schedule
  • Build a heavy duty, yet flexible, sources & uses of funds schedule
  • Make adjustments in one place – watch it flow error-free throughout entire model
• Line-by-line build-up and forecasting of the income statement and working capital schedules
• Modeling LBO debt
  • Term loan A/B/C– Modeling mandatory amortization as well as cash sweeps
  • Revolver – Automatic cash sweep, minimum cash balance, and maximum borrowing capacity
  • Pricing matrix driven off LIBOR spot rates + spread
  • Senior and Sub Notes – Modeling PIK vs Cash interest
  • Preferred stock – Allowing for equity kickers
• LBO returns and exit analysis for all providers of capital:
  • Sponsor equity and management rollover
  • Lenders and preferreds
• LBO modeling best practices
  • Circularity breaker and error trapping – no more DIV#0 and REF#!
  • Building key sensitivities into model outputs