Euromoney Audit Training

Course overview 

'Project Finance Modelling' is designed to help middle and senior managers deal with financial models from disparate sources, enabling them to be compared and analysed on a consistent and focussed basis.

The principal aim of the course is to teach 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.

Summary of course content

• Modelling best practice
• Building models that accommodate change
• Building integrated models that link cash flow, income statement and balance sheet seamlessly
• Building cash flow cascades to forecast debt capacity and investor returns
• Easy ways to model tax and inflation
• Modelling project risk

Methodology

This course is hands-on and practical, as application 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. As the time available is limited, and the needs of the participants will vary, each
section will not be covered in depth, but supporting materials will be available for further in-depth learning
according to participants' own needs.

Computer-based exercises 

All participants should bring a laptop loaded with Excel 2003 or later, with a CD drive or USB port in order to
transfer example files to be worked on in class.

Who should attend this training course?

• Project finance analysts
• Credit analysts
• Project sponsor analysts and financial controllers
• Financial consultants to project sponsors and financiers

Supporting publications

 

 

  

DAY ONE

Introduction and 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 and 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 and 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

Example: Model with flexible capital expenditure and sensitivity functions

Inflation / escalation factors

• Use of indices
• Controlling start time of inflationary pattern
• Applying multiple rates to different cost and 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 TWO

Interest and fee calculations

• Circularity, consequences and solutions
• Calculations of interest and fees
• Timing of payments
• Cash flow payment vs. amortization 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

Taxes

• Tax treatment of costs
• Allowing for deductibility and non-deductibility
• Capital allowances
• Cash vs. accounting treatment
• Tax losses generated
• Modelling techniques

Example: Review of tax calculations from an actual project finance model

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

DAY THREE

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 future model changes

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 and NPV calculations
• Components of the weighted average cost of capital ("WACC")

Exercise: From a given cash flow and balance sheet, calculate the above ratios

Sensitivity analysis

• Break-even calculations
• Stress-testing of model
• Varying inputs to assess effect on results
• Use of Excel tools, including goal seek, data tables and scenario manager
• 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'sview
• Risk-adjusted returns – lender's view

Exercise: For a given model, calculate risk-adjusted returns from potential risks in the project

Documenting the model

• Setting up base case model
• Recording changes to model structure
• Recording changes to assumptions
• User guides and data books
• 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

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.

Financing Energy Projects: Oil, Gas and Power

2-6 Jul 2012 (Singapore, Singapore)
10-14 Dec 2012 (Hong Kong, Hong Kong)

A practical guide shows you how to structure upstream and downstream oil and gas projects including field development, rigs, platforms, pipelines, and other transportation infrastructure

Renewable Energy Project Finance

18-20 Sep 2012 (Singapore, Singapore)

This course shows you the most effective approaches to financing renewable projects in many parts of the world, focusing on how these transactions differ from more conventional deals

Infrastructure Project Finance

15 Oct 2012 (Sydney, NSW, Australia)

Euromoney Training presents, 'Infrastructure Project Finance', intended for those who have an interest in the project finance aspects of the different infrastruture sectors and the risks involved. This is Module 3 of the 2nd Annual Asia Project Finance Academy. Modules are bookable separately or you can attend the whole Academy saving US$6,850

Mining Project Finance

16 Oct 2012 (Sydney, NSW, Australia)

Euromoney Training presents, 'Mining Project Finance', intended for those who have an interest in the area of project financing in the Mining industry and identifying the opportunities and potential pitfalls with this commodity-based project finance area. This is Module Four of the 2nd Annual Asia Project Finance Academy. Modules are bookable separately or you can attend the whole Academy saving US$6,850

Building Project Finance Models

19 Oct 2012 (Sydney, NSW, Australia)

Euromoney Training presents, 'Building Project Finance Models'. This is Module Six of the 2nd Annual Asia Project Finance Academy. Modules are bookable separately or you can attend the whole Academy saving US$6,850

3rd APAC Project Finance Academy

8-19 Oct 2012 (Sydney, NSW, Australia)

The Euromoney Training Asia-Pacific Project Finance Academy is a comprehensive 10-day academy that examines advanced techniques relevant to the current stressed global financial markets and enhances your ‘sector’ skills in three key areas.

Project Finance for PPPs

17-18 Oct 2012 (Sydney, NSW, Australia)

A 2-day workshop on the application of Project Finance to Public- Private Programmes (PPPs) with a special session on which PPP programmes work and which do not. This is Module Five of the 2nd Annual Asia Project Finance Academy. Modules are bookable separately or you can attend the whole Academy saving US$6,850

Advanced Project Finance Workshop

8-10 Oct 2012 (Sydney, Australia)

Intended for those who have an interest in the risk management and structuring aspects of Project Finance projects, it focuses on the core skills of risk analysis and structuring, with a special emphasis on political risk.

Energy Project Finance

11-12 Oct 2012 (Sydney, Australia)

This course examines advanced techniques relevant to the current stressed global financial markets and provides you with up-to-date sectors skills at financing energy projects.