Learning Excel / spreadsheet techniques for portfolio analytics and operations

10-15 isnt that big a name cell + roll-up macro will work wonders

Why aren't you using Argus?

They are multi-family properties, we only use Argus for office/retail

Makes sense

Well if you're going to do it in excel anyways you can link cells in other files. Its very easy a quick google of "how to refrence cells in other excel files" should help you out.

Unless I'm totally missing what you are asking.

I would recommend a macro

If that's too much of a black box for your bosses, at the very least I would assign codes for every line item in your cash flow so you can point to each step of the rollup

I don't think a macro is really necessary. You can just reference the other workbooks in a "summary" workbook. They auto update and will save you a ton of time compare to a macro with negligible benefit.

^ He can do that only if the same values (like Yr 1 NOI) are in the same cell in each workbook (if hes doing an indirect or something). Otherwise it will get ugly

I dont see why that has to be its just =[abc.xls]sheet1!a1 you can point them all anywhere and individually

build a roll-up of all financial statement line items from all 15 props then eliminate duplicates so that you have the same list of items on each sheet.

then you can build your statements from there, that way each property has the same line items (all starting in cell d3 for example) and you can quickly link all of them back through to a summary sheet.

No you dont have to do them individually, just click/drag/copy across the lines, it works the same way. On a portfolio level analysis I'm sure hes just taking EGI, gross expenses, NOI or each proprty and putting it into a summary.

Its not like hes copying every line of every workbook. Even if he were, with two click/drags he has the entire thing...

Edit: anyways moral of the story is its not any different than linking to another sheet inthe same workbook

True. When I roll things up, they are not always next to each other which is what i stated above could be the situation. For ex, pulling cap rates from a valuation table, pulling specific year IRRs, CoC, Lev IRR numbers, etc. which will not be an easy formula pull and have to be individually linked

if you set up each individual property summary in the same way and always starting in the same cell, its an easy link-through to a summary page.. or are we both saying the same thing?

I realised that this thread should perhaps be in the ER forum?

You can probably do what you're after. You'll need to write your VBA to look for the first empty row/column and then append your new data there. Just because you can do this, doesn't mean you should though. Excel is just not meant to house a large database of stock price/return data, and approaching it in this way will inevitably lead to a cumbersome and dysfunctional system. If you really want to do it this way, I think you'll get better results housing your data in SQL or some other relational database and doing your analysis in python or R.

Shameless bump

Do you have access to a bloomberg terminal?