Project Finance Modeling

Learn Financial Modeling for Project Finance and Infrastructure Valuation ( DCF and IRR ).

Last updated 2022-01-10 | 4.6

- How to build project finance model from scratch in excel based on the toll road project case study;
- What is project finance
- how infrastructure projects financing is arranged and who are the players in project finance;
- How to do financial modeling of complex
- real-life project finance transactions;

What you'll learn

How to build project finance model from scratch in excel based on the toll road project case study;

What is project finance

how infrastructure projects financing is arranged and who are the players in project finance;

How to do financial modeling of complex

real-life project finance transactions;

How to sculpt debt to a target DSCR;

How to create best practice macro’s and Excel VBA codes to break circularities;

How to model Debt Service Reserve Account and Maintenance Reserve Account;

How to model Shareholder Loan

Revolver and Blended Equity IRR;

How to carry out valuation analysis based on Discounted Cash Flow (DCF) and Internal Rate of Return (IRR) methods;

How to carry out sensitivity and scenario analysis;

How to build integrated

3 financial statements model;

How to integrate monthly construction sheets into semi-annual operations sheets;

How to prepare trusted project finance models tailored to investors and financiers.

* Requirements

* You will need previous exposure to Excel in a financial modelling context and basic knowledge of investment concepts such as NPV and cash flows.

Description

How to build project finance model from scratch in excel based on the toll road project case study;
What is project finance, how infrastructure projects financing is arranged and who are the players in project finance;
How to do financial modeling of complex, real-life project finance transactions;
How to sculpt debt to a target DSCR;
How to create best practice macro’s and Excel VBA codes to break circularities;
How to model Debt Service Reserve Account and Maintenance Reserve Account;
How to model Shareholder Loan, Revolver and Blended Equity IRR;
How to carry out valuation analysis based on Discounted Cash Flow (DCF) and Internal Rate of Return (IRR) methods;
How to carry out sensitivity and scenario analysis;
How to build integrated, 3 financial statements model;
How to integrate monthly construction sheets into semi-annual operations sheets;
How to prepare trusted project finance models tailored to investors and financiers.

Course content

35 sections • 135 lectures

Introduction to the Course 1 lectures

Introduction to Project Finance Modeling Course Preview 03:01

In this video we will go over the course structure and describe what we will learn in each part of the course. The course is divided into five parts:

- in part 1, we will learn basic project finance theory, so we understand in and outs of project finance before we begin modeling;

- in part 2, we will introduce the toll road case study, review the modeling methods to increase your excel productivity and we will jump into excel to model the construction period in our project finance model;

- in part 3, we will model out financial statements which will include income statement, balance sheet and cash flow statement.

-in part 4, we will introduce project finance items such as cover ratios, debt sculpting, debt service reserve account (DSRA), maintenance reserve account (MRA), revolver and shareholder loan. We will introduce to the problem of circularity in project finance models and we will learn how excel VBA can help us to resolve this circularity in excel. We will carry out valuation and model optimization in this part.

- in part 5, we will go over the advanced project finance modeling methods such as equity first financing, debt sizing macros, DSRA macros and scenario analysis automation.

It is highly recommended to watch this video so that you have the big picture of our journey ahead.

Part 1 - Project Finance Primer 22 lectures

What is Project Finance? Preview 05:38

In this lesson we will be talking about what project finance is in the context of raising capital to fund construction of infrastructure projects. We will review distinctive features of the project finance and how project finance is different from the corporate finance.

Test Your Understanding of what is project finance? - Quiz

Project Finance in Numbers Preview 02:29

In this lesson will explore the development of project financing method and review latest trend.

Test Your Understanding of Project Finance In Numbers - Quiz

Financial Players in Project Finance Preview 05:32

In this lesson, we will review the important financial players, that participate in project finance transaction. These include investors, which are called project sponsors in project finance. Project sponsors, as we shall see, can further be divided into industrial investors, financial investors and sometimes government may also be an investor in the project.

