The Ultimate Project Finance Modeling Package

Section Introduction and Context

• Section Overview
• Project Finance Primer

Project Finance Airport Case Study

• Case Study Overview
• Case Study Timeline and Process
• Construction and Operations Calculations
• Operations - Cashflow & Metrics
• Negotiations & Optimizations

Course Introduction and Context

• Welcome to the Course
• Section Overview
• Course Structure & Why the Memory Retention Exercises are a Powerful Component of this Course
• Putting the Course Content in Context

The Fundamentals

• End Game - Analysis of a Model Using Scenario Manager and Data Table
• Best Practice Financial Modeling
• Navigation & Anchoring

The 80/20 rule of Formulas used in Financial Models

• Top Ten Functions
• Call ups - Position Calculated
• Call ups - Position Input
• C1 - LOOKUP
• C1 - HLOOKUP
• C1 - LOOKUP challenges
• C2 - INDEX & OFFSET
• C3 - MATCH
• C4 - INDEX MATCH
• C5 - MATCH, INDEX, CHOOSE
• C5 - OFFSET, SUMPRODUCT
• C5 - SUMIF, LOOKUP
• C6 - OFFSET for Height & Width
• Call-Ups Summary
• Create Flags To Massively Simplify Timing Formula - IF & AND
• Create the Timing Strip - EOMONTH & YEAR

The Excel Tools to Use Consistently and Why

• Introduction to Tools: Expand your Excel toolkit with these 5 in built Excel ToolsTools Which Enhance Robustness
• Named Ranges & Data Validation
• Tools Which Enhance Presentation - Cell Styles, Custom Formats & Conditional Formatting

Building Speed - 3x Your Speed with Shortcuts

• Introduction to Shortcuts
• 5 Shortcuts to NAVIGATE the Model
• 17 Shortcuts for BUILDING the Model
• 12 Shortcuts for FORMATTING the Model
• 7 Shortcuts for CHECKING the Model
• 4 Shortcuts for AMENDING the Model
• THE ONE Shortcut for AUDITING the Model
• 4 Shortcuts for MS Office
• Recap: THE TOP 30 SHORTCUTS
• Exercises - Honing your Skills with Shortcuts
• Closing

Putting It All Together With Memory Retention Exercises

• Introduction to the Memory Retention Exercises
• Memory Retention Exercises 1
• Memory Retention Exercises 2
• MRE1 speed run
• MRE2 speed run

Principles of Project Finance

• Project Finance Mechanics: Discuss the Characteristics, Complexity and Players Involved in a Project Finance Transaction
• Types of Project Finance Deals - from Public to Private Infrastructure
• Timeline of a Project Finance Deal - Beginning to End

The Case Study and The End Game

• Introduction to Case Study & the Purpose of a Financial Model
• The End Game of Project Finance: Using the Scenario Manager to Analyze the Deal

The Start Model

• Introduction to the Start Model + (.xlsx download)

The Timing Sheet: Transform a Spreadsheet into a Financial Model

• Introduction to Timing & timing sheet setup
• Creating the Timing Strip with Dates & Flags
• Counters: The Connective Tissue of a Model
• Rounding out the Timing Sheet with Escalation & Base Rate Interest
• Finishing the Timing Sheet & Copy Sheet

Constructing a Greenfield Infrastructure Project and Calculating Who Pays What

• Introduction & Calculating Construction Spend Profile
• Creating A Robust Structure for Sources & Uses of Construction Funds
• The Cost of Debt: Calculating Interest During Construction and Financing Fees

Operations Over the Lifetime of the Asset - Revenue and Costs

• Introduction to Operations and Reviewing the Inputs
• Calculating Revenue of a Wind farm with Merchant Revenues
• Splitting Opex into Fixed and Variable, Escalating Fixed Opex
• Grouping, Recap & Explanations

Explore the Cashflow Waterfall; Theory and Bring In Cash Items to One Central Location

• Theory, Critical Line Items, Hierarchy of CFW and Difference Between CFW and CFS
• Modeling the CFW in Excel

Learn about Project Finance Debt Modeling through Annuity, Sculpting & Debt Metrics

• Intro & Theory: Components of a Term Sheet, Evaluating Debt Tenor & Four Repayment Types
• Setup & Modeling the Debt Account
• Calculating Interest Expense
• Modeling the Annuity Calculation from First Principles
• Modeling Debt Sculpting through the Target Ratio
• Debt Ratios: Understanding and Calculating the Debt Service Coverage Ratio; Pulling out Key Metrics Like Min, Min Date & Avg
• Debt Ratios: Understanding and Calculating the Loan Life Coverage Ratio

Equity Account, Distributions and Returns to Project and Equity Holders

• Putting Equity in Context: Cashflow Available to Equity
• Introduction and Calculating Distributions to Equity Holders
• Setting up Equity Returns Cashflows
• Theory and Calculation of Net Present Value from First Principles and XNPV Function
• Theory of IRR and Calculation using XIRR
• Calculating Project Returns

Using The Scenario Manager To Turbocharge Your Analysis

• Introduction to the Scenario Manager; What It Does & How it Works
• Setting up the Scenario Manager in Excel to Sensitize Key Variables Throughout the Model
• Creating a Data Table to Simultaneously Output Results for All Cases

Overview of the Course

