Bypassing the Pitfalls of Excel in a BI Environment
Business Intelligence (BI) projects can be lengthy, complex, costly and even risky. One of the top reasons such initiatives fail, according to a recent Business Intelligence Solutions Review, is user acceptance, because even the best analytics solutions still require skills some users don’t have. This scenario is especially true of the complexity associated with accessing and presenting data from the enterprise resource planning (ERP) systems over which BI tools are layered, which can require information technology (IT) or consulting expertise.
To create reports, many users have IT employ BI tools – or the ERP system itself – to export ERP data to a data warehouse for them. Then, they transfer it to a Microsoft Excel spreadsheet, cutting and pasting to format and manipulate it. But this manual process has inherent risks. Here, we will look at some of these risks and how to sidestep them while keeping end users ― and BI teams ― happy.
Popular, But Risky
According to a Deloitte survey, 70 percent of companies utilize spreadsheets to support business processes or financial reporting. Of course, well-known Excel is the most dominant spreadsheet application by far.
However, a number of individuals can work on one spreadsheet, introducing the chance of an error that could cost millions and even billions of dollars. One particular error from unautomated cutting and pasting between spreadsheets cost JPMorgan several billion dollars, causing one expert to name Excel as the “most dangerous software on the planet.”
According to a webinar from Ventana Research and FinacialForce.com on spreadsheet pitfalls, when spreadsheets aren’t “bound to a single, unified source,” the problem can start and snowball. Ventana’s research shows that 34 percent of large companies, 20 percent of midsize, and 23 percent of smaller companies have grappled with the problem of “dueling spreadsheets”:
“Even if the original data is downloaded from the same place, such as an enterprise resource planning system, collecting it at different times can result in mismatched spreadsheets. Additions or deletions made to some versions but not others can also create variances.”
Such is the case with manual exports from an ERP or BI system; static data arrives in Excel for analysis at various times. If more or different information is needed, the process begins again, and it ultimately becomes difficult to validate reports to the ERP source.
Spreadsheets also “bog down processes,” according to Robert Kugel, Ventana Research Senior Vice President and Research Director. For example, Ventana’s research shows that 54 percent of companies saying they are substantial spreadsheet users take seven or more days for monthly financial closes.
Sidestepping the Downside
Despite the potential pitfalls, Excel is without a doubt a powerfully important and familiar tool for professionals ranging from finance and accounting to marketing, human resources and beyond. So, how can companies capitalize on user familiarity and favor for Excel in a BI environment while still ensuring data accuracy in reports?
Companies who would like to avoid manual cutting and pasting spreadsheet errors like the one that contributed to JPMorgan’s $6.2 billion loss can still use Excel ― if it’s linked to a single source of truth in the ERP system. Reporting software that uses Excel as a direct interface to live data in ERP modules can bypass data warehouses required by BI and ERP systems to eliminate the exporting, cutting and pasting that cause costly errors.
In the case of the JPMorgan incident, the spreadsheet model developer was criticized for not having automated links between spreadsheets so manual cutting and pasting wasn’t necessary. With Excel-based reporting software, the direct link to real-time data ensures the model can be automatically and accurately updated with the ERP data of the moment. Users access the report via this software to see the single existing version. And because it updates so quickly, finance users, for example, can shave days off their monthly closes.
For companies with limited IT and implementation budgets, Excel-based reporting can act as an alternative to complex BI systems. However, even in the most robust BI environments, Excel-based reporting can expedite reporting of data while keeping users happy and productive.
Author: Michele Buson