Jim Connett, on July 08, 2020, 02:22 PM
Step Away from the Spreadsheet!
If you’ve spent time around construction sites or with a master craftsman, you likely have heard this axiom: “use the right tool for the job.” It’s possible to remove a screw from an old plank with a hammer, but the best tool for the job is a screwdriver. A tree can be cut down with a handsaw, but the best tool for this job is a chain saw. This axiom applies to many situations we face each day. Today, I’d like to bring even more clarity and wisdom to this axiom by adding one word:
Use the right tool for the right job today.
When it comes to enterprise applications, I’ve come across a wide array of both off-the-shelf and custom solutions ranging from “brilliant” down to true “head-scratchers.” At some level, I can at least understand and empathize with the efforts that compel us to develop and implement all types of solutions. There is, however, one situation which is all too common, painfully unsustainable, and very much in need of urgent reevaluation. That situation is when a company uses a spreadsheet for all manners of data collection and planning.
Data Collection in the ’70s
In 1979, VisiCorp released the first spreadsheet application called “VisiCalc” for the Apple II computer.1 Since that day, bankers, bean counters, and backroom analysts have glommed on to this application as THE tool (not “a” tool) in their toolbox. Over time, we’ve seen the capabilities and features of spreadsheet applications rapidly expand. If I had a spreadsheet available, I could chart this exponential growth in living 3D color for you.
Pros and Cons of Spreadsheets for Data Collection in Manufacturing
Why do we use a spreadsheet so frequently and for all manners of information collection in manufacturing? Maybe more directly to the issue, why should we NOT use a spreadsheet so frequently?
Instead of directly answering those questions and fully ignoring all the legitimate situations where a spreadsheet is a perfect solution, allow me to mention a few—but in no way a complete list – of the benefits and drawbacks of using a spreadsheet as a tool for data collection in manufacturing.
In the “benefits” category, a spreadsheet is:
- Easy to use, calculate, program, and generate visual content from a sea of numbers
- Ubiquitous, easily accessible, and quickly understood by author and reader alike
- Low-cost or free (generally speaking)
- Flexible in organizational structure using both individual tabs and as well as named collections of tabs
And for the “drawbacks,” a spreadsheet is:
- Uncontrolled. Most certification standards, regardless of the industry, require some level of control over master data. The data entered into a spreadsheet is freely modifiable. With some effort, passwords can be used to prevent the change of data…and passwords can be discovered with any number of “password crackers” available on the internet.
- Unversioned. Without a versioning or revision system to track changes to the contents of the spreadsheet, it is nearly impossible to identify points of change or revert to a previous cell value or formula or layout. Confusion quickly escalates when multiple copies of a spreadsheet circulate through email attachments within an organization.
- Inefficient. When a spreadsheet becomes the only tool in the toolbox of an engineer or production manager, then that tool, by default, must be able to handle all types of data collection, from route and product information to measurement information to documentation to reporting…the list goes on and on, and soon we’re cutting down an oak tree with a handsaw, complete with all the pain that occurs as a result of our efforts.
- Data silos. A spreadsheet can quickly become silos of information with no logical or calculable relationship between itself and any other data source – which may be another spreadsheet. If I want to find production process information, I go to this spreadsheet. If I want history or genealogy of material, I go to that spreadsheet. If I want to view data collected on this product, I go to yet another spreadsheet. If I want to perform any sort of advanced analysis using a combination of process, history, and product data, I’m completely out of luck.
Data Collection Solutions for Today’s Manufacturers
Today, cost-effective and easy-to-deploy options exist. These new tools can now (and should now) be added to your toolbox. For example, a properly structured, simple relational database using any number open-source database engines will resolve pretty much ALL of the drawbacks listed above. If someone must use a spreadsheet, then encourage them to write SQL statements within the spreadsheet application to query a database to retrieve the desired data (yes, this can be done with some help from YouTube tutorials to show you the way).
A spreadsheet may be widely used in your organization, and for some, that may be “good enough” for now, but I encourage you to develop a plan to take that first step away from the spreadsheet-as-data-collection-tool approach and move your organization’s data into a more strategic position for future growth. In the short term, a spreadsheet can provide a solution for collecting data, but the emphasis here is “short-term.” If necessary, a spreadsheet should only temporarily serve as a utility application for data collection while implementing a long-term data storage solution. A truly suitable long-term storage solution should address and resolve the shortcomings of spreadsheets. Regardless of how the data gets there, a suitable data storage solution affords your organization a wealth of accessible data that can be leveraged to gain insights toward improving many facets of your production environment.