Excel & Financial Modeling

An extension and application of fundamental knowledge naturally leads a financial analyst to excel.  Programs that we offer in this area can be combined and integrated with our other courses given your training needs.  Alternatively they may serve as stand-alone courses if the audience already possesses fundamental knowledge and only needs to learn the application in Excel. 

Microsoft Excel Basics
Financial and Valuation Modeling Suite – 5 day
LBO Modeling in Excel

Microsoft Excel Basics

This comprehensive introductory-level course is designed for anyone who would like to become more proficient in utilizing the functionality and analytical capabilities of Microsoft Excel.  While teaching best approaches for navigating within Excel, participants will also build speed through keyboard shortcuts and learn how to perform basic analysis.  The course will provide a solid foundation for attendees and is an ideal precursor for taking more advanced analysis and financial modeling programs.

Learning Objectives

Outline/Topics:

  • Apply keyboard shortcuts to develop speed and proficiency
  • Develop a set of Excel “Best Practices”
  • Create spreadsheets for data analysis
  • Properly format a spreadsheet for formal presentations
  • Perform basic data analysis within Excel
  • Sort data within Excel

Duration: One day

Program Level:  Basic

Prerequisites:  There are no prerequisites

1 Day Program
  • Basics & Best Practices
    • Moving between Menu toolbar and active sheet
    • Saving protocols
    • Setting workbook to manual iterations
  • Navigation
    • Between/within sheets
    • Between open applications
    • Check links / Edit links
    • Finding/searching data within sheet and workbook
    • Insert row / Column
  • Formatting
    • Color, number, cell, comments
    • Worksheet formats
    • Column and row width / Autofit
    • Financial statement formats
  • Functions
    • Copy, cut, paste, paste special
    • NAME creation
    • Auditing functions
    • Partial and absolute references
    • LOGIC: IF, nested IF, CHOOSE, AND, OR (and combinations thereof)
    • LOOKUP, HLOOKUP, INDEX & MATCH, ISERROR, RANK
  • Data Set Analysis
    • Data basic functions (Group, Ungroup, Sort, Delete)
    • Data validation
    • Dropdowns
    • Analyzing large data sets

For a detailed outline and additional information on this course or to find out about our other courses, contact us at (646) 244-5190 or [email protected]!

Financial and Valuation Modeling Suite – 5 day

Participants in this five day suite will gain a comprehensive understanding of the required elements of financial modeling including how to develop a 3-statement model, how to value a company through discounted cash flow analysis as well as how to construct a merger model in Excel. Participants will use case studies to build financial models with all the techniques and applications used by professional analysts. Ideal for range of professionals including investment banking associates, private equity and hedge fund analysts and corporate finance officers, this five day suite will blend best practices and applications of Excel modeling, financial statement analysis, corporate finance and security valuation.

Learning Objectives

Outline/Topics:

  • Develop a 3-statement model
  • Select, locate and develop appropriate projection drivers in financial statement models
  • Locate inputs for and estimate the weighted average cost of capital (WACC)
  • Calculate terminal value using various approaches
  • Value securities of a company using discounted cash flow analysis
  • Use data tables to analyze a broad range of scenarios with different assumptions
  • Construct a merger model to reflect the pro forma impact of different acquisition scenarios
  • Perform accretion/dilution analysis in a merger scenario
  • Revise financials of a company using purchase accounting
  • Format spreadsheets for formal presentations

Duration:  Five days

Program Level:  Intermediate

Prerequisites:  Prior to attending, participants should have completed a basic excel course as well as an introductory financial statement analysis program.

