Days 1 & 2 –Financial Modeling & Advanced Sensitivity Analysis
Overview and introduction to financial modeling
Excel best practices and efficiencies
Construction and Application of:
- Core statements
- Other long-term items schedule
- Working capital schedule
- Equity schedule
- Depreciation schedule
- Shares outstanding schedule
- Amortization schedule
- Debt and interest schedule
Troubleshooting the Model:
- Understanding and controlling for circular references
- Balancing the model
- Making the model “deal ready”
- Utilizing the Finished Product:
- Data tables
- Advanced Sensitivity Analysis:
- Adding scenarios to the model
- Creating a toggle for cases / naming cells
Day 3: Advance Macros, VBA for Finance
Macros
Accessing VB editor, Developer tab
Changing security to ‘Disable all macros with notification’
Recording your first macro – copy pasting values
Formatting chart with macros
Creating flexible CAPM model using Form Controls and Active X
Objects & Control statements in VBA code
Investment Banking – Merger & Acquisitions
Types of Mergers & Acquisitions (Consolidation, Spin off, Drivers of M&A)
- Differentiate between financing and operating synergies
- M&A Regulatory Environment
- Developing M&A Models
- Integration of Financial Statements
Day 4:M&A I & II
Meaning and Categories of M&A Merger
Motivations Forms of Payment in a Merger
Deal Hostile versus Friendly Offer
Evaluating a Merger Bid
Case: Estimating fair acquisition price of the target company in M&A
Discounted Cash Flows
Comparable Companies
Comparable Transactions
Case: LinkedIn acquisition by Microsoft
Finding out rationale for the transaction
Searching and researching relevant reports
Collecting and projecting numbers
Creating high level merger accounting for goodwill – Pooling method
Accretion/Dilution Analysis and Modeling a Financing Plan
Modeling a Financing Plan
Performing Sensitivity Analysis on Acquirer’s EPS, Contribution Analysis
Case Study
- Data Collection from Public Sources
- Understanding the Business Profile of the Company
- Modeling Revenue and Expenses Drivers
- Modeling Income Statement
- Modeling Implied EBITDA
- Balance Sheet Projections
- Cash Flow Statement Projection
- Performing Valuation and Sensitivity Analysis
- Calculating Enterprise Value and Implied Market capitalization
