Reply To: Case Study Challenge

[Lev]

For posterity, here is a link to the case study that I reviewed, submitted by Caringpineapple.

Elements I Appreciate

1. I like your use of (+) & (-) to indicate addition and subtraction. That improves the readability of this model, which is an important judgment criterion.
2. You’ve made a great effort to visually isolate different sections using colored headers, borders, and font style.
3. Your math is generally correct and well laid out. I apprecaite your demonstration of the growth formulas under the respective cash flow lines.
4. I like the left-side margin you create with column A. It would make it even sexier if you made a similarly-wide column at the end of the page too, and delineated the print range with alt+p+r+s. This visually confines the space.
5. It’s great that you included this mortgage debt schedule. many real estate private equity case studies I review often leave this out and it irks a lot of people, especially those with debt backgrounds.

Elements I would Suggest Changing

1. Monthly cash flows are hard to read as a manager. Over your real estate private equity career, your superiors will appreciate your ability to speed their review. Thus, I recommend you create an annual cash flow summary of the relevant cash flow line items. No need to annualize the backup build-ups that don’t directly generate IRRs. Of course, link to your monthly IRR calculation. More on the proper XIRR formula below.
2. Your use of borders is a bit noisy. If you start putting everything into some kind of box, then nothing is in a box. Perhaps a nice grey fill can indicate your T12 financials against the roll-up, or space. You have more visual elements in your toolkit that can separate information for your reader.
3. I’m personally against using multiple lines to name my cash flows. For instance, you put “Revenue” in column B and “(+) Base Rental Income” in column C.
4. Why am I against this? Imagine this single-asset model you’ve built was one of many within a portfolio roll-up model. If you want to find out what all of your assets’ property management fees are, you would probably use a SUMIFS or INDEX-MATCH. These formulas would need to be awkwardly retooled to lookup on two columns of names rather than one.
   1. The solution I prefer is to tab out your names using alt+h+6. This is good for two reasons. First, you achieve the same visual effect without messing up your column of potential lookup keys. Second, alt+h+6 is pure styling. Thus, you dont’ need to make your CF name ”    (+) Base Rental Income” (note the four spaces). Why this may seem small, your lookup key may not include those four spaces. In that instance, you would miss this item with spaces in its name. Hopefully you would catch this with a global tie-out check, but why run the risk?
   2. If you’re adamant about using two columns, and need to perform a lookup in the future, I recommend tagging your cash flows in column A (or adding an additional column on the far left) with standardized lookup names with no spaces, such as base_rental_income
5. You’re missing a unit mix. While it might seem stupid for an apartment with just one type of unit, your buy-side interviewers (or colleagues) are likely used to seeing unit mixes. It is visually easier for them to interpret that information, and will make you appear like an insider.
6. Every single one of your cash flow lines has a $ preceding the figure. All financial professionals, real estate private equity investors or otherwise, follow the following rules when formatting cash flows:
   1. The first in a block of lines starts with $
   2. Subsequent lines within the block do not start with a $, rather are just numbers with commas
   3. Sumlines always have $
   4. Anything afer a sumline does not have a $
7. Your IRR formula should just be XIRR. Look up the docs on how to use it, it’s pretty straightforward.
8. You’ve grown your cap rate with a hardcoded +0.0005. This would be better as its own input, perhaps right above the cap rate schedule. Most real estate private equity investors I’ve worked with stick to a linear expansion rate, but others may disagree. This isn’t a huge issue, the way you’ve done it is also common.
9. Your “NOI for the final year” formula is not flexible. Use a SUMIFS on the year #. This will allow you to flex the exit date. Same thing for your exit cap rate lookup, this could be achieved with INDEX or SUMIFS (and a few others).

Neat Formulas / Tricks You May Not Be Aware Of

1. You’ve hardcoded your “Hold Period Year.” I hope you didn’t manually type every single number, that would be a waste of time. If you weren’t aware of this trick, try typing this formula into cell E26 of your vanilla model (the first instance of “Hold Period Year 1”): =IF(MOD(D25,12)=0,D26+1,D26). Respond in this thread with what you think this formula is doing that makes it count the years properly. How would you use it to count quarters?
2. Real estate private equity investors like to know what year/month they’re reading as quickly as possible. Sure, you’ve labeled the “Hold Period Month” & “Hold Period Year” off to the left. But the first direction a reader’s eyes will go is up when they are looking to confrim the year/month. Thus, I recommend you style every cell  to identify which month/year it is with more detail. More specifcially, highlight all of your Year cells. Hit ctrl+1, tab, end, tab, type in “Year “0 (use the quotes like me, type everything from ” to 0). Enter. Repeat for the months. If you need to copy and paste style, hit ctrl+c (copy) then alt+e+s+t (paste only style).
3. Type alt+w+v+g to hide the grid. Do you see all the excess borders you copied & pasted over? This would reflect poorly during a real estate private equity case study interview. Make sure you don’t include noisy formatting like this.