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.
Date of release: 22/06/2018
Compatibility: See release notes below
Available downloads (click to download):
After downloading the add-in, follow the instructions of the video shown here.
The add-in will appear as a separate tab in Excel. Clicking on the button "Open BOM comparison" will activate the BOM comparison tool.
Why I created the BOM comparison tool
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
A BOM can also include a series of accessory information, like:
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.
How the BOM comparison spreadsheet works
The BOM comparison spreadsheet compares two BOMs and creates a list showing the differences.
Using a custom interface the user selects the areas of the spreadsheet where the old and the new versions of the BOM are located. The user then indicates which column contains the quantity and which one contains a unique identifier. This is the code that uniquely identifies a part and is normally referred as the part number. The user can then select how the software will handle duplicate parts (parts which are in 2 different lines of the BOM but have the same part number). As a standard, when a part is shown more then once in the BOM, the two (or more) lines are joined and the quantities added. As an alternative, the user can require that the repeated parts are not automatically joined. In that case the BOM comparison tool will stop in case a duplicate is found and highlight it. Once the user has manually solved the issue it will be possible to proceed with the comparison. A final option that is given to the user is to define how the results are shown. Three alternatives are available:
- Alphabetical order: the old and new bom are shown side by side and ordered by part number. This option is the quickest to calculate.
- New BOM order: the old and new BOMs are show side by side and maintain the order that the new BOM had before the comparison. This is particularly useful if the user wants to copy the results of the comparison into the preexisting BOM spreadsheet and be assured that all the lines will match.
- Old BOM order: the old and new BOMs are show side by side and maintain the order that the old BOM had before the comparison. This is particularly useful if the user wants to copy the results of the comparison into the preexisting BOM spreadsheet and be assured that all the lines will match.
After this step the calculation can be started. This will take a few seconds. At the end of the calculation a new page is added to the spreadsheet showing the user the comparison of the two BOMs. Filters are automatically added at the beginning of each column, allowing the user to filter the page depending on the BOMs content and the results of the comparison. As an example, the user could be able to find all the components that have a quantity inferior than 10 and which are present in the new BOM but not in the old one.
The BOM comparison tool can be downloaded as a spreadsheet and as an Excel add-in. In this case, the add-in will include the EnginExcel add-in pack, a collection of some of EnginExcel free material tools. The tool can be started by just clicking on the button and is ready to run in a few steps. Using the BOM comparison spreadsheet will save you an incredible amount of time and hassle.
BOM comparison spreadsheet release notes
EnginExcel free spreadsheets are free for personal and commercial use.
BOM comparison spreadsheet is tested on Microsoft Excel 2007, 2010, 2013 and 365 for Windows. Microsoft Excel for Mac is currently not compatible.
- BOM selection
- BOM can be selected from different locations/spreadsheets
- No BOM size constraints
- Automatic verification of BOMs congruence
- Duplicates analysis
- Duplicate components are automatically added
- If preferred, the user has the option to be notified if there are duplicate parts
- BOM comparison
- Automated BOM comparison
- Selection of results display order: alphabetical, old BOM order, new BOM order
- Display results
- Result automatically formatted
- Results can be filtered
- Add-in for Excel available
|How to start the BOM comparison tool||Click on "Run BOM comparison" button in the "Old bom" page or in the "New bom" page (they will both open the same page).|
|How to start the BOM comparison tool (add-in version)||Click on the "BOM comparison" button in the EnginExcel add-in bar.|
|How to select the old BOM||Open the BOM comparison tool.
Click on the button under "Select the OLD bom range (including titles):".
Select the cells where the old BOM is. The titles must be included in the selection and must occupy the first row.
|How to select the new BOM||Open the BOM comparison tool.
Click on the button under "Select the NEW bom range (including titles):".
Select the cells where the new BOM is. The titles must be included in the selection and must occupy the first row.
|How to launch a comparison||Open the BOM comparison tool.
Select the old and the new BOMs.
Click on "Next -->".
From the dropdown menu select the unique identifier column (the column that contains the part number, which must be unique for each part).
From the dropdown menu select the quantity column.
Click on "OK".
|How to define the order in which results will be displayed||Open the BOM comparison tool.
Click on "Options" button.
Select one of the options in the area marked "Results order?"
|What the results order options mean||ALPHABETICAL ORDER: results are ordered alphabetically by the unique identifier column. This option is the quickest to compute.
OLD BOM ORDER: results are ordered following the order of the OLD bom. This is preferred if you want to refer to the OLD bom to manually transcribe the results.
NEW BOM ORDER: results are ordered following the order of the NEW bom. This is preferred if you want to refer to the NEW bom to manually transcribe the results.
|How to define if duplicate parts (parts that appear more than once in the BOM) will be automatically joined.||Open the BOM comparison tool.
Click on "Options" button.
Select one of the options in the area marked "Automatically combine duplicate parts?".
|What "Automatically combine duplicate parts?" means||This option allows to define if you desire to automatically combine parts that appear more than once in the BOM.
YES: parts that appear more than once in the BOM are combined. This means that the duplicate parts will be shown only once, and the quantity shown will be the sum of all the quantities of the parts that have been recombined.
NO: if a part appears more than once, a message is shown to the user and the duplicate parts highlighted. The user can manually remove the duplicate parts.
EnginExcel free spreadsheets are free for personal and commercial use, but it may not be altered or sold without the written permission of the author.
Although the author has attempted to find and correct any bugs in the spreadsheets, the author is not responsible for any damage or losses of any kind caused by the use or misuse of the spreadsheets.
The author is under no obligation to provide support, service, corrections, or upgrades to the spreadsheets.
For more information, please send me and email (see contact page).
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 free material but would like to have it customised for your needs feel free to contact me.