Part 3 in the Excel Dispatch Series
Earlier parts of this series discussed the hidden costs of using Excel for jobs that it is not designed to handle, such as bill of materials (BOM) management, and offered survival tips to help you get by when you must rely on Excel. In this final part of the series, we examine how one company struggled with Excel while experiencing rapid growth and how a determined employee overcame management inertia and led his company to switch from using Excel to manage its product information to using a dedicated BOM management system. We also provide a number of tips for preemptively structuring your product data for migration to a modern BOM management solution.
(Editor’s note: In the story you are about to read the names have been changed to protect confidentiality.)
A year ago Karl Mueller wondered if his company was growing too fast to control. Or maybe, he wondered, the bigwigs would fire him for messing up something even though it was beyond his control. Whatever. He didn’t care. He just knew that the faster Holdem PDAs LLC grew, the faster it was running toward losing all control of its product information—and that he had to do something about it. But only if he could get his bosses to listen.
Mueller worried about Holdem PDAs’ out-of-control product information because he managed the company’s contract manufacturers (CMs). His contact with the CMs started at the earliest concept phase of a project, from sharing preliminary bills of materials and design data through releasing the final BOMs and designs for manufacture.
Holdem PDAs specializes in portable reading devices for high-end B2B (business to business) markets such as medical researchers and lawyers worldwide. In its first two years of business, Holdem PDAs had grown from seven employees and one product to more than 40 people with three product lines. Each product line is designed to be customizable to local market and customer preferences, making individual product variations essentially unlimited and production volumes low.
Management strives to have the company’s sales, design, manufacturing, assembly and shipping departments operating in a continuous flow so that no stage of the process is idle and a customer’s wait between order and fulfillment is minimal. This puts great stress on the organization: A constant stream of nonrepeatable products demanding tight scheduling of CMs as well as coordination between design, manufacturing and assembly staffs.
Holdem PDAs used Excel for all its BOMs. As part of his job, Mueller had to synchronize an endless stream of engineering change orders (ECOs) reflecting customization requests with the delivery of components and subassemblies manufactured all over the world. Bills of materials are his main vehicle for communicating design intent with his CMs, and it was the BOMs that left Mueller wondering what the future would bring.
Problems at Holdem PDAs began as soon as success struck. As its product lines grew, its Excel spreadsheets grew more complex and data control became tenuous at best. People invented part numbers on the fly, and spreadsheets were going out the door all the time.
Spreadsheets tend to go and live their own life. In the end everybody had their own version of the truth, and this became a severe threat to the daily operations of our company.
“Spreadsheets tend to go and live their own life,” says Mueller. “They get printed, adapted and updated. In the end everybody had their own version of the truth, and this became a severe threat to the daily operations of our company.”
Chaos in the part numbering system also grew critical. More than once Mueller realized that his bills of materials were using two numbers for a single part and one number for multiple parts. So, to ensure that Holdem PDAs’ contract manufacturers were using the correct BOMs, Mueller and his colleagues developed a regimen in which they manually checked and re-checked every Excel bill of materials before releasing it. It barely worked.
“Even in the best case scenario, it took us 24 hours to detect and correct a problem in an Excel BOM,” says Mueller. “The worst case was all those times we found out we had BOM issues by having useless PCB assemblies delivered to our doorstep.”
The longer Mueller fought with this system, the more obvious it became to him that Excel was not the way forward. “We were spending so much time correcting our bills of materials that we stopped paying attention to how much parts were costing us,” Mueller says. “Our motto became ‘as long as we get the boards.’ The bottom line was that we were losing money because of this Excel BOM management system.” Finally, management decided to hire an additional resource.
We were spending so much time correcting our bills of materials that we stopped paying attention to how much parts were costing us.
Perhaps growing weary of Mueller’s complaints about “his” spreadsheet problems, the company hired an Excel guru to be its product information manager. The guru’s first attempts to solve the BOM problem consisted of adding more automation to the Excel spreadsheets, but multiple systems and multiple scripting solutions fell short of solving the problem.
He next hit upon the idea of a subversion, or historical, repository. This repository held the latest revision of the Excel bill of materials and the current set of related data and documents as well as the historical records of every iteration of the product record. This information was then shared through e-mail or fax with the contract manufacturers. While this tactic solved the issue of sending CMs outdated BOMs, it did nothing to solve the part numbering mess. Nor did it reduce the workload on Mueller and his teammates; they still had to comb through every bill of materials searching for and correcting part numbers.
Engineering change orders were also handled with an update to the subversion repository. Here, the commit log contained all the extra information needed for the contract manufacturers to execute an ECO. However, Mueller and his teammates could not see if or when an ECO was put into effect on the factory floor, leaving them blind as to a project’s status.
Then, disaster struck: The Excel guru got a better job offer, and Holdem PDAs found itself with a homegrown BOM management process that consisted of incredibly complex Excel spreadsheets that nobody understood completely and that were filled with inconsistent data.
“It was unworkable,” says Mueller. “The level of complexity in the Excel BOM spreadsheets was so vast that only the guy who dumped us understood their internal structures and scripts.”
This led to manufacturing slowing down to a crawl, which, in turn, got management’s attention. Mueller argued that Holdem PDAs needed a dedicated BOM management system that everyone could use. Excel was just not designed to do the job they were making it do. He ticked off the systemic problems that were undermining the company’s long-term survival one by one:
Excel BOM management had failed because the spreadsheets became too complex and data entry was too disorderly.
The company was losing money with engineering and manufacturing time wasted on fixing problems every time an Excel BOM was produced.
Because the data was so inconsistent and problems were so difficult to identify and repair, the company often sent CMs faulty BOMs, which resulted in scrap and rework cost overruns.
Hiring an Excel expert had failed because it had left the company with a single point of failure, costing the company lost revenues as its process ground to a halt when nobody could work with the spreadsheet files.
A new Excel expert would perpetuate the known inefficiencies in the process and recreate a single point of failure destined to fail some day.
It still wasn’t an easy sell. Even with everything limping along, Mueller’s bosses continued to view BOMs as nothing more than a list of components needed to build a product. They found it difficult to imagine they were losing money because of poor bill of material management.
“The organization was so used to BOM trouble that it had become a part of our daily routine, which made it difficult to convince anyone we were losing money with our lousy data management,” says Mueller. “I told them so many times that we were hemorrhaging money trying to make Excel a BOM manager that they started to laugh whenever I brought it up,” says Mueller.
The organization was so used to BOM trouble that it had become a part of our daily routine, which made it difficult to convince anyone we were losing money.
But as the week of slowdown stretched into a month and no one had yet mastered the Excel spreadsheet BOMs, management relented and agreed to give Mueller a modern bill of materials management system. It worked.
“It was only after we introduced a true BOM management system and cleaned up our data that we could see the enormous costs savings we accomplished,” says Mueller, adding with a laugh that “I’m a hero now.”
We asked Karl Mueller for any insights that would help people using Excel to manage product information avoid the types of problems he experienced. Here are his recommendations:
Always enter supplier names in exactly the same format. Your computer does not know that “AVX Corp.” in your master supplier list is the same part source as “AVX.” Establishing a single convention for naming avoids confusion and mitigates the need for data cleanup at some later date.
Enforce data consistency with Validation Lists. These dropdown lists offer spreadsheet users a restricted menu of data they can enter into cells.
Avoid fancy colored text or cells. They add non-transferable complexity to your data conversion, which, in turn, risks data loss and manual data entry errors. Instead, use a separate column to draw attention to important information.
Use the “text” setting for your columns and cells. This prevents unwanted data truncation, like the loss of leading zeros, and reduces the likelihood of conversion errors like the interpretation of part numbers as dates.
If a part number remains to be determined, leave it blank. Do not insert placeholder values. Placeholders never die. Unexpectedly one day, a mock entry will be interpreted as a valid value and everything will get mucked up.
We also asked Mueller what tips he had to offer someone getting ready to move up from Excel to a modern BOM management system. Here are his top 5 tips for structuring your Excel data in anticipation of moving day:
Only prepare live data for conversion. Archived data can remain in Excel for posterity or for conversion at a later date.
Prepare your smallest dataset for conversion first. This will build up your understanding of the process and make it easier to uncover and repair kinks in your current data entry processes. Document what you discover and how you addressed the problems to create a guide for converting your next dataset.
Review and repair the data to be converted to ensure that it uses consistent standards for part naming and descriptions to avoid replicating problems with bad data in your new system.
Make sure that reference designators are clearly separated by commas to ensure proper conversion.
Do a final check of your data from the point of view of a computer. Make sure all values are entered consistently and labeled clearly, and avoid relying on visual cues (like tabbed indentions) to convey important details.
By following these steps, Mueller was able to facilitate his company’s transition to a modern bill of materials management system to much success. “Since we made the switch, we haven’t experienced any miscommunications with our contract manufacturers and haven’t spent a dime on scrap and rework. Our BOM reviews are quick and easy. Everybody in our company is on the same page now. By preparing like we did, the transition was nearly painless. I just wish we would have done it earlier.”
©2012 Arena Solutions, Inc. Arena and Arena Solutions are trademarks of Arena Solutions, Inc., Reg. U.S. Pat. & Tm. Off. All rights reserved. Other product and company names are the property of their respective holders. Contact Arena at questions@arenasolutions.com for permission to repost or syndicate this content.
Ready to start? Try Arena free for 10 days. Sign up