Lender is another financial player which participate in project finance transaction. In fact, lender make project finance possible, because, typically, a majority of construction costs are financed with debt in project finance transaction. We shall see that due to transaction size, lenders typically unite and form a syndicate to arrange a syndicated loan to the project.

Test Your Understanding of Financial Players in Project Finance - Quiz

Industrial Players in Project Finance Preview 03:04

In this lesson we will explore the role of industrial players in project finance transaction. These are the companies which build the project, provide operations & maintenance service, supply raw materials and buy the product of the project company.

Test Your Understanding of Industrial Players in Project Finance - Quiz

Role of the Government in Project Finance Preview 03:45

In this lecture, we will talk about the role of the Government and public private partnerships (PPP) in project finance. We will look at the development of PPA for power industry in the US and its effect on the PPP development. We will then review a dominant PPP form - concession agreement and its main varieties.

Test Your Understanding of Role of the Government in Project Finance

Risk Analysis in Project Finance Preview 09:53

In this lesson, we will be looking at the risks in project finance transactions. As in any business venture, the project company in project finance faces 4 types of risks, these are project specific risks, macroeconomic risks, political risks and natural disaster risks. We will discuss these risks and see how they are allocated to the party which is best capable of managing those risks.

Test Your Understanding of Risk Analysis in Project Finance

Capital & Operating Cash Flows in Project Finance Preview 04:02

In this lesson, we will be looking at the capital and operating cash flows in project finance at different stages of project development. We will review the composition of project costs and project's Capex, different financing methods and forms, and important cash flows for lenders and equity investors.

Test Your Understanding of Capital & Operating Cash Flows in Project Finance

Control Accounts & Cash Flow Waterfall Preview 05:32

In project finance, lenders usually impose strict restrictions on how the project company uses its cash. Cash movement in project company is controlled by means of control accounts and cash flow waterfall structure. In this lesson we will review these two important aspects of project finance transaction.

Test Your Understanding of Control Accounts & Cash Flow Waterfall

Covenants in Project Finance Preview 04:16

In this lesson we will talk about the covenants in project finance documentation. A covenant, also called an undertaking, is a promise by the borrower to the lender to do something or refrain from doing something in return for getting the loan. Covenants appear in the loan agreement. The financial covenants are extremely important to understand for modelers, because they are part of project finance models.

Test Your Understanding of Covenants in Project Finance

Quezon Power Plant Project Case Study - Part 1 Preview 09:48

In this lesson, we will review Quezon power plant case study which will show how proper risk allocation made it possible to finance construction of 800 million dollar power plant in the Philippines.

Test Your Understanding of Quezon Power Plant Project Case Study - Part 1

Quezon Power Plant Project Case Study - Part 2 Preview 04:59

In this lesson we will continue with Quezon Power Plant project.

Test Your Understanding of Quezon Power Plant Project Case Study - Part 2

Part 2 - Case Review, Timeline, Construction Cost & Funding Modeling 1 lectures

Welcome to Part 2 !!! Preview 00:37

Introduction to Modeling 12 lectures

Introduction to the Case Study Preview 08:38

In this lesson we will review the case study which will serve as a basis for our project finance model throughout the course.

Calculations In Blocks Preview 10:23

Paste Forward Link Preview 04:30

Daisy Chains and Link Formatting Preview 03:55

Using Placeholders Preview 02:13

Most Used Excel Shortcut Keys Preview 07:52

Excel Refresher - Understanding Anchoring Preview 03:52

Excel Refresher - Understanding Dates Preview 05:26

Excel Refresher - Understanding IF Statements Preview 04:52

Excel Refresher - Understanding SUMIF Function Preview 06:05

Excel Refresher - Understanding LOOKUP functions Preview 05:37

Excel Refresher - Understanding MIN MAX Functions Preview 02:38

Timeline & Construction Period Modeling 6 lectures

