Introduction
Welcome to part eight of our Public REIT valuation series where we build a real estate private equity model covering Aimco. In this post, I am going to publicize my greatest, never-before-revealed trick: global and local checks. Plenty of people build checks inside their models, but I’ve never seen anyone else do it this way. Once I came up with this trick, I started to model at least three times faster.
It’s so simple, it takes almost no effort to build. But these aggregated checks will make all the difference as your model’s complexity compounds upon itself. So without further ado, here is how I insist you model your checks to instantly know that the changes you make in one tab flow perfectly into the rest of your model.
(FYI – if you’re looking to download the Excel model for this post, you’ll need a subscription membership first!)
The Most Important Tab: global_check
The first tab I always make is called ‘global_check’. The apostrophes around the name just mimic how Excel references worksheets (e.g. =’global_check’!A1), so for the tab itself you’ll just name it global_check. This ‘global_check’ tab will roll up every local check we build in each other tab in our model.
In short, here is how ‘global_check’ works:
- Every tab has a local check that is displayed in A2 of the respective tab
- The ‘global_check’ tab references the local check result in cell A2 of every tab
- Finally, the result of the ‘global_check’ is displayed in cell A1 of every tab
I also like to put my title in the ‘global_check’ tab and reference it from the other tabs. So if I ever want to change the title of my model, I can easily do so. For this model, I’ll use the title “Aimco Public Valuation Model – 2Q20.” Here is how our new ‘global_check’ tab looks:
Inside cell D8, I have a function that “countifs” anything between D5 through D7 is not equal to zero. Right now, everything in that range is zero.
Building Our First Local Check
Let’s go ahead and make our next tab. We’ll call this new tab ‘NAV’. This is where we’ll build our NAV analysis, as the name suggests. Let’s go ahead and style this tab similarly to ‘global_check’, and let’s also create local check in the bottom. Let’s keep a blank row inside our local check on the NAV tab, and let’s build the same countifs formula on our local check.
Notice how a blank row flags a ‘1’ in the local check. That’s because in Excel, a blank cell is not the same as a cell with a zero inside it. This is important to keep in mind, and I leverage this to remind myself to finish check ideas I write out in the local check as I work through models. Let’s keep this error flagged, we’ll clear it out in a moment.
Here is how our ‘NAV’ tab local check looks. FYI, inside cell D10 is a formula that reads =COUNTIFS($D$7:$D$9,”<>0″).
Connecting Our First Local Check to global_check
The next step is to link our NAV local check into our global check tab. Go and replace the zero in ‘global_check’!D6 with a link to ‘NAV’!A2 (where we reference our local check) and change “none yet” in ‘global_check’!C6 to the tab name. Refresh and see that the NAV tab now flows in, flagging that error we purposely built with the blank row:
Building a Second Local Check
Let’s repeat the same process for another new tab called ‘itable’. The ‘itable’, or information table, is where we will keep our asset-level data in subsequent steps. Inside itable’s local check, instead of a blank row, make one called “none yet” and hardcode a zero inside the “result” column. Your ‘itable’ local check should resolve to zero. Below is how your new itable tab should look:
Then link your itable check into global_check the same way you did with the NAV check. Here is how your global_check will appear with both tabs’ local checks (cell A2) linked in:
Linking the Global Check into the Other Tabs’ Cell A1
Alright, we’re still missing something in every tab. As your model grows, you want to be certain that the changes you make in one tab do not break any other tabs. Our ‘global_check’ aggregation of all local checks tackles this problem pretty seamlessly. In each tab that isn’t ‘global_check’ (right now that’s itable and NAV), you should link to ‘global_check’!A2 inside the local cell A1. See how I’ve done it inside tab itable as an example (I’ve done the same in ‘NAV’ as well):
Examining the Power of Centralized Checks
The power of global and local checking is highlighted in the screenshot above. Let’s say I’m working in itable, and all of my checks pass everywhere. Then I tweak something – say I add an asset. I refresh, and my itable local check still passes. But something broke somewhere else. Maybe the NAV tab is excluding that new asset, and the total value on that tab does not match the total value on your recently-updated itable tab. If you do not have a ‘global_check’ setup and linked into your itable tab, you might not catch this error until your boss calls it out in a meeting! Throughout your career in real estate private equity, you never want to make avoidable mistakes such as these.
But with the aggregate result of ‘global_check’ flowing into cell A1 of each model tab, you will spot busts the moment they happen. If your global_check in A1 isn’t zero, you know something must be broken! Just don’t grow lazy, your checks are only as good as you build them. So check well and check often. There’s no such thing as too many checks, and there’s nothing wrong with accidentally building the same check twice if you forget. Just pile them on!
And before we go, you can go ahead and clear out that fake error in NAV by replacing the blank row with a zeroed out “none yet” instead. We’ll replace it with some real checks in a bit. Once you refresh, all checks should now be zero in A1 and A2 of all tabs.
Learn with the Best
Leveraged Breakdowns prepares you for a career in real estate private equity through hands-on instructions for outsiders by megafund insiders. We offer courses on building real estate private equity models and guides to prepare you for your next interview. On top of this, we’re active in our forums and comments sections for anyone with a question, regardless of whether you’re a subscription member.