Organisations go to great lengths to implement enterprise resource planning (ERP) systems, and for very good reasons. ERP systems such as Oracle or SAP support data centralisation and eliminate islands of information caused by software applications that do not communicate with each other. Despite these compelling reasons and the overall benefits of ERP systems in general, many users find them rigid and difficult to adapt to certain workflows and business processes.
In particular, financial and business analysts and accountants often cite the difficulty of creating financial reports within an ERP system’s data warehouse environment. In many cases, these financial ERP users depend on IT staff to program the query, which takes time and creates dependency on another department. While having a standing request for scheduled reports might work, running ad hoc reports that must be reviewed quickly by staff or delivered at the request of an executive can become an ordeal. Such pronounced dependency on the IT department on the part of Finance creates a high probability that productivity will be compromised, deadlines missed, and stress levels elevated. What’s more, data warehouses are expensive to maintain from an IT perspective and require a separate server.
Because of the programming required to create financial reports in a data warehouse environment, many financial ERP users often resort to manually cutting and pasting information from the ERP system into a Microsoft Excel spreadsheet. This time-consuming process takes valuable time that should instead be spent analysing data.
EXCEL IN AN ERP SYSTEM ENVIRONMENT: THE BUSINESS CASE
Excel is the world’s most widely used business productivity tool. Its familiar features lower training overhead, support collaboration in a common format, and enable input to be linked and aggregated from multiple sources. Excel provides extensive formatting capabilities for impactful communication, and the application’s robust functionality makes it easy to calculate, model, and project financial data.
Excel workbooks let users aggregate and assemble data easily and they allow for annotation, commentary, and projections to enrich data delivery. These rich capabilities were shaped by hundreds of millions of users over decades, resulting in a powerful and intuitive tool with the features financial users need. The net result? Financial users like it and want it.
Despite all of the positives Excel represents, using Excel incorrectly can cause problems. For example, use of Excel as a data store will result in data replication and creates the very disconnected islands of information an ERP system is meant to eliminate. Individual departments often extract information out of the system and capture it in Excel. Under this familiar scenario, decision-makers are dependent on Excel spreadsheets containing data that are not managed, controlled, validated, or secured. A financial model review by KPMG Management Consulting, London, found that 95 per cent of the financial models reviewed contained at least five significant errors.1 Similarly, Coopers & Lybrand found significant errors in 90 per cent of the spreadsheets audited in their research study.2
Understandably, this has been a cause for concern in respect of data governance. Can there be a middle ground that provides the flexibility of Excel but still enforces the governance required for financial reporting?
An excellent way to leverage an organisation’s existing investment in both its Microsoft and ERP applications is to implement tools that securely integrate the two platforms by extracting all data from the source. For example, a reporting tool that tightly integrates Excel with ERP data means financial users can work, report, analyse, and improve processes in the immediately familiar Excel environment. A direct link with live ERP data allows the reporting tool to provide real-time data as opposed to staged data, and allows financial users to bypass the data warehouse and associated programming requirements. This also allows companies to place a layer of governance that avoids all too common spreadsheet errors that creep into complex financial reports and models. For frequently used reports, the standard Excel password protection can be applied to create templates which will prevent changes to data whilst still empowering finance to own their data and build their own reports.
AN IMPORTANT DISTINCTION: REAL-TIME DATA VS STAGED DATA
Staged data provides data summaries, standardisation, and performance optimisation, which are all good, but doing this requires periodic extraction, data selection, data mapping, and datareconciliation. It also requires additional infrastructure, licensing and maintenance, and costly implementations and administration assistance.
Real-time data – the kind of data a direct link between Excel and an ERP system delivers – is always up to date immediately after the final adjustment is posted. This convenience leaves more time for analysis – instead of unnecessary busy work, like cutting and pasting – leading up to the deadline. Moreover, advances in in-memory databases are expected to make traditional data warehouses redundant. Using an Excel-based financial reporting tool that already bypasses the data warehouse is a future-proof decision worthy of consideration. A recent Gartner report stated that these specialised solutions provide “a tactical, cost-effective way to improve finance end-user reporting and analysis with minimal IT involvement”.3
THE IDEAL EXCEL-BASED FINANCIAL REPORTING TOOL
Excel-based financial reporting tools share some characteristics and differ in other substantive ways. The ideal solution will make it extremely easy for users to run reports from data pulled directly from an ERP system on a flexible and ad hoc basis, and provides for sophisticated presentations, security and auditability, and more.
Push vs pull
Excel-based financial reporting tools that work off staged data use “push” technology to produce reports. This requires the user to run reports out of the back-end system and then either cut and paste it into Excel, or publish it to a file, which is then imported into Excel – a rather involved process.
Tools that have a direct link to the ERP system allow the user to “pull” data into Excel from the ERP source. The advantage? Besides always pulling current data, users have complete control at the time of their choosing, from designing the report in the required periods and columns to actually refreshing the data in the Excel spreadsheet.
Columns and rows vs cells
Reports that come from a data warehouse are traditionally designed using the columns and rows concept and are therefore constrained by the data arrangement in the original system.
More advanced Excel-based financial reporting tools break from these constraints and give users the flexibility to define reports down to the individual cells. This allows for highly sophisticated data views and the ultimate in free formatting, while still providing the option to build formulas and apply column headings for consistent column and row definitions.
Static vs flexible and ad hoc analysis
Static reporting formats follow a consistent pattern and are reproduced monthly. Unfortunately, these types of reports are often so detailed that they are difficult to absorb or too summarised to initiate action. This would work perfectly if businesses were static, too.
As businesses are dynamic, the better option is to have a tool that provides the flexibility to support both static views and ad hoc analyses. These types of reporting tools allow users to produce high-level standard, static reports and provide a detailed view of the information that’s important for decision-making and immediate action.
Security and auditability
In today’s business climate of data breaches, compliance, and full disclosure, enterprises are concerned about the validity and reliability of financial data, as well as confidentiality. Information security ensures that only authorised users have access to sensitive data. ERP system authorisations maintain control over access, but if the data is staged in a data warehouse, then separate authorisation is required – a duplication of the security effort.
Excel-based financial reporting tools that pull directly from the ERP system negate additional authorisation because the information never goes to a data warehouse; it remains in the ERP system. Confidentiality is assured.
This confidence spills over into the audit process. Auditability requires that the timing and source of data is verifiable. Extracts that end up in Excel via the extract, transform, and load (ETL) process, as well as staged data, are less verifiable than data that is sourced directly from the ERP system into an Excel spreadsheet and refreshed on demand.
THE RESULT: LESS CUTTING AND PASTING, MORE TIME FOR DATA ANALYSIS
Only a simple, Excel-based financial reporting solution that is tailored to an ERP system’s specific strengths provides accountants and analysts with the ability to define, analyse, enrich, and present live data within the familiar Excel context – giving decision-makers the confidence that the numbers presented in financial reports are current, directly from the ERP source, and uncorrupted by any extraction/transformation or staging processes. The benefit of such a solution is less cutting, pasting and IT dependency, and greater productivity and efficiency within the financial function.
Particularly in tight economic times, the question often becomes not whether an organisation should use an Excel-based financial reporting solution, but rather whether the organisation can afford not to utilise such a solution. ❐
1 KPMG,Executive summary: Financial Model Review Survey, KPMG Management Consulting, London, 1997.
2 M Ward, Fatal addition, New Scientist, 16 August 1997.
3 Gartner Report, IT market clock for financial management, Analyst: Nigel Rayner, 14 August 2013.