venerdì 3 gennaio 2014

How to make a BOM in Excel, create a BOM with the EXCEL spreadsheet

In EXCEL you can create a bill of materials for the development of consumption of materials to use.

Below is how to make a BOM with EXCEL.

With the EXCEL spreadsheet, you can create a bill of materials, which calculates how many and what materials are needed to produce a given product, which also takes into account the difference in processing.
To make a BOM with Excel as follows:

1) Create a table like the one below, in the cells of blue put the net quantities of materials needed for the production of our product, in those white write the% difference in the use of various components.

2) In the brown boxes write a formula that multiplies the net quantity of the material, due to its rejection rate.

3) In the green cells are writing a formula that makes the sum of the net quantity plus the scrap. 

4) In the yellow cells instead, write a formula that multiplies the contents of the green cells for the quantity of kilograms to be produced in the cell indicated above, in this case 230 kg. To make the formula correctly but can be copied down we must fix this reference by clicking the F4 key on the keyboard which places the reference to $ C6, locking it.

5) Once you have copied all the formulas down, we count the explosion in the yellow cells also including waste, which tells us how many and what materials are needed to produce 230 kg of finished product covered by the bill.

6) If we change the amount of kg to produce, instantly we have a new counting with the new calculated quantities required by our BOM.

As one can easily deduce, in this way we can create various bills of materials for all materials that interest us, and obtain the quantities of ingredients required to produce it, including the percentage of processing waste.
Clearly we can freely change the parameters or the net quantity of waste to make all the necessary updates to our bill.