This webpage describes how to efficiently create a model of multiple projects that are put into a fund and then consolidated. This method of consolidating many similar — but in no way identical — projects into a fund is useful for both real estate projects (like residential homes) or rooftop solar projects on commercial buildings. The approach I suggest is to make a list of projects in a database and then create a template that can handle different project dates; different project sizes and costs; different construction periods etc. To do this you can gather the data into the template and then create a macro that copies the template for all of the projects. Finally, you can use the INDIRECT function to consolidate the individual projects into a fund. This method may sound a little difficult but it just requires a couple of simple macros. Using this method, individual project sheets and consolidations come from a master sheet and an SPV template file. You can create multiple sheets with different SPV’s and put different projects into separate SPV’s with a relatively simple macro. Once you have a lot of individual SPV’s in separate sheets, you can consolidate the SPV’s using the INDIRECT function. This method of consolidating many similar (but in no way identical) projects is useful for both real estate projects (like residential homes) or rooftop solar projects on commercial buildings. The files that are attached to the two buttons below illustrate the process using a macro and the INDIRECT function.
Playlist on creating portfolios
.
.
Excel File with Example of Creating a Fund with Common Timeline, Template, Simple Macro and Indirect
Excel File with Portfolio of Different Projects Consolidated to an Aggregate Loan Using Indirect
.
Financial Model where Different Projects in the Portfolio are Modelled with Separate Projects in a Fund without Separate Financing of Individual Funding
.
The file available for download below includes separate SPV’s for different facilities using the consolidation method with separately financing the SPV’s. You go to the master database sheet and set up names of the different facilities that could be residential homes, commercial buildings, solar roof-tops etc. with the start dates, size, construction periods, rental rates and other factors. Then you can copy the template for a single SPV to other SPV’s (using a macro). After creating the SPV’s and the sheet names, you can consolidate the SPV’s and put in financing. Finally, you can evaluate how the consolidated financing works with different scenarios and see how fast the consolidated loan can be paid off. Two files that work through this process are available for downloading by clicking on the buttons below.
.
The process of modelling separate SPV’s using a master database and then putting them into a consolidated analysis is illustrated below. Financing of the cash flow can be made at the consolidated level or the SPV level. In the first file above, the financing is at the SPV and the consolidated level. The diagram below demonstrates that how you create a database, then make a template model for a single SPV that can accept the various different data items from the database.
.
.
Here is the way that process works on a step by step basis:
- Step 1: Create a master project database file. This file has the name of each separate SPV and the assumptions that will be used to model the SPV including the start of construction, the end of construction as well as operating and even financial variables.
- Step 2: Create a template for a single project. This template accepts the start date of the individual project like a commercial real estate project or a roof-top solar facility for a commercial building. Some of the important features of the template file include:
- There is a name at the top of the template file that is the same as the excel sheet name. This is an essential part of the process that allows the template to look-up in the master page and find the inputs. You use the MATCH function with this name to find the row number in the project file.
- The format of the template is the same for all projects, but the timeline can be different. In other words, the projects can have different start and end dates, different costs and so forth. But the template is flexible enough to handle all of the assumptions. In the case that is illustrated below, the template can handle different currencies and different solar production characteristics from alternative PVSYST files.
- Use the macros below (copy them into your file). Alternatively and better yet, write your own macros that loop around all of the projects defined in the database. Also create a macro the clear the sheets because you may change the SPV template file and want to run the process again.
- Create the consolidation file with the INDIRECT function. For this function you should define the file name in the left column to retrieve the various data and you should also define the row number that you will take from the template file.
.
Step 1: Create Database and Move from the Master to the Template Financial Model
.
The first screenshot below illustrates how you can start the process with a master database. The one thing the database must have is the title for each of the SPV’s. Other data that will be imported into a separate file that simulates the SPV must be in the same row because of the way MATCH and INDEX are used. The data can be expanded and the template SPV file can be changed to read in new data. Note that if the template model is monthly and you have a whole lot of SPV’s, then the model can become slow.
.
.
The key idea about the process is the sheet name in the template file and how this sheet name is used to find the input data in the master page. Later you will see how to create many different sheets from the template sheet with different names that will in turn select different inputs. In the example below the file name in the template file is “Irani Brothers”. This name will be changed to the other names in the master page (Ghana Beer in the example below). Once you have the name, you use the MATCH function to find the row number of the project in the master sheet as shown below. You must use the 0 on the MATCH function because the names will not be in alphabetical order. Note that if you want to add columns to the master page you must be careful because the letters are defined in the INDEX function. So to add columns, you should put them at the right in an additional column.
.
.
.
Once you have the row number in the template model defined using the MATCH function, you can find any of the data that you will need. To do this, you can use the INDEX function with an entire column where you define the entire column. Then you refer to the row number defined from the MATCH function in the INDEX function (the old INDEX/MATCH). In the first example in the screenshot below, the row number of 8 is defined from the match function. Then you can find the start date of the project from column D in the master page.
.
.
Macro to Consolidate the Template into Separate Projects
After creating the template file, you can create inputs for a lot of different projects. Each of the projects is defined by a name that becomes the sheet name. You can then run or create a macro to copy the template sheet to another sheet. Once you have a lot of templates, you can consolidate the templates into a consolidation. The consolidation uses the INDIRECT function to gather data from the different files. A few screenshots may give you an idea of how the file to consolidate different SPV’s works. The first picture is intended to give you an idea of how the final consolidated process works. This consolidation works by using the INDIRECT function to add together cash flows and balances from individual sheets. The second screenshot below shows how you can use the INDIRECT function. The INDIRECT function looks painful, but you can get the hang of it quickly.
.
.
.
Macro Examples to Consolidate Files
.
Sub CreateTemplate()
lastsheet = Sheets.Count ' This is the number of the sheets to move the file to
currentsheet = ActiveSheet.Name ' This is where to go back to after finished
Newspv = row ' This is a public variable from the multiple sheet row
Sheets("Master").Select ' Start with the master sheet
Newname = Cells(Newspv, 2) ' This is the sheet name that you put in the template sheet to define variables
Sheets("Template").Select ' Go to the template sheet
Sheets("Template").Copy After:=Sheets(lastsheet) ' Copy the sheet to the end of the file
Sheets(lastsheet + 1).Select ' Go to the new sheet that you made
Sheets(lastsheet + 1).Name = Newname ' Put the name of the new sheet in the file
Cells(1, 2) = Newname
Sheets(currentsheet).Select ' Go back to the original sheet
End Sub
.
.
Sub multiplesheet()
remember_calc = Application.Calculation ' Will set calc to manual, so remember last value
Application.Calculation = xlCalculationManual ' Set to manual
Dim startrow, endrow As Single ' Need to dimension the variables that are defined by the input box
startrow = InputBox("Ligne de début ?")
endrow = InputBox("Ligne de fin ?")
For row = startrow To endrow ' Loop around rows in the master page
CreateTemplate ' Run the macro that creates a template
Calculate
Next row
Application.Calculation = remember_calc ' re-set calculation to original value
End Sub
.
.
Corporate Modelling with Consolidated Accounts from the INDIRECT Function
.
This is an analysis where multiple SPV’s are consolidated into a portfolio. A major innovation in the analysis is use of a flexible template for the SPV that includes currency risks and different types of financing. A macro copies that SPV to multiple different sheets. The INDIRECT function along with MATCH and INDEX are used to consolidate the SPV’s and analyse the results.
The third set of screenshots demonstrate the outputs of individual SPV’s that are eventually rolled-up into the consolidated analysis. This depends on the financing structure of at the SPV and whether debt is issued on a project basis. The graphs show how debt service is paid at the individual SPV and how the individual SPV receives cash flow from the solar project in different scenario cases.
.
Financial Model where Different Projects in the Portfolio are Modelled with Separate SPV’s without Separate Financing
.
The file below has a similar structure with a master database etc. But this file includes financing in the SPV template and financing assumptions in the master database. This means that separate financing is included for the separate SPV’s. When you include financing in separate SPV’s there will be some problems with circular references. This file resolves the circular references with the parallel model concept.
.
.
.
.
- The featured solar model page that is linked to this sentence includes completed finance models.
- There is also a file that allows you to automatically download panel prices and the price of silicon used in manufacturing the panels which is linked to this sentence.
- There is a webpage that works through resource issues including the performance ratio and how to find the best data on solar energy that hits a panel.
- There is a webpage that describes computation of the P50 versus P90, P99 etc. with some examples.
- There is a webpage that describes the arcane tax equity aspects of financing tax equity in the U.S.
- There is a page that works through how to consolidate multiple roof-top installations into a aggregate file.
The file below is an analysis where multiple SPV’s are consolidated into a portfolio. A major innovation in the analysis is use of a flexible template for the SPV that includes currency risks and different types of financing. The INDIRECT function is uses a lot. A macro copies that SPV template to multiple different sheets to develop the separate SPV;s. The INDIRECT function along with MATCH and INDEX are used to consolidate the SPV’s and analyze the results
Development Analysis with Separate SPVs.xlsm
This is a solar rooftop analysis that involves identical issues to real estate analysis that include creating a portfolio with different characteristics including start dates, end dates changes in utilisation and operating expenses. It includes detail methods for computing working and financing. There is a set of 15 videos that constitutes a course associated with both basic and advanced issues associated with this analysis.
Portfolio of Small Developments.xlsm