Is Your Microsoft Excel ERP Aware?

Organizations go to great lengths to implement enterprise resource planning (ERP) systems like Oracle, SAP, and PeopleSoft for good reasons. These systems support data centralization and eliminate islands of information caused by software applications that don’t communicate. Despite their overall benefits, many users find ERP systems rigid and difficult to adapt to certain workflows and business processes.

In particular, financial and business analysts and accountants often complain about the difficulty of creating financial reports within an ERP system’s data warehouse environment. Because of the programming that is often required to create and share reports in this environment, many financial ERP users manually cut and paste information from their ERP system into a Microsoft Excel spreadsheet due to its ease of use and flexibility. This time-consuming, error-prone process takes valuable time that should instead be spent on analyzing data. Furthermore, it allows for inherent risks associated with standalone spreadsheets.

One way to make financial users happy while also leveraging your organization’s existing investment in its Microsoft and ERP applications is to implement a reporting tool that integrates the two platforms, making Excel “ERP aware.” Tightly integrating Excel with ERP data means financial users can work, report, analyze, and improve processes in the familiar Excel environment. A direct link with live ERP data allows the reporting tool to provide real-time data and lets financial users bypass the data warehouse and its associated programming requirements.

An Important Distinction: Real-Time Data vs. Staged Data

Staged data from a data warehouse provides good data summaries, standardization, and performance optimization, but requires periodic extraction, data selection, data mapping, and data reconciliation. A data warehouse also requires additional infrastructure, licensing, and maintenance, and is costly to implement and administer.

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. Making Excel ERP aware leaves more time for analyzing the report rather than cutting and pasting from a data warehouse to create one.

Characteristics of ERP Aware Excel

Excel-based reporting tools make Excel ERP aware, connecting Excel directly to the high-level and transactional finance data in your ERP system. Below are some key characteristics of these reporting tools. 

• Push vs. Pull — When you cut and paste data from a warehouse into Excel, you “push” the data to create your report. Reporting tools that link directly to the ERP system let you “pull” live data into Excel from the ERP source. The advantage? Besides always pulling current data, you have complete control whenever you need it, from designing the report in the required periods and columns to refreshing the data in the Excel spreadsheet with a mouse click.

• 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. Advanced Excel-based financial reporting tools give you 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 they are difficult to absorb, or too summarized to initiate action. A better option is to have a tool that gives you the flexibility to support both static views as well as ad hoc analyses, because businesses are dynamic. Such a reporting tool empowers you to produce high-level standard, static reports and provides a detailed report on the information that’s important for strategic 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 authorized users have access to sensitive data. ERP system authorizations maintain control over access, but if the data is staged in a data warehouse, then separate authorization is required, a duplication of the security effort.

When a reporting tool pulls directly from the ERP system into Excel, you don’t need additional authorization because the information never goes to a data warehouse; it remains in the ERP system, so 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 and staged data are less verifiable than data that is sourced directly from the ERP system into an Excel spreadsheet and refreshed on demand.

The following table highlights the drawbacks of reporting with standard ERP tools and standalone Excel, as well as the benefits of reporting with ERP-aware Excel.

Reporting with Integrity

Only an Excel-based tool that links directly to ERP data can give you confidence that the numbers presented in your financial reports are current, directly from the ERP source, and uncorrupted by any extraction/transformation or staging processes. It gives finance professionals intuitive tools and full design control over their report presentation and eliminates their dependency on IT support resources. Because Excel-based reporting doesn’t require a full-blown implementation, it deploys in only a few hours, without the complexity and learning curve of more complex and often expensive reporting tools.