Project Finance Modelling

3 days 9-11 May 2016, Rio de Janeiro $3,500.00 Download brochure Add to basket
3 days 17-19 Aug 2016, Singapore $4,650.00 Download brochure Add to basket
3 days 17-19 Oct 2016, New York $5,190.00 Download brochure Add to basket
4 days 24-27 Oct 2016, London £4,295.00 + VAT* Download brochure Add to basket
4 days 7-10 Nov 2016, Johannesburg £3,345.00 Download brochure Add to basket
3 days 13-15 Dec 2016, Dubai £3,545.00 Download brochure Add to basket

* Claim back your VAT
Find out more

Request a different date or location for this course (we regularly add courses following requests).

{{alternativeRequestSuccess}}
{{alternativeRequestError}}

* Claim back your VAT
Find out more

Overview

FTS Eligible - Financial Training Scheme for Singaporean citizens and PRs only (more information)

Course objectives

This course will review lessons on valuation, risk assessment and forecasting that can be derived from the financial crisis. Participants will discuss mistakes made in valuing sub-prime loans and other famous valuation errors. This provides context for other subjects in the course.

Summary of course content

  • Valuation lessons from the current financial crisis
  • How structured financial models can be created that clearly define risks and value without being overly complex
  • The fundamental factors that underlie valuation and how can they be used in practice
  • What lies behind multiples such as the P/E and the EV/EBITDA ratios and how these multiples can be used in valuation
  • How to effectively present risk analysis in valuation analysis models
  • Whether Monte Carlo simulation and mathematical techniques can realistically be used to assess risk and compute value
  • The valuation of debt and measurement of debt capacity to provide alternative ways to value investments
  • The creation of models specifically used to evaluate LBOs, project finance and M&A

Methodology

Case studies, hands-on analysis and template models will be used as the primary teaching tools in the programme. If you are interested in practical mechanics of excel (macros, combo boxes, offset and indirect functions etc.) these can also be discussed after the course.

Other than the instruction in how to build, use and analyse financial models, you will receive a comprehensive suite of financial modeling software on CD that includes a number of template models and Excel add-ins.

The software consists of corporate models that accept historic financial data and generate alternative valuation measures; M&A models that consolidate two companies using alternative financing assumptions and produce accretion and dilution estimates; project finance models that measure the effect of alternative elements in a cash flow waterfall including debt service reserves, junior debt, covenants, defaults and pre-payments; LBO models that measure the debt capacity of a transaction; option pricing models that account for alternative structures; and debt valuation spreadsheets, Monte Carlo simulation models, tornado diagram and sensitivity analysis.

Computer-based exercises

This course will make extensive use of modelling exercises in Excel®
All delegates should bring their own laptops loaded with Microsoft Excel® 2010 or later to facilitate inclass
studies and exercises.

Free CD to take-away

Delegates will receive a comprehensive suite of financial modelling software on a CD that includes a number of template models and excel add-ins.

Pre-course survey

Book early and fill in our pre-course survey to ensure your specific needs are met in the course by the trainer.

FTS Eligible

This programme is approved for listing on the Financial Training Scheme (FTS) Programme Directory and is eligible for FTS claims subject to all eligibility criteria being met.

Please note that in no way does this represent an endorsement of the quality of the training provider and programme. Participants are advised to assess the suitability of the programme and its relevance to participants’ business activities or job roles.

The FTS is available to eligible entities, at a 50% funding level of programme fees, subject to a cap of $2,000/participant/programme and all eligibility criteria being met. FTS claims may only be made for programmes listed on the FTS Programme Directory with specified validity period. Please refer to www.ibf.org.sg for more information.

