BOM Comparison Spreadsheet

BOM Comparison Spreadsheet allows to automatically compare two BOMs and show the differences. It's a must-have for Mechanical Design Engineers and draughtsmen.

2 spreadsheets are available:

BOM Comparison Single-level

BOM Comparison Multi-level

BOM comparison Multi-level

What is the difference between BOM comparison single-level and BOM comparison multi-level

 

BOM Comparison Single-level

The BOM Comparison Single-level spreadsheet compares 2 single-level (also called "parts-only") BOMs. A single-level BOM only contains the fundamental components that form an assembly. Typically these are parts, fasteners, purchased components, etc.

The components in a single-level BOM are shown in their total number, so they will only appear once in the BOM and their quantities will be added. For example, if a fastener is used once in a sub-assembly and twice in another sub-assembly, it will appear once in the single-level BOM and it will have quantity 3 (see Part 2 in the image below).

An example of the structure of a Single-level BOM is shown below.

BOM Comparison Multi-level

The BOM Comparison Multi-level spreadsheet compares 2 multi-level (also called "structured") BOMs. A multi-level BOM contains the all the items that form an assembly. Typically these are assemblies, parts, fasteners, purchased components, etc.

All the items in a multi-level BOM are shown in the position that they occupy in the assembly. For example, if a part and a fastener belong to a sub-assembly, they will appear in the BOM as children of the sub-assembly. Because of that, components can appear more than once in a multi-level BOM, and each instance can have different quantities (see Part 2 in the image below).

An example of the structure of a Multi-level BOM is shown below.

Bom comparison Single-level

Bom comparison Multi-level

Why I created the BOM comparison tools

The Bill of materials (BOM)

During my working career I have worked in small and big companies. I have been involved in design work in all of them. At the end of the design of a machine the purchasing of the components must be fulfilled. A bill of materials (BOM) is usually created to facilitate the purchasing process. The BOM is an organised list of all the components that form of a machine and is used to facilitate the purchasing process. The BOM is organised so that the buyer has all the information that is needed to purchase every single component. This normally include:

  • Part number
  • Name of component
  • Quantity

A BOM can also include a series of accessory information, like:

  • Material
  • Vendor
  • Price
  • Weight
  • Appearance/Color
  • Etc.

Every company usually personalises the elements of the BOM depending on the requirements needed for the specific field in which they operate. For example, a machinery company may have to know the vendor and the material, while a company which produces consumer goods may be interested in the color and the expected delivery time of a component.

In the last 15 years the industry has moved to the use of 3D CAD systems to assist the user in the mehanical design. These are very common now and most of the designers are probably using softwares like Pro Engineer, Creo, Inventor, Catia, SolidWorks or Solid Edge on a day to day basis. Differently from the 2D systems, 3D CADs associate each part with a series of properties. Some of them are physical properties (e.g. weight, volume, etc.) and are calculated automatically by the software, others (like name, material, vendor, etc.) are manually defined by the user.

When a machine assembly is completed the user can automatically export the BOM from the CAD software. From this point onwards there is a significant difference between companies. Big and middle-sized companies can afford expensive PdM softwares, which are used to track the full life cycle of a component, including costing and purchasing. These softwares are able to evaluate if there are any differences between 2 versions of the same assembly and automatically prompt the user to define how to proceed with components that have been modified.

Small companies are more likely to complete this process manually. Most of them will export the BOM and then manipulate it on Excel adding information which the CAD software cannot handle. This system works fine but shows some flows when modifications are made to the assembly in the middle of the project. Let's imagine a part quantity needs to be changed. What the designer will probably do is update the CAD to have the right number of components and then manually update that quantity in the Excel BOM. It's not difficult to imagine how doing this could lead to human errors, which could cost time and money. The alternative could be to re-export the BOM every time a modification is done. This guarantees that the newly exported BOM is up to date, but creates the problem of the reconciliation of the data derived from the CAD with the data only available on the BOM. This leads people to download the updated BOM and then compare it line by line with the old one to spot and update the differences. This process is better and safer than the previous, but also very long and tedious. The BOM comparison spreadsheet was created to simplify this process.

Availability

The BOM comparison tools can be downloaded as a spreadsheet and as an Excel add-in. The tools can be started by just clicking on the button and are ready to run in a few steps. Using the BOM comparison spreadsheet will save you an incredible amount of time and hassle.

Any comments?

If you have any comments on this material I’ll be happy to receive your feedback. You can contact me here.

If you enjoy this but would like to have it customised for your needs feel free to contact me.