Course dates
Featuring:
- Revision of best practice in model structures.
- Building the cash flow financing section of a model.
- Calculating the cost of different types of debt capital.
- Creation of balance sheet.
- Use of sensitivity analysis - model structures and advanced Excel tools.
- Ratios and risk analysis of PPP deals.
- Review the objectives of PPP, and the implications for model structure.
Course Exercises
Time will not permit creation of an entire model from scratch. The outline of a model will be populated with the required factors, including basic assumptions, input data and calculations to calculate results. Delegates will then build on the model to add sensitivities, inflation factors, financial structures, accounting statements, equity returns, ratios and cover factors, and risk assessments.
Course Methodology
The learning methods used are practical, as practice of newlylearned 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.
Supported by:
Day 1
Introduction & course objectives
- Revision of best practice in model structures.
- 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.
- Use of corkscrews.
- Checks and totals, and error reporting.
- Inputs & assumptions.
- 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 Debt Service Reserve Accounts and Maintenance Reserve Accounts.
- Pricing assumptions.
- Use of lookup functions to change expenditure timings.
- Building in sensitivities.
Exercise – building in flexibility for capital spend timing changes and sensitivities.
Day 2
- Brief overview of modelling taxes.
- Tax treatment of costs.
- Allowing for deductibility and non-deductibility.
- Capital allowances.
- Cash versus accounting treatment.
- Example - Review of an example of tax modelling for an investment project.
- 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 a given term sheet of interest rates and fees, model interest and fee cash flow and P&L effects.
- Building the cashflow financing section of a model.
- Cash flow driven.
- Cash positive periods and interest earned.
- Debt service reserve accounts.
- Fees to be included in drawdown amounts.
- Use of multiple facilities for different purposes.
- 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.
- 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 modeled.
- Introduce exercise to do outside class – model multiple, variable rates and analyse a separate set of actual rates.
Exercise: from a given P&L.
Day 3
- Creation of balance sheet.
- Link between modeled 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 the balance sheet.
- Derivation of ratios.
- Cash available for distribution and free cash flow.
- Debt service coverage ratio.
- Interest cover ratio.
- Equity returns: IRR & NPV calculations.
- Best practice in calculation & presentation of ratios in the model.
Exercise – from a given cash flow and balance sheet, calculate the above ratios.
- Comparing different updates & versions of the model.
- Separate runs and variation of inputs.
- Ability to compare results.
- Reviewing future implications of variances.
Example – creating a comparison worksheet to enable variance analysis of any two versions of a model.
- 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.
Excercise: 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 & lender’s view.
- Excercise:
- For a given model, calculate risk-adjusted returns from potential project risks.
- Perform a risk assessment applicable to participants’ own projects, 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.
Day 4 – Extra day
- Overview of PPP.
- What is PPP and how is it different?
- The objectives of PPP deals.
- The parties to a PPP deal.
- The structure of a typical PPP deal.
- Different forms of service delivery and/or construction delivery: BOT, BOO, BTO, DBFO, BBMT.
- Risk profiles of PPP deals.
- Examples of, and reasons for, PPP failure. Only when the nature of PPP is understood, can modelling of its structure commence.
- Structure & layout of models.
- Review the objectives of PPP, and the implications for model structure.
- Implications for deal of fixed deal end-dates.
- Tax implications and unused capital allowances.
- Practical tips in building models.
- Financing section.
- Simple financial structures used in PPP deals:
- Local Currency.
- Foreign Currency.
- Mezzanine.
- Preference Capital.
- Convertibles.
- Contingent.
- IPOs/Floats at project start or at exit.
- Credit Guarantees.
- Buy-Back/Put Options.
- Renationalisation Rights.
- Leasing/Leveraged Leasing.
- Monoline Insurers.
- Financing constraints:
- use of covenants.
- restrictive ratios.
- debt service cover factors.
- loan cover factors.
- loan to value calculations.
- Returns to equity – NPV, IRR, other measures.
- Comparators.
- Public sector comparators (PSC) and their role.
- Using comparators to assess overall deal.
- Review of actual PSC example.
- Wrap-up.
- Overall review.
- Key points to re-iterate.
- Brief introduction to further exercises.
- Final questions and issues to discuss.
Johannesburg Hotel, Johannesburg, South Africa
This programme takes place on a non-residential basis at a central Johannesburg 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.
Courses run by this instructor
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