Tutorial 3: Electrical BOMs

For this tutorial, we’ll be creating a BOM for an Arduino Uno. The Arduino Uno is beloved by all makers as an easy-to-use programmable microcontroller that’s the perfect brain for any new project. Its open-source nature makes it a handy example for an electronics QuickBOM project.

Why use QuickBOM in this instance?

  • If you were designing an Arduino Uno from scratch, you would no doubt be using a program like Altium to design the board and keep track of your bill of materials. That’s good! QuickBOM is not a replacement for the BOM that is generated by a board layout program. Rather, its best used in tandem.

  • Using QuickBOM throughout the design process would allow you to have purchasing working in the flat BOM (the Library tab) while your engineers try out different parts in the structured BOM tab.

  • While designing, QuickBOM will allow you to keep a running tally of cost. You can have multiple tabs of multiple different versions of the board going at once as you optimize for all sorts of different factors.

Arduino Uno

Requirements:

  1. Download the latest version of QuickBOM

  2. Download the Arduino Uno Flat BOM.xlsx

Step 1: Get your files ready

  1. Open your blank QuickBOM file, and save it as a name of your choosing (suggested QuickBOM - Arduino Uno.xlsm). If you need a fresh copy, download one from here.

    NOTE: The first time you attempt to open the file, Microsoft will attempt to block it. Follow these instructions to get around it: quickbom.com/enable-macros.

  2. In your QuickBOM file, on the Instructions tab, click Create New BOM.

  3. Select Manual BOM, and give it a name (suggested Arduino Uno). Click Create BOM.

  4. Notice that a new tab called MAN_Arduno Uno was created.

    NOTE: A MAN_<your BOM name> is a BOM that is manually entered by the user. You have full control reorder parts, create assemblies, change quantities, etc… This is different from a CAD_<your BOM name>, a CAD driven BOM, which you’ll see in Tutorial 2.

  5. Open Arduino Uno Flat BOM.xlsx in another window.

Step 2: Build the BOM

  1. First, let’s clear out the default content. Go to tab MAN_Arduino Uno and do the following:

    1. In cell B1, click the button Disable to Disable Sync.

    2. Select columns H through Z, right click, and delete columns.

    3. In cell B1, click Enable to Enable Sync.

    4. Highlight rows 5 through 20, and click the button Delete Row(s) in cell D1.

      Do not try to delete Row 4. Every BOM in QuickBOM must have a top level assembly.

  2. Type a top level name for the assembly into cell D4 (suggested Arduino Uno). Your file should now look like this:

Now, let’s take a second to switch over to work on the flat BOM in the Library. The next steps will be easier there.

3. In Arduino Uno Flat BOM.xlsx, highlight cells B2 to B34. Hit ctrl+c to copy then to the clipboard. In your QuickBOM file on tab Library tab, highlight cell A4. Right click, and Paste Values.

4. In Arduino Uno Flat BOM.xlsx, highlight cells C1 to Q1. Hit ctrl+c to copy them to the clipboard. In your QuickBOM file on tab Library tab, highlight cell C2. Right click, and Paste Values.

5. In your QuickBOM file on the Library tab, type the word Custom into cell C1. That lets the sheet know that this property is entered by the user rather than imported by a CAD system. Copy cell C1 and paste it to cells D1:Q1.

6. In Arduino Uno Flat BOM.xlsx, highlight cells C2 to Q34. Hit ctrl+c to copy them to the clipboard. In your QuickBOM file on tab Library tab, highlight cell C4. Right click, and Paste Values. All of the raw part data is now in the Library tab of your QuickBOM file. Your file should look like this.

NOTE: Notice that we never entered the quantities into the flat BOM (the Library tab). These will come later.

Now let’s get going on the structured BOM in the MAN_Arduino Uno tab.

7. Let’s start by pasting in the part numbers. In Arduino Uno Flat BOM.xlsx, highlight cells B2 to B34. In your QuickBOM file on the tab MAN_Arduino Uno, highlight cell D5. Right click and paste values. Click YES on the popup dialog box. This operation will take about 8 seconds.

