Connecting Your ERP to Excel: Why and How

The built-in reports that come with most ERP software packages are notoriously deficient from the standpoint of finance and accounting professionals. They often lack the key information that decision-makers need. Native reporting tools are difficult to use, require specialized expertise, and are often inaccessible to front-line finance and accounting personnel. Perhaps most importantly, built-in reports do not easily lend themselves to analysis.

Every finance team aspires to close the books faster, provide insightful information about the business, and answer questions from the C-suite promptly. In a hypothetical world where information requirements never change, stock ERP reports might be just fine. In a rapidly changing business environment, however, F&A teams need greater agility and flexibility. They need to be able to quickly find the answers to questions that might never have been asked before. That requires a toolset that is designed from the ground up for adaptability.

Excel: The Tool of Choice

For finance and accounting professionals, Excel is often the tool of choice for manipulating, analyzing, and visualizing data. It is functionally rich, highly flexible, and familiar to virtually everyone in the business world who works with numbers. Excel is full of benefits for the finance team.

The challenge for most finance and accounting teams, though, is that there is a significant gap between the software systems that house their vital business data and the spreadsheets where they do most of their analysis. As a practical matter, this means that someone needs to extract data from the ERP system, reformat it, and paste it into Excel. That is a tedious process, usually performed by someone whose time is valuable and should not be wasted on these kinds of manual tasks.

In addition, the copy/paste process often introduces errors. A missing value in the source data, for example, can result in numbers being pasted into the wrong rows. That, in turn, can produce erroneous results.

To make matters even worse, this manual process needs to be repeated every time the information is updated in the ERP system. Otherwise, analysts are working with old data, and they cannot get an accurate picture of what is happening in business right now.

Making Reporting and Analysis Easy

There is a better way. By connecting Excel directly to the information in the ERP system (as well as the organization’s other business software), analysts can build highly flexible, real-time reports that update automatically whenever new data is recorded in the ERP system.

Let’s say your company is using SAP’s Finance and Controlling (FICO) module, for example, and you want to produce a cash flow report projection that reflects the current state of your aged receivables. With insightsoftware’s Wands for SAP, you have access to specialized formulas embedded directly into Excel that provide up-to-the-minute information about your customer balances.

Using Wands, you can easily add aged customer balances to Excel. An end user can add drill-down capabilities, making it easy to investigate the details behind the summary aged balances. Whenever data changes in the ERP system (for example, when a customer invoice is paid), the updated information will automatically be available in Excel.

Better Than Stock

Excel-based reports from insightsoftware give you greater flexibility and more information than the stock reports built into the ERP system. For example, SAP’s built-in aging report lacks a critical piece of information: the customer description. Using Wands for SAP, though, you can easily add any field from the SAP system and drill down to those details.

Wands give you the ultimate in flexibility. If you want to define custom aging buckets, for example, you can simply adjust the aging periods right in Excel. Because all of the reporting design elements are driven from within Excel, reports can be designed and modified without the assistance of IT specialists or expensive outside consultants.

The security of your ERP data is handled automatically by insightsoftware. Data access permissions are automatically inherited from the ERP system for each user accessing data from Excel. In our SAP example, Excel users would only have visibility of ERP data that they are permitted to access within SAP.

This functionality is not just available for SAP users; with integration to over 140 different ERP software systems, insightsoftware can provide Excel connectivity for virtually any business.

Upgrade Your Financial Reporting

Excel-based reporting tools from insightsoftware can also provide greater power and flexibility when it comes to financial reporting. Profit and loss statements, balance sheets, and statements of cash flow are especially challenging if you are working with standard reporting tools.

Most companies need to quickly and easily filter financial reports based on product lines or divisions. With insightsoftware, an analyst can view results for a single company division simply by switching a single parameter within Excel.

Grouping, filtering, and masking GL account numbers is likewise a common requirement. To capture all revenue accounts in a single row of the spreadsheet, for example, it may be useful to simply reference an account range, such as “41000-42999.” This makes the process of building financial reports faster and easier. Just as importantly, it dramatically reduces the need to modify existing reports when a new GL account is added. Following on the example above, if a new revenue account “42551” is added to the general ledger, it will automatically be included in the specified range.

Adjusting date ranges is extraordinarily simple with a tool like Wands. By simply changing the date value within an Excel report (for example, at the head of a column showing the GL account balance), data is automatically updated to display the appropriate results.

Although there are some sophisticated tools on the market to address financial reporting, none of them has the power and simplicity of working directly in Excel.

For the last few years, businesses have been faced with a new set of challenges. For many, this has provided a wake-up call, emphasizing the need to streamline operations and to empower highly skilled employees to work more efficiently. Finance and accounting teams, in particular, have played a critical role in assessing the crisis, engaging with government assistance programs, and ensuring accurate reporting and compliance. By connecting your ERP system to Excel, you can achieve greater efficiency, accuracy, and responsiveness while lowering overall costs.

To see how it all works in action, request a free demo today!