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

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