Day 1
  • Key Excel skills for financial modeling
    • Basic Excel techniques
    • Keyboard shortcuts
    • Excel default settings for modeling
  • Structure of financial models
    • Standard modeling structures and formulae
    • Best practices in modeling
    • Avoiding common errors
    • Ensuring accuracy and integrity
    • Circular reference and iteration problems (#REF)
  • Format of financial models
    • Formatting best practices
    • Data table set-up
    • Ratio page
    • Individual statement format
Day 2
  • Assumptions and projection drivers
  • Cash flow statement
  • Balance sheet
  • Income statement
  • Supporting schedules:
    • Equity statement
    • Debt schedule
    • Working capital
    • Interest schedule
    • Taxes
    • Property, plant & equipment
  • Interconnecting the core financial statements
  • Automatic “balance checks”
  • Non-recurring charges
  • FASB regulations on use of financial models
Day 3
  • Company valuation approaches
    • Comparable company
    • Comparable transactions
    • Enterprise value
    • Multiples
    • Intrinsic value models
    • Sensitivity analysis
  • Enterprise value
    • Measure of income relative to EV
  • Earnings, cash flow multiples
Day 4
  • Unlevered earnings
  • Free cash flow to the firm/to equity
  • Working capital, deferred taxes and other accruals
  • Cost of capital techniques
    • Debt
    • Equity
  • Calculating WACC
  • Terminal valuation estimates
  • Discounting process
  • Sensitivity analysis
Day 5
  • Merger model overview
  • Deal assumptions (cash vs. stock, purchase premium, asset write-ups, fees)
  • Treatment of convertible securities
  • Allocating purchase price and calculating goodwill
  • Pro forma balance sheet adjustments
  • Sources & uses of funds
  • Operating & synergy projections
  • Pro forma income statement
  • Accretion/dilution per share
  • Error-checking
  • Revenue, EBITDA, and Net Income contribution analysis
  • Deferred taxes asset value step-up considerations

For a detailed outline and additional information on this course or to find out about our other courses, contact us at (646) 244-5190 or [email protected]!

LBO Modeling in Excel

Leveraged buyout (LBO) modeling is used extensively by private equity firms and investment banking analysts as an important valuation and investment tool. In this program, participants will create from the ground-up a sophisticated and complex LBO model that can be used across a wide range of industries. The program will provide an overview of how LBO transactions are structured and then begin a step-by-step process of building an LBO model covering the most frequent and complex issues encountered along the way. The course will also include important topics such as purchase price allocation, asset write-ups and deferred taxes.

Learning Objectives

Outline/Topics:

  • Explain common LBO and recapitalization structures
  • Develop an LBO model starting from a three-statement financial model
  • Integrate switches for LBO vs. Recap accounting treatment
  • Incorporate various debt structures and preferred stock
  • Include a PIK (payment in kind) toggle to various debt tranches
  • Integrate cash sweep functionality into term loan debt tranches
  • Construct a Sources & Uses (S&U) schedule
  • Calculate IRRs for sponsors, management, preferreds, and debt holders
  • Include complex accounting adjustments (e.g., deferred taxes, fair value write ups, and goodwill)
  • Include a DCF valuation for use in an LBO model

Duration:  Two days

Program Level:  Intermediate

Prerequisites:  Prior to attending, participants should have completed a basic excel course as well as an introductory financial statement analysis program.

Day 1
  • Introduction to LBO and recapitalization
    • Participants
    • Structure
    • Sources and uses (S&U)
    • Capital (term loans, bonds, mezz., prefs, bridge, equity)
  • Model development (Part I)
    • Initial valuation and assumptions
    • Diluted shares
    • Enterprise value
    • Sources & Uses schedule
    • Forecasting income statement/working capital schedules
Day 2
  • Model development (Part II)
    • LBO debt structures
    • Term loans, revolvers, senior/sub notes, prefs
    • LIBOR and other rate inputs
    • PIK toggles in debt tranches
    • Exit and IRR return analysis
      • Sponsor equity/management
      • Lenders/preferred investors
    • Circular breaker and error trapping
    • Sensitivity inputs and analysis
    • Best practices in LBO modeling
  • Purchase price allocation
    • Goodwill
    • Asset write-ups
    • Deferred taxes
  • Use of DCF analysis in LBO models
  • Asset sale versus stock sale tax treatment
  • Modeling dividend recaps

For a detailed outline and additional information on this course or to find out about our other courses, contact us at (646) 244-5190 or [email protected]!