Understanding Timeline in Project Finance Models Preview 02:02

In this lesson, we will be talking about timeline development in project finance.

Test Your Understanding of Timeline in Project Finance Models

Modeling Timing - 1 Preview 05:46

In this lesson we will model important dates in our project finance model.

Modeling Timing - 2 Preview 06:30

In this lesson we will start with modeling flags in our project finance model.

Modeling Timing - 3 Preview 05:48

In this lesson we will continue with modeling important flags in our project finance model.

Modeling Timing - 4 Preview 06:34

Modeling Escalation 3 lectures

Understanding Escalation Preview 02:43

In this lesson, we will introduce you to the calculation of escalation factors based on the such price index as Consumer Price Index.

Test Your Understanding of Escalation

Modeling Escalation Preview 06:43

In this lesson we will incorporate escalation calculations in our project finance model.

Construction Costs 3 lectures

Understanding Construction Costs Preview 02:12

In this lesson we will review construction costs in infrastructure projects.

Test Your Understanding of Construction Costs

Modeling Construction Costs Preview 06:43

In this lesson we will model construction costs.

Construction Funding 3 lectures

Understanding Construction Funding Preview 01:40

Test Your Understanding of Construction Funding

Modeling Construction Funding Preview 07:04

In this lesson we will model construction funding in our project finance model.

Financing Costs 4 lectures

Understanding Financing Costs Preview 03:39

In this lesson, we will learn about the financing costs that construction debt generates. These are arranging or upfront fee, commitment fee, agent bank fee and interest during construction.

Test Your Understanding of Financing Costs

Modeling Financing Costs - 1 Preview 06:57

In this lesson we will model construction debt drawdowns and equity investment on pro-rata basis, and after that, we will model upfront fee and commitment fee.

Modeling Financing Costs - 2 Preview 05:36

In this lesson, we will continue with financing costs modeling, and will focus on agent bank fee and interest during construction modeling. After that, we will complete the modeling of sources and uses of funds.

Part 3 - Modeling Financial Statements 1 lectures

Welcome to Part 3 !!! Preview 00:22

Revenue 3 lectures

Understanding Revenue Preview 01:43

In this lesson we will review the revenue that toll road project generates.

Test Your Understanding of Revenue

Modeling Revenue Preview 06:44

In this lesson we will model toll road project revenue generated by light vehicles and trucks.

Operating Costs 3 lectures

Understanding Operating Costs Preview 01:46

In this lesson we will learn about the fixed and variable maintenance costs and fixed management cost that the toll road project generates.

Test Your Understanding of Operating Costs

Modeling Operating Costs Preview 06:29

In this lesson we will model the operating costs that the toll road project generates.

Non-Current Assets 4 lectures

Understanding Non-Current Assets Preview 01:44

In this lesson we will learn about non-current assets and depreciation expense.

Test Your Understanding of Non-Current Assets

Modeling Non-Current Assets - 1 Preview 05:53

In this lesson, we will model non-current assets ("NCA"), NCA depreciation expense and maintenance capex.

Modeling Non-Current Assets - 2 Preview 07:33

In this lesson we will continue with NCA modeling. We will model maintenance capex depreciation expense and we will link the NCA balance, maintenance capex and depreciation expense to financial statements.

Working capital 3 lectures

Understanding Working Capital Preview 02:38

Test Your Understanding of Working Capital

Modeling Working Capital Preview 10:01

In this lesson we will model working capital in our project finance model.

Term Loan 3 lectures

Understanding Term Loan Preview 01:39

In this lesson we will go over the construction debt refinancing and term loan.

Test Your Understanding of Term Loan

Modeling Term Loan Preview 08:19

In this lesson we will model term loan and interest expense.

Tax 4 lectures

Understanding Taxes Preview 02:25

In this lesson we will learn about tax expense, tax paid and deferred tax liabilities.

Test Your Understanding of Taxes

Modeling Taxes - 1 Preview 05:17

