3 Things Asset Managers Can Do to Prevent Spreadsheet Errors
Spreadsheets are fundamental to commercial real estate. After all, most of the information that powers leasing lives in static Excel files spread across email inboxes and network folders.
We practically live in these files. Which makes the fact that roughly 90% of them have errors that much more upsetting.
That’s right. According to an academic study done a few years back, 88% of spreadsheets have errors in them. In other words, if you trade 10 different Excel sheets during the course of a deal, all but one will have an error. Those mistakes add up to real dollars.
“A very disturbing picture”
Raymond Panko, the academic that ran the initial study, found the results painted “a very disturbing picture” about spreadsheet development. He wrote, “Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable at any organization.”
Although the number may sound high, it is apparently in line “with error rates found in other human activities.” As a result, larger spreadsheets are most susceptible to errors — yet “even relatively small ‘scratch pad’ spreadsheets will have a significant probability of error,” wrote Panko.
Adding salt to the wound, Panko learned that the prevalence of mistakes increased when multiple parties are involved in spreadsheet development and manipulation — a phenomenon that CRE knows well. A recent survey found that the average commercial deal involves six different people with six different reports. How many errors do you think sneak through the cracks with that many points of failure?
Why are they so common?
Although you might feel extremely confident in your Excel skills — after all, you’ve probably developed thousands of them — even the pros are very susceptible to making mistakes. In another study, nine “highly experienced spreadsheet developers” were given three spreadsheet development tasks. The results were alarming: all made at least one error, and 63% of the spreadsheets overall had errors.
One of the reasons errors are so prevalent in spreadsheets is because they can appear in a variety of ways. Mistakes can be made during data input, formula writing, copy and paste, and countless other ways. Whatever the cause of the error, the results are equally as consequential to the spreadsheet’s validity.
Not only can these slip-ups deliver incorrect data to your clients — which could inform poor decisions for investments, leases, and notice dates — they can tarnish the reputation of a given team. If an error is discovered in one part of the spreadsheet, it can undermine the credibility of the entire team and the data you’ve provided to date.
How to avoid them
Needless to say, such a high rate of errors is a bad thing. Luckily, there are a few ways to reduce your exposure to spreadsheet errors:
After Panko released his study — causing a wave of alarm among spreadsheet-dependent industries — several programs and certifications were created to establish spreadsheet best practices. Although these programs may help reduce the frequency of error, the chances of a certification eliminating all errors from spreadsheets is very unlikely.
2. Peer review
If you approach spreadsheet development with a spot-checker, you can dramatically reduce the prevalence of errors. Panko’s study demonstrated that people can be quite good at finding mistakes — if they are looking for them. Just having two people work on the same spreadsheet does not error hunters make. Although this approach can be effective, it can also be extremely timely and expensive. Imagine having two brokers on every account so one can check the excel work of the other? Creating and updating spreadsheets for a deal is already a time-consuming and archaic process — introducing another person and checkpoint to the system would make the process even slower.
3. Get a leasing management platform
The best way to eliminate spreadsheet errors is to remove the spreadsheet entirely from your deal process. Although it may sound like a dramatic proposition, given that spreadsheets are the foundation for most of commercial leasing and asset management, a new wave of technology is helping to remove that very dependence on siloed systems and antiquated technology. Leasing management platforms — like VTS — enable commercial brokers and owners to streamline their leasing business with cloud-based technologies that serve as the single system of record for all data, insights, and reports.
Maybe Panko will run his study and find the results are much more comforting. Maybe.