Financial Modelling Using Excel

IFF Training

How long?

  • 3 days
  • in person

What are the topics?

IFF Training

Disclaimer

Coursalytics is an independent platform to find, compare, and book executive courses. Coursalytics is not endorsed by, sponsored by, or otherwise affiliated with any business school or university.

Full disclaimer.

Reviews

Make sure this course is right for you.

Get unbiased reviews and personalized recommendations.

About the course

Different types of analysis require that the financial model be built in a particular way. This course covers:

Model Design

  • the different modelling approaches required for:
  • valuation models
  • volatility models in structured financings
  • statistical probability models (Monte Carlo)
  • data manipulation models

Analysis Worksheets For

  • sensitivity analysis
  • scenario analysis
  • breakeven analysis

Modelling Best Practice

  • including the potential consequences of not applying such practices. . Model Review

  • procedures upon receiving a model which has been prepared by others.

How You Will Learn

Trainer Richard Capps has extensive banking experience so can relate the modelling to the real world very well. His practical sessions include:

Setting up the Model; Creating the Forecast of Financial Statements & Cashflows; Modelling Currency Exposures; Breakeven Analysis; Modelling of Operating Costs; IRR and NPV Calculation; Annuity-based Amortisation; Straightline Amortisation; Cash Sweep; Sculpting Loan Amortisations – to ADSCR; Sculpting Loan Amortisations – to LLCR; Cashflow Waterfall; Sensitivity Analysis; Scenario Analysis; Model Administration

Agenda

Modelling Best Practice

There are eight principles of modelling best practice. The different principles will be addressed progressively throughout the programme as opposed to a formal session, with illustrations of the problems if the principles are not respected

Consistent timelines – how to protect

Consistent formulae across rows

Hard coding in formulae

Usability

  • organisation and accessibility of model inputs
  • organisation of summary outputs
  • the use of flags and masks to simplify formulae
  • naming conventions, formatting conventions
  • the Status worksheet

NW to SE workflow

Modelling efficiency

  • organisation of worksheets within a workbook
  • maintaining a log – version control
  • maintaining a data book

Circular references

  • which are permissible and which not
  • work-arounds if the model needs to be audited

Macros

  • the dangers and the precautions that should be taken
  • editing, to minimise complications
  • implementing macros for common purposes
  • looping macros

Model Design and Planning

This session gives an overview of the all-important model planning process.

  • Identifying the purpose and mode of use of the model
  • Designing the Analysis worksheet
  • Planning the logic flow and the interface of the worksheets
  • Determining the flexibilities required and the variable inputs
  • What causes models to be slow
  • Actions to speed up models that are unacceptably slo

Different Model Types

An overview of how the modelling approaches alter in different analytical contexts.

The four types of financial modelling

  • valuation
  • transaction structuring
  • statistical probability
  • data manipulation

The Analysis Worksheet

Models are built to be subjected to analysis. The precise detail and nature of the analysis will change from transaction to transaction. A well-built model has sitting on top of it a single worksheet where the analyst spends all of their time, controlling the inputs, observing the outputs and driving the functionality – scenarios, sensitivities or break-evens. If the analyst ever leaves the Analysis worksheet the model hasn’t been built properly. The explanation is near the beginning of the course, the practical implementation of them at the end of the course.

Demonstration of:

  • sensitivity analysis
  • scenario analysis
  • break-even analysis

Practical Exercise 1 – Setting up the Model

An exercise to group worksheets for formatting purposes, establishing a master timeline, deploying it across all worksheets to block insertion of columns and changes, the creation of flags and masks.

  • Worksheet grouping
  • Data validation
  • Index match
  • Array functions
  • Date
  • EOMont

Models For Valuation & Appraisal Purposes

his is the simpler form of modelling as we are not interested in capturing the volatility of the cashflows. Some pre-existing knowledge of DCF valuation is presumed (otherwise request pre-course reading notes). However it starts with a brief confirmation of the ‘moving parts’. It then demonstrates the errors that are frequently encountered in valuation models. The main focus is on the applicable Analysis worksheet.

Risk and Free Cash Flow (FCF)

Frequent errors in implementing valuation models

  • the impact of the inflation rate
  • matching time horizons of numerator and denominator
  • dealing with currency exposures
  • the derivation of the risk free rate
  • modelling of capital expenditures
  • tax calculations
  • the continuing period
  • obsolescence

Practical Exercise 2 – Creating the Forecast of Financial Statements & Cashflows

The implementation of the projection of P&L and Balance Sheet. Accompanied by a written set of step-by-step instructions and explanations

  • Revenue - with volume and pricing drivers
  • Operating costs
  • Working investment
  • Capital expenditures
  • Depreciation
  • Tax
  • Financing
  • Dividends and distribution

Practical Exercise 3 – Modelling Currency Exposures

