Advanced Modeling (3 days)

Day 1 - Fully Integrated M&A Modeling

During this session, participants learn how to build a full-blown merger model which combines two fully-integrated financial statement forecasts. Practical consolidation issues are addressed, and the analysis undertaken in the previous day is greatly expanded. The deal analysis focuses on the financing structure, pricing, earnings and credit impact and value creation.

Key topics:

• The advantages of a full-blown merger model
• Preparing the stand-alone data for acquirer and target
• Preparing key deal data
• Building a flexible funding structure using a sources and uses of funds table
• Calculating goodwill
• Dealing with fair value adjustments to the target's net assets
• Dealing with refinancing of target's debt
• Modeling fees (advisory, debt-issuance and equity-issuance)
• Consolidating the financial statements of acquirer and target
• Synergies
• Earnings accretion / dilution and relative P/E analysis
• Assessing the value creation potential of the deal using return on invested capital (ROIC) analysis
• Contribution analysis
• Analysis at various prices (AVP)
• Net present value of synergies versus control premium
• Identifying the maximum offer price and a suitable financing mix

Day 2 - LBO Modeling

This program aims to teach participants how to structure an LBO and model the financial impact of the financing structure. This session will concentrate on understanding the implications (both modeling and deal) of the finance structuring. Participants complete a fully integrated model with an income statement, balance sheet and cash flow statement.

Key topics:

• General overview of a levered transaction: what it is and basic principles
• Drivers of value creation in a levered transaction
• How leverage increases the return on equity
• What makes a good LBO candidate
• The concept of cash flow lending
• The lender’s perspective: risk, return and exit routes
• Structural subordination
• Financial instruments used in levered transactions
• Senior debt (revolving facility, term A, term B, term C)
• Second lien
• Mezzanine loans
• High yield bonds
• PIK notes
• Preferred shares, shareholder loans, vendor loan notes
• Ordinary equity
• Build a full LBO model from a template
• Main deal assumptions: focus on cash flow drivers and how to sanity-check preliminary assumptions
• Sources and uses of funds table
• Ownership structure
• Goodwill calculation
• Creating the opening balance sheet
• Deal adjustments - amortization of debt issuance fees
• Dealing with tax losses carried forward
• Calculating cash flows for debt servicing
• Setting up a repayment schedule for individual debt instruments
• Making accelerated payments using a cash sweep mechanism
• Modeling the revolving credit facility
• Dealing with the circularities created by the interest calculation
• Dealing with PIK interest
• Calculating credit ratios
• Calculating and interpreting returns to the equity investors
• Calculating and interpreting returns to mezzanine investors
• Sensitizing the outputs of the analysis
• Modeling different operational scenarios using Excel functions

Day 3 - Excel Power Modeling

This program covers a range of tools and techniques in Excel designed to improve effectiveness and efficiency. The program covers building scenarios into models using a variety of functions, together with adding in on-sheet controls, sensitivity analyses and flexible output tables. The topics in this menu can be chosen according to participants’ needs.

Key topics:

• Modeling with speed and style (1 - 2 hours 2003 stds)
  • Setting up toolbars so that Excel can be used as efficiently as possible
  • Setting up formatting styles
  • Custom number formats
• Keeping circularities under control (1 - 2 hours 2003 stds)
  • Working with intentional circular references
  • Debugging a model with non-intentional circular references
  • Avoiding intentional circular references with a circular reference macro
• Flexible output tables (1 - 2 hours 2003 stds)
  • Use functions to extract information from a model
  • VLOOKUP
  • HLOOKUP
  • OFFSET
  • INDEX
  • MATCH
  • Data validation
  • INDIRECT
• Scenarios (1 - 2 hours 2003 stds)
  • Building scenarios into the model
  • CHOOSE
  • OFFSET
  • INDEX
  • MATCH
  • On sheet controls
  • Conditional Formatting
• Working with Text and Dates (1 - 2 hours 2003 stds)
  • Creating dynamic headings and footnotes in the model
  • Working with dates
  • Concatenation
  • TEXT function
  • UPPER, LOWER, PROPER functions
  • TRIM and CLEAN functions
  • RIGHT, LEFT & MID functions
  • DATE, DAY, MONTH, YEAR functions
• Sensitivity analysis (1 - 2 hours 2003 stds)
  • Run sensitivities on model outputs
  • One-input data tables
  • Two-input data tables
  • Three-input data tables
  • Data table secrets
• Lists (1 - 2 hours 2003 stds)
  • Creating lists
  • Sorting
  • Basic and advanced filtering
  • SUMIF and COUNTIF
  • Calculate with advanced criteria with database functions
  • Pivot tables
• Other functionality (1 - 2 hours 2003 stds)
  • Printing with views
  • Print macro
  • Print macro with a form for easy print selection
  • User defined functions
  • Beauty save macro
  • Protecting your work
  • Charts
  • Arrays
  • Range names
  • Shortcuts