Real Estate Investment Case Study Challenge #1: Solution Step 1 of 3

Introduction

This post covers the solution to our inaugural real estate investment case study challenge. You may download the solution here. Submissions were due April 30, 2019. Because practice makes perfect, you should first actually try solving this simple real estate investment case study on your own. The original forum thread is a great resource if you get stuck. Feel free to continue asking questions about your real estate LBO models in that original forum thread, even after the deadline. Finally, this solution post assumes you’ve read the prompt and judgment criteria, shared in the original challenge post.

Download the Solution Here

This post breaks the solution into bite-sized steps. You can download the solution to the real estate investment case study challenge by clicking this link. The Excel file mirrors the steps described in this series so you can easily follow each stage of the modeling process. Each step builds on the last, with the last step representing the complete real estate LBO model solution.

Step One: Build your Control Panel

In this first step, you will translate all relevant data from the prompt into a blank Excel to start making your real estate LBO model. Again, you can find the prompt for the real estate investment case study at the bottom of the original challenge post.

In step one, we enter all relevant data into a section we call the Control Panel. To make your life easier, we have created this control panel in tab ‘Step 1’ of the solution package. Step one is primarily data entry, so the working notes below focus on style and arrangement (our second judgment criterion).

Notes to Guide your Review of Step One

1. Check out the ‘Style’ tab in the backup section of the downloadable Excel solution should you wish to copy any formatting
2. Notice how our model is contained within a print range. You should always limit the content of your real estate LBO models within defined print ranges, even if you don’t plan on actually ever printing it. Professionals use Excel print ranges to define boundaries. Without defined boundaries, it is impossible to tell if some random cell far in the distance might impact your model. With defined boundaries, it is obvious where your model starts and stops. Anything outside of the print range is assumed to be unimportant and could be deleted without consequence to the model
   1. Set a print range with the following shortcut: alt+p+r+s, then alt+p+w+1+enter, then alt+p+h+1+enter
   2. Type alt+w+i and alt+w+L to switch between print preview and normal mode
   3. Turn off gridlines with alt+w+v+g. A rookie mistake is to color every cell white, don’t do this
3. Giving each page a proper title and subtitle is an extremely low-effort feature that makes your real estate LBO model easy to read. Your title and subtitle should clearly explain the entire function of every tab
4. Elevator columns allow you to quickly jump between sections and subsections with ctrl+down arrow and ctrl+up arrow. Try it out yourself. You should never need to touch the mouse during a live real estate investment case study
5. I give major sections the styling of Header 1 (see the ‘Style’ tab), which I like to make dark grey fill and white font. For instance, the entire Control section is a major section that is separated into different subcomponents for assumptions regarding hold period timing, valuation, operations, and leverage
6. We give each subcomponent the dark blue Header 2 to visually identify the information as subordinate to the main Control section. Structuring our information this way allows any reader to quickly find whichever control feature they may be reviewing.
7. I always give the first column a width of 5 and the subsequent elevator columns widths of 2 (set width with alt+h+o+w)
8. On font color, all hardcodes must be blue, all formulas must be black, and all external links to other sheets must be green. Every bank and buy-side fund across the globe styles their real estate LBO models this way. Note, this model will have no external links, so nothing should have a green font
9. Always save your models with the cursor in cell A1 of each tab. You can do this quickly by hitting ctrl+home to hit A1, and ctrl+PgUp/PgDn to switch between worksheets
10. I’ve created a basic rent roll even though we don’t specify rents by unit type. Interviewers would appreciate seeing a rent roll, even if the data is one line and doesn’t include average unit size

Benefits of a Leveraged Breakdowns Membership

Want to conquer your next real estate investment case study? Nothing beats live mentorship. By subscribing to a Leveraged Breakdowns membership, you get access to hours upon hours of live real estate LBO model walkthrough lessons. Further, you support the development of high quality educational materials for outsiders, by insiders.

On to Step Two

Stay tuned, solution step two will be published in a couple days.