Excel for equity analysis and research

Excel for Equity Analysis and Research

Learn how to

  • Build a best practice financial model
  • Capture a company’s historical information in the most flexible way
  • Create projection and easily switch between them, either at:
    • a high level e.g. growth in overall sales
    • a detailed level e.g. sales by division
  • Create a working Income Statement, Balance Sheet and Cash Flow statement that seamlessly moves from historical to projections
  • Create statements to assist with your valuations whether it is free cash flow to enterprise or equity, adjusted earnings multiples or other methods
  • Handle the more complex projections particularly
    • Debt requirements and repayments with amortisation tables
    • Fixed Assets and depreciation
    • Working Capital issues
    • Major capital projects
    • Changes in time lines for major financial decisions
    • Purchase or sale of businesses/ divisions/ departments
  • Use Excel’s available valuation tools including
    • NPV, IRR, payback periods
  • Run sensitivities on the financial model to discover
    • what are the most sensitive inputs
    • where are the break even points
    • what is the minimum return we expect
    • what are the constraints and risks to growth
    • what does the company look like in a bull, bear or base case (aka high, low or realistic cases)
    • what the current share price is implying the growth will be.
  • Re use the model on a continuous basis so that it is easy to
    • update annually (or quarterly) with the new financial reports
    • keep a history of projections to see how good the company is at forecasting

Course Outline

  • Best Practice in Financial Modelling
  • Types of financial models
  • Incorporating and recording historical information into your financial models
  • Different ways of modelling projections and forecasts for issues related to
    • Macroeconomics like inflation and exchange rates
    • Consolidating multiple projections
    • Sales
    • Cost of sales
    • Gross Margins
    • Fixed and semi fixed costs
    • Variable costs
    • Depreciation/ wear and tear and the associated fixed assets
    • Interest and the associated debt
    • Tax including assessed losses
  • Generating the relevant financial statement particularly the income statement, balance sheet and cash flow statement
  • Valuation techniques and incorporating them into the financial model
  • Monitoring your model as you work on it
  • Using Excel’s inbuilt sensitivity tools including
    • Goalseek and Solver
    • Data Tables
    • Scenario manager
  • Creating dashboards and reports to present your findings by incorporating:
    • Graphs including waterfall graphs
    • In Cell visualisation i.e. conditional formatting, sparklines and other tricks
  • During the course the Excel tools and functions we will cover includes:
    • IF
    • AND/ OR
    • VLOOKUP
    • SUMIF/ COUNTIF/ SUMIFS/ COUNTIFS
    • OFFSET
    • INDEX/ MATCH
    • SUMPRODUCT
    • NETWORKDAYS and other date related issues
  • End product is a working model of the selected company

Feedback & Who Should Attend

  • Investment bankers,
  • Equity Research Analysts,
  • Private equity players,
  • Corporate and project finance
  • CFA and MBA students

and anyone who deals with the analysis of listed or unlisted companies and needs to perform any sort of valuation on them.

Pre Requisite (s)

Knowledge required for Excel Dashboard Training:

  1. Basic Excel skills
  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  1. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  2. Formatting