Rolling Up Multiple Asset Models

WSO - how do you guys usually go about rolling up the cash flows for a portfolio?

Often times our models are so complex that it doesn't make sense to just build the proforma in another tab, rather, we have individual worksheets for each asset and then link to the other worksheet for the rollup.

Is there a better way to do this?

Comments (11)

Feb 23, 2019

I think what you're doing is fine. Some people might advise against having links in a workbook, but personally I haven't had issues with it.

But you're right is all properties aren't working off the same chart of accounts it can be difficult. However, I find the best way is to have a "consolidated CF" tab which will have a line item for each peculiarity, should it exist. That way whoever is looking at the tab will be able to see that. Of course for additional detail they just scroll to the individual property tab.

Not a helpful answer really, but simply, yes what you're doing is likely the best bet.

Feb 26, 2019

Create a tab that you can drop in each model that contains all the pertinent information you need to roll up.

Write a macro to copy/paste this data from each individual workbook into your master roll-up model that has individual tabs for each model.

Funniest
Feb 26, 2019

=sum

    • 6
Feb 26, 2019

can't you use indirect(address()) to link the names of each different workbook/asset cash flow to a portfolio cash flow workbook instead of excel links. I think the only limitation there is that the seperate workbooks need to be open at the same time, but it's less expensive than linking

    • 1
Feb 26, 2019

Less expensive?

    • 1
Feb 26, 2019

They all have to be open or it doesn't work. I've tried to do this, and make them dynamic so you can turn on/off linked cash flows but I've come to conclusion that one has to write a macro to automatically update. And that's where my skills end.

Feb 26, 2019

You can make it dynamic try this cell (A1): =if(vupdate = ON, indirect(address(blah)), A1). It's a circular reference so you have to toggle iter calcs but it's just self referring (excel stores A1 value in short term memory before updating it so the initial value is never lost).

I think that should do it on the dynamic side. I always just keep the workbooks open so the links update so as to reflect the most recent pro formas in any roll up.

Feb 27, 2019

Have your own individual models in the bottom. Then a clean-roll up above it. Then either =INDEX(MATCH or label the tabs in sequential order and use this formula =SUM('1:2'!)

Feb 27, 2019

Set up two tabs, say for example >> and << make sure all rows in models are correct. Drop tabs between those two tabs and in the portfolio model =sum('>>:<<'! Cell reference here)

It will sum across all cells, say A1, across all tabs within the>> & << tabs

If you're adamant in keeping everything separate, build a macro that fetches the print values across different spreadsheets, print the spreadsheet and drop it into your model. You lose flexibility but it's better than doing a live link imo

Feb 27, 2019
lolcakes:

Set up two tabs, say for example >> and << make sure all rows in models are correct. Drop tabs between those two tabs and in the portfolio model =sum('>>:<<'! Cell reference here)

It will sum across all cells, say A1, across all tabs within the>> & << tabs

If you're adamant in keeping everything separate, build a macro that fetches the print values across different spreadsheets, print the spreadsheet and drop it into your model. You lose flexibility but it's better than doing a live link imo

This is the best way if your assets are fairly standardized proformas.

if not I'd use indirect. Linked files becomes a pain when you're trying to send them to people especially those with bad excel skills. My old company linked like 18 asset workbooks together and the guys at our equity partner (Blackstone) could never figure out how to make it work properly.

    • 1
Mar 4, 2019
Comment