Hello Experts,
i need to plan my purchase requirements , we have a multilevel Bill of materials & few components are manufactured inhouse , few are from external vendors , our vendor base is also global..
few components are common for more than 1 finished goods..
some components are required to manufacture product as well as we directly sale those parts...
please note below multilevel bill of material data..
now we have a monthly requirement of finish goods from customer as below....
point we need to consider in material planning is.
customer requirement - (available stock @ plant + transit material)
suppose available stock @ plant is as below...
and transit material is as below
now in a part wise summary sheet or any other sheet i need below planning details....(part number should be unique , no duplication).
it would be great help if anyone can provide a solution as currently for this i need to do multiple calculations in multiple sheets..
i need to plan my purchase requirements , we have a multilevel Bill of materials & few components are manufactured inhouse , few are from external vendors , our vendor base is also global..
few components are common for more than 1 finished goods..
some components are required to manufacture product as well as we directly sale those parts...
please note below multilevel bill of material data..
| Finish Goods which we sale to customer (A) | Components required for producing FG (B) | Raw material required to produce B ( C ) | Part Number | UOM | Cat | Component Quantity/FG | Remarks | Procurement type |
| 1 | 6209900 | Pc | FG | 1 | inhouse | |||
| 1 | 906049970 | Pc | component | 1 | external vendor | |||
| 2 | 62035000 | Pc | component | 1 | inhouse | |||
| 2-1 | 3120090 | KG | Raw material required to produce Component | 82.547 | external vendor | |||
| 2 | 230115 | Pc | FG | 4 | ||||
| 1 | 62919000 | Pc | component | 1 | external vendor | |||
| 2 | 906839971 | Pc | component | 1 | external vendor | |||
| 3 | 907009972 | Pc | component | 1 | external vendor | |||
| 4 | 830115000 | Pc | component | 1 | inhouse | |||
| 4-1 | 9400003 | Pc | Raw material required to produce Component | 1 | external vendor | |||
| 3 | 230885 | Pc | FG | 1 | inhouse | |||
| 1 | 62009000 | Pc | component | 1 | external vendor | |||
| 2 | 6202000 | Pc | component | 1 | inhouse | |||
| 2-1 | 244600 | KG | Raw material required to produce Component | 51.48 | external vendor | |||
| 3 | 62049900 | Pc | component | 1 | these are trading parts which sale to customer directly as well as use as a component to produce another FG .. | external vendor | ||
| 4 | 830885000 | Pc | component | 1 | external vendor | |||
| 5 | 900109970 | Pc | component | 1 | external vendor | |||
| 4 | 232265 | Pc | FG | 1 | inhouse | |||
| 1 | 62569000 | Pc | component | 1 | external vendor | |||
| 2 | 62909000 | Pc | component | 1 | external vendor | |||
| 3 | 832265000 | Pc | component | 1 | inhouse | |||
| 3-1 | 3112153 | Pc | Raw material required to produce Component | 1 | external vendor | |||
| 4 | 906019971 | Pc | component | 1 | external vendor | |||
| 5 | 907019972 | Pc | component | 1 | external vendor | |||
| 5 | 62049900 | Pc | FG & Component | 1 | these are trading parts which sale to customer directly as well as use as a component to produce another FG .. | external vendor | ||
| 6 | 62059900 | Pc | FG & Component | 1 | external vendor | |||
| 7 | 620299 | Pc | FG | 1 | inhouse | |||
| 1 | 900109970 | Pc | component | 1 | external vendor | |||
| 2 | 6202000 | Pc | component | 1 | inhouse | |||
| 2-1 | 244600 | KG | Raw material required to produce Component | 82.547 | external vendor |
| Customer Reuirement | |
| FG Part Number | Customer Requirement |
| 6209900 | 2,00,000 |
| 230115 | 50,000 |
| 230885 | 20,000 |
| 232265 | 10,000 |
| 62049900 | 2,00,000 |
| 62059900 | 50,000 |
| 620299 | 3,00,000 |
customer requirement - (available stock @ plant + transit material)
suppose available stock @ plant is as below...
| Part Number | Qty |
| 6209900 | 155 |
| 906049970 | 1,136 |
| 62035000 | 1,117 |
| 3120090 | 1,947 |
| 230115 | 137 |
| 62919000 | 1,830 |
| 906839971 | 1,917 |
| 907009972 | 1,925 |
| 830115000 | 426 |
| 9400003 | 322 |
| 230885 | 228 |
| 62009000 | 1,486 |
| 6202000 | 1,365 |
| 244600 | 481 |
| 830885000 | 811 |
| 900109970 | 1,759 |
| 232265 | 1,474 |
| 62569000 | 1,193 |
| 62909000 | 492 |
| 832265000 | 314 |
| 3112153 | 779 |
| 906019971 | 1,444 |
| 907019972 | 562 |
| 62049900 | 1,755 |
| 62059900 | 998 |
| 620299 | 644 |
| Part Number | Qty |
| 906839971 | 1,000 |
| 907009972 | 1,000 |
| Part Number | Source | Total Customer Requirement | available stock @ plant + | transit material | Net Reuirement |
| 6209900 | inhouse | ||||
| 906049970 | external vendor | ||||
| 62035000 | inhouse | ||||
| 3120090 | external vendor | ||||
| 230115 | inhouse | ||||
| 62919000 | external vendor | ||||
| 906839971 | external vendor | ||||
| 907009972 | external vendor | ||||
| 830115000 | inhouse | ||||
| 9400003 | external vendor | ||||
| 230885 | inhouse | ||||
| 62009000 | external vendor | ||||
| 6202000 | inhouse | ||||
| 244600 | external vendor | ||||
| 830885000 | external vendor | ||||
| 900109970 | external vendor | ||||
| 232265 | inhouse | ||||
| 62569000 | external vendor | ||||
| 62909000 | external vendor | ||||
| 832265000 | inhouse | ||||
| 3112153 | external vendor | ||||
| 906019971 | external vendor | ||||
| 907019972 | external vendor | ||||
| 62049900 | external vendor | ||||
| 62059900 | external vendor | ||||
| 620299 | inhouse |
it would be great help if anyone can provide a solution as currently for this i need to do multiple calculations in multiple sheets..
