The Challenge

Often when listening to a CIMdata presentation, their consultant will offer a tongue in cheek remark: “the most widely used PLM tool is Excel”. So, is it true? It is worth examining the issue in more detail.

Before proceeding, a better definition of the title is in order. The more accurate formulation – “Does an Excel spreadsheet contain the master definition of the eBOM during product design in your organization?”

There are two crucial points in this question

1. Master definition implies that the BOM of record for final design is maintained in a spreadsheet
2. eBOM is distinct from the mBOM maintained in an ERP system. Organizations generally do not use Excel for this purpose.

The Reality

So how prevalent is the master Excel BOM? If you are reading this article, you can answer silently for yourself. Aside from that, let us look at two data points:

Tata Technologies has conducted a PLM benchmark assessment at approximately 150 different organizations over the last 4 years. Based on our results, approximately 75% of these organizations use Excel in one form or another to control the eBOM.

Try a Google search on “Bill of Materials” and access the results for “Images”. Count how many of the images show Excel. By my informal count, at least 60% of the images from that search are Excel sheets of one form or another.

So, the prevalence of using Excel for eBOM would seem to be very high. Is that good or bad? Let’s examine that question in following sections.

The Good

Excel is perhaps one of the great inventions of the IT revolution. It is conceptually a simple tool but can be used in sophisticated applications. For generating and specifying a simple eBOM, it has the following merits.

1. It is flexible and can be formatted in various was to portray an eBOM structure. The inherent layout of rows and columns is convenient for defining a structure
2. One can add many columns to define and specify the various attributes of Parts in the eBOM
3. Because Excel can easily perform calculations, the spreadsheet can automatically give aggregate results like total cost, total or sub assembly weights, total selling price, margins etc.
4. It is a readily available tool across all businesses because of the persuasive nature of MS Office and everybody knows how to use it. Probably most businesses see it as an economical and inexpensive tool; dependent on budgets this may or may not be true
5. Although it takes a little bit of dedicated formatting, the eBOM can show indented levels and collapsed sub-assemblies

The Bad

Of course, Excel may have some strengths, it has some significant weaknesses when it comes to eBOM management:

1. It is difficult to do a BOM compare in Excel. Such functionality is required when two versions of the BOM need to be compared to see the differences
2. Change Management in any Product Development environment is always a big challenge. While it may be possible to do revision control on an Excel spreadsheet (e.g. in SharePoint), it is impossible to do change control on individual parts in the eBOM
3. Excel has no safeguards against anyone inadvertently deleting line items or details out of the eBOM.
4. Calculations and formulas in Excel must be constantly checked to see they cover the correct ranges as changes will compromise these. Many companies have fallen to the problem of Excel formula mistakes
5. By their nature, Excel spreadsheets are difficult to control and can proliferate easily, resulting in multiple copies. Which one is then the master?
6. Excel eBOMs are always entered manually into ERP systems; requiring considerable effort and allowing the possibility of errors

The Ugly

Even if the “Bad” above is manageable, there are some situations where it is impossible to even consider using Excel for eBOM. Here are some of those situations:

1. Large complex eBOM with part counts in the 200 and above range. No human can reasonably manage this in an Excel worksheet.
2. Situations were the product has multiple configurations or variants. Because the number of resolved eBOM’s grow exponentially with the number of variants, it becomes impossible very quickly to manage
3. When multiple downstream users must access the eBOM. This compounds the proliferation problem
4. When the eBOM changes rapidly – keeping everybody up to date becomes impossible
5. An eBOM is the very core of Product Development. Why use a half-baked tool?
6. Excel cannot connect to any digital representation of the Part (3D model, Drawing, Specs etc.). This is a major shortcoming!

The Change

So, are you comfortable driving an automobile knowing that 66% of the eBOM sub-assemblies are managed in Excel?

There is technology that overcomes all the Bad and the Ugly. It exists today and is proven. The change to these systems may be difficult and potentially expensive but given the critical nature of an eBOM to a Product Development organization, it must be embraced!

Watch for the next article.