Leveraged Breakdowns

Live Case Study #1, Part Six: Formatting

Introduction

You’ve probably begun to sense a bit of rhyme and reason to my formatting. If it isn’t clear, I’m an absolute nut when it comes to consistent formatting. I am always inventing new tricks on my real estate private equity career path, but I make sure every trick slots into my preferred schema. Models exist to convey information, and formatting serves to guide your reader through your documents. We like it when models are easy to read, easy to understand, and easy to use.

My Style, Explained

You will probably dislike some of my styling choices and that’s fine. You’ll invent your own style during your career in real estate private equity. The important thing is your style is easy for an outsider to read, comprehend, and trust as quickly as possible. Without further ado, here’s a list explaining my primary styling components. I’ve developed these over the years, and it’s always evolving.

  1. Elevators: The X’s on the left are elevators. They help you skip between blocks (3 X’s), subsections (2 X’s), and detail breakouts (1 X). You can skip from one non-empty cell to another non-empty cell by pressing ctrl+arrow (up, down, left, right).
  2. At the top level, blocks group information and logic that serve the same purpose. Shown as black background, three elevators. For instance, our first block will have all our control toggles (as you can see in the snipping above). Below Control, we’ll make a second block with our monthly and annual cash flows.
  3. At the middle level, subsections are subordinate to blocks and help further separate information. Shown with dark blue background and two elevators. In this specific instance, I’ve separated informational data (just there for our benefit) vs. live data that will actually control our cash flow model.
  4. Finally, detail breakouts (light blue background, 1 X) help further subdivide information that is generally similar. There are no hard and fast rules, but I find having three hierarchies of grouping help me model cleanly.
  5. Hardcoded inputs are always blue and I like to also give dark grey backgrounds to direct inputs that impact returns.
  6. The little grey rows help with a specific but frustrating problem. Whenever you build an array formula (sum, index-match, etc.), it stretches when you add rows and contracts when you remove rows. When you’re moving fast, it’s hard to remember exactly where these rows start and stop. If you want to add something into a list, these little grey rows help you know for sure it’ll be placed within the array’s range.
  7. I like to collapse things starting from left-to-right and top-to-bottom. I also flag when things are collapsed.
  8. My timelines are designed to avoid repetition of common logic. Rather than build every CF line with an if statement, I repeat the common expressions with one and zeros. I think link to those rows with multiplication rather than build an if statement every time that may change. Also makes it way easier to shift timeline controls if I need to change the location of a source exit date or something like that.
  9. Unless you have a reason not to, always set your model’s dates to month end. I see too many people model on the first day of the month (heathens). Why do I have a problem with modeling on the first day of the month? Because cash flows should represent the activity for the period in question, and it’s needlessly confusing when 6/1 cash flows represent activity through 5/31. Just put 5/31! But I’m going to diverge from this month-end rule because my bond matures on the 15th and coupons are paid on the 15th.

Don’t spin your wheels on this formatting bit, I just wanted to provide the rationale behind my formatting and layout styling if you are unfamiliar. When you’re just starting on your real estate private equity career path, it’s good to at least be aware of formatting. But you don’t need to be nuts like me.

If you do wish to improve your formatting, the best way is to continue practicing models. In fact, my own style is always evolving. I just have the advantage of a full-time career in real estate private equity.

Member Exclusive: Download Complete Model

Leave a Comment

Scroll to Top