8. Next, we’ll paste in the quantities. In Arduino Uno Flat BOM.xlsx, highlight cells A2 to A34. In your QuickBOM file on the tab MAN_Arduino Uno, highlight cell C5. Right click and paste values. Click YES on the popup dialog box. This operation will take about 8 seconds.

9. Now that the parts are in there, let’s add some data. But we won’t add all the data from the Library tab, let’s just do the most important ones. Click Sheet Controls, and click Insert Column Header. With the Custom radio button selected, choose the Column Name Description from the dropdown and click Create Column. Create columns for MFN, MPN, Subsystem, and Cost the same way. Notice that the data for each part gets pre-populated in the cells below each header.

10. Let’s create a column to sum the cost. Click Sheet Controls, and click Insert Column Header. This time, choose the Subtotal radio button. Subtotal of (Cost) will automatically be populated in the dropdown. Click Create Column.

If desired, you can re-order any of the columns F and on.

  • Disable Sync by clicking Disable in cell B2.

  • Highlight a column, hit ctrl+x to cut, select the column where you want it to go, right click, and select Insert Copied Cells.

  • When you’re done, re-enable Sync by clicking Enable in cell B2.

11. Finally, let’s add a bit of structure to the BOM to make it more readable. Notice that all of the components are divided into Subsystems 16U2_Sub, Shield_Headers, USB_Cnxn, and Voltage_Mgmt.

  1. Highlight row 5 and click Insert Row(s). Type 16U2_Sub into the newly created cell D5. Highlight cells D6 to D17 and click -> to indent them to level 2 and make them children of 16U2_Sub.

  2. Highlight row 18 and click Insert Row(s). Type Shield_Header into the newly created cell D18. Highlight cell D18 and click <- to dedent it to level 1. Highlight cells D19 to D21 and click -> to indent them and make them children of Shield_Header.

  3. Highlight row 22 and click Insert Row(s). Type USB_Cnxn into the newly created cell D22. Highlight cell D22 and click <- to dedent it to level 1. Highlight cells D23 to D27 and click -> to indent them and make them children of USB_Cnxn.

  4. Highlight row 28 and click Insert Row(s). Type Voltage_Mgmt into the newly created cell D28. Highlight cell D28 and click <- to dedent it to level 1. Highlight cells D29 to D41 and click -> to indent them and make them children of Voltage_Mgmt.

12. But there’s one part that wasn’t in the original BOM. That’s the printed circuit board itself! Let’s add it now. Highlight cell D5 and click Insert Row(s). Type PCB into the newly created cell D5. Give it a Description of Printed Circuit Board, and type TBD for MFN and MPN. Let’s estimate the cost as $3.50 for this by typing 3.50 into the Cost cell.

Note: Every time you add data to this sheet, QuickBOM will sync it to the Library tab. That’s what it’s doing when the loading bar briefly flashes on the screen after typing data.

When complete, your QuickBOM File should look like this

General Tips & Tricks

  • If you share this file using OneDrive or SharePoint, you can have multiple users in the sheet at one time.

  • Engineers generally work in the tab that shows the structured BOM (the MAN_Arduino Uno tab)

  • Purchasing generally works in the flat BOM (the Library tab).

  • Any changes on MAN_Arduino Uno tab or the Library tab will be reflected on the other tab.

  • During periods of heavy design time, it is recommended that engineers keep the structured BOM (the MAN_Arduino Uno tab) up to date at all times. This will allow engineering and purchasing to always be working with the same data.

  • Experiment with adding other “Custom” columns. In my experience, it’s very helpful add custom columns for the following.

    • Status - I use this column to track where the part is in it’s design process. Examples: Drafted, Ready for Checking, Redlined, Ready for Purchasing, etc… Every company’s process will be slightly different

    • Engineering Assignee - I generally assign an engineer from the team to every single part. If there is a drafting error on that part, I make that person go back to it and fix it.

    • Purchasing Assignee - I generally assign a purchasing person for every part. That way, I can be confident that we will fully cost a design on time.

    • Stock as Spare Part - When an engineer identifies a part as a wear item, they indicate it in this column. That tells supply chain to stock extra parts to be sold as spares.