Bill of Material

[tommy1234]

Hello
My boss asked me to do pricing calculation for huge bill of material (i attached an example).
the real BOM has 7 levels of hierarchy. in the example i used only 5 levels
I have to create sum of prices for each level / sub-assembly , level 4 has level 5 as its child, level 3 has level 4 & level 5 as its child.
after that i have to create a percentage column in which each child / sub-assembly will get its relative percentage from the top assembly (called "BBB").
the problem is that i have to overcome duplicate values and the sum of the percentage should be 100% (level 2 include 2 sub-assemblies 40% and 60%)
Please help !!!

Thank you

[ashleyuk1984]

I can't get my head around what is actually going on with your spreadsheet... "level 4 has level 5 as its child, level 3 has level 4 & level 5 as its child". This might make perfect sense to you (because this is your line of work), but this makes no sense to me.

Are you able to either explain it better, or create a better example spreadsheet (with an example sum if possible).

[SamT]

You have to work the process from bottom to top first, using each cost of each of the lowest levels to find the actual cost of the final Units.

Then find the percentage of cost of each second level sub-assembly compared to the cost of a top level item. You will notice that there are no duplicate numbers.

The colored cells on this example are formulas. Study them and you will see why it is so complicated get totals of 100% on all columns. Any Level of %'s that has no children must be included in the all child level % columns in order for those columns to total 100%.

But what about the 8 can release mechanisms. do you use the % that all 8 cost, which will give a total 100%, or just the % of 1 mechanism, or do you use 8 lines, one for each mechanism?