BOM Comparison Multi-level

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

Sign up below and be the first to know when the spreadsheet is released.

NOTE: this is not a newsletter. You will only receive one communication when the spreadsheet is available for download.

BOM comparison Multi-level

EnginExcel mailing list

Why I created the BOM comparison Multi-level tool

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 Multi-level spreadsheet will be available as a spreadsheet and as an Excel add-in. Using this tool will save you an incredible amount of time and hassle.

Do you like it? You might also be interested in my BOM Comparison Single-level.

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.