Spreadsheets Behaving Badly: 3 Common Mistakes to Avoid

You don't have to go far to find a CFO or budget manager who has suffered from making spreadsheet mistakes. In fact, it happens more than you think.A 2015 YouGov study that looked at spreadsheets found some surprising data: 20% of surveyed businesses "suffered a direct financial loss due to poor spreadsheets." And 16% of large companies found spreadsheet mistakes more than ten times in a single year.If you find those numbers startling, you're not alone. Many organizations use spreadsheets as their underlying tool for their biggest financial decisions.And small errors in spreadsheets can have significant impact. Forensic investigations of Enron found 24% of its spreadsheets with a formula contained errors. And the London Olympics oversold an event by 10,000 tickets due to a spreadsheet input error.The same is true with government organizations as well. In the past few months both a Tennessee school board and a Washington State sheriff's office had to explain massive budgeting errors caused by spreadsheet issues. In this post I want to share with you some of the most common spreadsheet mistakes I see. You want to avoid them at all costs.

Multiple Versions of One Spreadsheet

This is likely the most common source of spreadsheet errors in business. I'd guess that most of you have come across a situation where there were two, three (or more!) versions of the same spreadsheet, all with different data. Situations like this can wreak havoc, especially around budgeting time. Pulling numbers into a budget from a spreadsheet that has inaccurate information can be the source of a lot of issues down the line. As your organization looks to reduce risk and increase agility in decision making, relying on the wrong data means you can fail before you've even begun. To solve this, create a system where you're operating from a single source of truth. Setting up your system this way will ensure all data is pulled from one core source that is always correct, helping to reduce errors.

Lack of Security

Quite a bit of incredibly important (and confidential) financial data is stored electronically today. That includes not only budgeting and forecasting information but also personal data too. The last thing a CFO wants is to have information like employee salaries seen by unauthorized people. While locking an entire spreadsheet is easy, it isn't always the best solution. It's not uncommon for members of the finance or accounting department to need access to certain pieces of information but not everything.This is another common setback with spreadsheets. It's virtually impossible to lock people out from certain data. Not only that but locking and unlocking certain parts of spreadsheets can be time-consuming as well. It can make data gathering and analysis less efficient. Creating more spreadsheets for particular employees can cause even more confusion. Businesses can improve their data security by working with a system that has layers and levels of access. This will ensure the people who are meant to see specific data can always see it, streamlining the entire workflow.

No Real-Time Data

Having real-time and accurate data is the lifeblood of any business. Organizations want to analyze big data better and more deeply. They want to make decisions quickly, respond to challenges and take advantage of opportunities as they arise.That's very difficult to do with inaccurate and outdated numbers. Real-time data is necessary to a competitive advantage. Smart CFOs and budget managers know this. They see how big data, and the management of it, is critical to success.Keeping up with the sheer amount of data available is incredibly difficult with spreadsheets. In many finance and accounting departments employees spend much of their time entering data into spreadsheets, not analyzing it.Technology can help. Automated systems have the bandwidth to pull in data in real time and update information consistently. Real-time data means better decision making, which can only improve the organization over the long run.

Final Thoughts

Errors happen—that's a fact of business life. However, smart CFOs know that they must put budgeting and planning software systems in place to dramatically reduce errors. They'll need to develop solutions that will improve security, provide one single source of truth and deliver data in real time.