As Tnemec, a Missouri-based manufacturer of architectural and industrial coatings, transitioned from a legacy IBM AS400 system to an Oracle enterprise resource planning (ERP) system, both users and internal developers encountered challenges. In particular, frustration with reporting became evident when Oracle’s modules for Financials were first installed.

Accounting and information services (IS) at Tnemec began exploring third-party tools to bridge the gap between Oracle and more familiar applications like Excel – an accounting favorite. Discovery of GL Wand, an Excel-based financial reporting tool from Excel4apps, answered the needs of accounting and finance users, but Tnemec eventually sought easier reporting from a range of other Oracle modules. Excel-based Reports Wand, also from Excel4apps, became a likely candidate for fulfilling this need.

Preparing to expand Oracle
Installed in 2008, GL Wand provides real-time, drillable access to financial data in the Oracle General Ledger (GL) module with a simple mouse click, and it is used to build the Tnemec’s standard financial reporting package. But as the company faced a January 2013 “go live” with Oracle’s Order Management and Inventory modules, it needed a similar way to access the new data. 

Specifically, Tnemec sought a replacement for the query functionality provided by its outgoing IBM Infinium system, which allowed users to embed queries in Excel spreadsheets. And, based on past experience, using Oracle Business Intelligence Discoverer for the newly-required queries wasn’t a practical option because it required IS support for creating, testing and validating reports, according to Michael Alkire, Applications Operations Manager at Tnemec.

With the success of GL Wand for financial reports, Tnemec decided to trial another Excel4apps product in summer 2012. The Reports Wand tool could provide access to live data in a variety of Oracle modules via an Excel interface and supported easy creation of customized queries.

“While migrating from the AS400 to Oracle was a big change for accounting and other users who were accustomed to data in Excel, it was also hard for our developers, who weren’t yet used to Java, XML and forms reports,” said Alkire. “Both Reports Wand and GL Wand provided a way to get back to the Excel workbook functionality our people had used for years.”

From an IS standpoint, installation of the Excel4apps software was simple.

“Their documentation is good, and if we encountered a hiccup I just contacted support and they got back to me within an hour,” Alkire described.  “It didn’t matter if we were working in a test instance or production instance; Excel4apps’ replies were timely and helpful.”

Smooth transition to Reports Wand
In preparation for the January 2013 go-live date for implementation of the Order Management and Inventory modules, Ken Lloyd, Operations Controller at Tnemec, gathered reporting requirements from the users and researched the inventory and shipping tables in the Oracle database.  The query tool Toad for Data Analyst was utilized to create the SQL statements for the Reports Wand reports. 

After finalizing a query, Ken sent the SQL statement to IS for creation of the Reports Wand report.  According to Alkire, the process for creating these highly tailored reports was simple, with perhaps the biggest challenge coming from the end users, who often changed their requirements after seeing what Reports Wand could provide. “They’d want us to pull additional data,” explained Alkire.

A big hit with shipping and inventory
Ken created two Excel workbooks, one for inventory reports and one for shipping (delivery details) reports, each of which contained several refreshable Reports Wand reports. The query data was then summarized using Excel pivot table functionality, which could be updated each time a Reports Wand report was refreshed. The combination of live ERP system data and refreshable pivot table reports provided dashboards for the Shipping departments to manage orders throughout the day.  Both the inventory and delivery details workbooks are utilized by all eight of Tnemec’s domestic distribution centers. 

“The delivery details workbook allows shipping clerks and supervisors to enter a shipping status and see what delivery lines are ready to pick release, are on an order header or order line hold, or are currently being pulled on the warehouse floor; optionally, these clerks can enter a specific order number to view the order’s delivery lines,” Lloyd described. “This same workbook also contains Reports Wand reports for delivery exceptions, soft ‘suggested’ allocations, orphaned deliveries, and shipment history. 

The inventory workbook contains a perpetual inventory listing, an expired inventory listing, an aging inventory report, a cycle count report, and a material transactions report.

“Reports Wand is the primary software tool used by inventory managers at Tnemec,” said Lloyd.  “Before Reports Wand, there were an unmanageable number of inventory report versions in circulation. Now, the Reports Wand inventory workbook ensures the data used throughout the company for management decision-making is consistent and reliable. Both the Reports Wand inventory and shipping workbooks have been big hits with users.”

In addition to Reports Wand’s shipping and inventory applications, Tnemec’s Sales and Marketing departments use the tool for sales reporting. A wide range of other queries have also been converted to Reports Wand, such as reports for accounts payable distributions, physical inventory tags, variances, and more. 

Alternative business intelligence tool
With the addition of the Oracle modules for order-to-cash processes in 2013, about 70% of Tnemec runs on the ERP system. After upgrading to Oracle EBS Version 12.1.3 in 2013, Tnemec next recognized that Oracle support of Discoverer was questionable, as Discoverer’s functionality was being rolled into the Oracle Business Intelligence (BI) Suite as of June 2015. Given the steep learning curve and expense with an Oracle BI implementation, the company chose to utilize Reports Wand as its primary BI tool.

Also, Oracle BI tools would still require exports of static data to Excel, while the real-time data from Reports Wand is always up-to-date immediately after adjustments are posted. This convenience leaves more time for analysis - instead of unnecessary busy work like cutting and pasting - leading up to the reporting deadline.

“It’s an intelligence tool because it is helping us do what we want to do the way our company wants to do it,” said Alkire. “We are also leveraging the existing knowledge and skills of our staff.”