Project finance and infrastructure modeling: Model overview

Project Finance and Infrastructure Modeling Course Model Overview 

This is an extract from our pre-course videos for our project finance and infrastructure modeling course.

Please click  here to see the course overview and syllabus.

Video

Video Transcript 

Welcome to the Project Finance and Infrastructure Modeling course. It’s so nice to have you with us.

Before we start modeling, we would like to familiarize you with the contents of the final model. This should help you to visualize what we’ll cover and where we’re going in the course. We will start with a quick orientation of the model structure and the contents of each sheet. The model is quarterly, meaning each column of the model represents three months or one calendar quarter.

Our inputs sheet, which is in gray, includes all of the inputs required in our model. This input sheet sends information to our calculation sheets which are the blue sheets in the model, and the blue sheets also connect to each other, mostly in order from the Ops sheet for operations to the Funding sheet, and then to the Depreciation and Tax sheet which is called D&T.

The output sheets are colored red and they include the FS sheet for quarterly financial statements and the A-N-N or the Ann sheet for annual financial statements and also a Dashboard sheet. These output sheets consolidate all of the calculations from the blue sheets. The gray L and N sheets include our best practice styles and the named ranges. With that, let’s take a deeper dive into the contents of each sheet.

The Inputs sheet includes all of the inputs of the project including inputs to run sensitivities and scenarios and the macros required to optimize the size of our equity and debt investments in the deal. The model we will develop is a hospital public private partnership deal, often abbreviated as PPP or P3 for those in the US. You can also download a toll road PPP model.

The Ops tab builds up the project cash flows including Revenue, Operating expenses, Major maintenance capex, Accounts receivable and payable and Construction costs, and we will also calculate here the project Unlevered returns.

The Funding tab includes the Cashflow waterfall, the senior Term loan sizing and repayment calculations. The debt ratios, including the Debt service coverage ratio known as the DSCR and that Loan life coverage ratio, the LLCR, the Debt service reserve account, a Major maintenance reserve account, Shareholder loan calculations, project Distributions, Construction period funding, and finally our equity returns. The depreciation and tax sheet calculates the total depreciable basis, book depreciation, our tax depreciation, tax bill income, and net operating losses carried fault often called NOLs. We will also calculate here the returns for a pass-through entity which is common in North America.

The FS sheet includes the quarterly financial statements, which includes the cashflow waterfall, the income statement and the balance sheet. We also include a section called Model Checks, and this section summarizes if there are any potential errors in the model which the user might need to address.

The annual summary sheet consolidates the quarterly financial statements into annual financial statements. It also consolidates other annual outputs, which will be used on the dashboard sheet.

The dashboard page includes important outputs required to make an investment decision. That is the purpose of our model after all. This page includes summaries of important outputs, cash flows and graphs which help visualize the results for ease of interpretation and analysis.

The order in which we build this model is included in the model checklist which we recommend that you download right away. It includes the order of each section that we will model with a checklist so that you can tick off each item as you complete it. Details of the relevant video lessons relating to the section for you to review the concepts and the row numbers that you’ll be modeling. So the checklist will be a great guide if you wanna rebuild the model yourself without watching all of the model walkthrough videos. And if you can rebuild the model with relatively little guidance, you have truly mastered the craft of financial modeling.

Note that the shortcuts that we’re typing will be shown at the top right hand side of the spreadsheet on the far right of the formula bar. Now that you understand what we need to include in the model, let’s get going.