Are Spreadsheets Your Love/Hate Relationship?
It will probably come as a shock to engineers to find out that the tool they rely on the most is not engineering software. It’s the spreadsheet. Created as a business application in the previous century, the spreadsheet has found widespread use in all sorts of unintended ways. Maybe that’s because spreadsheets are easily available and adaptable, leading to their use for part and document management—even project management. Engineers would be the first to tell you that they should use the right tool for a job. So how did we get into this mess? Let’s take a look at what the right tools for part and data management might be—and hopefully we’ll see how we’ve been doing it all wrong.
There are many spreadsheets an engineer could be filling out. In fact, managing multiple spreadsheets can be a problem all by itself. It’s not unusual to have a spreadsheet to manage other spreadsheets. A few examples follow from my own experience:
- Master Parts list spreadsheet to create awareness of new parts being created and make sure that parts and part numbers aren’t duplicated.
- A release to manufacturing form that communicates the purpose and finalizes the parts used in my new design.
- A change process form for any parts that were modified from existing designs to suit my design.
- A finalized BOM spreadsheet that can be imported or manually input into my company’s ERP system.
- A new product development spreadsheet form that communicates the status of my progress for the overall design.
- A project management spreadsheet to communicate progress to the project leader.
- A spreadsheet to track tasks and projects for my personal prioritizing.
- A spreadsheet each for tracking raw materials, paint colors, work station codes, sequence numbers and manufacturing BOM’s
Are Smart Spreadsheets Smart Enough?
Spreadsheets are powerful and can adapt themselves to many tasks. All of the aforementioned spreadsheets can be outfitted with some intelligent function. Some can pull information out of an ERP system. Some can email automatically. Some can fill out forms. Some are smart enough to make other spreadsheets.
But forcing spreadsheets do so much can require considerable time and effort. That shifts an engineer’s focus to IT, when the best use of their time would be in innovation, design, analysis, manufacturing and other tasks for which they are better suited.
Even if IT resources are freely available (they’re not, most IT teams are just as understaffed as their engineering counterparts), we have to ask ourselves if spreadsheets are the right tools to be using. Even if we can get someone else to do it, should we? Should we force IT and engineering to sit through tedious and painful meetings, create specifications for systems and conjure up in-house solutions that work somewhat like what we had envisioned, but may break down the next day?
Look Around, Solutions Exist
Companies only need to realize that they are not alone with the basic problems of too much data and not enough neat processes—and that someone may already have solved the problems. Or at least, gone partially down that road. As it turns out, many vendors have lined up to offer solutions for data management and process control, enough to form a whole class of software called PDM/PLM. Depending on the vendor and the robustness of the solution, the P can stand for part, sometimes for process, the D for data or documentation while the M is for management.
SOLIDWORKS EPDM, which in this case stands for Enterprise Product Data Management, may be a natural choice for engineering firms already using SOLIDWORKS. It can also work well for many other CAD platforms, including Autodesk Inventor and PTC’s Creo and Pro/ENGINEER. At a few thousand per seat (pricing varies on quantity and vendor), it’s highly cost competitive when judged against enterprise PDM vendors. EPDM offers a variety of user roles for users and pricing to match. A lot can be done with their free web viewer (for non CAD folks) after initial purchase.
Real Life Benefits of a PDM Product
A good PDM product will let a company roll into it all the tasks, milestones, approvals and product related data that were previously done in spreadsheets. With a CAD-based PDM product, the interface to the PDM is in the familiar CAD interface, or in a web client with dashboards and easily understood reports.
Here are some examples of how SOLIDWORKS EPDM has been used:
- Part number management can be completely automated on each save of each part. Numbering schemes can be modified with drop downs and project selections during the initial save. This helps make part number duplications a thing of the past.
Release and change management forms can be automated. Required fields and processes for approvals are built into the software. You many need to create forms but they come with drag and drop functionality. Fields can be manipulated at any stage that is required by the appropriate person responsible for that data
One version of many communication selection tools available. Image courtesy of Dassault Systèmes online demonstration.
- Connect with an ERP system to push and pull information. BOM comparisons are extremely intuitive. Discrepancies can trigger e-mails to the appropriate personnel. Those people then have a host of tools to mark up and modify a variety of connected BOM’s. Manufacturing BOM’s, shipping BOM’s, engineering BOM’s and many other types can be fully controlled within an easy to use interface instead of multiple unintelligent spreadsheets. Or an ERP that is completely separated from engineering data.
An example of BOM management tools. Image courtesy of Dassault Systèmes online demonstration.
- Status can be part of the save process. Engineers can be prompted for anything during any stage. This information can then be translated into a dizzying amount of reports and dashboards for sharing with management and co-workers.
EPDM workflow tools. Image courtesy of Dassault Systèmes online demonstration.
- Work centers, raw materials, etc. can all now be controlled within EPDM and easily applied at any stage of the process
- Communication is built in with messaging and email. It is flexible in its set up but can control users so every piece of necessary information is entered and available at the next step.
- Deadlines and reminders of required tasks can be automated, showing management delays and bottlenecks.
SOLIDWORKS EPDM, while addressing most of the problems mentioned at the beginning, may not be a perfect solution for really large companies that have a huge portfolio of products and need a full scale project management tool or an MES system. Growing companies can, however, tack on functionality with third-party add-ons. But for all that in one box, you’ll have to get an enterprise PDM or PLM solution—and face the large enterprise prices. For most small and medium sized companies, however, a CAD-based PDM solution will get a hasty ROI.
More importantly, for engineers, it will get them back to engineering and free them from their love/hate relationship with spreadsheets.
About the Author
Ryan Reid is a CAD administrator, PLM enthusiast, designer, GD&T specialist, lead, lean philosophy supporter, Microsoft Office expert, 3D printing hobbyist and manufacturing-focused professional with 17 years of combined experience in those areas. Reid has accomplishments in all aspects of manufacturing engineering, from cradle to grave plastics/mold to structural, systems, process and change management design.