Peer-to-Peer

Consolidating data from different sources

Alfa Laval India Limited used Prosys eMIS to consolidate data from three different systems. Renuka Vembu does an in-depth study of the implementation

Alfa Laval India Limited (ALIL) is a supplier of plate and spiral exchangers, centrifugal separators and decanters and sanitary flow equipment. The company was widely spread geographically, and wanted to move from a VAX based system to Movex ERP. While some of its divisions had already made the move, others still used the old legacy system, while still others were on a different ERP system, viz. Scala. Data was stored on three different systems, and it was difficult to get a clear picture of customer receivables, conduct data analysis or measure performance.

Concerns to be met

The company had its base at many locations; some of the concerns that needed to be addressed while deploying a solution were that it wanted all collections to be delivered to key personnel at different locations. Being engaged in both products and projects, it needed a dashboard to study the AR behavior of a market segment, branch and customer across varied times. Also, it required a mechanism wherein in the future, even in case of M&A activity, where companies acquired by it would likely be using different ERP system.

From this emerged the need to implement a solution that consolidated data, and generated various operational, analytical and strategic reports—all this, while the requisite skill-sets to deploy and operate the system were available in India. It had to be cost-effective to boot.

Challenges posed

Kishore Daryanani, CIO, Alfa Laval India, opined, “Our management required that reports should not be just a dump of data, but they should be actionable and provide support for decision-making, apart from satisfying operational needs. Identifying such needs and creating cross-functional reporting was a big challenge. Various indicators were built into the system namely Potential Overdues, DSO based on Month-end and Average AR, Weighted Credit Period Enjoyed, etc. Mapping of sales, receipts and AR in a single report and that too with the flexibility of summarizing and drilling down was a challenge, which was met.”

The right solution

ALIL selected the Prosys eMIS—MIS Automation Solution, developed by Prosys Infotech, based on the Microsoft Business Intelligence Framework. The solution came embedded with various components including the SQL Server database, Excel Pivot Tables, Pivot Graphs and VBA, Access Macros and Forms, Prosys Excel Add-in, Menu System in Prosys eMIS.exe in VB and Lotus Notes for messaging.

The entire solution is user login-based and menu-driven, and can be operated by end users themselves. Various components of extract/ transform and load, business rules and report generation are developed and externally registered to the menu. This architecture enables them to add more reports in the future without having to depend on a vendor.

Deployment details

The Prosys eMIS implementation was spread out over four years. Initially, data was stored in three locations with different structures—Vax (legacy system), Movex (ERP for some divisions) and Scala (ERP for some divisions). The deployment began in 2005 with data consolidation across the three systems and generation of some basic operational and analytical reports. Some of the major reports were—AR with reference to due dates with different age buckets, AR with reference to invoice dates, and AR trends. Then in 2006, various reports required for reporting to the parent company in Sweden were added.

In 2007, the focus was on creating metrics to measure performance. More analytical reports like DSO calculation, weighted average actual credit period calculation, mapping of AR, sales and receipts in a single report, etc. were included. In the final stage, in 2008, a new module to send proactive reminders to customers as well as to get a balance confirmation was added.

ALIL was already using the SQL Server database for other applications. It also had the relevant Excel licenses for its users. Therefore, at an infrastructure level the costs were zero. However, the total development and implementation costs were pegged to be around Rs 18-20 lakh, inclusive of cost to the vendor, cost of internal hours for implementation and deployment, cost of the server machine, etc.

The process

Daryanani gave a detailed version of the actual process of generating the reports on a day-to-day basis: ETL modules of eMIS convert the text file outputs of ERP into SQL Server tables. Various business rules are applied on this raw data to make it reportable, and such data is stored in reporting tables. Excel Pivot Table report templates are automatically refreshed by the users, and controls are checked. Prosys Excel add-in is used to split the data by market segment or region and is sent to the appropriate entities.

Benefits reaped

One of the main aims of data consolidation has been achieved successfully. The reporting tables are generated within an hour of closing, and then the reports can be gathered immediately, thereby saving both time and effort. As the reports were interactive Excel Pivot tables, acceptance has been high; moreover, users have the option of creating views according to their own liking. This has taken some off the burden off the IT staff. Daryanani added that in a traditional environment, a different view or a summarization would have required a full cycle of development and testing. In Pivot tables, the new view is created using simple drag and drop. The vendors provided a simple utility to split the pivot table according to responsibilities. These pivot tables split on branches are sent to the branches and similarly to the market segments.

He stated that the user could summarize and drill down to the lowest level even when he is disconnected from a server. “Users carry these reports on their laptops and are able to discuss the AR related issues with customers. Analytical AR reports are now being generated every week by the functional users themselves, thereby considerably helping the collection process,” added Daryanani. Regular proactive reminders to customers and balance confirmation have helped the company considerably. Electronic generation has put a stop on the usage of paper required for printing, leading to cost reduction too.

Future plans

The project is being constantly upgraded, a Customer Reminder and Confirmation Module is being rolled out this year. ALIL is planning to take forward its association with Prosys for Collection Budgeting and Collection Efficiency Module, AR Key Performance Indicators and various other topics in this domain.

renuka.vembu@expressindia.com