Euromoney Audit Training

The principal aim of the course is to enable participants to create, use and analyse a project finance model. These skills can be used to support credit approvals and reviews by lenders and to support organisations which run or sponsor projects. This will be done by reviewing best practice in model structures and logic, and using tools to highlight areas of risk, particularly in sensitivity analysis.

The 3-day programme features:

• Essential model lay-outs and rules
• Incorporation of risk into the model
• How to translate the project documents into the model
• Matching appropriate domestic and foreign taxes in the model
• Calculation & advanced analysis of essential items such as:
• Liquidated damages
• 'Pricing the Deal' and scenario analysis
• Auditing techniques for new and existing models

Teaching Methodology:

The learning methods used are practical, as practice of newly-learned techniques enables a deeper and more effective building of skills. Each section will be covered briefly as a module in a traditional class style, but the real learning experience will be found in the exercises within each module. Suggested solutions to each exercise will be provided and discussed, and participants will be encouraged to review their work independently.

Course Instructor:

The course will be led by project finance modelling expert Alan Brooke MBA, CA (SA) CA (NZ). Alan has extensive experience in financial modelling in different sectors, including projects in the property development sector, major utilities and government departments. This has involved building, developing and using models to support negotiations, analyse risk, test scenarios and forecast results. With an extensive accounting background, Alan also brings accounting knowledge and analytical skills to transactions and financial modelling.

Day 1

Introduction & Course Objectives

• Brief overview of project finance
• Review of models and their objectives
• Introduction of a simple model and its components

Overall Model Structure

• Best financial modelling practice
• Overall structure of the model
• Separation of inputs, calculations and outputs
• Logic flow within the model
• Use of switches to allow option selection
• Use of flags to control timing factors
• Set-up to ease flexibility
• Accommodating multiple options at early stages of project
• Checks and totals, and error reporting

Inputs & Assumptions

• Principles of model structure
• Building assumptions off the term sheets
• Using the assumptions sheets as a sign-off document
• Building-in ability to change and work changes through the model
• Restricting ranges of inputs and validation criteria
• Version control
• Tracking changes

Exercise – creating an assumptions input sheet with built-in flexibility.

Revenue & Cost Build-Ups

• Build-up of construction or other capital costs
• Correct matching of units
• Treatment of fixed and variable costs
• Use of maintenance reserve accounts
• Pricing assumptions
• Use of lookup functions to change expenditure timings
• Building in sensitivities

Taxes

• Tax treatment of costs
• Allowing for deductibility and non-deductibility
• Capital allowances
• Cash versus accounting treatment

Interest and Fee Calculations

• Circularity and consequences
• Calculations of interest and fees
• Timing of payments
• Cash flow payment vs. amortisation in the P&L
• Capitalised fees and interest

Exercise – from given term sheet of interest rates and fees, model interest and fee cash flow and P&L effects.

Day 2

Financing Section

• Leverage, risk and the debt/equity equation
• Calculating the cost of different types of debt capital
• Cost of equity capital
• Use of Debt Service Reserve Accounts
• (DSRA)
• Use of the cash flow waterfall
• Modelling issues arising:
• Timing of debt and equity funding
• Fee costs, upfront and spread
• Interest costs, capitalised interest
• Interest rate ratchets
• Debt repayment profiles
• Rate switches or refinancings at various stages of deal
• DSRA interest margin
• Debt repayment profiles and built-in options
• Dividend and other equity returns
• Constraints on dividend payments
• Overall risk profile

Exercise – creation of simple model to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints.

Modelling Multiple Drawdowns

• Cash flow driven
• Cash positive periods and interest earned
• Debt service reserve accounts
• Fees to be included in drawdown amounts
• Multiple facilities

Exercise – given a pattern of cash flows, calculate facility drawdowns and related interest cost and earned.

Multi-Currency Modelling

• Modelling foreign exchange rates
• Dealing with changes in exchange rates
• Comparing actual with forecast

Exercise 1 – for a given pattern of cash flows in various currencies, model the amounts to be drawn in each currency.

Exercise 2 – compare modelled rates and actual, and separate effect of exchange rate changes from other variances.

Inflation/Escalation Factors

• Use of indices
• Controlling start time of inflationary pattern
• Applying multiple rates to different cost & revenue items
• Varying inflation rates over life of the project
• Comparing the effect of actual inflation vs modelled

Exercise – model multiple, variable rates and analyse a separate set of actual rates.

Day 3

Creation of Balance Sheet

• Link between modelled cash flow and P&L
• Key balance sheet items and their calculation
• Non-cash items: depreciation, deferred tax
• Assumptions required to be made
• Use of existing figures or opening balance sheets
• Creation of check totals

Exercise – from a given P&L and cash flow statement, calculate balance sheet.

Derivation of Ratios

• Cash available for distribution and free cash flow
• Debt service coverage reserve ratios
• Interest cover ratios
• Equity returns
• IRR & NPV calculations
• Components of the weighted average cost of capital ("WACC")

Exercise – from a given cash flow and balance sheet, calculate the above ratios.

Comparing Actuals to Previously Modelled Results

• Separate runs and variation of inputs
• Ability to compare results
• Reviewing future implications of variances

Exercise – from a modelled forecast and actual results, calculate variances and project the resulting future model changes.

Sensitivity Analysis

• Break-even calculations
• Stress-testing of model
• Varying inputs to assess effect on results
• Use of goal seek
• Use of statistical techniques – probabilities and Monte Carlo simulations
• Version control to allow comparison of outputs
• Comparison of actual results against forecast as a sensitivity analysis

Exercise – from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even.

Risk Reviews

• Use of risk matrices
• Relationship to model and sensitivity analysis
• Probability analysis
• Risk-adjusted returns – equity view
• Risk-adjusted returns – lender’s view

Exercise 1 – for a given model, calculate riskadjusted returns from potential risks in the project.

Exercise 2 – perform a risk assessment applicable to participants’ own organisations, and model probability-weighted outcomes.

Documenting the Model

• Setting up base case model
• Recording changes to model structure
• Recording changes to assumptions
• User guides
• Running scenarios: descriptions, comparisons to base, version control

Wrap-Up

• Overall review
• Key points to re-iterate
• Brief introduction to further exercises
• Final questions and issues to discuss

• Alan Brooke

  Alan Brooke, MBA, CA(SA), CA(NZ)
  Consultant
  
  Alan has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney since 2005.
  
  Alan trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed Sales Planning Manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia, as their General Manager Finance; in this role, he guided the group through a period of major change and financial turnaround.
  
  For the past 11 years, Alan has worked as a freelance financial modeller and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multibillion pound franchise bids in the UK rail industry, forecasting models for private equity investment in the waste management sector, and a number of PFI transactions in the utilities, health and support services sectors. With an extensive accounting background, Alan brings accounting knowledge and analytical skills to transactions and financial modelling.
  
  Alan has built up a lot of experience in financial modelling in different sectors, including property, insurance, outsourcing, utilities, transport, energy sectors, as well as central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

  Courses run by this instructor

  • Financial Modelling in Excel - Dubai

Interested in holding this course in-house? Please fill out your details and a member of our team will be in touch with more information.

MENA Project Finance Academy - Dubai

2-10 Sep 2012 (Dubai, United Arab Emirates)

A 7-day programme created specifically for the MENA region and designed to provide delegates with the skills they will need to evaluate project financing transactions in a wide variety of industrial sectors.

Advanced Project Finance Workshop - Riyadh

2-6 Dec 2012 (Riyadh, Saudi Arabia)

This unique 5-day training course examines the latest structuring, funding and cashflow modelling techniques.