- 50% Excel / 50% Theory
- Prerequisite: Basic Excel
- Who would benefit from this course:
Anyone wishing to learn more about Project Financing, in particular those wishing to work in:
- Project Finance in a Bank, or
- Any industry that typically uses Project Financing : i.e. Resources, Infrastructure, Energy, Construction, PPP, etc.
- This course is: Excel intensive and some complex Excel functions used
You will build a project finance model from a blank Excel spreadsheet. We will teach the key calculations required in a Project Finance model, the logic/ rationale behind the numbers, and show how to quickly change inputs and create sensitivities.
Part A: Project Finance 101
- What is Project Finance? Which industries typically use it? What are its benefits? What are its negative points?
- What do all these Investment Banking terms mean? Project Finance/“vanilla” Corporate Banking / M&A / Leveraged Finance
- What’s the difference between DSCR, ICR, PLCR, LLCR? What is Reserve-based lending?
- How do you model a cash waterfall account including capitalised interest, and additions to/withdrawals from DSRA and Lock-up accounts? How can you change the amortisation profile from credit foncier to linear in one keystroke (and what is credit foncier?). Abilities such as these are key if you wish to have a fully automated model that you can quickly run different scenarios in.
- How to ascertain maximum debt capacity in a project finance deal?
Part B: Best Practice Financial Modelling
After the initial theory we will briefly look at the key tenets of best practice financial modelling, as you could be faced with modelling a business with substantial operations, and your job will be made easier, if you get in the habit of following best practice financial modelling:
- What makes a good financial model? The planning/ logic flow/ auditing/ error minimisation and other key tenets that should be followed to create a robust model
- Excel’s more advanced functions that we will be employing to build the model, particularly the financial functions
Part C: Building the Model/Arriving at the Cashflows
- Planning/ logic flow/ model set-up / Cover and Log sheets / Model Maps/ creation of error checks as we go along (the more we have, the less chance of error).
- Dates : get them right, as the model quite often hinges on dates – particularly when there are different phases i.e. construction, commissioning, operations.
- Assumptions: Entry of, and validation of every input
- Outputs (Quarterly): Construction versus Operations phases / Creating the calculations to arrive at business cashflow / Working Capital/ Depreciation / Tax / Post-tax Cashflow (known as CFADS: Cash Flow Available for Debt Service)
- Outputs (Annual) : the same quarterly data but shown annually- and how to do this in just a few keystrokes (Issues to look out for: balances versus flows, % outputs, tax schedule).
Once we arrive at the business’ CFADS, we then add the Project Finance specific calculations as follows:
- Will debt repayment be via Credit Foncier, Linear or Sculpted? And what do they all mean? How to sculpt the repayment profile to achieve a certain DSCR (and what does the average DSCR tell us?)
- Amortisation Schedules: How to calculate capitalised interest & fees
- Cash Waterfall: from CFADS to Reserve accounts (DSRA), to additional prepayments based on covenants, lock-up accounts, and releases to Equity
- Testing our waterfall calculations: We then switch to a deliberately adverse downside case, because in the base case everything that must be paid is paid. It’s only when we look at downside cases we can see whether our cash waterfall calculations are correct or not. Accordingly, we change the inputs to produce “good, bad, and good again” results, and then ensure the cash waterfall reflects that: from CFAD’s “ trickling “ right down to distributions to equity. In this course we go into much more detail re the cash waterfall account, than we do in our course Focus on Finance, as the cash waterfall, and flows into and out of, can be one of the most complex items in PF transactions to calculate, particularly due to capitalised interest, catch up debt payments, etc.
- Debt Ratios: How to calculate common project finance ratios : DSCR, ICR, LLCR, PLCR
- Breakeven Analysis: What assumptions will deliver a DSCR of 1.00?
- Payback/cash sweep analysis : We construct a table to answer the question:“ How long would it take to repay debt if a cash sweep commences in year x?”
Part D: Audit and Results
Before we perform sensitivities (below) we must check that we got the base case correct- and also look at impressive ways of presenting the results:
- We will create a shadow model to get the same results.
- We look at various audit tools available, both in Excel, and commercially available software.
- Which of Excel’s tools are best for your summary purposes: Charts? Floating charts? Pivot Tables? Data Tables? Normal tables?
- We create impressive summaries via use of conditional formatting and charts
Part E: Sensitivity Analysis
- We create various scenarios-and look at the options in Excel to do this i.e. Scenario Manager or Data tables- what’s the difference? Which is better? Which is quicker?
- Apply conditional formatting to data tables to provide excellent presentation tools.
- Understand not just how to create sensitivities, but be able to confidently explain them as well