International Rectifier, Mexico
Excel is an excellent data-analysis tool. If you need to dissect, or “slice and dice” amounts of data there is probably no better tool available for regular business users. However, if Excel is used in business processes it has two inherent weaknesses; one is that it has no audit trail (if an amount or a formula is changed in a cell there is no record of what was there previously) and the second is that it does not have version control (as files are downloaded or moved around by email it is difficult to keep track of the versions used.
The following case study illustrates the problems of using Excel for more than it was designed for. The company relied on data being downloaded to Excel files from various disparate systems and then combined to produce financial and monthly operational results.
Situation The company had a large Maquiladora set up in Tijuana which produced a large portion of the semi-conductors that the company made. This operation had difficulty controlling its Work-In-Progress (WIP) and could not account for many of its assemblies, and was concerned that it was producing inaccurate monthly financial results. We were called in to 1) find out the extent of any problem and 2) to correct the immediate issues and recommend improvements going forward. A major hurdle that they had was they had five disparate systems covering Purchasing, Planning, Operations, General Ledger, and Accounts Payable.
In order to get the needed data and be able to send the monthly financials to Head Office, they had created an Excel network of downloaded data from all the separate systems, and had an exceptionally knowledgeable IT person maintaining and running the whole data network. Each system would have its data downloaded to an Excel work sheet and then each of these were merged and manipulated so that the General Ledger detail would then be sent off in the correct format.
Approach This data network was of such sophistication that the only person in the operation that understood the mechanics was the IT person that had put it together. To everyone else in the operation it had become a black box. The first step was to get a conceptual understanding of the black box. It then became apparent that there was no real connection between the data from these separate areas, and when the WIP was analyzed in detail it became apparent there were real issues.
The local management had assumed that the downloading and merging of files was being done correctly and had not checked by going into the operational system and listing out all open work orders. When the WIP report was extracted from the system there were work orders showing that had start dates of up to three years previously. With a maximum production cycle time of 2 weeks it was obvious that the closing mechanism was not functioning correctly and the WIP was overstated by almost $3 million.
Result Modifications were made to the month-end process that included management review and cross checking. Also recommendations were made for the company to implement a comprehensive ERP system across all functions that would give the local management the visibility it had been lacking. Management and staff were also educated in the use of Excel, showing how the software can be used very effectively in data analysis, but if it is used in the processing of data, weaknesses such as the lack of an audit trail and the lack of editing of the reasonability of data can cause real problems.
