Course dates
This 3-day course covers:
- Best financial modelling practice
- Revenue and cost build-ups
- Taxes, interest and fee calculation
- Leverage, risk and the debt/equity equation
- Modelling multiple drawdowns
- Multiple currency modelling
- Inflation/escalation factors
- Derivation of ratios
- Risk reviews
Course aims
The principal aim of the course is 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.
Course 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 PC-based 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.
Course level
All attendees must be familiar with the use of Excel spreadsheets for financial modelling purposes. Delegates are likely to be working with an existing PF model which requires updating or refining.
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. 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
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 – for a given pattern of cash flows in various currencies, model the amounts to be drawn in each currency
Separate exercise – 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 future model changes resulting
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 – for a given model, calculate risk-adjusted returns from potential risks in the project
Exercise - perform a risk assessment applicable to participants’ own organizations, 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
Amsterdam Hotel, Amsterdam, Netherlands
This programme takes place on a non-residential basis at a central Amsterdam hotel. Non-residential course fees include training facilities, documentation, lunches and refreshments for the duration of the programme. Delegates are responsible for arranging their own accommodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.
-
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.
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.
Course dates