In this lesson we will model tax depreciation.

Modeling Taxes - 2 Preview 07:51

Equity, Cash and Dividends 4 lectures

Understanding Equity and Dividends Preview 03:15

In this lesson we will learn about what constitutes equity and how dividends are paid out.

Test Your Understanding of Equity and Dividends

Modeling Equity, Cash and Dividends - 1 Preview 05:46

Modeling Equity, Cash and Dividends - 2 Preview 07:42

Part 4 - Modeling Project Finance 1 lectures

Welcome to Part 4 !!! Preview 00:32

Debt Service Coverage Ratio ("DSCR") 4 lectures

Understanding DSCR Preview 06:24

Test Your Understanding of DSCR

Modeling DSCR - 1 Preview 08:41

In this lesson we will model current DSCR and backward - looking DSCR.

Modeling DSCR - 2 Preview 08:05

In this lesson, we will model forward-looking DSCR, average DSCR and weighted average DSCR.

Loan Life & Project Life Cover Ratios ("LLCR" & "PLCR") 4 lectures

Understanding LLCR & PLCR Preview 05:30

Test Your Understanding of Loan Life and Project Life Cover Ratios.

Modeling LLCR Preview 08:24

Modeling PLCR Preview 10:47

Debt Sculpting 3 lectures

Understanding Debt Sculpting Preview 04:46

In this lesson, we will review the definition of debt sculpting and go over the debt sculpting calculations.

Test Your Understanding of Debt Sculpting

Modeling Debt Sculpting Preview 08:32

In this lesson, we will remodel the debt repayment based on the sculpted debt repayment profile.

Debt Service Reserve Account ("DSRA") 5 lectures

Understanding DSRA - 1 Preview 02:24

Test Your Understanding of DSRA

Understanding DSRA - 2 Preview 05:24

Modeling DSRA - 1 Preview 08:42

Modeling DSRA - 2 Preview 09:55

Maintenance Reserve Account ("MRA") 5 lectures

Understanding MRA - 1 Preview 02:25

Test Your Understanding of Maintenance Reserve Account

Understanding MRA - 2 Preview 04:23

Modeling MRA - 1 Preview 09:19

Modeling MRA - 2 Preview 10:18

Revolving Credit Facility 5 lectures

Understanding Revolving Credit Facility - 1 Preview 02:14

Test Your Understanding of Revolving Credit Facility

Understanding Revolving Credit Facility - 2 Preview 04:46

Modeling Revolving Credit Facility - 1 Preview 10:05

Modeling Revolving Credit Facility - 2 Preview 10:26

Shareholder Loan 5 lectures

Understanding Shareholder Loan - 1 Preview 03:06

In this lesson we will go over the shareholder loan and learn about: what is shareholder loan, why project sponsors may structure their investment as shareholder loan and some other important features of shareholder loan in project finance transaction.

Test Your Understanding of Shareholder Loan

Understanding Shareholder Loan - 2 Preview 04:24

In this lesson, we will work out the shareholder loan calculation. We will work on the shareholder loan balance and on items that increase (interest expense rollover, drawdown) and decrease (principal repayment) shareholder loan balances.

Modeling Shareholder Loan - 1 Preview 07:43

In this excel based lesson, we will start modeling shareholder loan in our project finance model.

Modeling Shareholder Loan - 2 Preview 06:53

In this excel based lesson, we will continue with shareholder loan modeling and see how shareholder loan interest expense creates circularity in our project finance model.

Automation and Breaking Circularity With Excel VBA 4 lectures

Understanding Circular References in Project Finance Models Preview 02:36

In this lecture, we will learn why shareholder interest expense produces circularity in our project finance model.

Test Your Understanding of Circular References

Goal Seek Automation Macro Preview 10:06

Using the macros and vba coding in excel, we will carry out automation of the manual tasks of resolving construction debt or debt sculpting, when we have to manually use goal seek to bring those values to their target values.