Multi level Bill of material

hello everybody
can anyone help me to create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e

if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
thanks in advence.

please see the attached file i tried to explain:
I have 2 sheets:
Formulas include 2 fixed formulas (white base; varnish)
Combined formulas: where I need to list the ingredients of a mix between white base formula and varnish formula and that depending on the % of each of them.
If I take 60% white and 40%& varnish, I need to list the ingredients with their percentages
60/40 may change than the ingredients % will change accordingly.

hello again

calculations are correct but is there a way to get also the ingredients list also? (yellow part must be filled automaticly if I select white ink from the drop down list)
because this may change from color to color
I added black color

i attached a new file

thanks

Originally Posted by Richard Buttrey

Hi,

I could have been more certain if you has included a specific example of the output required as I suggested in my first post. However see if the attached is what you want. If not attach the workbook again but this time with a worked example.

Regards

please see file attached

For standard Bill of Material schedules, you need 6 columns to calculate Material costs:
1) Item
2) Components that make up the material portions of item
3) Cost of each component
4) Rate of component that goes into the item
5) Scrap (if any)
6) Material sum after calculation (cost * rate / scrap)

E.g. A Sledge Hammer
Takes 0.2 pounds of wood
Takes 0.7 pounds of steel
Hammer 1:
1) Item
a. (Small Sledge Hammer)
2) Components that make up the material portions of item
a. (Wood)
b. (Steel)
3) Cost of each component
a. (Wood: $2.43 per pound)
b. (Steel: $6.33 per pound)
4) Rate of component that goes into the item
a. (0.2 lb of wood into each hammer)
b. (0.7 lb of steel into each hammer)
5) Scrap (if any)
a. 1.0%
b. 2.2%
6) Material sum after calculation:
2.43 * 0.2 / (1-.01) = 0.4909
6.33 * .7 / (1 - .022) = 4.5308

Total cost of hammer = $5.0217

To do subcomponents, let’s say you need Steel and Paint for the Steel portion of the Steel for the hammer.
First, determine that Steel is a sub component
Second, use SUMIF to add subcomponents
Then calculate like above (cost * rate / scrap)

See attached worksheet. Not sure if it'll help. But it's one way it's done. And this can be used for hundreds of products and thousands of components.

hello everybody,
this is a little complicated but i attached detailed file.
in brief:
if A=55%B+32%C+9%d+3%e+1%f
B=50%b+50%b'
C=70%c+30%c'

if i select A, i must see a complete list of raw materials (only percentages of b,b',c,c',d,e,f)

please look at the file on help if you can

thanks

I cannot follow your instructions

if A=55%B+32%C+9%d+3%e+1%f
B=50%b+50%b'
C=70%c+30%c'

Is this what you mean?

If
A = 55%
B = 32%
C = 9%d + 3%e + 1%f

B = 50%b + 50%b'
C = 70%c + 30%c'


Is this not the same as this Post? Or at least an extention of it?

bill of material (multi)

thanks all, sorry for the duplication,
you are right its a little confusion to do this kind of calculation, i have a software but the problem it doesnt do all the calculation, and i dont have the source to change it.
as for VBA, i know nothing in this language.
anyway i will look at the attached file
thanks again

hello
sorry for the delay i was traveling.
i added a subformula (into the formulas sheet) but it seems not working, i think you limited to row 19.
can u update the attached file and limit it to row 500.

i think that you mentioned that better to write codes if i have 3 or more sub product.
is it easy to update your file with codes?

thanks

hello DonkeyOte

i tried with a new thread (VBA section) but no one help.
can you add one more tier and make it as a form:
one form to add raw materials
one form to create formulas
and one form to view the formulas

thanks for your help

Hi,
how can I do if the products on A are not sorted by order...

For example you have a row with "Varnish 4347" between 2 rows of "Yellow 13".

I'm stuck on this and can't figure how to do.

Thanks for your help

Originally Posted by DonkeyOte

For ref. attached would be a three tier setup which also accounts for a bottom tier product being used multiple times by a mid tier product (eg NC Solution A-10).

after all that time i can find solution anyone can help even with vba

I'm a tad confused here Post #20 is dated 02-25-2010 today is 02-29-2012.
What have you done in the last two years.

Originally Posted by Marcol

I'm a tad confused here Post #20 is dated 02-25-2010 today is 02-29-2012.
What have you done in the last two years.

well i am still waiting, no help; can you help me please?

@ shg,

LOL And I thought a week or two at the very most, is long. But TWO YEARS?

@ shg,

Your sense of humour is cracking me up. Hope he comes right, 2 or 5 years, or ? . Maybe I should consider relocating to Dubai, the rat race over here is killing me.

Originally Posted by shg

Hey, he checked back after two years -- I only waited another five months.

well guys sorry i am always here but no one gave me an answear!; any news after 5 months?

Originally Posted by shg

Hey, he checked back after two years -- I only waited another five months.

i can see that no one can help, i am still waiting !, i am not using anything i am calculating manualy

@ Winon

I'm told there is no beer there, the thought of that frightens me , but maybe they have something else there?

Perhaps someone, once in a while, rubs the bottle he retires to?

hello guysss