Macroeconomic risk is one of the main risk elements. This practical illustrates how currency exposures should be captured in the base case and then subjected to the analysis of both schism risk and divergence risk.

Visual Basic Introduction

The assumption is that the majority of participants will be encountering VBA for the first time. So we start with a familiarisation of the features.

  • The Project Explorer, properties and code windows
  • VBA best practice rules
  • Declarations and variables
  • Understanding procedures
  • Using objects

Practical Exercise 4 – Breakeven Analysis

A practical implementation of an analysis worksheet that identifies the breakeven power tariff to deliver a target Debt Service Cover covenant. Involves practical implementation of VBA coding

  • Use of VBA to automate breakeven calculations
  • VBA IF statements
  • Pasting results automatically through VBA

Volatility Modelling

In highly-stressed financings where there is little margin for error (e.g. project finance, leveraged finance, large scale M&A) the ‘killer’ is the volatility of the cashflow. The Base Case never kills, but then the Base Case never happens – something else does. Volatility models have to be built a certain way in order to accurately quantify that volatility under a range of possible scenarios.

  • Project dynamics
  • Modelling of cost structures
  • Quantification of ris

Practical Exercise 5 – Modelling of Operating Cost

Procedures Upon Receiving a Model

Any analysis performed on a model is nonsense if the model itself is nonsense or if it has material errors. There is no shortcut to model audit - to ensure that there are no errors at all - every unique formula in the model would have to be checked. But Model Review is a procedure that allows a recipient to discover if the model has credibility within a maximum time-frame of 30-40 minute.

The recommended layout and inter-relationship of worksheets for a typical structured finance model

Shortcuts to determine a received model’s architecture

The use of audit software

  • detecting breach of excel best practice rules
  • listing of formulae and cell references that need checking

Tracing the logic flow

IRR and NPV

A majority of models implement NPV and IRR calculations in an erroneous way. This session explains the errors made and then illustrates the correct calculation for the various types of model being prepared.

  • A review of what IRR represents
  • What’s wrong with NPV, IRR and XIRR functions
  • Correct calculation of IRR

Practical Exercise 6 – IRR and NPV Calculation

A couple of practicals to implement IRR and NPV derivation on a model.

Financing

There are a number of different financing methodologies which involve a different modelling approach for the calculations of interest expense and loan amortisation. A series of practicals address the majority of structures encountered.

Financier worksheets in

  • valuation models
  • leveraged finance
  • project financing – infrastructure
  • project financing – extractive industrie

Practical Exercise 7 – Annuity-based Amortisation

A short practical to illustrate the functions used to populate cashflow, income statement and balance sheet for the amortisation of debt structured on annuity basis.

Practical Exercise 8 – Straightline Amortisation

A more involved practical to demonstrate a worksheet which is fully responsive when any of the terms of the financing are altered.

Practical Exercise 9 – Cash Sweep

Introducing a cash sweep to the previous exercise.

Practical Exercise 10 – Sculpting Loan Amortisations – to ADSCR

Implementation of a loan amortisation that matches the cash available for debt service on a dynamic basis.

Practical Exercise 11 – Sculpting Loan Amortisations – to LLCR

Implementation of a loan amortisation that matches the cash available for debt service on a dynamic basis.

Practical Exercise 12 – Cashflow Waterfall

For limited recourse and leveraged financings – an implementation of a cashflow waterfall with lockup.

Analysis Worksheets

A practical for breakeven was covered previously. We conclude with the development of worksheets to process each of sensitivity analysis and scenario analysis.

Practical Exercise 13 – Sensitivity Analysis

An extensive practical for building an analysis worksheet based on Sensitivity analysis, allowing remote control of the inputs worksheet and instant output presentation of all KPIs. This practical is estimated to take approx 4 hours.

Practical Exercise 14 – Scenario Analysis

Implementation of various alternative methodologies for controlling and exhibiting the outputs of scenario analysis.

Practical Exercise 15 – Model Administration

Practicals to implement a Status Check worksheet to debug models and the creation of a model log.

Experts

Richard Capps

Richard obtained a law degree from Cambridge University and was called to the English Bar in 1976. Shortly thereafter he entered banking joining Chase Manhattan Bank where, for most of the 5 years service, he was a corporate relationship manager in London and New York. For the following 6 years ...

Videos and materials

Financial Modelling Using Excel at IFF Training

From  3599 GBP$4,632

Something went wrong. We're trying to fix this error.

Thank you for your application

We will contact the provider to ensure that seats are available and, if there is an admissions process, that you satisfy any requirements or prerequisites.

We may ask you for additional information.

To finalize your enrollment we will be in touch shortly.

Disclaimer

Coursalytics is an independent platform to find, compare, and book executive courses. Coursalytics is not endorsed by, sponsored by, or otherwise affiliated with any business school or university.

Full disclaimer.

Because of COVID-19, many providers are cancelling or postponing in-person programs or providing online participation options.

We are happy to help you find a suitable online alternative.