Engineers’ love-hate relationship with spreadsheets
Engineering efficiency | Product design
For engineers, there’s no getting around them. Spreadsheets, that is. From bills of material, to engineering formulas and calculations, to product costs, spreadsheets are an everyday part of the job. They bring some advantages, but they also come with a great deal of drawbacks. They’re easy to use, but very limited in scope. They require a great deal of discipline just to keep them updated over time. Yet you may have a love-hate relationship with the spreadsheet. In this article, we’ll take a deeper look at the pros, cons and even some alternatives to spreadsheets.
Why the Foothold?
Spreadsheets owe at least part of their popularity in the engineering world to their capability to be configured according to need. A lack of structure means you can define their own structure.
Sometimes when designing you simply need a method of capturing your ideas. As much as we’d all like them to be, ideas might not be well structured until they’ve been formed, remolded, and formalized. In the meantime, spreadsheets offer a great way to capture initial formulas, calculations, cost configurations, and other thoughts.
They also offer the capability to create very complex formulas that can be customized with functions. Many engineering organizations have developed their own invaluable equations that correlate to performance, testing, and prototyping.
What about the Downside of Spreadsheets?
The very lack of structure that makes spreadsheets appealing also makes them subject to human error. Because they can’t include built-in automated or logical error checking, one small typo could balloon through the equations and eventually make its way to the shop floor.
Because they exist as files–rather than in the cloud–they can easily get lost on a desktop, which means you must use some type of intelligent naming and versioning system to label them. Of course, your organization will likely have a lot of these files, so keeping them straight becomes an exercise in frustration and futility.
These types of files aren’t intelligent about updates. So a change to a CAD assembly won’t automatically update the spreadsheet used to manage the BOM, and vice versa. You’ll need a significant amount of discipline to keep everything synchronized across various spreadsheets needed to manage the product lifecycle.
You’ll find still another drawback in sharing and collaborating via spreadsheet. Sure, you can email these files back and forth, asking viewers to make their own changes and to insert comments. But you’ll have separate versions of that first spreadsheet returned, each from a separate reviewer, each with its own changes, and each with comments to compile and track.
It’s also too easy for everyone at work on a project to maintain his or her own separate version of a spreadsheet. For example, other engineers might have other ways of building a BOM, each maintained in a separate spreadsheet. Those variations will lead to errors downstream.
And while spreadsheets can capture complex formulas, they can’t present them professionally. The equations viewed within such files don’t look like anything that would appear in an engineer’s notebook.
Other Appealing Spreadsheet Options
Luckily, software that brings the advantages spreadsheets offer along while doing away with their drawbacks is now available.
One of these choices may be surprising: cloud-based spreadsheets. Some spreadsheet apps can now run in a browser. While they don’t include all the functionality found within desktop-based spreadsheet tools like Excel, they’re advantageous in a couple pretty significant ways. They track every single change made. With them, you won’t need to worry about checking files in and out. Sharing the spreadsheet with someone else is as easy as sending someone a link. The apps track comments and even modifications simultaneously, so even as multiple users access the spreadsheet remotely it’s always up to date with the latest changes. All users are working from the same, updated document.
Engineering calculation software is yet another option to desktop-based spreadsheet software. This is another file-based solution, but with engineering calculation software, equations actually look like formulas. The software includes mathematical, scientific, and engineering notation.
PLM or ERP systems are a great alternative to spreadsheets. Some information traditionally kept in spreadsheets actually can—maybe even should—be housed in a formal enterprise system like a product lifecycle management or enterprise resource planning system. These are systems of record, after all. But let’s say you want to develop your work in process before formalizing it within such a system. The good news is that some of these enterprise systems now offer sandbox workspaces that allows for informal work. Here, the information formerly housed in spreadsheets can be worked on and dealt with until it’s formalized in a final version within the enterprise system.
Spreadsheets continue to be necessary for managing many parts of your job, including tracking BOM and housing engineering calculations and formulas. But newer alternatives can act as better stand-ins for spreadsheet. And maybe even end your love-hate relationship with the sheet.
Do you love spreadsheets, hate them, or stand somewhere in between? How does your company use them? Does it use one of the alternatives listed? Another alternative? Would another organization and tracking method, beyond the spreadsheet, help?