I love visuals in place of linear data, vis-à-vis spreadsheets. Even though Excel is actually a piece of software Microsoft nailed, I still find myself wanting to see my data in something more than rows and columns, especially at 4 in the afternoon when it’s easy to get cross-eyed. I’ve come across countless organizations where Excel is used rampantly and as the habitual default – even when there are professional tools available to them.
With all of the PDM conversations I’ve had, I’ve started making it my goal to see how many Excel spreadsheets I can kill with one application… SolidWorks PDM has stepped up to the plate in spades and now we have the addition of SolidWorks Manage.
The focus of this blog post will be to present scenarios of organizations I’ve visited and their usage of Excel in lieu of professional tools. And yes, there is plenty of custom tools for Excel, so I will be exploring its use as an OOTB tool with no custom tools for automation. Side note on custom tools – with security and viruses these tools create an extra layer of software management for your IT department.
First, how does one get data into Excel? Copy/paste, export from somewhere else or File > Open from a neutral format – all of these and other methods make it quite easy to get the raw data into Excel.
Second, once you have the data in Excel, then it’s time to clean it, filter it, sort it, spin, rinse and fold then put away… If it’s a repeatable process then you can streamline these steps. But even talking with my own controller, he’s had to do a lot of manual processing over the years to get data in and back out to our managers.
Third, most users will want to color code, conditionally format, build pivot tables, etc. to better “see and absorb” their data.
With SolidWorks PDM and SolidWorks Manage, you can tag team much of your Excel needs and knock them out of the ring…
Let’s take a look at a few customer scenarios to lay out the problems and solutions:
- Product Lifecycle Spreadsheets – Customer X manages lots of products. Each of the product managers have their own spreadsheets to manage the product’s lifecycles and status. The only way for anyone to know anything about a product is to go find that product’s manager so they can look in their own spreadsheets. Weekly meetings with status reports are great, but who wants to wait a week to get info. Data Silos are created and become bottlenecks for information sharing.
Solution: SOLIDWORKS PDM and/or SOLIDWORKS Manage. Both tools have great OOTB features and methods to track and manage product lifecycles. No need to collect data as designers perform their task which automatically update product lifecycle information. SOLIDWORKS Manage Dashboards and Reports then present the data to all users to easily consume.
- Product Part Numbering – Customer Y has a large spreadsheet with 100+ tabs (yes really over 100). Each tab is a product with part numbers listed, date taken out, who took it, description and few other fields. There are well over 40 engineers that need to access this one spreadsheet so you can imagine what can happen if more than one needs to get part numbers and forget to close it out. I actually still run into customers who have a real physical book where they take out numbers. Nostalgia is great, but not when it comes to production.
Solution: SOLIDWORKS PDM is great at creating part numbers. There are countless organizations who already rely on their PDM vault to create their part numbers. From simple sequential numbers to smart numbers, SOLIDWORKS PDM is definitely the way to go. Toss those spreadsheets out and let SOLIDWORKS PDM create and manage your part numbers for you.
- BOM Management – Customer Z is required to use an Excel template for their BOM creation. Technically their BOMs are mostly created in SolidWorks, then they transpose the data from SolidWorks to Excel where they finish off the BOMs. Then, they have to get this data into their ERP tool. That’s a lot of “material handling” and touch labor costs.
Solution: SOLIDWORKS PDM and/or Manage. SOLIDWORKS PDM has great tools to extend your design and build out your Bills of Materials. Whether you use your SOLIDWORKS Computed BOMs or build out a Named BOM with non-modeled items you can then use automated tools to export them to XML for your ERP to gobble up. SOLIDWORKS Manage expands the use of BOMs even further so you have lots of options.
- Project Management – Companies A-Z – just about everyone I’ve visited has used Excel to manage projects at one point or another. Yes, there are plenty of PM tools out there but let’s keep our focus on projects that relate to engineering and its data. If you’re using excel for project management, you have no way to collaborate with Gantt charting, Task assignment, Resource Allocation and scheduling – and absolutely no way allow the users to directly include their engineering data (SOLIDWORKS data or course) in their projects.
Solution: SOLIDWORKS Manage. Wouldn’t it be great to create a project, define its Gantt chart, assign tasks with hours assigned/needed and completion percentages AND be able to attach a Bill of Material, SOLIDWORKS design data or just needed PDF and Office files? All in one place!! Add Dashboards and Reports that pull all the data together and you can’t lose.
Hopefully, if you’ve found yourself in one of these scenarios you have some knowledge to take a closer look at how you use Excel as a crutch.
By: Steve Ostrovsky • Technical Services Manager • TPM