Some idea exchange about a proper BOM workbook

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
Hi,

I am preparing a BOM project.

Sheet5: Raw Materials
Column A: Raw Material Code
Column B: Raw Material Name
Column C: Raw Material Unit (meter, kg, etc...)
Column D: Raw Material Cost

Sheet4: Semi-Products
Column A: Semi-Product Code
Column B: Semi-Product Name
Column C: Semi-Product Unit
Column D: Semi-Product Cost (Sheet3 is a support table where I relate Semi-Products with Raw Materials and calculate the cost. For example =SUMIF(Sheet3!A:A;Sheet4!B'value';Sheet3!D:D))

Sheet3: Semi-Product support table
Column A: Semi-Product Name (A validation list from Sheet4 Column B)
Column B: Raw Material Name (A validation list from Sheet5 Column B)
Column C: Raw Material Count (How much that raw material is needed.)
Column D: Raw Material Cost (A sub-total for that record row. For example =VLOOKUP(Sheet3!B'value';Sheet5!$B:$D;3;0)*Sheet3!C'value'))
*The formula in Sheet3 Column D now makes more sense.
*So, at this point you can connect more than one raw material to the same semi-product in different rows.


Sheet2: Products List
Column A: Product Code
Column B: Product Name
Column C: Product Unit
Column D: Product Cost (Same thing what we did in Sheet4. Sheet1 is a support sheet where I relate Products wşth sub-parts such as semi-produts or raw materials.)

Sheet1: Products support table
Column A: Product Name (A validation list from Sheet2 Column B)
Column B: Sub-Material Name (A validation list??????)
Column C: Sub-Material Count
Column D: Sub-Material Cost (As the same in Sheet3)

Well, things are starting to get complicated for me at this point... Since I can use both a semi-product or raw material in the same product, I need a validation list which can list both Sheet5 Column B and Sheet4 Column B continuously... But how Sheet1 Column D will know which sheet to lookup to calculate the cost.

Hmm........

I think I found my own answer at this point..... So maybe I can add one more column next to Sheet1 Column A which allows to select what kind of sub-material I want to add to the product. A semi-product or raw material? Hmm.. So I can write an INDIRECT function to the validation list which enables to shift between source sheets. Also I can try the same trick for Sheet1 Column D VLOOKUP formula.

- OK folks, what do you think? Am I on the right track? Is it an appropriate way to prepare a simple BOM?
- My final aim is to visualize the BOM tree with a pivot table where you can expand sub-materials under products in the same column. Also you can monitor the sub costs and total costs for each product. I am planning to use Sheet1 for this, right? I guess there will be no other column labels in pivot table. What do you think? That can I add more in pivot?

Thanks for everyone!
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,130,008
Messages
5,639,516
Members
417,093
Latest member
Citrusandsage

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top