Excel has been around since roughly 1985 and although there have definitely been some feature improvements in 30 years, it can only do so much to prevent mistakes. At this point most people know that 90% of spreadsheets have errors, which makes double and triple checking data and calculations a must. Many companies have tried to mitigate these errors by creating policy and standards for formulas or instating a peer review system. Even with these checks and balances in place there is still room for an accidental keystroke, an overlooked transposition, or entering information that already exists. So let’s talk about how CRE tech can replace Excel, one Excel problem at a time.
Spreadsheet and data auditing
Spreadsheet and data auditing are necessary evils due to human mistakes, redundancies, and the sheer amount of information dealt with on a daily basis. Luckily in 2016 there is a rich technology ecosystem waiting to automate and improve upon any menial, manual task you might face. You really can eliminate checking for duplicate data, finding labeling inconsistencies, and trying to figure out which data to rely on.
There is a remove duplicate function in Excel that can scan your spreadsheets to eliminate any identical sets of data, but you may still end up with similar data if it’s not an exact match. You also miss out on knowledge of where the duplicates occurred. So now you have a spreadsheet with less duplicates from before, but not zero, and data that has just disappeared. YIKES! As a company that works in data, this is a terrifying thought.
For a long time that was your only option, then came some technologies that assisted in data cleaning such as Google Refine. While these programs are incredibly powerful, they still require a good bit of work and, as an open source program, it means you get what you get, bugs and all.
With all the new technology available for CRE, any data platform that is worth their salt should offer to do the data and spreadsheet auditing. This low level task should no longer be the responsibility of a research or data team when they can be using their time to explore that data and provide their teams with analysis.
Making sure setup is 100% correct
Most calculations in spreadsheets depend on linked cells. That leaves plenty of room for worrying about hidden columns, rows, and sheets or misplaced numbers causing inaccuracies in calculations.
If the cells are linked incorrectly or there are hidden fields being taken into consideration, it could mess up an entire table of cap rates. The best way to avoid that magnitude of error is to use something to calculate that isn’t exposed to human error or reliant on cell linking. It is safer to use a platform or a web-app that handles the calculations behind the scenes, only leaving inputs, so there is no chance to change an equation.
There are an abundance of basic CRE calculators online to choose from, but if you rely on proprietary equations, it would be worth your time to look into services or products that already have or can build proprietary calculators. When calculations are hidden behind an input interface, the world is a better place.
Data auditing and linking cells is one thing, but when it comes to numeric input, if the values entered are wrong, it won’t save your data. You can never guarantee there won’t be a rogue zero or a misplaced decimal. What can help in this situation is having calculations done behind the scenes, like we mention above, with established ranges for both inputs and outcomes.
Instead of manually going into your spreadsheet to set every single cell with a range or locking down the static numbers in an equation, let technology take care of that for you. Although calculators with equations hidden behind the scenes are safer, unfortunately there aren’t very many that will 1. Provide ranges to further protect against wrong values and 2. Will allow you to store those results for later use.
For commercial real estate professionals to be completely covered from spreadsheet errors they will need calculations to be done in a format they are familiar with that also include ranges. If these calculators are built with CRE in mind they will have ranges to alert users of outliers in pricing or square footage. If there are less inputs choices with a proper range system, there are less chances to mess something up.
Avoid paying hundreds for certifications
This is one that most aren’t going to be too sad to see go. It is not uncommon to spend hundreds of dollars and weeks of time on Excel certifications. With the new wave of CRE tech hitting the market, needing many full day courses to learn how to manipulate cells is going to be completely unnecessary.
If you are working with a pure technology company one of the things they tend to pay more attention to than your average in house build is usability. If users can’t figure it out quickly, they are less likely to continue using. Complex tricks and formulas are not going to cut it in that space, and training has to be straightforward as well as quick. Typically a technology company will include training in the cost of the product, which saves you from bargain hunting a worth-while certification program.
Save your money and your employees’ time by looking to the right commercial real estate technology to handle all your data and organizational needs.
Limiting users making changes and tracking them
Collaborating on a workbook comes with its frustrations. If you forget to turn on track changes in the beginning, you’re in for a world of confusion. If you turn on track changes you automatically make a workbook shared and, if it is stored in common area, anyone can start making changes.
While it seems like a no-brainer to keep track changes on when you are storing data in a workbook that many people are working in, you also have to consider the limitations. For example you can’t simply undo changes, you have to rely on the history to copy data back. Certain things like formatting changes and some features like hiding/unhiding columns or comments are not tracked, and if a cell value changes from a formula recalculation, that isn’t covered either. Also change history defaults to 30 days, which means after 30 days change history is delete, so you have to be extra careful in the configuration of master versions of data spreadsheets.
CRE tech can make those challenges disappear. Generally technology can now prevent changes by only allowing a few trusted people to change data with a clear approval process. The approval will prevent the need to undo someone else’s erroneous changes and since changes are under a controlled environment, everything is tracked. The makers of technology tend to err on the side of caution when it comes to data, so chances are you’ll be able to see the history of changes far beyond the default 30 days too.
Storing your data in a living platform as opposed to static spreadsheet or workbook can sound scary, but it is well worth it. What you get in return is extra precautions against human errors, smaller chances of miscalculations, and only allowing a few trusted people to change data with a clear approval process. Commercial real estate technology can give you peace of mind, save you time, and be a more economically sound decision in the long run, if you give it a chance.
Photo Credit: “Report Profits” by suphakit73 Source: freedigitalphotos.net