Using Excel for ERP Reporting

ERP Reporting – Background

ERP systems are very powerful software systems and I find they are often badly implemented in smaller companies. This often happens because the vendor or the customer underestimated the implementation cost, or because the customer has few sophisticated users, or time and money simply run out. Sometimes this results in a broken system, or just an expensive GL system or no system at all. Being a B2B CFO^® partner, with my extensive background in financial management and systems, I am often called upon to try and help make sense of the situation.

Reporting is often a clear area of frustration for the smaller company. Often they have been used to the flexibility of report design in QuickBooks – which I believe is world class – and have no internal skills to deal with products like Crystal Reports, or proprietary ERP reporting tools. My approach is generally quite simple. Everyone knows Excel. If we can get the data to Excel then everyone is on the same page, and we start using information to build corporate value.

I recently developed several very interesting reports for a Distributor using Excel and simple database techniques to find and grab my data directly from the underlying ERP tables. This technique allows me to quickly build some very useful management reports. While the initial set up is a little tricky, maintenance and updates can often be done by the client. The resulting reports focus on understanding monthly performance and identifying weakness in the major customer/ vendor relationships.

ERP Reporting – 1st Indicator: F9 for Financial Reporting

1. F9 for Financial Reporting

F9 is a an Excel based add-in that uses dynamic cell references to pull data directly from ERP tables. It supports a variety of ERP products, but I used it in a MAS 90 environment. My objective was to build a useful financial summary report comparing current and prior months and years with budget and an updated forecast. Set up was very easy and I decided to create different tabs in Excel for my Budget, Forecast, Current and Prior year – and pull in the data from my ERP tables. I was then able to build an elegant summary variance reports on another Excel Tab. This report is now used to report on weekly/ monthly and YTD income statement performance.

ERP Reporting – 2nd Indicator: Excel Data Connections

2. Excel Data Connections

Excel has a data Connections capability using Microsoft Dbase query capabilities. My client wanted a report that would tell him how different client and vendors were performing. For example what were the top ten Customers and which vendor products were they buying. He had previously been frustrated by the high costs of developing Crystal Reports that were static and unhelpful. Because the need for information was so dynamic, I decided grab my data from the MAS 90 tables and build a small Excel database. Then I was able to use Pivot tables to quickly build multiple report options – and then turn it over to my client for monthly use. The process involved building a query to get the data fields I needed: such as Customer order details by Item. The tricky part was associating Vendor with each customer order. Once I got the data – the Pivot tables were very quick. Now the power of the reporting showed itself. Some of the options I can now produce on the fly include:

a. Top Customers with Vendors showing sales and margins

b. Top Vendors with Customers – this is an elegant “flip” of the above report.

c. Detail Drill downs on specific customers or vendors.

d. Revenue analysis by quarter

e. Items shipped by selected period

ERP Reporting – Conclusion

While much of this information may or may not be available as canned ERP reports, it often comes in the form of huge Crystal Reports (aka “electronic greenbar”) and is not easy to review. With my system, which is easy to refresh for current data, you can quickly expand and contract and drill down to really understand the gross margins so important to a distributor.

The fastest way to get value from an ERP system is to develop simple reports with powerful information. A seasoned CFO with technical skills can help you get there faster.

Ask one of our B2B CFO^® professionals experts on how they can help.