Renewable energy modeling: Model Overview

Overview

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

Video

Video Transcript 

So before we start modeling, it’s always nice to know where we are going, what the final model will include.

So we will start with a very quick orientation of the model structure, and the contents of each sheet.

This model is quarterly, which means each column of the model will represent one quarter, with the exception of some annual output sheets.

So our input sheets here, in gray, include all of the inputs required for our model, and this gray input sheet sends information to our calculation sheets, the blue sheets in the model.

The blue sheets also connect to each other, but in order, from the operations tab, to the funding tab, to the tax and returns tab.

The output sheets, including the financial statements, the annual summary, and dashboard, consolidate the calculations in the blue sheets and are colored red.

The TEQ, or Tax Equity Tab, the back, is for a stand-alone tax equity component for those of you in the US, and the L and N sheets include our best-practice styles and the name ranges.

So let’s take a slightly deeper dive into the contents of each sheet.

You will appreciate the huge amount of modeling that you will complete by the back end of this course.

So the input sheet includes all inputs to the projects, including scenario management and the macros required to optimize our model to the target returns of the investors.

The operations sheet here includes all of our timing calculations, including what year of operations we’re in. It includes generation, the sale of energy, costs, the accounts receivable and payable, the constructions costs, our cash flow available for debt service, and our unlevered returns.

The funding sheet includes all of our term loan debt calculations, including debt sizing and sculpting to various P50 and P99 forecasts, the DSCR and LLCR debt ratio calculations, our construction loan and equity calculations, the project distributions, and the pre-tax equity returns.

The T&R, or Tax and Return sheet includes our accounting and tax depreciation, the tax credits, tax calculations with net operating losses, called NOLs, and our levered post-tax returns, to both an efficient, and an inefficient tax payer.

The L sheet is our styles legend with an explanation of each style or format that we use in the model, and the N sheet is a list of all of our name ranges we use. We will leave the tax equity, or TEQ sheet, for now, and we’ll come back to that much later in the course.

The FS sheet, or Financial Statement sheet, includes our three-way financial statements, including the cashflow waterfall, our income statement, and our balance sheet on a quarterly basis, which matches the rest of the model. We also include here some checks as to if the model is functioning properly.

The Ann, or annual sheet, includes a summary of our three-way statements again, but this time on an annual basis, together with other general outputs required for our Dashboard page.

And then on our Dashboard page here, this includes all of the important outputs required to make an investment decision. This is the purpose of the model after all.

The order in which we will build this model is included in the model checklist, which we strongly recommend you download. It includes the order of each section we will model, with a checklist so you can tick off each item as you complete it. It also includes the details of the relevant lessons relating to the concepts required for that section. And it includes each individual row that you will model, in order. It’s a great guide if you want to rebuild the model yourself without guidance.

Note that the shortcuts we are typing will also be shown on the top, right-hand side of the spreadsheet, on the far right of the formula bar. So with that, we will now step into the first section shown on the model checklist called, Model Timeline.