PPP Financial Modelling
Starting dates and places
Description
The training programme will provide participants with sets of techniques, greater understanding and hands-on practice in: Building financial models for project finance transactions, specifically those related to infrastructure and PPP projects Structuring financial models using best practice, to facilitate ease of change or updating and ease of understanding for other model users Building scenarios for analysis and stress-testing of the model Using the model to analyse forecast cash flows, choose between options and to assess risk Practically using models to support credit reviews and allocations of funding Course introduction The emphasis in this program is on learning skills about best pra…Frequently asked questions
There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.
Day 1 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 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 Turning assumptions into model calculations for PPP Build-up of construction or other capital costs Correct matching of units Build-up of operating expenditure items Use of maintenance reserve accounts Revenue drivers: Volume and pricing assumptions Use of lookup functions to change expenditure timings Building in sensitivities Building from existing operations vs. entirely new project Exercise: Building in flexibility for capital spend timing changes and adding sensitivities to model Forecasting techniques Forecasting Simple linear Exponential growth Regression Seasonality Multiple trends Statistical functions in Excel Day 2 Interest, debt fees and circularity in debt-driven model Circularity, its causes and resulting problems Use of the solver function and other ways to overcome circularity Calculations of interest Debt fee types and their calculation Use of debt service reserve accounts (DSRAs) and modelling them Capitalised fees and interest Exercise: From given term sheet of interest rates and fees, model interest and fee cash flow and P&L effects, without creating circularity in the model Cash flow modelling for PPP Principles of leverage Cost of debt capital and equity capital Weighted average cost of capital calculations and their use Cash flow waterfalls: Structure and logic Use of IF statements and MIN functions Controlling debt drawdowns vs. available facility limits Modelling issues arising Exercise: Creation of simple cash flow waterfall model to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints Sensitivity analysis in a PPP model Break-even calculations Varying inputs to assess effect on results Stress-testing of model Setting outputs to a pre-determined level by altering inputs Version control to allow comparison of outputs Comparison of different forecast versions against a baseline Tools for performing sensitivity analysis: Dynamic what-if Goal seek Tables Index function Scenario manager 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 More advanced excel for modelling Consolidation functions and techniques Use of links between workbooks Financial functions in Excel – e.g. PPMT, XIRR, NPV, depreciation functions Logical functions in Excel – IF, OR, AND and using them in combination Pivot tables Creating charts Exercise: For a given model of data, create a set of pivot tables to manage and consolidate data and chart the results Day 3 Brief overview of modelling taxes in PPP Tax treatment of costs Allowing for deductibility and non-deductibility Capital allowances Thin capitalisation Modelling tax losses Cash flow issues Exercise: Review of an example of tax modelling for a PPP investment project Inflation/escalation factors in PPP 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: Model multiple, variables rates of inflation in a project Creation of balance sheet in a PPP model 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 Proving each figure in the balance sheet Exercise: From a given set of cash flow data, calculate a dynamic balance sheet which updates as assumptions change, and in which every figure is provable Creating simple macros What is a macro? How macros help in financial modelling Best practice in creating and using macros – range names, documentation Exercise: Creating a macro without using VBA Wrap-up Re-iteration of main points Introduction to further exercises Further reading on the topic Final questions and issues to discuss Course summary and close
Share your review
Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate £1.- to Stichting Edukans.There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.