06 Nov 2016

How Spreadsheets Lie and What You Can Do About It

Adam Geha

06 Nov 2016


Proverbs are tweets from our ancestral past – messages that have so persistently proven their utility as to float above the implacable undertow of time.

Most messages are lucky to survive 10 years – so when a message has endured for over 500 years, it’s generally worth listening to. And here’s one of my all-time favourites:

For want of a nail, the shoe was lost,
for want of a shoe, the horse was lost,
for want of a horse, the knight was lost,
for want of a knight, the battle was lost,
for want of a battle, the kingdom was lost.
So a kingdom was lost—all for want of a nail.

It’s a medieval warning about the potentially catastrophic consequences of a small detail overlooked: in this case, a solitary nail to fasten a horseshoe. The consequences of course are only apparent in hindsight – who amongst us looks upon his unshod horse and thinks the kingdom might fall because of it?

And when searching for the missing nail in real estate deals, look no further than your trusted financial model. Spreadsheets are like the friends we choose to believe, even though we know they are flattering us.

Why do we so willingly believe our deceivers? Because, to borrow the words of Jack Nicholson in A Few Good Men, “deep down in places you don’t talk about at parties” you want the spreadsheet to lie, you need the spreadsheet to lie … to flatter the returns (just enough) to justify a winning bid.

In the world of flesh and bone, some cash flows are more equal than others.

And nowhere is the financial model a more seductive liar than when it speaks to you of cash flows. For in the abstract world of spreadsheets, all cash flows are created equal. But in the world of flesh and bone, some cash flows are more equal than others.

This is why at EG we run all our spreadsheets through a polygraph test (PRISMS®), and one of the key metrics we focus on is the ‘density’ of the income streams (i.e. their ‘stochastic density’ or likelihood of occurrence).

Spreadsheets pretend that a dollar of contracted income (income from executed leases) is no different to a dollar of uncontracted income (assumed income from renewals or new tenants). This is patent nonsense – but to the spreadsheet, when it comes to computing an internal rate of return (IRR), all dollars are created equal.

Spreadsheets are like the friends we choose to believe, even though we know they are flattering us.

So, the first thing that PRISMS® provides is a percentage split between contracted and uncontracted income. The higher the percentage of uncontracted income, the riskier the return. This may be a pompous statement of the obvious, but you’ll be surprised how often this fact is overlooked (or under-appreciated) when comparing two deals with similar IRR’s.

Still, even when the percentage of contracted income is identical, the ‘density’ of the cash flows may still vary considerably. This is because contracted income varies in stochastic quality, depending on the creditworthiness of the tenant.

PRISMS® does this by scanning for the creditworthiness of the tenants and computing an Income Security Coefficient (ISC). The ISC measures the likelihood that contracted income in a spreadsheet will actually materialise: a score of 90 is virtually certain (a lease to Government or, say, BHP) while a score of 10 is highly uncertain (a lease to a cash-burning start-up).

At EG, we assign one of four designations to contracted income: blue chip (strong); blue chip (weak); non-blue chip (strong) and non-blue chip (weak). Blue chip tenants are relatively easy to recognise given the wealth of public information typically available – but how does one go about discriminating between strong and weak non-blue chip tenants, especially since most of these tenants are private companies?

As with most real world problems, the better path is to attempt to be approximately right (and estimate it) – rather than precisely wrong (and ignore it).

It turns out that modern research on corporate failure can tell you a great deal about your tenants. Here are three criteria that “strong” non-blue chip tenants tend to exhibit:

1. Annual revenues of at least $10m. Most tenants will happily provide a ball park revenue figure but if this is not available, then use the number of employees as a proxy – in this case, at least 50 employees.

2. Length of time in business of at least 10 years. Statistics show that 50% of small businesses fail within 5-years and 67% fail within 10-years. The failure rate drops off dramatically, the longer a business survives.

3. More than one office or trading location. Evidence shows that businesses with multiple office or trading locations are 60% less likely to fail than companies with a single office or trading location (in other words, geographic diversification is key to long term survival).

The information above is readily accessible but again you would be surprised how few investors – even the most professional and sophisticated – bother to collect it. In large part, this is because we are all lulled by the great lie that all cash flows are created equal.

As for me – I shall be telling this with a sigh, somewhere ages and ages hence: two spreadsheets diverged in due diligence and I, I took the one that did less lie and that has made all the difference.