If you can build a basic model but still find yourself wanting to learn more in order to deliver peak performance, you should participate in the Debt Modelling Masterclass. This course builds on the key concepts obtained in the Project Finance Modelling course.
Course Summary - "How to build realistic debt optionality into an existing model"
- When and how to implement debt sculpting mechanics (algebraically)
- Building an easy to analyse DSRA/c (Debt Service Reserve Account) and incorporating into the cashflow waterfall
- Incorporating mezzanine debt funding into construction and operations and the cashflow waterfall
- Project finance ratios including mezzanine debt and the different ways of doing this
- Calculating interest on an average balance without a circular reference and without VBA
- Establishing efficient mechanics for debt focused cash sweep payback calculations without complicating Debt Accounts mechanics
- Coding a Cut and Paste loop in Visual Basic
- Creating complete "Cashflows for All Scenarios All of the time"
- Creating 2D Data Tables for negotiation analysis
- Professional level integrity checks
- Continuous emphasis on working at a professional modelling level of efficiency and accuracy
Day one
Review of the Project Finance Modelling course model and its key features and shortcomings.
Debt Modelling
- Create a Robust “Copy & Paste Macro” to solve a circular reference due to Interest on an Average Balance
- Calculating Interest on an Average Balance without a circular reference and without Visual Basic
- Principal Sculpting – Algebraic solution of repayment for a constant / target DSCR (without Visual Basic)
- Principal Sculpting - Building a Graphical User Interface to control sculpted principal repayment
- Establishing efficient mechanics for debt focused cash sweep payback calculations without complicating Debt Accounts mechanics
- Discussion on commonly documented “Pre-Payment” methods using Inverse Order of Maturity and Pro Rata repayment mechanics
Debt Service Reserve Account
- An overview of the elements of the Cashflow Waterfall – focus on Seniority and practicality
- How to model Debt Service Reserve
- Accounts - with a focus on transparency
- The impact of including/excluding DSRA/c in the DSCR and LLCR
- Calculating and including the Interest earned on a DSRA/c in the Cashflow Waterfall
Day two
General Discussion on the practicalities and pitfalls of advanced debt modelling
Advanced Cashflow Modelling
- Straight line depreciation without using matrices – keep your ‘audit’ costs low
- Using a 1D Data to value the IRR and NPV benefits of the Interest Tax shield
- 2D Data Table and project finance applications – get a head start in negotiations
- Create Cashflows for each Scenario so they are ‘live’ all of the time, without Visual Basic.
Equity & Project Returns
- Group NPVs in 0-5 years, 6-10 years, etc
- Cumulative IRRs/NPVs
- Multiple IRR problem - discussion of possible and practical solutions
Advanced Graphing
- Dynamic highlights of minimum/maximum in a graph
- Setting up a default chart/library of charts
- Custom symbols for professionally designed graphs
Finishing Touches
- Professional Level Integrity Checks
- Is it possible to create Data Tables with off-sheet references? We show you how it can be done.
- Custom symbols for professionally designed graphs
