Pursuing Acquisition Analyst Position - Modeling Question

If your lost by putting assumptions into a model and forecasting cash flows than you have no business in that position. He is asking you to build a proforma to demonstrate that you know how to underwrite an investment and show the projected yields. If you cant do this for a simple single property acquisition in excel, than your going to have alot of trouble when it comes to portfolios and developments. The majority of investment analysts sit at their desk all day looking at deals and this is a pretty straight forward request.

If your serious about this job, I would buy the BIWS real estate modeling course or something off REFM b/c you clearly dont know the basics yet

I'm guessing since you dont have a ton of experience, it doesnt have to be TOO detailed. You have the OM so you can use that to justify all of your assumptions. For instance, if the management says something about keeping occupancy rates high, you can have a less aggressive growth in revenue because of this. If they're more concerned about revenue, then you can have a more aggressive growth rate.

Make a somewhat high level Income statement w/ some line items in there (Rev - Operating Exp - Management fees = NOI or something like that... I think the other guys might can speak more to this)... Trend it out 5 years and account for some inflation like 2%

Create some growth percentage you feel comfortable with for the revs. Again, use the clues in the OM to help you with this.

Make everything a percentage of the revenue based of the years, or base it on the tenants that will be in the building.

For instance... if Maintenance is fixed, keep it solid and just inflate it 2% per year.

If labor is variable, based on revs... and the line item is 20 percent of revenue, then maintain that through the 5 years.

If there are some comparables in the OM, use that.. If not, go do some research and find out.

I'm not sure if they want you to valuate the company, but if they do, get to some cash flows, plug it into a DCF model w/ a few random loan to cost ratios, and discount it back to get a value and see if they over/underpaid

I'm just sitting at work bored, though.. So if I'm wrong w/ the high level, step in and correct me, folks.

You should really take a course. You might be able to get a model from a friend or dig something up on Google but if you don't have a solid understanding of how a model flows then you're going to get pretty tangled up and it will be obvious when you submit for this position. Maybe I'm not giving you enough credit, I'm only going off your original post. Getting the foundation of how to set up the model and the right functions to use is critical. Once you get that down, it's like anything, you just keep building on more complexity and functionality.

Feel free to PM me your email address and I can send something over to you. My guess is this is what they're looking for:

1. Project the scheduled rent for 11 years. The rent roll in the OM should have in-place rents, scheduled rent bumps, etc... For sake of simplicity, when a lease ends just assume their rent grows at an inflation rate (make this one of your assumptions).

2. Project the tenant reimbursements. It'ss an office deal, so a majority of leases will be gross or modified gross. Your best bet is to just grow the in-place number on the OM by your inflation rate, or just take the OM's numbers for the next ten years.

3. Project other income. Just use the OM's number for the first year (could have this be an assumption as well) and then increase it by the inflation rate.

4. Throw in some general vacancy (also have this as one of your inputs as a percentage). General vacancy is whatever percentage you have as an input multiplied by the sum of the scheduled rent, tenant reimbursements, and other income.

5. Project the operating expenses. Just use the OM for the first year (could have this as an assumption) and then grow it by an inflation rate (create a different assumption for the operating expenses inflation rate).

6. You now have an NOI number (unlevered cash flow) for the next 11 years.

7. Create debt assumptions (interest rate, loan-to-value ratio that determines loan amount, how many years it's amortized over, number of interest only years). Use them to project debt service for the next 10 years.

8. Whatever additional expenses the OM has, just copy or use the in-place and then grow it by your expense inflation assumption. You're not going to be able to project these.

9. You now have a Net Cash Flow number (levered cash flow) for the next ten years.

10. Hopefully it's pretty clear how to calculate the unlevered / levered IRR's and DCF values at this point, but your unlevered 0 period cash flow is the total purchase cost of the property (make this one of your assumptions) and the asset disposal cash flow in year 10 is the year 11 NOI / exit cap rate (make this one of your assumptions). Your levered 0 period cash flow is the equity you put into the deal and your year 10 asset disposal cash flow is the year 11 NOI / exit cap rate net of debt repayment. Make your levered and unlevered discount rates assumptions as well.

There are other things that would be good to include (sources and uses, sensitivity analysis, etc...), but there's just no way to get you up to speed on that stuff quick enough. Good luck and keep in mind you need to be able to talk through all of this in your interview, so if you don't understand it make sure you do.