The Top 8 Best Practices To Ensure Good Data In Spreadsheet Financial Reporting

Given the brutal fact that using spreadsheets for complex financial reporting is putting companies’ financial positions at risk, let’s look at the picture of absolute musts for maintaining spreadsheet use. Organizations need to be performing formal quality control of their spreadsheet reports due to the proven error rates and programming-level complexities necessary for spreadsheet development. We’ve taken the top eight criteria which experts agree is imperative to address the current crisis for organizations that insist on maintaining a spreadsheet status quo.

First:

Never delegate the design of financial reports in spreadsheets to anyone who isn’t thoroughly educated on the use of spreadsheets, formula writing, and implementation methodologies that are proven to work in a corporate environment. Researchers are consistent in their warnings about handing over spreadsheet development to non-experts.

Second:

Define end-user requirements for each report by doing a complete needs analysis with all the relevant decision-makers. To avoid endless rework, define what “done” looks like. In agile development for software applications, this is a critical step that saves teams loads of time. Incorporate the use of “acceptance criteria,” which states explicitly the necessary elements needed in the report, the definition of scope, and what will and won’t be done. This process will guarantee synergy between the end-user and the spreadsheet developer. It can’t be stressed enough that spreadsheet development is a form of programming, so incorporating pertinent features of software development best-practices will reduce errors and one of the biggest time-killers: rework.

Third:

Design the report layout. Is it readable? Does it meet the needs of end-user? In the design, have a layout that makes it easier for you to spot errors, particularly in formulas since they’re the most common type. A common suggestion is to separate formula cells from data input cells so users don’t accidentally overwrite formulas, which can have a cascade of traumatic consequences. Also, consider locking all formula cells that point to raw data.

Fourth:

On a cell by cell basis, the document which balances contain which account numbers. Make sure to have up-to-date supporting documentation ready at all times so that when questions arise about balances, it’s easier to see which roll-ups are being used in a particular cell.

Fifth:

Determine how the data is getting from the ERP system into a spreadsheet. Two of the most common methods are (1) physically keying data from the standard ERP reports into a spreadsheet, and (2) having IT export the data from the ERP into a spreadsheet. Keep in mind that organizations physically keying in data will often have to do it hundreds of times for each period. A lot of errors arise from keying in wrong balances, which is exactly the sort of thing we find in ancient manuscripts. It’s not an ideal way of getting the data, so if possible, it’s better to have IT export it into a spreadsheet. The downside of this option is having to rely on an already over-burdened IT department. But perhaps the real problem with either (or any) method is that, by insisting on spreadsheet reporting, organizations are trying to make spreadsheets into a database. Unfortunately, they never will be.

Sixth:

To maintain data integrity, have a mechanism in place that informs the report writer when any change has been made to the chart of accounts (e.g., addition or deletion of an account number). This is because all the relevant links will need to be updated to reflect the change in the chart. Failing to do so is one of the biggest causes of errors in financial reports. Given that this is a form of programming, it needs to be given due attention by appointing particular people – with the proper formula writing expertise – responsibility for the changes. In addition, use one of the available audit tools that can help trace precedents and dependents of a cell (precedents of a cell are those referenced by it; while the dependents of a cell are those that reference it). Finally, when a change is made, update your sheet that documents which account numbers roll-up to each cell (the fourth point above).

Seventh:

When updating reports with the current month/quarter/ year, the team is essentially creating a new report because new data has to be included in the current sheets. To ensure proper quality control, all the relevant guidelines we’ve mentioned will need to be repeated.

Eighth:

Organizations need to take every security precaution when distributing reports to end users. While it’s common to simply attach spreadsheets to an email, there’s a tangible risk of data breach because once emailed control is lost. At a minimum these should be password protected. The trouble is that there are multiple ways to recover lost passwords, which makes this a less than optimal solution. Another common distribution method is to email a link to a central spreadsheet repository. The downside is that these will most likely have to be accessed within the network – meaning limited options for remote access – and there’s still the possibility of someone doing a “Save As,” which presents its own risk. An important and often overlooked fact is that spreadsheets reference raw data from all the balances in the chart of accounts. And while the report may not use that data, it still exists in the spreadsheet – and the underlying data will most likely contain highly sensitive information. Therefore, it’s important to follow available best practices for locking spreadsheets down as much as possible. Normally in software development, there’s a robust ratio of quality to development personnel, with many maintaining a 1:1 relationship. Given the current state of affairs with the documented percentages of spreadsheet errors and the high potential of harm to the organization, companies need to invest more heavily in quality control. Even without additional quality resources, you can see that all of this comes at a steep cost. Much of what’s required involves heavy reliance on an already over-burdened IT department and specialized people within finance who understand spreadsheet development; along with a deep understanding of how financial reports are built (a skillset needed every time a report is built), not to mention all the supporting roles that go into gathering the data. It’s already taking organizations weeks to publish reports, and that’s without proper quality controls. Done properly, it will take much longer, most likely adding at least a week or two to the process. Yet that’s what should be happening to bring error rates down to an acceptable range. Fortunately, there are tools built to save you time and money, such as Synoptix Reporting Software, that will integrate directly to your multiple data sources and provide you a single source of truth.