Acquisition Model vs. Asset Management Model
Hi all,
I've been tasked with building an AM model for a mixed-use deal we're about to close on (70% resi / 30% commercial). The acquisition model used for this deal has run its course and the model's structure will make it a nightmare to build in AM capabilities.
All prior models I've built have been acquisition models but I understand, to a certain extent, the difference between the two:
- AM model is significantly more detailed: hard / soft costs broken down by line item and then modelled in more detailed (e.g S-curve, flat line) - also applies to income line items
- Being able to show any variance between the acquisition underwrite and actual costs / revenues for reporting purposes
- Reforecasting abilities should event XYZ take place (e.g. construction delays)
- Capital Events (capital contributions/calls, distributions, hold/sell/refi analysis)
In terms of setting up the structure of the model, what should I be keeping in mind? Any obvious elements that should be included? Any examples?
Any guidance on how to deal with capital events would be greatly appreciated.
Curious to hear your thoughts/experience.
Thanks
EDIT: Thanks to all who've replied. At this stage I'm putting together an outline of the model - taking into account the feedback below - and will present to my principals.
I made a model similar to this years ago, can't remember all the exact nuances, but generally this is what I suggest....
I would start with a simplified acquisition model as a template (like, a few revenue line items and 5-10 expense line items all condensed into major categories) with simple toggles for inflation, exit cap/year of sale, etc.. At acquisition have it match the acquisition model in terms of annual rev/expense/NOI projections but much cleaner to look at and easier to manipulate with broad assumptions. From there, I would:
1) give yourself the ability each year (or quarter if you want to go crazy) to replace operating projections with actual results (or put them side-by-side), and then re-project the remaining years using assumption toggles
2) give yourself a detailed section below NOI to plug in various capex and debt scenarios to update the flow of NOI to cash flow, i.e. ability to move major projects from year 2 to year 3, or change their total costs, or move a refi from year 8 to year 7, etc.
3) below that section, create a space to run the cash flows through the JV structure so you can see how any changes from #1 and #2 above (and how plugging in actual results vs original model projections) impacts the returns i.e. GP IRR or LP cash-on-cash or gross equity multiple or whatever else you care about. Note that for this you shouldn't just assume all cash flow is distributed, but use what is realistic... i.e. if this property is going to hold $200k in reserves and distribute in multiples of $5k, and cash flow is $311,234.98, make the distribution $110,000, and track LP capital balance for if/when the distribution is in promote, etc. For the rows tracking capital balance, you can also give yourself the ability to add in new calls.
Sorry this ended up being to hard to help more thoroughly without my old model in front of me.... hope this is somewhat helpful and let me know if you have Qs.
any chance you could create a file of this model with all of your inputs cleared and share? would be incredibly helpful trying to learn on the fly here
Lol I don't want to be rude here, but it would be a tremendous amount of work for him to build that out for you.
Something with this level of depth and flexibility takes a lot of time to build out, it's not like a 1 hour modeling test.
? by "create" I just meant a new file save that was clear of all inputs that might be identifiable or deal-specific - sounded like it was just a matter of finding the model, not actually creating the model from scratch
For asset management its more updating and tracking the current and then using that to forecast and adjust the future.
You still obviously need an assumptions tab like usual however you wouldn't want to be changing this as once you've added in the actual acquisition assumptions and financing assumptions you really don't want to change them unless of course things change such as variables but things that are constants don't change like the purchase price.
So it needs to have ability to adjust for each years change in events that will impact the yearly and monthly cash flows and the change that has on risk metrics and return metrics e.g:
Dynamic ammort schedule for any debt on property and if interest rates change this needs to be able to adjust in the model.
Changes in inflation each year
Changes in vacancies each month and added cost of damages ect.
Missed payments and late rent collection and updated probabilities of future missed payments by other tenants in same industry.
Probabilities on loan default on leverage in relation to vacancies, cap ex, interest rate jumps every quarter ect.
Updated cap ex when new cap expenditures need to be deployed like e.g. air con repairs and bullshit like that.
Depreciation schedules and tracking that as each depreciation item in the aoetmentd and suites depreciate at their specific dep rate per period and how that relates to the irr and cap ex.
For acquisition models its mainly assumptions upon assumptions basically a big giant educated guess. Your models with look a lot less attractive. You're taking the real data and updating the present and the model should be formulated so future assumptions are updated automatically.
Then you can do your best to quantify qualitative information like market confidence in office or retail and how this may effect vacancies in future, or talks of increased corp tax and how companies may want to purchase and write off interest on their properties instead of lease their office. These qualitative info points can be quantified into probabilities and percentages which can reflect a realistic assumptions of the future and how this affects the asset.
You can visualise the data as it updated every month or week or day for the asset like the amort schedule s curve and equity return graph cap ex graph and so on. These should be dynamic so as the assets events unfold and are imputed into the model you can instantly visualise these effects and make educated decisions for the asset.
That's what I think anyway.
Also if you're in reit asset management you can incorporate yahoo finance into model so you get a live update on share price in relation to model which can be helpful.
The first question is are you required to build the AM model to flow draw from the acquisitions model? If so I would recommend using seperate files for the Acquisitions model and AM model. This makes it easier to keep the acquisitions model "static" when you have the data flow from a completely different file.
As for how to set up the AM model I always first like to get clarity on exactly what is the purpose of the model. Complexity for the sake of complexity is never a good idea. Many models I see on a daily basis are tracking and projecting things that are completely unnecessary to capture the important tracking values.
For example I see many value add 24 - 36 month exit deals tracking expected changes in utilities costs on MF deals where the tenants are responsible for 100% of the utilities. Sure it can tweak the projections of the model slightly but in the grand scheme of things it isn't relevant to the story.
Find out what the KPIs are and build the model to fit that, eschew added complexity.
Yeah don't want to over do pointless stuff
Dolores cum perferendis fugit et eum. Perspiciatis quasi odit sit distinctio et optio earum. Commodi sed et dicta amet. Quidem qui perferendis pariatur aspernatur cumque non voluptas eaque.
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...