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, Excel used in business processes 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.
The company had a large Maquiladora* set up in Tijuana which produced a large portion of the semiconductors 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 was that 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 the 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 could then be sent off in the correct format.
This data network was of such sophistication that the only person in the operation who understood its 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 that black box.
What became apparent was that there was no real connection between the data from these separate areas, and when the WIP was analyzed in detail the real issues surfaced. The local management had assumed that the downloading and merging of files was being done correctly and had not performed any verification, such as 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 with 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.
Modifications were made to the month-end process that included management review and cross checking. 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 when used in the processing of data that weaknesses, such as the lack of an audit trail and the lack of editing of the reasonability of data, can cause real problems.
*Maquiladora: A factory physically situated in Mexico, run by a foreign company. The finished components or goods are exported to the company’s home country.