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?
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 >:> &
[quote="lolcakes"] Set up two tabs, say for example >> and >:> &
Deserunt id est culpa labore eveniet. Vero adipisci molestiae a ab. Ratione mollitia ut voluptates cupiditate et nesciunt ut qui.
Commodi aut impedit magnam doloremque culpa quia ea id. In sint voluptates velit non quos molestiae a.
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...