This webpage reviews the essential points of creating a portfolio of assets. The process of consolidating is pretty easy with a macro and the creation of a database requires the inputs to be structured in a particular manner, generally with single columns for a project. The big deal for me is to create a completely flexible template where you can add lines, delete sections and change anything you want. This template must be able to handle every single input you made in the database. The template is by far the most important thing you do in this process.
To consolidate after running the macro, use an old fashioned formula from lotus that sums everything in the same cell over a range of sheets:
SUM(Start:End!A1)
.
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.
.