sameershandilya
New Member
Hi there,
" WARNING: Long, but interesting Post
"
I am using an indented bill of material of a product to calculate inventory exposure at different lead time targets, by applying the backward scheduling logic.
Overview of certain terms:
> Indented BOM: A multi-level bill of materials (BOM), referred to as an indented BOM, is a bill of materials that lists the assemblies, components, and parts required to make a product in a parent-child, top-down method.
ex. Level 1 is a child of level 0, level 2 is child of level 1, and so on. Level 0 can have multiple level 1s, which could have their own multiple level 2s, and so on.
> Backward scheduling logic: This logic is used to provide due dates of individual components based on the due date of final assembly (Level 0).
If target completion of a product is X days, then plan start of level 0 will be X - Mfg lead time of Level 0 item. Similarly, plan start of all Level 1 items will be plan start of Level 0 - Respective lead times (buy or make) of level 1 items. Same logic would apply on subsequent levels following the parent-child structure.
Attached excel file has the example of what I am trying to build:
Cumulative lead time of this sample product is 102 days. So, if you enter 102 in cell E2 under Target, none of the plan starts in column F will be negative. On the other hand, if you enter 50 as target, you will see some items with negative plan start, highlighted in red. From business perspective, these are the items we need to hold in inventory, in order hit the target of 50 days.
Trying to achieve..
> You could see that in the file I am manually calculating the plan start by updating the formula to pull plan start of parent item for each item. For Level 0, plan start is Target - level 0's mfg lead time i.e. 50 - 2.09 = 47.91.
For level 1 items (row 1, 35, 41, 43, 45, 47 to 56), their plan starts are Plan start of Level 0 - their respective lead times (buy or make) i.e. F2 - H2 = 38.39 for row 3, and F2 - G50 = 27.91 for row 50.
Same logic is applied to level 2, and 3.
>> Is there way to write a VBA code that can apply this logic and highlight the items which have a negative plan start based on target days?
>>> Second, when calculating the total cost of negative start items --- if both parent and child items have negative starts, then I want the cost of parent item only, as it includes the cost of child.
For example in the attached file, I am not considering the cost of row 5 item (38441735), as it is a level 3 item which goes into level 2 item (12654066), which also has a negative start.
I have been working on this for few weeks now, there are certain products which have a 2000 row BOM, which goes upto 6 levels. Updating the formula for such a BOM takes a very long time.
Has anyone tackled indented BOMs in excel before? Can be done in vba code?
Please let me know if you have any other questions.
Thanks in advance!
Sameer
" WARNING: Long, but interesting Post
"I am using an indented bill of material of a product to calculate inventory exposure at different lead time targets, by applying the backward scheduling logic.
Overview of certain terms:
> Indented BOM: A multi-level bill of materials (BOM), referred to as an indented BOM, is a bill of materials that lists the assemblies, components, and parts required to make a product in a parent-child, top-down method.
ex. Level 1 is a child of level 0, level 2 is child of level 1, and so on. Level 0 can have multiple level 1s, which could have their own multiple level 2s, and so on.
> Backward scheduling logic: This logic is used to provide due dates of individual components based on the due date of final assembly (Level 0).
If target completion of a product is X days, then plan start of level 0 will be X - Mfg lead time of Level 0 item. Similarly, plan start of all Level 1 items will be plan start of Level 0 - Respective lead times (buy or make) of level 1 items. Same logic would apply on subsequent levels following the parent-child structure.
Attached excel file has the example of what I am trying to build:
Cumulative lead time of this sample product is 102 days. So, if you enter 102 in cell E2 under Target, none of the plan starts in column F will be negative. On the other hand, if you enter 50 as target, you will see some items with negative plan start, highlighted in red. From business perspective, these are the items we need to hold in inventory, in order hit the target of 50 days.
Trying to achieve..
> You could see that in the file I am manually calculating the plan start by updating the formula to pull plan start of parent item for each item. For Level 0, plan start is Target - level 0's mfg lead time i.e. 50 - 2.09 = 47.91.
For level 1 items (row 1, 35, 41, 43, 45, 47 to 56), their plan starts are Plan start of Level 0 - their respective lead times (buy or make) i.e. F2 - H2 = 38.39 for row 3, and F2 - G50 = 27.91 for row 50.
Same logic is applied to level 2, and 3.
>> Is there way to write a VBA code that can apply this logic and highlight the items which have a negative plan start based on target days?
>>> Second, when calculating the total cost of negative start items --- if both parent and child items have negative starts, then I want the cost of parent item only, as it includes the cost of child.
For example in the attached file, I am not considering the cost of row 5 item (38441735), as it is a level 3 item which goes into level 2 item (12654066), which also has a negative start.
I have been working on this for few weeks now, there are certain products which have a 2000 row BOM, which goes upto 6 levels. Updating the formula for such a BOM takes a very long time.
Has anyone tackled indented BOMs in excel before? Can be done in vba code?
Please let me know if you have any other questions.
Thanks in advance!
Sameer