Project to Portfolio Rollup via VBA

[Gregg Wolin]

I finance and develop residential subdivisions and i want to to update my model to (among other things) analyze the capital needs (and results) of various growth trajectories to present to several institutional investors.

The work-in-progress i uploaded is currently functional for underwriting a single project with a single layer of debt and equity. I wanted input from the forum about the VBA required to take the single-project results (m_Project) and with a series of additional "Portfolio / Fund" assumptions including (i) the number of projects executed during each month over the next several years, (ii) the number of periods during which new projects are initiated and (iii) whether cashflow distributions are reinvested or not; create a cashflow and asset base analysis (m_Portfolio) that substantially mirrors what I have in the m_Project tab.

Then, using these Portfolio Results, add Portfolio-level leverage to create a Fund-level cash-flow analysis (similar to the one in the m_Leverage tab).

 

[Hui]

Gregg

This sounds like a task that could be done without VBA using Data Tables

Typically with Data Table you can setup a list of scenarios, where each scenario is a Row or Column in a Table

Each scenario contains all the inputs required for your model,
Eg: Lets assume you have 10 scenario's you setup 10 rows of data representing the inputs to each scenario

Then you have a Selected Row which looks up a selected Scenario and extracts the inputs for the selected Scenario into a single row/column

You then link the model to that Selected Row. So that all the inputs now come from the selected Row

Now finally you setup a Data Table

The Data Table will have a list of Scenario Numbers, then a list of connection/references to both the Inputs and Outputs of the model

When you execute the Data Table it:
1. Passes the Scenario Number from the Data Table to the Scenario Table,
2. The selected Data Changes accordingly,
3. The model is updated and
4. The data input and output data is returned returned and stored in the Data Table

You can now use that to summate and base further calculations on

To start you off I have setup a few entries in such a setup


You can add any data to the Scenario Table including Text, Dates, Numbers or Dollars, But only add data that you want to use in either the model calculations or reporting eg Description.
If a field is not relevent to either Calculations or Reporting don't add it

After having setup a few scenarios, now link the model to the Selkected Project Row where indicated by the arrow

Finally highlight all the cells/fields that need to be returned from the model

If you want to complete this we can then go to the next step

you modified file is attached

You may want to email me directly so please use my email