This is an extract from our tax equity modeling course.
-Welcome to the course. It’s lovely to see you. Unfortunately, due to COVID-19, there are some videos that we can’t record in our film studio. So I’m at the top of the screen, not in my usual home office, but instead stuck away in my attic to stay away from my slightly noisy children. Before modeling, it’s nice to know where we are going, what the final model itself will include. So we can start with a quick orientation of the model structure, and the contents of each sheet, just to orientate you. This model is annual, which means each column of the model represents one year, with the exception of the construction sheet, which has been modeled quarterly. Our input sheet, in gray, includes all of the inputs required for our model. The input sheet sends information to our calculation sheets, they’re the blue sheets in our model. The blue sheets also connect to each other, but generally in order from the Coms tab, to the SPV tab, to the Partnership tab, the Sponsor tab, and lastly the HLBV tab. The output sheets, including a summary of our capital accounts called Cap Acc, and the Dashboard sheet consolidate the calculations in the blue sheets, and they’re colored red. The gray L and N tabs include our best practice styles and name ranges. Let’s take a deeper dive into the contents of each sheet. You’ll appreciate the huge amount of modeling that you’ll complete by the backend of this course. The input sheet includes all inputs to the project, including scenario management, and the macros required to optimize the size of our tax equity investment for our deal. The model includes the calculations for a wind project, as well as both a yield-based flip, and a fixed-time based flip for a solar project. The Cons tab builds up the construction costs, and calculates the funding from the sponsor, equity, tax equity, and senior debt for our project. The SPV tab is where all of our operational project calculations are included. This includes the timing calculations, i.e. what year of operations we’re in, the generation, the sale of energy, costs, and a summary of project cash flows. It also includes the project level benefits derived from depreciation and tax credits, as well as a total summary of all benefits from the SPV, and our unlevered returns. The Partnerships tab includes the calculations allocating the different benefits between the sponsor equity and tax equity, as well as the section 704b, tax capital accounts, and our outside basis tax capital accounts. It also includes a summary of the benefits to each partner after adjustments due to the capital accounts, the tax equity partner returns, and the analysis of the sponsor buyout option. Finally, we include a balance sheet for the total partnership. The Sponsor tab includes everything from the whole code level up. This is the benefits derived from the partnership, the back-levered loan debt sculpting and sizing, and our senior debt ratios. It also includes the sponsor tax calculations for both a hold and a buyout scenario, as well as the returns to the sponsor. The HLBV sheet includes the total partnership book income allocation, and all of the partnership hypothetical liquidation at book value, known as HLBV, calculations required in order to calculate the book value to each partner. The Cap Acc sheet includes a summary of the section 704b tax capital accounts, the outside basis tax capital accounts, and the HLBV accounts for each partner. It also includes some graphs of the capital account balances over time. Finally, our Dashboard page includes all the important outputs required to make an investment decision, and that’s the purpose of a model after all. Now, this is as simple as a model as you can get for tax equity, but tax equity itself isn’t easy, so it’s a lot more complicated than the model you will have seen in our renewal energy project finance modeling course. It also assumes a lot of knowledge that we expect you already to have, such as how to size debt, and sculpt debt, how to calculate net operating losses carried forward, how to run scenarios, and how to create a basic macro to optimize a model. 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 in the top right-hand side of the spreadsheet, on the far right of the formula bar.