Excel-based reporting and ERP

Excel-based reporting solutions are becoming a bigger feature of the suite of Business Intelligence (BI) tools offered by midrange ERP vendors. It’s a trend that seems to make sense given the ubiquity of Excel, but could there be a downside?

The rise of Excel-based reporting tools

I’ve recently attended system demos for MS Dynamics NAV and Epicor 9 as part of an ERP selection project I’m working on. In both cases Excel-based reporting tools were prominently positioned as key elements in the suite of BI tools being proposed. For Epicor, their XL Connect tool (an Epicor-specific version of a tool developed by BizNet Software) was demonstrated, while Jet Reports was the tool used to report on data from MS Dynamics NAV.

Without going into specific details as to what each tool can and can’t do, in essence both tools are quite similar in that they allow users to report on data from the ERP system database using Excel. The idea is that users leverage standard Excel functionality to format the data, generate charts, graphs and pivot tables.

This approach to reporting and analysis is undoubtedly a boon for those already comfortable working in Excel (most accountants!) as it makes use of existing skills and allows users to work in an environment they already know well. Even if users need training it’s essentially Excel training that’s required rather than starting from scratch to learn a new piece of software.

Sounds great so far. And it is, in the sense that user adoption of the reporting solution is likely to be far better when the tool being used is already so familiar to the user community. My concern is that this strength is also, potentially, a weakness.

The report-writing process

Think of the process that happens in many organisations when a user requests a new report from the IT department. The detail of the report is specified, the report is developed and tested and eventually made available to the user. Certainly this can be a slow process (plainly an issue in itself), but for the most part the user can trust the data output when the report is run. The report developer knows the data, business logic has been applied to filters and calculations, and the report has been tested and verified before it goes live. Even in organisations where the IT department doesn’t get involved there are likely to be a few key individuals assigned responsibility for report development, and the process is likely to be similar.

The report-writing process with Excel-based tools

Now consider the same scenario where the user is working with an Excel-based reporting tool. This time the ball is in their court. They have to know the data, they have to understand what filters to apply and how to code the calculations, and they have to verify the output. Potentially users who would never be expected to write a report using a reporting tool like Crystal Reports are now tasked with generating their own reports using Excel. Even if the report is generated successfully you’re still left with the widely-documented dangers of using spreadsheets – especially the risk of errors in formulas and calculations and the risks associated with governance and control of spreadsheets.

Exercise caution when deploying Excel-based reporting tools

Clearly training users in report development techniques will help, and procedures and checks can be introduced to catch mistakes, but the crucial point is that, while there’s much to admire in Excel-based reporting tools, the risk of error is much greater than with traditional BI tools. Making such tools widely available in an organisation may be tempting, but I advise caution.

(This guest post was written by John Donagher, Principal Consultant at Lumenia Consulting. Read John’s blog here.)