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
• 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?