+ Reply to Thread
Results 1 to 37 of 37

Multi level Bill of material

  1. #1
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    bill of material (multi)

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,378

    Re: bill of material (multi)

    Hi,

    It's certainly not clear to me what you're trying to achieve from your data. You need to specify which is the data you want to use, give several examples of what results you want and most importantly say what logic you are using in order to get the results.

    Do this and I'm sure we can help further.

    Rgds
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: bill of material (multi)

    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.
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,378

    Re: bill of material (multi)

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-18-2009
    Location
    Cincinnati
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: bill of material (multi)

    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

  6. #6
    Registered User
    Join Date
    08-18-2009
    Location
    Cincinnati
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: bill of material (multi)

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: bill of material (multi)

    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
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: bill of material (multi)

    Quote Originally Posted by Richard Buttrey View Post
    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Multi level Bill of material

    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
    Attached Files Attached Files

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Multi level Bill of material

    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)

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    Having looked at this I couldn't think of a really elegant way of doing this using formulae - no doubt a route exists... however, I wonder if the attached is of interest ?

    In the attached... I have added Cols D & E to hold basic Counts... and added a lower table A21:D41 to find the sub items and calc % apportionments ... results are F & G
    (your results left for comparative purposes)

    I would say that generally speaking VBA is far better suited to these kind of recursive tasks than native functions.

    FILES RELOADED AT 13:25 UK Time (error in prior .xls version)
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-11-2010 at 09:25 AM.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    Also ... just as a heads up...

    Avoid using SUMPRODUCT with entire column references... eg:

    Please Login or Register  to view this content.

    Where Products is defined as:

    Please Login or Register  to view this content.

    meaning:

    Please Login or Register  to view this content.

    returns 1084576 ... we're processing 1m+ rows in the SUMPRODUCT

    This is not a good idea.... it's always a good idea to keep the ranges as lean as possible.
    (pre XL2007 the above would return #NUM! error)

    We can revise the Products Named Range so that it refers only to those rows necessary by using:

    Please Login or Register  to view this content.

    now

    Please Login or Register  to view this content.

    returns 19

    so the SUMPRODUCT will now be far quicker to calculate...

    (as per our earlier discussions you may need to adjust the above formula per your own regional settings - assuming they're not UK/US)

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    Quote Originally Posted by Marcol View Post
    Is this not the same as this Post? "bill of material (multi)"

    Or at least an extention of it?
    Marcol, thanks for the pick up - I missed this.

    neorez, do not post duplicates - you will quickly alienate those who may assist you if you do.
    If you feel your struggling on the original - restate the question - provide clearer examples.

    I have on this occasion merged the threads such that all responses are included.

  14. #14
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    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

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    whether or not you need VBA will all really come down to how many "levels" of product you have... in your example you have only 2, ie

    Product -> Sub-Products
    (in some cases no Sub Products exist)

    However, if those Sub-Products themselves had Sub-Products then obviously it gets increasingly complex function wise to work backwards.

    In the example posted I simply tried to establish a list of products for which no "sub products" existed and used those items as the basis for the final output (per your requirements).

    The added complexity of your example is the requirement that no product should be listed more than once in the final table
    (ie all instances should be consolidated should different parent products be using the same sub product)

    What my example didn't do (I realised belatedly) is list in order of appearance based on parent product which I think was your preference.. how big an issue that is for you I don't know (?)

    So the above working with a 2-level hierarchy is clearly possible... if you were to add a further two or even one additional layer into proceedings and wanted to keep the use of helpers to a minimum I would at that point say that you should stop trying to use native functions altogether and revert to code.

    I hope the above makes sense ?

  16. #16
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    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.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    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

  18. #18
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    Yes, I would say that an introduction of a third tier would warrant VBA.

    You could certainly add another table to the current file to calculate the same using formulae but it will become increasingly complex.

    In essence with each new "tier" you add using the present setup you require another sub table and I would say that's neither practical nor flexible.

    If you opt to pursue a VBA approach please create a new thread in the Programming Forum and provide a link to this thread in your initial post. Be specific that you're looking for a VBA based solution.

  19. #19
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi level Bill of material

    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).
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    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

  21. #21
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

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

  22. #22
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Multi level Bill of material

    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.

  23. #23
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    Quote Originally Posted by Marcol View Post
    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?

  24. #24
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: bill of material (multi)

    neorez, you stll around?
    Entia non sunt multiplicanda sine necessitate

  25. #25
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: bill of material (multi)

    @ shg,

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

  26. #26
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: bill of material (multi)

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

  27. #27
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: bill of material (multi)

    @ 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.

  28. #28
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Multi level Bill of material

    @ 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?

  29. #29
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: bill of material (multi)

    Quote Originally Posted by shg View Post
    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?

  30. #30
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Multi level Bill of material

    I'll ask again. What have you done since this thread was started 2 years 10 months and 3 days ago?

    Do you have some date number thing? Joined 6-7-8, 4y 5m 6d ago and posted on 12-12-12 ...
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  31. #31
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: bill of material (multi)

    Quote Originally Posted by shg View Post
    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

  32. #32
    Registered User
    Join Date
    11-05-2011
    Location
    Nashville, Tn
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Multi level Bill of material

    Hi,

    I know this is an old thread however multiple level BOM calculations are quite easy with a simple formula. I have used a pivot table to for summary. My companies BOM is over 30 levels.
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    thanks, but this will not help:
    A=aa1+aa2+aa3
    B=bb1+A+bb2
    C=cc1+A+B+aa3+bb1
    D=A+B+C+aa1

    when i select A or B or C or D, i should get the raw figures aa,bb,cc
    please see attached.
    Multi-Level BOM Calculation.xlsx
    Last edited by neorez; 02-18-2013 at 04:31 AM.

  34. #34
    Registered User
    Join Date
    07-06-2008
    Location
    dubai
    Posts
    38

    Re: Multi level Bill of material

    hello guysss

  35. #35
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,378

    Re: Multi level Bill of material

    Hi,

    It's not at all clear (at least to me) what it is you're trying to achieve. In any case this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

  36. #36
    Registered User
    Join Date
    08-20-2014
    Location
    Ho Chi Minh
    MS-Off Ver
    2007
    Posts
    3

    Re: Multi level Bill of material

    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
    Quote Originally Posted by DonkeyOte View Post
    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).

  37. #37
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Multi level Bill of material

    lulums, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1