Financial Modeling and Analysis in Excel: Syllabus

Atlanta Sep 18-19, 2012

Part 1 – Introduction to Excel

• Quick review of the Excel program
• Using “mouseless” excel techniques to create an efficient and robust historical analysis of the case study
• Sample excel “short-cut” and “hot keys” techniques employed are:
  • Navigating the spreadsheet without the mouse
  • Shortcut keys using “alt” and “ctrl”
  • Anchoring using the “F4”
  • Editing using the “F2”
  • Hiding columns and Freezing Panes for faster modeling
  • Overall efficient model lay-out techniques
  • Function wizard, e.g. - logic functions like “IF” statements, “PMT,” plus a series of other embedded excel functions
  • These techniques will be used throughout the two days, plus additional excel techniques will be introduced in later sessions
• Simple EBITDA coding exercise
  • Transposing
  • Goal Seek
  • Nominal versus Real
  • Addressing pro forma currency issues
    • Purchase power parity (“PPP”)
    • Interest rate parities
  • Simple financial analysis – comparing Income Statements to Balance Sheets by using “The Line”

Part 2 – Introduction to Financial Statements

• Review the relation between the major components
• Balance Sheet as a “snap-shot”
• Income Statement as a “moving picture”
• Statement of Retained Earnings
• Statement of Cash Flow – “tying them all together”
• Sample financial statement analysis
  • Common-size financial statements
  • Vertical analysis
  • Year-on-year growth

Part 3 – Introduction of a Pro Forma Model

• Elements to consider when building a pro forma model
  • An exercise in back-engineering from desired outputs
• Top-down versus bottom-up modeling
• The assumption page

Part 4 – Assets: Purchasing Versus Leasing

• Implications on:
  • Calculating different depreciation schedules
  • Interest payments
  • P/L multiples
  • Taxes
  • Capital Budgeting
  • Discussion on Operating versus Capital Leases

Part 5 – Operational Worksheets

• Calculating Revenue
  • Drivers
  • Average price conventions
• Calculating Cost of Goods Sold (“COGS”)
  • Function of sales
  • Independent of sales
  • Using COGS to drive inventory accounts
  • Using Inventory Turnover to drive Units Sold
  • LIFO versus FIFO versus Average costing
• Other Operating Expenses
  • Growing expenses
• Arriving to EBIT
• Using Revenues and Costs to derive Accounts Payable and Accounts Receivable

Part 6 – Debt

• Financial discussions
  • Effective interest rate
  • Using Excel’s Function Wizard to calculate Annuities
  • Average Loan Life
  • Tax shields
• Calculating debt schedules
  • Decomposing debt service
  • Tricking the model to accommodate different time horizons
• Coding and selecting from different debt schedules
  • Interest impact on taxes
  • Principal repayment and Time Value of Money (“TVM”)

Part 7 – Coding Full Financial Statements

• The Income Statement
  • Revenue based assumptions and outputs, versus
  • Fixed cost assumptions and outputs
  • Depreciation impact
  • Using the correct interest payment
  • Coding tax
  • Discussion on accounting-based valuation multiples
• The Statement of Retained Earnings
  • Dividend Payout Ratio
  • Retention Ratio
  • Addressing losses and dividend coding
  • Dividend policy impact on future growth and share price
• The Balance Sheet
  • Carrying items forward
  • Early discussions on book versus cash issues
  • The balancer
• The Statement of Cash Flows
  • Indirect method versus direct method
  • Trapped cash
  • Integrating cash flow into the statements
  • Auditing and checking for balancing
  • Common mistakes to the balancer
    • Gross versus net fixed-assets
    • Retained earnings
    • Dividends
    • Correct use of current accounts
• Reviewing, interpreting and making decisions based on outputs
  • Using the model to manage expectations, “below” and “above” you

Part 8 – Discussion on Valuation

• Discounted Cash Flow Method (“DCF”)
• Dividend Growth Model (“DGM”)
• Price-to-Earnings Multiples (“P/E x”)
• What are the advantages and disadvantages to each method
• Decomposition of the case study’s historical share price
  • Book value versus market value
• Decomposition of pro forma using financial statement analysis
  • Profit margins
  • Asset turnover
  • Capital structure
  • Return on Equity (“ROE”) using the DuPont Analysis

Part 9 – Discounted Cash-Flow Modeling

• The “denominator” to valuation
• How to calculate Cost of Capital
  • What is the Weighted Average Cost of Capital (“WACC”)
  • How to calculate the Cost of Equity (“r_e”)
    • Capital Asset Pricing Model (“CAPM”)
    • What is the Beta (“β”)
      • Covariance and timing issues
    • What is the Risk-free Rate (“r_f”)
    • What the Market Risk Premium (“r_m – r_f”)
  • How to calculate the Cost of Debt (“r_d”)
    • Market value versus book value
    • Historical rates versus prevailing rates
    • Importance of capital structure to r_d
  • Applying corporate tax rates (“T_C”)
  • How to address leverage (or gearing) in the WACC
  • What are the commercial implications to WACC
• The “numerator” to valuation
• Elements to arrive at cash flow
  • Accounting entries versus cash flow
  • What is Free Cash Flow (“FCF”)
• Continuing Value (“CV”)
  • How to calculate CV
    • The importance of growth (“g”) to CV
  • The importance and weight of the CV to the overall DCF valuation
  • Balance time horizons of assumption validity and the CV

Part 10 – Using the Model to Price the Asset

• Different valuation techniques
  • What does the time value of money (“TVM”) mean
  • The concept of opportunity costs
  • Risk versus reward
  • Calculating Net Present Value (“NPV”)
  • Calculating Internal Rate of Return (“IRR”)
  • How to calculate the Enterprise Value
  • What are the benefits and deterrents of both the NPV and IRR methods
• Decomposition of pro forma using financial statement analysis
  • Profit margins
  • Asset turnover
  • Capital structure
  • Return on Equity (“ROE”) using the DuPont Analysis
• Stressing the model by changing assumptions
  • Logging scenarios and changes
• Sizing the Debt Capacity
  • Debt Coverage Ratios
    • Debt Service Coverage Ratios (“DSCR”)
    • Liquidity ratios versus maintenance ratios
    • Using the model to negotiate debt covenants
• Excel techniques employed
  • Goal-seeking
  • Sensitivity tables
• Reviewing, interpreting and making decisions based on outputs
  • Using the model to manage expectations, “below” and “above” you

Part 11 – Monte Carlo Simulation Analysis

This course leads to Vair's various Specialty and Master Classes