Join our financial modeling class to learn essential finance and accounting concepts, build a 3-Statement Financial Model and a Discounted Cash Flow (DCF) Model in Excel. Learn advanced Excel skills, explore corporate finance and valuation, and create a financial model for a real company. This hands-on course will teach you the core financial skills required for Equity Research, FP&A, Investment Banking, Private Equity and Corporate Finance.
Overview
Syllabus
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
- Projects #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 PV and IRR used in financial modeling?
- How companies raise capital through debt and equity issuances
Enterprise value
- Stocks, dividend yields, trading terminologoy, 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
Day III: In-depth Financial Modeling & Valuation
Company Analysis
- Discuss the business model and value drivers of a public restaurant company
- Capital Structure: Calculate the total enterprise value and build a capital structure table
- Historical Financials: Populate the historical financials
- Ratios: Calculate the financial ratios
Financial projections
- Create financial projections for revenues
- Create financial projections for expenses
- Create projections for the working capital items
Terminal Value
- Estimate the company's terminal value using the Gordon Growth Model
Discounting Cash Flows
- Use CAPM to estimate the company's cost of equity
- Calculate the weighted average cost of capital (WACC)
- Discount future cash flows with the midpoint convention
Corporate Valuation
- Calculate the diluted shares outstanding using the treasury stock method
- Derive a value per share from total enterprise value
Final Analysis & Reasonability Testing
- Where does our model need additional analysis?
- How does our value per share compare to the current price?
- How do our financial projections compare to management's and the analyst community
- Analyze the output and determine reasonability of the model and assumptions
- What areas or assumptions require additional research?
- What assumptions drive the valuation and how would changes impact our thesis
- Should we invest in the stock at current prices?
Taught by
Brian McClain, Mourad Kattan, Adebayo Norman, and Garfield Stinvil