• Welcome to the Course
• Course Content
• Intro to model

The Relationship Between Debt Sculpting & Debt Sizing and Debt Sizing to a Term Sheet

• Introduction to Debt Sculpting & Debt Sizing
• Seeing the Relationship Between Debt Sculpting & Sizing with the Mini-model: Setup, Sculpting & Tenor vs Sizing
• Seeing the Relationship Between Debt Sculpting & Sizing with the Mini-model: Debt sizing
• Seeing the Relationship Between Debt Sculpting & Sizing with the Mini-model: Resolving Circularities
• Seeing the Relationship Between Debt Sculpting & Sizing with the Mini-model: Manual Sizing, Bisection Method,
• Recap
• Capital Structure - Macro Sheet & Max Permissible Debt Size
• Capital Structure - The Calculated/Applied Structure and Linking

Writing Elegant & Robust Macros to Size Debt Quickly

• Introduction to Macros
• Recording & Triggering a Macro to Get Started
• Getting into VBA & Refining Code
• Using the For Loop to Iterate the Macro
• Improving Robustness & Using Named Ranges
• Using the Do While Loop to Iterate the Macro
• Basic Improvements to Speed up the Macro
• Recap to Understand the Role of Loops, Using Shortcuts and Speed Improvements

Incorporating a Debt Service Reserve Account to Reduce the Probability of Default

• Introduction to the DSRA
• Overview of the DSRA - The Logic Behind Additions & Releases of Cash
• Setting up the DSRA & Establishing the Target Balance
• Funding the DSRA from Sources of Cash
• How to Break the Circularity & Creating a DSRA Macro
• Funding from Cashflows to Top-Up the DSRA
• Release from DSRA in the Event of CFADS Shortfall
• Release from DSRA for DSRA in Excess of Target Balance
• Review of DSRA, Linking to Financial Statements and Distribution Lock-Up Flag

Refinancing Debt: the Why and the How

• Introduction - Motivations for Refinancing Debt & Types of Refinancing
• Inputs for Refinancing Debt
• Timing of Refinancing Debt & Modifying the Senior Debt Structure
• Calculating the Refinancing Fee
• Calculating the Refinancing Margin & Incorporating it into the Debt Structure

Sophisticated Equity Structures with Shareholder Loans

• Introduction to Shareholder Loan, Purpose and Order of Repayment
• Creating Inputs for the Shareholder Loan and Building an Account
• SHL Drawdowns - Integrating the SHL into Funding Sources & Updating the Capital Structure
• Calculating SHL Interest Capitalized and Interest Paid
• Calculating SHL Principal Repayment and Updating Distributions
• Updating the CFW, Testing and Recap

Analysis & Advanced Macros

• Introduction to Automating Debt Sizing & Equity Calculations with Macro's
• Introducing the Debt Sizing Switch & Scheduling Repayment
• Creating a Master Macro to Run All Macro's Once
• Testing & Updating the Scenario Manager
• Setting up the Data Table for a Macro
• Writing the Data Table Macro to Run All Cases AND Run Debt Sizing Macro's Where Required
• Performing Debt and Equity Analysis
• Re-profiling Debt In the Case of Gearing Governed Debt Size to Improve Equity Returns

Model Working Capital to Adjust Revenue & Expenses from Accrual to Cash Basis

• Working capital: Corporate Finance vs Project Finance
• Introduction to Working Capital
• Debtors & Working Capital adjustments
• Creditors & Working Capital adjustments

The Profit & Loss Statement

• Introduction to the P&L
• Modeling the P&L

Modeling Accounting & Tax Depreciation Flexibly Using the OFFSET Method Introduction & Tax vs Accounting Depreciation

• Types of Depreciation
• Challenges of Depreciation & Introduction to the Old School Depreciation Table
• The Depreciation Setup in The Model
• Calculating Maintenance Capex
• Depreciation Inputs: Categories & Useful Life
• How to Summarize Capex into Additions Categories
• Creating a Template Account & Depreciation Base
• Linking Additions & Depreciation and Reviewing Depreciation
• Replicating the Templates, Updating for Tax Depreciation & Summarizing

Geared and Ungeared Tax Calculations

• Introduction to Tax
• Calculating Taxable Income & Modeling the Tax Loss Account
• Calculating Tax Payable & Tax Paid
• Un-geared Tax Calculations for Stripping Out the Capital Structure
• Recap of Tax

The Balance Sheet - Fitting Everything Together Like a Jigsaw Puzzle

• Introduction to the Balance Sheet
• Establishing the Balance Sheet Framework & Linking in Balances
• Balance Sheet Checks and Correcting Errors Using Timing & Magnitude
• Using the Scalpel Method to Isolate Errors
• Correcting Errors to Balance the Balance Sheet

Warnings & Alerts Checks to Improve Model Robustness

• Setting up & Creating Alerts
• Creating Warnings, and Using Rounding & Formatting
• Setting up the Ribbon for Best Practice Transparency

Extensions

• Tax Depreciation - Introduction to MACRS Depreciation
• Incorporating MACRS Depreciation into the End Model
• Closing

Bringing Everything Together

• Overview & Specific Changes
• Investigating the DSRA Circularity

Extending the Model

• Extensions: Adjusting Construction Periodicity From Quarterly To Monthly