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?
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.
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.
=sum
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
Less expensive?
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.
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.
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'!)
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.
Inventore voluptas ullam a soluta. Quia aut exercitationem inventore minima voluptate aut. Voluptas sit enim nemo tempore omnis commodi laborum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...