Financial Modeling Bootcamp

Day I: Advanced Excel for Financial Modeling

Advanced techniques to Expedite Workflow

• Efficient formula writing
• Formula & model auditing
• Advanced cell locking
• Hot Keys to work without the mouse
• Windows & work with multiple applications
• Go To Special
• Paste Special
• Project #1: Revenue build-up by store count and same store sales growth

Advanced Analytical Tools & Sensitivity Analysis

• Goal Seek
• Data Tables
• Scenario Manager
• Project #2: Cash flow modeling & sensitivity analysis

Database Functions for Finance

• Advanced SUMIFS
• VLOOKUP-MATCH
• INDEX-MATCH

Functions for Financial Modeling

• Financial functions
• Date functions
• Nested IF statements
• IF Statements with AND/OR
• CHOOSE function
• Weighted average calculations
• IFERROR
• Project #3: Loan amortization model

Day II: Intro to Corporate Finance, Accounting, & Modeling

Intro to Corporate Finance

• Net Present Value (NPV) and Internal Rate of Return (IRR)
• How are NPV and IRR used in financial modeling?
• How companies raise capital through debt and equity issuances

Enterprise value

• Stocks, dividend yields, trading terminology, and valuation metrics
• Market Capitalization: Calculate the market cap for public companies
• Shares outstanding: How does share count change over time?
• Enterprise Value: Calculate the enterprise value for public companies

Financial Accounting & Statement Analysis

• Cash vs. accrual accounting
• Assets = Liabilities + Shareholder's Equity
• Annual and quarterly filings, and press releases
• Income Statement: Analyze line items on an income statement; calculate growth rates, and TTM
• Balance Sheet: Review assets, liabilities and shareholder's equity
• Cash Flow Statement: Analyze a public company cash flow statement
• Working Capital: Calculate and understand working capital

Integrated Financial Modeling

• Calculate historical ratios for revenue growth, margins, and working capital
• Project cash flows based on historical metrics
• Create debt, equity, and working capital supporting schedules
• Review how financial statements tie together and balance the model
• Integrate the income statement, balance sheet, and cash flow statement in Excel

Intro to DCF Modeling

• Calculate unlevered free cash flow from the integrated model
• Discount cash flows using the WACC
• Derive a share price from total company value

Sensitivity Analysis

• Use Data Tables to sensitize the model's key inputs
• Conditional Format with formulas to highlight cells that meet certain criteria
• Calculate the revenue growth required to hit a certain share price

Intro to LBO Modeling

• Review Leveraged Buyouts (LBOs) basics
• Create a simple LBO model
• Calculate the IRR and Equity Multiple