Perhaps it’s the exceptional usefulness of spreadsheets that makes problems with them inevitable. Many organizations we know have experienced being handcuffed to spreadsheets, for one or more of the following reasons:
- They lack a good reporting system.
- Their reporting systems are available to some but not all of their locations or departments.
- They need reports that draw data from several systems that aren’t integrated.
- Their IT team can’t deliver changes to system-generated reports timely.
- Their ERP doesn’t provide the needed functionality so data is downloaded, massaged and reloaded into the ERP system.
- They’re changing so quickly that yesterday’s carefully-constructed reports must now be rejiggered on the fly.
Spreadsheets seem so handy in these situations, but the hazards are many:
- Individuals develop their own personalized reports, and spreadsheets are seldom maintained centrally. It’s difficult to know if the spreadsheet version you’re reviewing is the latest and greatest one.
- No two people derive precisely the same conclusions from similar spreadsheets, which draws scrutiny and creates doubt.
- Spreadsheets lack proper controls, so fat-fingering a single entry could invalidate all the formulas that include or reference the bad value.
- Quality of data in spreadsheets can’t be certain; it could originate from systems of record, word of mouth, and anything in between.
- Spreadsheets lack the rigor of a full-fledged system that includes proper data definitions, automated enforcement of business rules, logging and testing.
- Managers and colleagues may not know all the steps to produce a key spreadsheet report. If its author becomes unavailable, the organization is stuck with a ghost process no one understands.
We have many spreadsheet horror tales to share: One team, recently reviewing sales contracts, discovered a single customer who’d been underbilled by several million dollars. The cause turned out to be a ghost process – one individual with a faulty understanding of the billing system was extracting data from it and manipulating the data in her own spreadsheet. Then, she added back manual entries to achieve the correct billing. Only she knew how this worked. And then she left the company, costing them millions of dollars.
Another team had become dependent on an entire suite of spreadsheets to manage their information. Their contract resource billed over 100 hours monthly to keep these spreadsheets operating in sync. A closer look revealed that these hours were spent cutting and pasting, from cell to cell, and from tab to tab. (This story has a happy end: We’re working with this team to standardize all information on a single report with built-in audit controls, and we anticipate reducing the time to maintain this master spreadsheet to six or fewer hours per week.)
While the causes for spreadsheets run amok are many, we find that the solutions generally fall into three categories. Organizations can:
- Govern spreadsheets better. Create a master spreadsheet that’s well-controlled and managed, as in our second example above.
- Use the systems in place better. Ensure staff are trained to use the systems’ reporting features; enhance the systems you have to provide the reports you need.
- Build a reporting platform. Create a data repository that draws from all relevant systems, then pair it with self-service tools.
Better Spreadsheets
Spreadsheet wizards can rapidly identify hard-coded information, broken references, inconsistent formulas and other bugs. Avoiding risk can be as straightforward as locking down critical functions of an existing spreadsheet, adding automated controls and incorporating audit routines. Creating or enhancing a template keeps spreadsheet software novices from making inadvertent errors, and can include macros that automate several steps into a single command.
Spreadsheet templates can be complex, and should be under proper governance. Careful review of template changes and centralized decision-making bring spreadsheet reporting risk under control. Train every user on how any template is used.
Better Systems Use
Often, we find individuals creating new spreadsheets when they could be using a more reliable system that’s available to them. In these cases, the solution is to make teams aware of what’s available and train them to use systems more effectively.
Individuals will sometimes say the system is not configured to deliver what they need, or that its reports are flawed. Frequently, what’s behind these statements is a lack of expertise. In the event the system is misconfigured, the best answer very well may be that it should be corrected.
Better Self-Service Reporting
Then again, claiming that the system is not configured to deliver reports may be an accurate assessment. Current systems, while effectively supporting the business, can be weak on reporting. Sometimes, IT can’t deliver report modifications fast enough, or there’s a need to use data from multiple systems that aren’t integrated. In these situations, the data can be loaded into a repository. Self-service reporting tools can be pointed to that single, central repository so users can develop their own business intelligence from it on demand.
We see it everywhere: When reports are effective, a single answer leads to several new questions. Data transformed into information becomes intelligence; this prompts the pursuit of greater insights. When this pursuit is only supported by uncontrolled spreadsheets, the effort is time-consuming, unreliable and error-prone. When the pursuit is supported by flexible reporting under appropriate controls, high confidence in the insights leads to business excellence.
