Part 2 in the Excel Dispatch Series
In the first dispatch in this series, we discussed the hidden costs of using a great tool like Excel for jobs that it is not designed to handle, such as bill of materials (BOM) management. But it’s undeniable that Excel remains the most widely deployed engineering and operations management tool in use today.
And why not? In business you do what you have to do to succeed and, well, you’ll deal with the consequences of today’s decisions later. Which, in a nutshell, is why so many companies rely on tools like Excel longer than they should. You paid for it already, so it feels free. And you can use it for jobs that it is not designed to handle, sort of like using a wrench as a hammer.
Their intellectual property has become both their most valuable asset and their biggest liability because a single, slender point of failure stands between their progress and their decline.
But this workplace reality also explains why so many companies arrive at the doorstep of the next level in their business, pause, and wonder how they can move forward. Their intellectual property — engineering documents, vendor information, manufacturing data and BOMs — has become both their most valuable asset and their biggest liability because a single, slender point of failure stands between their progress and their decline.
In this dispatch, we examine one company’s use of Excel and the challenges it faces while trying to grow, and then provide ten tips for when you must use Excel BOMs to keep your product information accurate and secure.
When Victor Newhouse founded Focused Photon, he took to heart the warning from Confucius to “plan ahead or find trouble on the doorstep.” (Editor’s note: Names have been changed to preserve confidentiality.) But all too soon “plan ahead” ran into the wall of compromises and workarounds needed to get the first Focused Photon products to market. Now, four years later, the consequences of decisions made in haste then have become a roadblock to continued growth.
Now, four years later, the consequences of decisions made in haste then have become a roadblock to continued growth.
Focused Photon is a high-tech manufacturer of rugged fiber-based laser sources for markets such as materials processing. Newhouse, a design engineer by trade and acting operations manager of Focused Photon, describes his company as a small, low-volume manufacturer of highly complex products requiring very rigid control specifications. Since its inception, Focused Photon has used Excel to define part numbers, manage bills of materials, purchase parts and components, track inventory levels and maintain the status of sales and receivables.
Newhouse calls his no-nonsense purchasing manager (and de facto document control manager), Amanda Hubley, an “Excel Wrangler.” Hubley, an amateur rodeo rider in the few off-hours she enjoys, has created more than a dozen Excel spreadsheets to handle everything from design revisions to purchasing data to vendor lists.
Hubley creates semi-intelligent, sequential part numbers in Excel and distributes them to Newhouse as needed. Newhouse uses these part numbers in his CAD designs, then exports his bills of materials back to Excel when his designs are complete. Hubley then takes the exported Excel BOM and uses it as a basis for sourcing parts and components. Every once in a while, Excel interprets a part or vendor number as a date, but Hubley has trained herself to be on guard against that. Hubley recalls that when they upgraded to Excel 2007, “All of our part number prefixes for the main assembly 012035 turned into ‘20-Jan-1935.’ I lost a whole day trying to figure out the formatting and verifying that my data wasn’t lost.”
The master Excel spreadsheets reside on Hubley’s laptop computer, meaning that only she has instant access to the most current files. She determines all spreadsheet naming conventions and revision numbering. Hubley’s spreadsheets use indented data but eschew hidden worksheets. Each week she backs up her spreadsheets to a server that’s stashed in a closet.
All of our part number prefixes for the main assembly 012035 turned into ‘20-Jan-1935.’ I lost a whole day trying to figure out the formatting and verifying that my data wasn’t lost.
One key to the Focused Photon process is that Newhouse and Hubley are the only people touching the Excel spreadsheet product data. This ensures an easy audit trail and enables the company to avoid confusion on calculated fields like unit of measure. Each time an Excel BOM is created, Newhouse and Hubley manually inspect the information before releasing it to others.
Newhouse, as the lead design engineer, communicates his engineering change orders (ECOs) and purchase requests through email. Hubley then tracks them in a log file. For purchase requests, Newhouse loads up part numbers and required quantities and sends Hubley a notification email. She digs through her “approved vendors” spreadsheet to find the vendor and part information and cross-checks the data with her “parts and specs” spreadsheet. She then manually keys that data into Excel to track costs and maintain inventory. The manufacturing engineer emails or phones Hubley when he grabs a part from inventory so she can update her inventory data in Excel.
Newhouse sees Focused Photon slowing down at a time it should be moving ahead. He wants to pursue other products, and he knows that updates to the existing product line are a must. But Hubley is already having trouble keeping up with the current demands on the existing product. She doesn’t see how she could maintain current information or provide instant data if more hands were touching the data or using up inventory, or if a new product were introduced. Further, Newhouse would love to hire another design engineer, but has major reservations about the company’s ability to scale.
To avoid currency conversion complications, Focused Photon uses domestic components only, although bids from overseas contract manufacturers are starting to be evaluated. “We feel that we need to start looking at international suppliers to support our growth and go global when the time comes,” says Newhouse, “but I’m afraid that if we add all the variables that come with offshoring into the spreadsheets either they or Amanda will explode.”
Still, Focused Photon is making its process work for now. Hubley has tight control of her spreadsheets and knows intimately how they operate. She and Newhouse communicate frequently and work hard to ensure that their data is current and correct. When asked what would happen if she disappeared, Hubley replied, “They’d have my laptop. I make copious notes. About all that they’d lose is a week of work that I had not backed up.”
But Newhouse isn’t so sure. Acknowledging Hubley as a single point of failure, he remarked, “It would be ugly. She’s the only one that knows what’s going on with those Excel spreadsheets. Things would suffer. We’d have to let some of the spreadsheets and the data they hold go. Then we’d wing it until we figured something else out.”
It would be ugly. She’s the only one that knows what’s going on with those Excel spreadsheets.
By making the decision to use Excel — rational as it seemed at start-up — Focused Photon traded the time it would have taken to select and implement a dedicated business system for time to build itself up. Now, however, the inertia resulting from that decision has left them with a single point of failure — and left them wondering how they can get themselves out of that situation while still continuing to grow.
If you find yourself forced to use Excel, chances are you too are relying on a single point of failure. When you have to get by with Excel, you can minimize your risk by working smarter. Here are some tips for how to best manage your product data in Excel:
Keep it simple — Use a simplified, predetermined set of numbers to identify vendors and parts
Take care with your conventions — Excel can interpret numeric strings as dates, which can wipe out your data in a flash
Secure your data — Minimize the number of people authorized to make changes to a spreadsheet, use locked workbooks, protect vital data with passwords, make sure shared files are read-only and back up your data frequently
Watch out when importing data — Excel makes it too easy to overwrite data, so avoid importing data into Excel unless you can guarantee that the source file and destination spreadsheet template are 100% identical, even at the cost of inefficient double data entry
Avoid hidden worksheets — While hidden worksheets provide a measure of protection, formulas remain active and anyone can reveal and edit an unprotected worksheet
Sort all your data — Always select the entire spreadsheet when sorting, because that’s the only way to ensure that you’ve included every cell
Don’t spread out your data — Spreading related data across too many Excel worksheets or workbooks makes analyzing data and generating reports more difficult
Check yourself — Whenever you modify a spreadsheet, generate a BOM or enter data, inspect and verify your work carefully before you release it to others
Keep good records — Maintain a separate log file for auditing, and keep separate digital and physical records for compliance
Plan ahead — Lay the groundwork now for expansion to other tools such as BOM management and ERP by standardizing your Excel spreadsheets and using import-friendly templates whenever you can
©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
