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