The Vair Companies
Home | Training | MBA Programs | Advisory | Executive Search | About Us | Media | Contact | Blog | Cart
Financial Statement Modeling and Analysis

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 – Purchase versus Lease
  • 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 – Incorporating the revenue model with the historical financials and operational worksheets to create a series of pro forma 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 (“re”)
      • Capital Asset Pricing Model (“CAPM”)
      • What is the Beta (“β”)
        • Covariance and timing issues
      • What is the Risk-free Rate (“rf”)
      • What the Market Risk Premium (“rm – rf”)
    • How to calculate the Cost of Debt (“rd”)
      • Market value versus book value
      • Historical rates versus prevailing rates
      • Importance of capital structure to rd
    • Applying corporate tax rates (“TC”)
    • 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

Return to overview >>

Available Dates

TESTIMONIALS

"The Vair course was outstanding on every level."

- World Bank/IFC Advisor
Home | News | Testimonials | Privacy Statement | Sitemap| Follow us on Facebook Twitter LinkedIn