SHARE

# Excel Diploma for Project Finance Lawyers

With illustrated real-life examples you will learn the SMART rules required to set up best practice Excel spreadsheets that will give you confidence in the outcomes of your reports.

Additionally, Excel Diploma for Project Finance Lawyers will provide you with invaluable financial model review and validation tips and tricks that you can use to confidently review a project finance model and take ownership of the mechanics and outputs.

Upon completion of the course, you will not only have accelerated your modelling skills and learnt to decrease risk in your use of Excel and financial models, but you will also be able to confidently repair broken models.

## The Excel Diploma for Project Finance Lawyers course will enable you to:

• Master the separation of data, calculations and outputs
• Develop transparent Excel spreadsheets that can be easily understood and modified by other users
• Understand key Excel functions such as SUMIF, SUMPRODUCT, LOOKUP, MATCH, OFFSET, and INDEX
• Improve your Excel speed by learning useful shortcuts
• Learn how to analyse data using Excel commands such as pivot tables and data tables
• Avoid errors with systematic ‘checks’ and audit techniques (e.g. don’t rely on VLOOKUP)
• Familiarise yourself with the VBA interface and learn how to write basic macros

This course is designed for those wishing to advance their knowledge of the Excel environment within the project finance context, learning key Excel functionality, data manipulation and analysis.

## Day One: Best Practice Excel Fundamentals

### Excel fundamentals - working through function types

Functions to calculate and manipulate data are some of the best known features of Excel. Most Excel users are familiar with basic calculation functions, and many have learned how to use more advanced functions through trial and error. This approach leads to bad habits being formed as best practice Excel usage isn’t enforced. This session focuses on the fundamental principles on using business-related functions in Excel, including:

• Essential calculation functions – SUM, AVERAGE, COUNT
• Date calculations – date transformation tools such as EOMONTH, EDATE, YEAR, MONTH, DATE
• Logical statements – understanding IF, AND, OR
• Lookup functions – learning when to use LOOKUP, INDEX and MATCH, and why not to use VLOOKUP and HLOOKUP
• Financial functions – how to use NPV and IRR, as well as their updated XNPV and XIRR counterparts
• Text functions – cleanse data using tools such as LEFT, MID, RIGHT, VALUE
• Advanced data functions – SUMIF, SUMPRODUCT, OFFSET

### Excel best practice

The biggest problem with self or peer-taught Excel users is that bad habits are often passed on and there is little convention or standardisation in the approach; either in a person’s own work, or across the broader organisation. This module will go into detail about the rules required to set up best practice Excel models and spreadsheets, and the processes that should be followed to ensure that the final reports are fit for purpose. The topics covered will include:

• Understand best practice modelling – formulae structure and presentation
• Learn how to manage files – naming conventions, log of changes, model ownership
• Understand the spreadsheet development process and the governance required

Excel is more than a simple calculator. There are powerful features for analysis, presentation and improving integrity that are commonly overlooked even by seasoned users. This module will provide a guide to the critical features most useful in the workplace:

• Data Tables
• Conditional formatting
• Data validation
• Named ranges
• Styles

# Determine the quality of any received model and identify risks and errors quickly

• Increase your awareness of model risks, proven techniques to identify these and common error causes
• Understand the role of the model audit process and key tools used in this process
• Learn quick and easy methods to ‘self-audit’ a received model in-house and apply in on-going example modelling to identify errors
• Implement model features to mitigate and manage investment and model risks

### Ensure correct implementation of key due diligence findings in a model

• Develop logical step-by-step methodology for assessing due diligence findings have been accurately assigned in model
• Cross check that subsequent calculations of these assumptions are correct

### Gain key credit analysis skills and an approach to assessing a modelled project

• Identify the key cash flow drivers in a project (to be used in sensitivity analysis)
• Assess whether financing and operational cash flows require support of a reserve facility – demonstration of a Debt Service Reserve Account provided
• Review ratios and gearing ratios in a project, identify headroom, debt tail and points of concern
• Analyse the key returns metrics for equity and understand broad ranges for different industries and geographies
• Consider what equity metrics imply of the risk / return profile of the investor

### Take control of robust and rapid sensitivity and scenario analysis through powerful Excel techniques

• Learn how to quickly implement powerful scenario analysis into a model
• Save time and deliver rapid analysis with our approach to scenario management
• Learn how to use a scenario manager  as yet another way to test the model in different scenarios, all of the time
• Build confidence in your analysis through pre-programmed combinations of model inputs

## schedule

No dates currently scheduled