Advanced Financial Modelling

Start Dates: 01/01/2019

Duration: 3 days

Full Fee: €1500

Network Members Fee: €1000

Programme overview

This three-day course is designed to teach you modelling ‘best practice’ and how to build well-structured financial models. It also critically assesses the merits of the different tools available in Excel, and looks at the practical applications of a number of Excel techniques and less commonly used functions. The course then looks at building a discounted cash flow valuation models and an LBO model

Learning outcomes

This three-day course is designed to teach you modelling ‘best practice’ and how to build well-structured financial models. It also critically assesses the merits of the different tools available in Excel, and looks at the practical applications of a number of Excel techniques and less commonly used functions. The course then looks at building a discounted cash flow valuation models and an LBO model.

Courses are capped at a maximum of 12 delegates to ensure you receive the most effective training during your session.

A sound knowledge of Excel formulae and functions and their practical

Course Objectives

  • Know how to build a financial forecast
  • Know how to link the statements
  • Scenario building
  • Know how to discount

Applications is assumed, as well as familiarity with the common problems encountered when building financial models.

Who is the course for

This course will be of benefit to those who are already familiar with Excel and its functions, who are looking to enhance their ability to build financial models and to analyse other people’s or standard models.

Modules

Financial modelling

  • Golden Rules of Model Design
    • Input, calculation and output areas
    • Key rules to follow
    • Design rules, model structure and set up
  • Building a financial forecast
    • Objectives, structure and building the core of the model
    • Sources of information
    • Historic information, cleaning the numbers
  • Forecasting the income statement
    • Dealing with segment forecasts – divisional analysis
    • Forecasting to EBITDA
    • Dealing with taxation – determining the correct tax rate, marginal taxation, tax losses and deferred tax
    • Forecasting dividends – pay out as a driver, yearly off set
  • Forecasting the balance sheet
    • PPE – capex, depreciation and disposals, links to the cash flow statement
    • Working capital – drivers for receivables, payables and inventory, days based drivers
    • Provisions – distinguishing between cash and non cash provisions, income statement and balance sheet issues
    • Shareholders equity – retentions, share issues and buy backs
  • Linking up the cash flow statement
    • Key interactions between financial statements
    • Constructing the debt schedule – creating a debt waterfall
    • Debt modelling without circularity
    • Problems of circular models
  • Working with Scenarios
    • Different methods to select the scenarios – numbers, data validation, simple Visual Basic (VB) tools, using the key form functions
  • Key Aditing Tools
    • Using ratio analysis – growth and CAGR, primary and secondary ratios
    • Completing the auditing process – creating a check sheet, auditing tools, finding links

Principles of valuing cash flows – discounted cash flow theory and rationale, absolute and relative valuation

  • Basis of DCF – earnings compared to cash flows, DCF in context Accretion/dilution analysis – pros and cons
  • Understanding which cash flows are discounted in valuing a corporate – core assets vs. non-core assets, free cash flow and NOPAT
  • Forecasting free cash flows – cyclicality
  • Discount rates – calculating the cost of capital
  • Understanding issues surrounding identifying the correct discount rate
  • Discounting the free cash flows
  • Terminal values – gain understanding of key issues surrounding the terminal value in a discounted cash flow forecast, terminal value approaches
  • Generating an enterprise and equity value
  • Using the discounted cash flow model to build a total value of the firm
  • Dealing with core and non-core assets
  • Building the value bridge

LBO Modelling

  • Key elements of an LBO model
  • Comparing and contrasting DCF and LBO models
  • Sources and uses of funds
  • Key drivers in an LBO model
  • From stand alone valuation to LBO analysis
  • Case Study II: Participants use the stand alone valuation of the target to complete an LBO model
  • Assessing debt capacity for LBO financing
  • Financial interdependencies
  • Financing growth
  • Sustainable debt
  • Target debt capacity assumed in a WACC calculation, debt capacity and interest cover
  • Debt capacity in LBOs
  • Debt capacity multiples in practice and credit analysis
  • Capital providers and their typical characteristics
  • Institutional and management equity
  • Traditional/new lenders
  • Senior tranche profiles – A, B, C, RCF
  • Subordinated tranche profiles -Second lien, Mezzanine (with/without warrants), PIK, High yield bonds

Trainer

 

This course is also available as a 2 day programme. Please contact info@ifsskillnet.ie for further details.