Dubai Office +971 (0)50 1755 162
emea@capitalcitytraining.com
Singapore Office +65 8452 9027
apac@capitalcitytraining.com
London Office +44 (0)20 3286 0836
info@capitalcitytraining.com

Excel Skills for Financial Modelling Course

Aims

This programme is targeted at those who have some knowledge and experience of using models and Excel, want to do some financial analysis around an existing model / data, but don’t want to build a full-blown integrated financial model. The ultimate objective is to empower delegates with the ability to efficiently and effectively flex and analyse financial data, using best practice techniques.

The focus is on analysis and manipulation of third party data . This is more suitable to mid/ senior level investment professionals.

Delegates will spend 1 day (or 2 half days) building upon third party data / models to enable efficient and re-usable, scenario analysis, sensitivity analysis around a number of key financial variables and outputs. The actual data and outputs to be analysed can be tailored to client needs. But would typically look at EBIT, Free Cash Flows, Terminal Values, DCF to arrive at enterprise value, and reconciling to Equity Value – dependent on the start data.

By way of illustration, one option is to present delegates with a well-structured, best-practice compliant model – complete with forecast P&L, Cash Flow and Balance Sheet, fully integrated. Firstly the program will review the tenets of best practice construction within the model, and then help delegates develop analytical skills using Excel tricks and functions. Excel short-cuts and practical tips will be picked up and applied throughout – as appropriate for the audience. Topics such as model-structure, formatting, auditing, Data Tables, Data Validation and other switch mechanisms will comprise the first day.

Delegates will become more confident in navigating Excel models, using keystroke shortcuts, and recognising how to use core Excel functions such as IF, IF(AND…), IF(OR…), VLOOKUP, INDEX, MATCH and other data retrieval functions.

Data Tables and scenario analysis with simple ‘switches’ will be integrated into the analysis.

By the end of the course, delegates will:

Outline of topics covered

Intro to the ideal model structure and formatting Auditing the structure
  • Aims and objectives; what is best practice modelling for, and is there just one standard?
  • What does a good income model look like?
  • What aspects / modules would you expect to see?
    • Inputs; Workings; Outputs
    • Modularity
    • Front sheet and checklists
    • User notes and instructions
    • background error checking
    • linked files
  • Some excel tools and tricks to navigate and audit the structure
  • Financial statement integration: auditing how it all fits together and creating a self-maintained checklist.
  • Exercises: Delegates will us a pre-structured model to identify basic flaws in best practice structuring, and form an opinion as to whether it is workable, or needs to be returned to sender.

Some Excel tools and tricks
  • Navigation along the way
  • Scenario integration
    • scenario creation and location
    • building in switches
    • Data retrieval: LOOKUP; INDEX & MATCH; OFFSET; CHOOSE
    • Data Validation
  • Dynamic labelling and formatting for ease of modelling
  • RangeName revision

Exercise: LOOKUP and Data Validation to create a simple data retrieval mechanism to pick data out of a model.

Output Analysis
  • Auditing the inputs and workings before progressing
  • Re-organising the model outputs to make them usable
  • Balance sheet integrity and linkages to IS and CFS
  • Dupont and extended Dupont to critically analyse the financials
  • Spotting Balance sheet weaknesses- building in ‘warning sign’ checks
  • Charting techniques for analysis
  • Data Tables
  • Goal Seek
Case study exercises: applying to the internal model
Working with existing Models
  • The audit process for a model §location and navigation
    • initial checks applied
    • reorganising and formatting for ease
    • tracking editing changes
    • F5 special
    • error values
    • Finding links and the Excel Formula bar and useful functions
    • Fit for purpose?
    • Other model review tools and tips
  • Working with the Outputs
    • What outputs are there, what’s missing
    • Structuring our own outputs using templates already created
    • An analytical review of the outputs
Delegates will work with the trainer to critically asses a 3rd party model, looking out for best practice applications and identifying flaws and inconsistencies.
Having done this, the aim will be to reorganise the model as needed, construct a set of outputs for analysis, or audit existing ones, and then go on to create scenarios and sensitivity analysis.