If you are in investment banking or project/corporate finance at any level of seniority, knowing how to build, adapt or review financial models is crucial to the success of your team and the transaction.
Over two interactive days – you will be working on your own laptop - we take you through the following components
How to build a transparent, flexible and well presented cashflow model
- How to layout and build a greenfield project finance model
- Structuring calculations in a simple logical manner
- Using the Excel environment in an ultra-efficient way
- Build a Scenario manager that runs 'all scenarios all of the time'
- Debt repayment mechanics from first principles
- Construction of a Structured Finance 'friendly' Cash waterfall (without circular references)
- Calculation of key Project Finance metrics (DSCR / LLCR)
- Transparent calculation of 'Project' and 'Equity' returns NPV and IRR
- Using {Arrays} as smart functions.
- Continuous and strong emphasis on ultra-efficient and reliable working using 'shortcuts' and 'styles'
- Regular discussions on aspects such as what not to model, circular references and model review considerations.
Throughout the course we will demonstrate smart ways to use the Excel environment, and highlight shortcomings of different modelling approaches and Excel settings and functions. The majority of this highly interactive course is spent developing a flexible project finance model, which allows you to take the key techniques away with you. Comprehensive printed notes, Excel files and a handy menu of short-cuts are provided upon arrival.
By balancing the experience of the small number of participants, we ensure that all participants understand each concept before moving on. Typical attendees include analysts, managers, senior managers and Associate Directors from investment banks. We regularly welcome Senior Management and CFOs from the industry, to give them a better understanding of how a solid financial model can assist their company in strategic decisions and negotiations.
Day one
Financial modelling foundations
- A walk-through of a complete Navigator model to demonstrate our approach
- The lifecycle and pitfalls commonly encountered in a financial model
- Characteristics of a model which will survive in a live deal environment
- Methodology and key steps required to achieve a robust and efficient model
Key characteristics of a good model
- Easy to use and update, readily checked, user- friendly and professionally presented
- Efficient to calculate, and capable of rapid sensitivity analysis
- Robust and dynamic to survive a transaction
- Cost-effective to be ‘audited’ by an independent third party
The Excel Environment
- Optimising the ‘Calculation’ settings
- Essential Excel shortcuts/tips and tricks
Model Infrastructure
- Set-up of the overall infrastructure and presentation of inputs/grouping of logic
- The easy way to achieve flexible timing to accommodate delay scenarios
- How to use binary timing flags to assist in minimising IF statements
- Real Vs nominal - the calculation of escalation indices
- Using Range Names in a practical manner
- Building checks into a model and making iteasy to ‘audit’
Efficient modelling of cashflows
- EPC Contract
- Interest during construction
- Dynamic funding mechanisms
- Sources and uses of funds
- Fixed and variable opex
- Revenue
- Interest Calculations
Debt repayment calculations
- Refinancing of construction facility
- Debt mechanics for annuity repayment (constant P+I)
- Debt repayment calculations from first principles
- Incorporating and testing flexible debt term and interest- only periods
Day Two
Key project finance metrics
- What the ratios mean, how they are used and strengths/ weakness discussion
- Debt : project cashflow ratios (ICR/DSCR/LLCR/PLCR ) from first principles
- Equity: NPV analysis from first principles
Advanced modelling techniques
- 1D Data Tables
- Avoidance of Circular References
- Applications of Conditional Formatting
- Application of Data Validation (incl offsheet list selection)
Smart Functions
- Discussion around recommended functions and their applications
- SUMIF/COUNTIF/ SUMPRODUCT/ DATE FUNCTIONS
- + takeaway file of demonstrations
- Array Functions {Average()}, {Min()},{Transpose()}
Sensitivities /Scenarios
- Using Flex Cells to ensure the base case remains intact
- Building a scenario manager – the structured approach to sensitivities/scenarios
- 1D Data Tables – this technique will save you days and nights of work
- Generating all sensitivities all of the time ‘live’
Consistency and efficiency
- Setting up and using Excel ‘Styles’ for
- Grouping/outlining – efficiently work with ‘deep’ sheets
- Technical Inputs – controlled optionality
- Colour schemes in Excel - Apply your organisation’s exact corporate style to the model
- Efficient use of Range Names
