Preliminary Comments
- A review of the essential elements of a project financing structures;
- The role of the model within the overall process;
- Project Financing risk and its management;
Model Design
Financiers focus on sculpted debt serviceability whilst Sponsors focus on the valuation – the Sponsor IRR that the project generates. The course will embrace both perspectives.
different analytical purposes = different methods of modelling implementation
- different analytical purposes = different methods of modelling implementation
- valuation and feasibility models;
- finance structuring models;
- statistical probability models;
- data manipulation models.
- designing the Analysis Worksheet;
- determining the functionality of the model;
- structuring how inputs will be accessed and controlled;
- the importance of the logic flow through the model;
- the layout of the worksheets within the workbook;
- the layout of the individual worksheets;
Modelling Best Practice
There are 8 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
- Circular references:
- which are permissible and which not;
- workarounds 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;
Practical 1
- the creation and formatting of the worksheets;
- setting up timelines and flags;
Construction Phase
A critical worksheet within a project financing financial model. However, there is a large commonality in its structure across all sectors.
- identifying (and explaining) the elements the model needs to capture:
- cost overruns;
- delays;
- variation orders;
- staged payments;
- maintenance and refurbishment cycle;
- liquidated damages;
- performance bonds;
- retentions;
- multiple currencies;
- the financing structure;
- multiple tranche drawdowns;
- interest during construction;
- standby financings
Practical 2
- implementing the construction worksheet;
Volatility Modelling Explained
There are 4 types of operating cost from a modelling perspective and 6 characteristics that need to be captured. The Operating Costs worksheet is the most important in the model because it is the main determinant of cashflow volatility
- Project dynamics;
- Modelling of cost structures;
- Quantification of risk;
Practical 3
- Modelling of Operating Costs
Generation, Revenue and Operating Costs
Having dealt with operating costs in a generic context, it is proposed to adapt it to a financial model for power generation project, since power generation is the largest sector to use project financing structures.
- identifying (and explaining) the elements the model needs to capture:
- rated capacity and internal works power;
- plant load factor;
- planned outages
- unscheduled outages;
- the heat rate;
- degradation;
- thermal efficiency of fuel;
- carbon content of fuel;
- various other operating costs
Practical 4
- implementing the model to Free Cash Flow stage;
Financing
Because the bank is active across all sectors, a number of different financing worksheets will be encountered. We review each and then implement them in a series of practicals.
- identifying (and explaining) the elements the model needs to capture:
- annuity-style amortisation;
- straightline amortisation;
- sculpted amortisation;
- cash sweeps
- loan life cover ratio
- annual debt service cover ratio
- cashflow lockups
Practicals 5++
- Implementing several different styles of financing worksheet – annuity, straightline, sculpted to ADSCR (average debt service coverage ratio), sculpted to LLCR (loan life coverage ratio), cash sweeps.
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 minutes.
- The recommended layout and inter-relationship of worksheets for a typical project finance model;
- shortcuts to determine a received models architecture;
- the use of audit software:
- detecting breaches of excel best practice rules;
- listing of formulas and cell references that need checking;
- tracing the logic flow;
Analysis
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 his/her time, controlling the inputs, observing the outputs and driving the functionality – scenarios, sensitivities or break-evens. If the analyst ever needs to leave the Analysis worksheet the model hasn’t been built properly.
- identifying (and explaining) the elements the model needs to capture:
- sensitivity analysis;
- breakeven analysis;
- scenario analysis.