Who should attend

  • Corporate Finance/Corporate Treasury
  • Capital Markets
  • Audit/Product Control/Risk Management/ALM
  • Research and Analysis
  • Sales and Trading
  • Investment Management
  • Origination
  • Securitisation/Syndication
  • Structured Finance
  • Money Markets/Repo
  • Systems Programming
  • Funding
  • Government/Agency Funding and Investment
  • Regulation/Compliance/Documentation
  • Instructors

    We work with a series of expert instructors, please select the course location of interest to review the credentials of who will be delivering the programme.

    Dubai
    Alan Brooke

    The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

    He 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 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion 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, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

    He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

    Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

    Singapore
    Ed Bodmer
    Ed has created innovative forward pricing, productivity measurement and investment valuation software for consulting clients throughout the United States. He has taught energy economics and finance throughout the world, and formulated significant government policy and corporate strategy in the U.S. 

    His consulting clients include investment banks, commercial banks, research institutions and government agencies on a wide variety of complex valuation and advisory matters. He has constructed a unique framework for electricity price forecasting and valuation using production cost modelling techniques combined with option price theory and Monte Carlo simulation.

    He is also an adjunct professor at leading University where he teaches courses in microeconomics. Along with his practical experience that covers a multitude of major advisory projects, he has taught specialised courses in financial modelling, electricity pricing, option valuation, mergers and acquisitions and contracting to investment banks, commercial banks, industrial corporations and electric utility companies.

    He was formerly Vice President at the First National Bank of Chicago where he directed analysis of energy loans and also created financial modelling techniques used in advisory projects. He has used the models in providing expert testimony on subjects ranging from capital structure to investments in multi-billion dollar nuclear plants to complex valuation of new investments.

    He received an MBA degree specialising in econometrics (with honours) from the University of Chicago and a BS degree in finance from the University of Illinois (with highest university honours). He has written many articles and is in the process of completing a textbook on valuation of electricity assets.
    Johannesburg
    Alan Brooke

    The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

    He 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 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion 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, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

    He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

    Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

    London
    Paul Olson
    The course instructor is an expert in Project Finance Modelling.

    He is an experienced financial modeller and highly software literate. Following an initial career in aerospace market forecasting he became a business analyst at Rolls-Royce Power Ventures (RRPV) responsible for producing financial models and investment analysis for project finance and equity funded power projects. 

    He has developed financial models for: * Gas fired power stations throughout the world, * Several wind power projects in the UK, * A coal fired power station in Africa * A number of UK PFI health projects * Electricity transmission networks in Eastern Europe * A desalination project in the Middle East * Combined water and power projects in the Middle East. * Valuation of a portfolio of renewable generation in Greece.

    He currently works for Project Financing Solutions, a boutique project finance advisory firm that has advised three of the ten European Greenfield IPPs that have closed in the last four years.
    Rio de Janeiro
    Ed Bodmer
    Ed has created innovative forward pricing, productivity measurement and investment valuation software for consulting clients throughout the United States. He has taught energy economics and finance throughout the world, and formulated significant government policy and corporate strategy in the U.S. 

    His consulting clients include investment banks, commercial banks, research institutions and government agencies on a wide variety of complex valuation and advisory matters. He has constructed a unique framework for electricity price forecasting and valuation using production cost modelling techniques combined with option price theory and Monte Carlo simulation.

    He is also an adjunct professor at leading University where he teaches courses in microeconomics. Along with his practical experience that covers a multitude of major advisory projects, he has taught specialised courses in financial modelling, electricity pricing, option valuation, mergers and acquisitions and contracting to investment banks, commercial banks, industrial corporations and electric utility companies.

    He was formerly Vice President at the First National Bank of Chicago where he directed analysis of energy loans and also created financial modelling techniques used in advisory projects. He has used the models in providing expert testimony on subjects ranging from capital structure to investments in multi-billion dollar nuclear plants to complex valuation of new investments.

    He received an MBA degree specialising in econometrics (with honours) from the University of Chicago and a BS degree in finance from the University of Illinois (with highest university honours). He has written many articles and is in the process of completing a textbook on valuation of electricity assets.
    New York
    Ed Bodmer
    Ed has created innovative forward pricing, productivity measurement and investment valuation software for consulting clients throughout the United States. He has taught energy economics and finance throughout the world, and formulated significant government policy and corporate strategy in the U.S. 

    His consulting clients include investment banks, commercial banks, research institutions and government agencies on a wide variety of complex valuation and advisory matters. He has constructed a unique framework for electricity price forecasting and valuation using production cost modelling techniques combined with option price theory and Monte Carlo simulation.

    He is also an adjunct professor at leading University where he teaches courses in microeconomics. Along with his practical experience that covers a multitude of major advisory projects, he has taught specialised courses in financial modelling, electricity pricing, option valuation, mergers and acquisitions and contracting to investment banks, commercial banks, industrial corporations and electric utility companies.

    He was formerly Vice President at the First National Bank of Chicago where he directed analysis of energy loans and also created financial modelling techniques used in advisory projects. He has used the models in providing expert testimony on subjects ranging from capital structure to investments in multi-billion dollar nuclear plants to complex valuation of new investments.

    He received an MBA degree specialising in econometrics (with honours) from the University of Chicago and a BS degree in finance from the University of Illinois (with highest university honours). He has written many articles and is in the process of completing a textbook on valuation of electricity assets.

    Venue

    Rio de Janeiro

    Rio de Janeiro

    This program takes place on a non-residential basis at a central Rio de Janeiro hotel. Non-residential course fees include training facilities, documentation, lunches and refreshments for the duration of the program. Delegates are responsible for arranging their own accommodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.

     

    Singapore

    4-5 Star Hotel in Singapore

    All of our courses are held in 4 – 5 star hotels, chosen for their location, facilities and level of service. You can be assured of a comfortable, convenient learning environment throughout the duration of the course.

    Due to the variation in delegate numbers, we will send confirmation of the venue to you approximately 2 weeks before the start of the course. 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.

    New York

    New York Hotel

    This program takes place on a non-residential basis at a New York 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.

    As with all programmes on-site administrators are with you throughout the programme to ensure smooth administration and group interaction.

    London

    Central London Hotel Venue

    All courses are held at four or five star venues in Central London, Zone 1. We strive to provide you with a training environment of the highest quality, to ensure that the whole learning experience exceeds your expectations.

    Your training venue will be confirmed by one of our course administrators approximately 3-4 weeks before the course start date.

    Johannesburg

    Johannesburg Hotel

    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.

    Dubai

    Dubai Finance

    This programme takes place on a non-residential basis at a central 4 to 5* Dubai 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.

    Related Courses

    Agenda

    Agendas are localised, please select your preferred location.

    Day 1

    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

    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 mulitple 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

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

    Course summary and close

    *FTS Eligible - Financial Training Scheme for Singaporean citizens and PRs only (more information)

    Day 1: Multiples and discounted cash flow

    Key themes and concepts

    Measuring and managing risk as well as translating risk into valuation for decision making will be emphasised along with strategic and economic drivers of value.

    • Mechanical aspects of valuation
    • Understanding financial ratios used in valuation
    • The appropriate use of multiples (P/E vs. EV/EBITDA)
    • Choosing among alternative techniques and assuring that the valuation techniques make sense

    Valuation fundamentals and mechanics

    Introduce general categories of valuation models and establish a foundation in valuation mechanics. The discussion will focus on how value is created by a company.

    • Real sources of value - cash flow, discounting and growth
    • Valuation of risky bonds and excel short-cuts
    • Derivation of P/E ratios and real world discounting problems
    • Fundamental basis of DCF and major problems with the DCF

    Valuation using multiples

    Participants work with multiples in developing valuations, focusing on which multiple is most appropriate for alternative business circumstances.

    • Valuation using multiples
    • Examples of multiples
    • Reconciliation between alternative multiples
    • Appropriate multiples in different circumstances
    • Problems with multiples

    Valuation using discounted cash flow (DCF)

    Participants learn DCF valuation in theory and practice.

    • Construction of simple valuation model for computing DCF
    • Problems with valuation ranges in the DCF
    • Use of multiples in terminal value
    • Valuation from earnings growth

    Case study: Implementation of valuation

    Real world issues in applying the DCF model

    Participants learn how to deal with real world complications that arise in applying the DCF model.

    • Computation of stable working capital changes for terminal value
    • Realistic relationships between growth and cost of capital
    • Construction of value drivers to determine appropriate EV/EBITDA multiples
    • Appropriate modelling of deferred taxes and NOL carryforwards
    • Alternative ways of dealing with management stock options
    • Incorporation of high coupon debt in valuation
    • Relationship between capital expenditures and depreciation
    • Derivative valuations and the DCF model

    Day 2: Corporate modelling for valuation

    The models created in the first day are extended to include the central corporate finance issues of measuring value and assessing risk, culminating in a comprehensive corporate model.

    Corporate model structure

    Forecasting models are the centrepiece of most valuation analyses. Common valuation errors made in corporate models will be discussed. The errors are normally conceptual and logic mistakes in the model from a business perspective. In discussing model structure, the course covers:

    • Model objectives
    • Mistakes in modelling (base case definition, ignoring financial
    • ratios, capital expenditure consistency, growth rates, business
    • cycles)
    • Model structure for alternative transactions (corporate,
    • project finance, leveraged buyout and M&A models)
    • Model layout (inputs, working analysis, debt structure,
    • financial statements)
    • Financial statement analysis for modelling
    • Model complexities (depreciation, taxes, circularity, minority interest, deferred taxes)

    Construction of a corporate model

    Participants will construct a corporate model from A to Z. The model exercise includes a discussion of spreadsheet conventions such as the positive number convention, organisation of spreadsheets, use of range names and construction of "cork screw" accounts.

    • Objectives of well designed models
    • Model organisation (sheet order, repeating inputs, sheet
    • colours, sheet columns)
    • Spreadsheet conventions (positive number, switches,
    • corkscrews, switches)
    • Simple formulas (formula length, max and min statements,
    • range names)
    • Model documentation (macro names, column titles, units)
    • Auditing and error checking

    Case study: Corporate model

    • Construction of working analysis, debt structure and financial statements
    • Calculation of debt and cash plugs
    • Use of history in forecast
    • Scenario and sensitivity analysis
    • Template corporate model

    Day 3: Risk analysis, valuation and modelling

    It will cover risk analysis as well as options pricing exchange rate risk, interest rate risk and debt management. Traditional risk analysis tools and mathematical approaches to measure risk are examined. Debt management issues include credit analysis, forwards, swaps and financial engineering. The final subject is computation of value at risk (VaR) for foreign exchange and interest rate risk.

    Sensitivity analysis, scenario analysis and tornado diagrams in valuation

    The most time spent on valuation analysis is developing the economic assumptions that form a base case. Evaluating risks and developing sensitivity analysis should be an integral part of valuation. A case study is used to develop economic assumptions and to demonstrate use of sensitivity analysis, break-even analysis and tornado diagrams.

    • Risk analysis of economic drivers
    • Break even analysis and credit
    • Sensitivity analysis
    • Scenario analysis
    • Tornado diagrams

    Cost of capital and adjusted present value (PV)

    Application of cost of capital is discussed with emphasis on discount rates in real world circumstances. The final topic addresses adjusted PV where all-equity cost of capital is applied.

    • Survey of cost of capital techniques
    • Estimation of beta and working with stock prices
    • Details of computing weighted average cost of capital in valuation
    • Risk neutral valuation, and arbitrage pricing model
    • Adjusted net present value

    Monte Carlo simulation

    Options can be valued with Monte Carlo simulation and binomial trees. Monte Carlo simulation can also be used to compute the probability of default in credit analysis. Monte Carlo simulation involves developing time series analysis and incorporating scenarios in a financial model.

    Exercise: Monte Carlo simulation

    • Time series analysis parameters
    • Mean reversion, price boundaries and equilibrium
    • Application of Monte Carlo simulation
    • Computation of probability of default with alternative structural enhancements

    Day 4: Alternatives to DCF and multiples - leveraged buyout (LBO), structured finance and M&A valuation

    The final day examines valuation issues associated with LBOs, structured finance and M&A. Issues include modelling cash flow waterfalls in LBOs and structured finance as well as accounting and economic issues associated with valuing M&A transactions.

    Valuation in structured finance

    The debt capacity in valuation provides an alternative way to assess risk and make valuations. We review the theory and practice of using structured finance as an alternative to DCF.

    • Overview of valuation models that do not require estimation of WACC or terminal growth
    • Theory of using debt capacity to assess risk and value
    • Alternative forms of structured finance – project finance, LBOs and asset backed securities
    • Construction of simple structured finance model

    Valuation in LBOs

    LBO and project finance valuation involve computing the present value of free cash flows and allocating that value to alternative debt and equity instruments. Issues include:

    • Building an LBO model
    • Valuation with equity and project IRR
    • Debt capacity and equity valuation

    Case study: LBO

    M&A case study

    Valuation of M&A transactions involves valuing synergies and considering accounting and tax issues.

    • M&A terms
    • M&A transaction and accounting modelling
    • M&A consolidation and earnings accretion/dilution

    Case study

    Day 1

    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

    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 mulitple 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

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

    Course summary and close

    Day 1


    Project finance modelling

    What are financial models used for in project finance?

    Introduction to the modelling exercise

    • Developing a project finance model for an independent power project (IPP)
    • How the exercise applies to other types of project What is involved in building a financial model?
    • How to approach the problem
    • Gathering the information you will need
    • Understanding the requirements of the model
    • The structure of a model
    • Materiality

    Good modelling practice

    • How to structure your models so that they can easily be understood and audited
    • Separating assumptions from calculations
    • Assumptions required for construction phase calculations
    • Sources of information
    • Project timings
    • Costs and timing of costs

    Practical exercise: participants will start to construct their project finance models. They will be taught how to use named ranges and learn how to establish a timeline using date functions. The course director will provide guidance on the use of Excel where necessary and will break to highlight key learning points.

    Modelling the effects of inflation

    • The use of real and nominal values
    • How to tackle indexation
    • The importance of timing of the costs

    Modelling in multiple currencies

    • Setting up your model to work with more than one currency
    • The use of forward exchange rate curves and purchasing power parity

    Construction phase sensitivities

    • Discover how to set up sensitivities

    Using lookup functions

    • Introduction to the choice of lookup functions
    • Why we use lookup functions
    • Problems associated with lookup functions

    Construction phase funding

    • Modelling interest during construction
    • How to calculate commitment and arrangement fees
    • Building a debt tracking account

    Circular references

    • What are they?
    • How do they occur?
    • Why should we avoid them?

    Practical exercise: participants will develop a construction funding worksheet including idc, commitment fees and a debt tracking account

    Day 2

    Review of first day’s topics

    • Summary and Q&A session

    During day 2, participants will continue to build their models, adding operating phase revenues and costs

    Modelling project revenues

    • How are the revenues of project financed projects structured
    • The reasoning behind tariff structures
    • The differences between capacity, availability and output
    • Modelling bonus and penalty mechanisms
    • Dealing with multiple currency tariffs

    Operating revenue assumptions

    • Source of assumptions
    • Operating revenue sensitivities
    • The effects of over / under performance

    Practical exercise: participants will build operational revenue calculations including fixed/variable tariff elements and bonus/penalty mechanisms. The course director will also show real life examples of thesemechanisms.

    Setting up the operating costs calculations

    • Source of assumptions
    • Operating cost sensitivities

    Practical exercise: participants will develop operational cost calculations including fixed and variable components applying the relevant indexation to each of the cost items.

    Modelling taxation

    • Modelling different types of depreciation
    • Carrying tax losses

    Practical exercise: participants will develop tax calculations including depreciation and a tax loss tracking account.

    Day 3

    Review of second day’s topics

    • Summary and Q&A session followed by quiz

    Determining the project’s debt capacity

    • Sources of funding

    Practical exercise: participants will develop funding calculations including base and standby debt tracking accounts and debt service reserve account.

    How to model different senior debt structures

    • Refinancing
    • Cash sweep mechanisms
    • Bullet loans

    How to construct a cashflow statement

    Key funding ratios

    • Introduction to NPV and IRR
    • Calculating cash available for debt service
    • Learn how to calculate annual debt service cover ratios and loan life cover ratios

    Practical exercise: participants will develop a cashflow worksheet including calculation of key funding ratios. They will learn how to calculate the debt capacity based on these funding ratios.

    Model optimisation

    • Adjusting repayment profiles to maximise debt capacity

    Use of macros

    • How they are used in project finance deals
    • The dangers and precautions that could be taken Income statement
    • Pulling together relevant information to produce an income statement
    • Dividends and other points related to equity financing

    Balance sheet

    • Constructing a balance sheet
    • Techniques for balancing
    • Using the balance sheet as an internal check on your model

    Techniques for project appraisal

    • Evaluating investor returns
    • Using the offset function


    Day 4

    Review of first 3 day’s topics

    Summary and Q&A session followed by quiz

    Output from financial model

    • Producing reports
    • Examples from real life projects developed by the course director

    Risk assessment

    • Sensitivity and break-even / default analysis
    • Pre-completion and post completion risks

    Problem solving session: participants are invited to ask questions related to using the models they have built in their own workplaces, or ask questions related to models they have inherited.

    Course summary and close

    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

    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


    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

    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

    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:
    • Debt finance:
      - Local Currency
      -
      Foreign Currency
      -
      Mezzanine
    • Equity finance
    • Preference Capital
    • Convertibles
    • Contingent
    • IPOs/Floats at project start or at exit
    • Government finance:
      -
      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

    Course summary and close

    Day 1

    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

    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 mulitple 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

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

    Course summary and close

    Why us


    We have a combined experience of over 60 years providing learning solutions to the world’s major organisations and are privileged to have contributed to their success. We view our clients as partners and focus on understanding the needs of each organisation we work with to tailor learning solutions to specific requirements.

    We are proud of our record of customer satisfaction. Here is why you should choose us to help you achieve your goals and accelerate your career:

    • Quality – our clients consistently rate our performance ‘excellent’ or ‘outstanding’. Our average overall score awarded to us by our clients is nine out of ten.
    • Track record – we have delivered training solutions for 95% of worlds’ top 100 banks and have trained over 250,000 professionals.
    • Knowledge – our 150 strong team of industry specialist trainers are world leading financial leaders and commentators, ensuring our knowledge base is second to none.
    • Reliability – if we promise it, we deliver it. We have delivered over 20,000 events both in person and online, using simultaneous translation to delegates from over 180 countries.
    • Recognition – we are accredited by the British Accreditation Council and the CPD Certification Service. In an independent review by Feefo we scored 96% on service and 95% on product