Free Bill of Materials template spreadsheet to negotiate and track pricing

written by GORILLA ROI - updated July 26, 2020

bill of materials spreadsheet template
Bill of Materials template spreadsheet available for download at the end

By the end of this article, you will

  • understand why Amazon sellers should use a BOM
  • how to use it to negotiate with your suppliers
  • how to design your own bill of materials template
  • download a free bill of materials template spreadsheet

What is a Bill of Materials (BOM)?

A bill of materials (BOM) is an extensive list of raw materials, components, and assemblies required to construct, manufacture or repair a product or service. – Investopedia

Think of it as a list of ingredients of a recipe to manufacture a product. Just without the instructions.

I also think of it as a checklist to manufacture a product and to record my costs.

Here’s why.

Biggest mistake of not using a Bill of Materials

If you manufacture in China, one of the biggest mistakes you can make is not using a Bill of Materials.

  • You can easily get ripped off
  • You end up with low quality goods
  • You find quality fade with future orders

Why?

There are great factories in China. After many failed products and disasters, we finally found our perfect manufacturers. But it took a lot of effort and using a BOM helped substantially.

Tell me if I’m wrong, but typically, your process for finding and ordering with suppliers will be like this:

  1. Go to Alibaba and search for a product
  2. Contact 3-5 factories and ask for a quote of the product they listed
  3. Finalize down to a couple
  4. Get samples
  5. Choose a single factory
  6. Make a small order
  7. Send inspector
  8. Send shipment
  9. Receive shipment
  10. Get shocked with low-quality products or how you were ripped off

In China, a Bill of Materials will help to prevent this.

How a BOM will save manufacturing problems

Ok.

A BOM won’t save all your manufacturing troubles, but it eliminates miscommunications and murky expectations immediately.

By having a detailed breakdown of how the product is manufactured, you end up with a lot of leverage, transparency and you can control the product.

With a BOM:

  • you know how much each component will cost
  • if prices rise – you can trace it to which component is causing the price increase and determine whether it’s true or a lie
  • if quality fades – you can trace it to the problem
  • when comparing suppliers – you can compare apples to apples and see what materials they are using and how much they are charging
  • know if you are dealing with a middle man or not. Middle men won’t be able to give you a BOM or detailed quote.

If your price quotes are very basic and look something like this from a supplier:

  • yoga mat 20×30: $12

where you are only getting a single dollar value, and there is no breakdown, you are likely overpaying.

It’s convenient to get an all in one price, but it’s never the best. You don’t want your supplier to roll up prices into a single value.

Knowing your exact costs means that you can control the negotiations. Not be controlled.

bill of materials template cogs breakdown

This is a screenshot from our pricing calculator, but a BOM template is simpler.

Bill of Materials example – Plastic organizer

Regardless of how simple you think your product is, there are too many different materials to ignore.

Look at this plastic organizer.

bom example

When manufacturing this, you could go the easiest route and ask for the exact same thing and only change the sticker or the box design.

This is what most newbies do anyway – and then lose money and cry that it’s impossible to sell on Amazon.

But you’re the serious buyer and the supplier will ask you questions like:

  • What type of plastic do you want to use? PP, ABS, AS
  • Quality of the plastic
  • Density of the product
  • Finish of the plastic
  • Color of the plastic
  • Mold costs and if there are more than 1 required
  • Labels that go with the organizer
  • Type of packaging
  • Any wrapping
  • Type of cardboard, thickness, quality
  • and the list goes on

Sure. Not everything is related to the BOM, but it can easily be integrated.

You may think you know it all as you go through it one by one with the manufacturer, but what about 1 year later when you have a make a change?

Or when another person in your company needs to take over the project?

Or what if you need to switch manufacturers?

A BOM means you don’t have to start from scratch and you know your exact requirements and cost.

If you were paying $0.20 for a certain GSM cardboard, and the new supplier is quoting $0.50, you can bet they are taking a cut.

You can now bring this up and ask what the difference is. You may choose to supply the cardboard to them if they keep insisting it is the best price they can offer you.

How to use this free BOM template

There are two main tabs.

  1. Parts List to host all the detailed information regarding raw materials, parts and all the nitty gritty details.
  2. The BOM template where you would construct the full list which you can then use for reference or save as a PDF.

1. Parts list of the BOM spreadsheet

In the tab titled “1.PartsList”, this is where you enter detailed information of each part. The more granular you get, the better in the end.

Add as many columns as needed like manufacturer, weight, size, shape, brand, supplier and more.

The spreadsheet is unlocked and you can customize it 100%.

bom template instructions parts list
BOM template instructions parts list

Most of the data will have to be entered manually. You probably already have a list that you maintain somewhere else. You can copy it into this sheet.

You can mix and match data from Amazon if you wish. If there is a specific data point like brand or dimension that you want to pull, use the matching  Gorilla function to get this information.

E.g. you can get product info like titles, weight, dimensions using GORILLA_PRODUCT(), category using GORILLA_CATEGORY() and also the pricing of the final product using GORILLA_MYPRICE().

A quick word on the naming convention.

You can give a part a “part number” or a “SKU”. They can be used interchangeably because each part should be unique.

The parts list is not the “product SKU” because parts can include raw materials and all the little components that make the final product.

Consider the parts list as the master data file. You have all the detailed data information and the BOM template will pull the matching details from this parts list into the final BOM list.

The concept of organizing this bill of materials is the same as the in-depth tutorial on creating the sales tracking spreadsheet or the Amazon inventory management spreadsheet.

2. Compiling the BOM list

bom compilation template

This is where the fun begins as you can see the fruit of your labor.

The first column is linked to the part number you entered into the parts list. A drop down is used so the information is quickly pulled using vlookups.

The middle body section where the cells are gray, means that it is using a formula (vlookup) to automatically load the information for you.

Quantity must be entered manually as every product will be different.

The total of the product cost is displayed at the bottom.

Now you can keep this tab as it is and make a copy of it to create a BOM for another product, or save as PDF and keep on file.

Download Bill of Materials template for Amazon sellers

Although my focus is on Amazon FBA, you can use this free BOM spreadsheet template for any business that involves making something.

Free Bill of Materials template spreadsheet to negotiate and track pricing 1

Other free spreadsheets and resources for Amazon sellers

What is Gorilla ROI?

Gorilla ROI automatically pulls Amazon data into Google sheets for you to make sense of the data without the inconvenience of manually downloading, sorting and updating spreadsheets.

Learn how you can centralize your data and use it to increase your ROI.

Leave a Reply

Your email address will not be published. Required fields are marked *