No.

Sheet Name Column Name

1 BOM & COGS RATIO Level

Part Code
Part Name
Parent Code

Quantity

Unit
Supplier
Origin
Product Item Code
COGS Ratio (%)

COGS Value (USD)

2 SUPPLIER PROFILE Part Code
Part Name
Supplier
Origin
Supply Ratio (%)

Minimum Order Quantity (pieces)

Production Lead Time (days)

Shipping Time Origin Countries ->

Phu Tho (days)
Unit Price (USD/piece)
3 FACTORY INFORMATION Line
Part Code
Part Name
Cycle Time (second/piece)
Monthly Capacity (piece)
Overall Equipment Effectiveness (%)

Utilization (%)

Production Waste Rate (%)

 Explanation
The hierarchy level of each part in the Bill of Materials (BOM). Level 1 represents
components’ level and level 2 represents raw materials’ level in one front brake
part.
Code of each component or raw material in the BOM.
Name of each component or raw material.
Code of the parent assembly to which the part belongs. Used to establish parent–
child relationships in the BOM.
Number of units of the component/raw materials required in the parent assembly.

Measurement unit of the quantity.

Code of each component or raw material in the BOM.
Name of each component or raw material.
Internal product code linking the component/raw material to the finished part.
Percentage of the component/raw material’s cost in the total Cost of Goods Sold
(COGS) of the front brake part.
Absolute Cost of Goods Sold (COGS) of the component/raw material in USD.
Code of each component or raw material in the BOM.
Name of each component or raw material.
Code of each component or raw material in the BOM.
Name of each component or raw material.
Proportion of supply volume covered by each supplier compared to total
procurement of that material.
Minimum order size required by the supplier for each purchase.
Number of days needed by the supplier to produce and prepare the material for
shipping.
Shipping time in days from the supplier’s origin country to the Phu Tho factory.

Purchase price of one unit of material in USD.

Production line in the factory.
Code of each component or raw material in the BOM.
Name of each component or raw material.
Average time required to produce one piece, measured in seconds.
Maximum number of units a line can produce in one month.
Overall efficiency of the production line in percentage.

Percentage of the production capacity actually utilized compared to the maximum

capacity.
Percentage of input material wasted during the production of the part.
Level Part Code Part Name Parent Code Quantity Unit
1 FB-001 Hose Caliper Front LH 1 pcs
1 FB-002 Hose Caliper Front RH 1 pcs
1 FB-003 Seal - Brass Hydraulic 4 pcs
1 FB-004 Retain Clip Hose Frame Attach 2 pcs
1 FB-005 Stainless Brake Lines 2 pcs
1 FB-006 Caliper Disc Front LH 1 pcs
1 FB-007 Caliper Disc Front RH 1 pcs
1 FB-008 Rotor Disc Brake 2 pcs
1 FB-009 Bearing Inner Wheel 2 pcs
1 FB-010 Bearing Outer Wheel 2 pcs
1 FB-011 Washer Retainer Outer Bearing 2 pcs
1 FB-012 Cotter Key 2 pcs
1 FB-013 Seal Grease 2 pcs
1 FB-014 Cap Dust 2 pcs
1 FB-015 Pads - Disc Kit 1 set
1 FB-016 Tube Hyd Front Distribution 1 pcs
2 RM-SS-TUBE-316L Stainless Steel FB-001 1 pcs

2 RM-RUBBER-EPDM/NBR Synthetic Rubber FB-001 1 pcs

2 RM-SS-TUBE-316L Stainless Steel FB-002 1 pcs

2 RM-RUBBER-EPDM/NBR Synthetic Rubber FB-002 1 pcs

2 RM-BRASS Brass FB-003 1 pcs
2 RM-RUBBER-EPDM/NBR Synthetic Rubber FB-003 1 pcs
2 RM-STEEL-ALLOY Steel Alloy FB-004 1 pcs

2 RM-SS-TUBE-316L Stainless Steel FB-005 1 pcs

2 RM-STEEL-ALLOY Steel Alloy FB-006 1 pcs

2 RM-STEEL-ALLOY Steel Alloy FB-007 1 pcs

2 RM-STEEL-ALLOY Steel Alloy FB-008 1 pcs

2 RM-BEARING-STEEL Bearing Steel FB-009 1 pcs

2 RM-BEARING-STEEL Bearing Steel FB-010 1 pcs
2 RM-STEEL-ALLOY Steel Alloy FB-011 1 pcs

2 RM-STEEL-ALLOY Steel Alloy FB-012 1 pcs

2 RM-GREASE High Temp Grease FB-013 1 pcs

2 RM-RUBBER-EPDM/NBR Synthetic Rubber FB-013 1 pcs
2 RM-PLASTIC-POM Engineering Plastic FB-014 1 pcs
2 RM-RUBBER-EPDM/NBR Synthetic Rubber FB-014 1 pcs
2 RM-FRICTION-MAT Friction Material FB-015 1 pcs
2 RM-STEEL-ALLOY Steel Alloy FB-015 1 pcs

2 RM-SS-TUBE-316L Stainless Steel FB-016 1 pcs

 Supplier Origin Product Item Code COGS Ratio (%) COGS Value (USD)
FB_SET_001 4.28% 12
FB_SET_001 4.28% 12
FB_SET_001 2.14% 6
FB_SET_001 0.36% 1
FB_SET_001 5.71% 16
FB_SET_001 17.83% 50
FB_SET_001 17.83% 50
FB_SET_001 17.83% 50
FB_SET_001 7.13% 20
FB_SET_001 7.13% 20
FB_SET_001 0.36% 1
FB_SET_001 0.14% 0.4
FB_SET_001 1.43% 4
FB_SET_001 0.71% 2
FB_SET_001 10.70% 30
FB_SET_001 2.14% 6
SteelCorp Ltd. Japan FB_SET_001 39.61% 4.7532
Nihon Metal Works Japan
GlobalSteel Industries South Korea
PolyRubber Inc. Vietnam FB_SET_001 60.39% 7.2468
SteelCorp Ltd. Japan FB_SET_001 39.61% 4.7532
Nihon Metal Works Japan
GlobalSteel Industries South Korea
PolyRubber Inc. Vietnam FB_SET_001 60.39% 7.2468
MetalWorks Co. Vietnam FB_SET_001 64.31% 3.8586
PolyRubber Inc. Vietnam FB_SET_001 35.69% 2.1414
SteelCorp Ltd. Japan FB_SET_001 100% 1
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 16
Nihon Metal Works Japan
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 50
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 50
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 50
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 20
SteelCorp Ltd. Japan FB_SET_001 100% 20
SteelCorp Ltd. Japan FB_SET_001 100% 1
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 0.4
GlobalSteel Industries South Korea
ChemLube Ltd. Vietnam FB_SET_001 69.54% 2.7816
PolyRubber Inc. Vietnam FB_SET_001 30.46% 1.2184
PolyRubber Inc. Vietnam FB_SET_001 59.41% 1.1882
PolyRubber Inc. Vietnam FB_SET_001 40.59% 0.8118
FrictionTech Co. Japan FB_SET_001 69.82% 20.946
SteelCorp Ltd. Japan FB_SET_001 30.18% 9.054
GlobalSteel Industries South Korea
SteelCorp Ltd. Japan FB_SET_001 100% 6
Nihon Metal Works Japan
GlobalSteel Industries South Korea
 Part Code Part Name Supplier Origin Supply Ratio (%) Minimum Order Quantity (pieces)
RM-SS-TUBE-316L Stainless Steel SteelCorp Ltd. Japan 40% 20000
Nihon Metal Works Japan 30% 15000
GlobalSteel Industries South Korea 30% 25000
RM-RUBBER-EPDM/NBR Synthetic Rubber PolyRubber Inc. Vietnam 100% 30000
RM-BRASS Brass MetalWorks Co. Vietnam 100% 10000
RM-STEEL-ALLOY Steel Alloy SteelCorp Ltd. Japan 60% 20000
GlobalSteel Industries South Korea 40% 25000
RM-BEARING-STEEL Bearing Steel SteelCorp Ltd. Japan 100% 20000
RM-GREASE High Temp Grease ChemLube Ltd. Vietnam 100% 10000
RM-PLASTIC-POM Engineering Plastic PolyRubber Inc. Vietnam 100% 20000
RM-FRICTION-MAT Friction Material FrictionTech Co. Japan 100% 15000

1567
1617
1671
1728
1792
1856
1923
1993
2067
2142
2221
2303
Production Lead Time (days) Shipping Time Origin Countries -> Phu Tho (days) Unit Price (USD/piece) PRICE AFTER
40 12 4.20 4.452
35 12 4.10
45 14 4.15
20 3 2.90
18 3 3.60
40 12 8.50 8.925
45 14 8.40
42 12 9.5
22 4 2.5
18 3 1.1
40 15 19.5

Month Sedan_001 SUV_001 Hatchback_001

Jan 940 392 235
Feb 970 404 243
Mar 1002 418 251
Apr 1037 432 259
May 1075 448 269
Jun 1114 464 278
Jul 1154 481 288
Aug 1196 498 299
Sep 1240 517 310
Oct 1285 536 321
Nov 1333 555 333
Dec 1382 576 345
Total lead time
52
47
59
23
21
52
59
54
26
21
55

1567
1617
1671
1728
1792
1856
1923
1993
2067
2142
2221
2303
 Line Part Code Part Name Cycle Time (second/piece)
L0 FB_SET_001 Front Brake Assembly 90
L1 FB-008 Rotor Disc Brake 90
L2 FB-001 Hose Caliper Front LH 60
FB-002 Hose Caliper Front RH 60
L3 FB-005 Stainless Brake Lines 45
L4 FB-016 Tube Hyd Front Distribution 40
L5 FB-006 Caliper Disc Front LH 80
FB-007 Caliper Disc Front RH 80
L6 FB-009 Bearing Inner Wheel 50
L7 FB-010 Bearing Outer Wheel 50
L8 FB-003 Seal - Brass Hydraulic 35
L9 FB-004 Retain Clip Hose Frame Attach 20
L10 FB-014 Cap Dust 25
L11 FB-015 Pads - Disc Kit 70
L12 FB-011 Washer Retainer Outer Bearing 20
L13 FB-012 Cotter Key 15
L14 FB-013 Seal Grease 30
Note: Each line runs for 24 hours.
Monthly Capacity (piece) Overall Equipment Effectiveness (%) Utilization (%)
24000 85% 85%
24000 85% 85%
30000 85% 87%

34000 85% 88%

45000 86% 87%
30000 90% 86%

35000 87% 85%

35000 87% 86%
57000 88% 90%
100000 85% 90%
84000 91% 90%
33000 90% 85%
98000 90% 88%
140000 86% 90%
60000 85% 86%
Production Waste Rate (%) Real Capacity
2.5% 17340 5780 600
2.5% 17340 5780 600
3.0% 22185 7395 900
3.5% 22185 7395 0
2.0% 25432 8477 680
2.5% 33669 11223 1125
2.4% 23220 7740 720
2.6% 23220 7740 0
3.1% 25883 8628 1085
4.5% 26187 8729 1575
4.0% 45144 15048 2280
2.0% 76500 25500 2000
2.4% 68796 22932 2016
3.0% 25245 8415 990
2.7% 77616 25872 2646
2.5% 108360 36120 3500
2.5% 43860 14620 1500
 Part Code Unit Price (USD/piece) PRICE AFTER Supplier Demand
RM-SS-TUBE-316L 4.20 4.452 SteelCorp Ltd.
Nihon Metal Works
4.10 3%
4.15 GlobalSteel Industries 3%
RM-RUBBER-EPDM/NBR 2.90 PolyRubber Inc. 3%
RM-BRASS 3.60 MetalWorks Co. 4%
RM-STEEL-ALLOY 8.50 8.925 SteelCorp Ltd. 4%
8.40 GlobalSteel Industries 4%
RM-BEARING-STEEL 9.5 SteelCorp Ltd. 4%
RM-GREASE 2.5 ChemLube Ltd. 4%
RM-PLASTIC-POM 1.1 PolyRubber Inc. 4%
RM-FRICTION-MAT 19.5 FrictionTech Co. 4%
4%
 Forecast 1 2 3 4 5
Jan 1567 1567 1567 6268 3134 3134

Feb 1617 1617 1617 6468 3234 3234

Mar 1671 1671 1671 6684 3342 3342
Apr 1728 1728 1728 6912 3456 3456
May 1792 1792 1792 7168 3584 3584
Jun 1856 1856 1856 7424 3712 3712
Jul 1923 1923 1923 7692 3846 3846
Aug 1993 1993 1993 7972 3986 3986
Sep 2067 2067 2067 8268 4134 4134
Oct 2142 2142 2142 8568 4284 4284
Nov 2221 2221 2221 8884 4442 4442
Dec 2303 2303 2303 9212 4606 4606

Forecast SS SR Brass SA Bearing Grease

Jan 7835 15670 6268 17237 6268 3134
Feb 8085 16170 6468 17787 6468 3234
Mar 8355 16710 6684 18381 6684 3342
Apr 8640 17280 6912 19008 6912 3456
May 8960 17920 7168 19712 7168 3584
Jun 9280 18560 7424 20416 7424 3712
Jul 9615 19230 7692 21153 7692 3846
Aug 9965 19930 7972 21923 7972 3986
Sep 10335 20670 8268 22737 8268 4134
Oct 10710 21420 8568 23562 8568 4284
Nov 11105 22210 8884 24431 8884 4442
Dec 11515 23030 9212 25333 9212 4606

Cost order 42 Inventory 25%

LUC SS SR Brass SA Bearing Grease

Jan 1.0553605615826 0.72768028079132 0.900001069 2.1274366189 2.381700702 0.638401404
Feb 1.0526381909548 0.72631909547739 0.900001036 2.1261991777 2.3782977387 0.6315954774
Mar 1.0517301750772 0.72586508753862 0.9000010025 2.1257864432 2.3771627188 0.6293254377
Apr 1.0512760139754 0.72563800698770 0.9000009694 2.1255800064 2.3765950175 0.6281900349
May 1.0510029850746 0.72550149253731 0.9000009348 2.1254559023 2.3762537313 0.6275074627
Jun 1.0508210341120 0.72541051705601 0.9000009026 2.1253731973 2.3760262926 0.6270525853
Jul 1.0506911304920 0.72534556524601 0.9000008711 2.1253141502 2.3758639131 0.6267278262
Aug 1.0505937654626 0.72529688273132 0.9000008405 2.1252698934 2.3757422068 0.6264844137
Sep 1.0505180708030 0.72525903540150 0.9000008104 2.1252354867 2.3756475885 0.626295177
Oct 1.0504576160384 0.72522880801918 0.9000007821 2.1252080073 2.37557202 0.6261440401
Nov 1.0504082227730 0.72520411138650 0.9000007542 2.1251855558 2.3755102785 0.6260205569
Dec 1.0503671328671 0.72518356643357 0.9000007274 2.1251668786 2.3754589161 0.6259178322
 6 7 8 9 10 11 12 13 14 15 16
1567 1567 3134 3134 3134 3134 3134 3134 3134 1567 1567

1617 1617 3234 3234 3234 3234 3234 3234 3234 1617 1617
1671 1671 3342 3342 3342 3342 3342 3342 3342 1671 1671
1728 1728 3456 3456 3456 3456 3456 3456 3456 1728 1728
1792 1792 3584 3584 3584 3584 3584 3584 3584 1792 1792
1856 1856 3712 3712 3712 3712 3712 3712 3712 1856 1856
1923 1923 3846 3846 3846 3846 3846 3846 3846 1923 1923
1993 1993 3986 3986 3986 3986 3986 3986 3986 1993 1993
2067 2067 4134 4134 4134 4134 4134 4134 4134 2067 2067
2142 2142 4284 4284 4284 4284 4284 4284 4284 2142 2142
2221 2221 4442 4442 4442 4442 4442 4442 4442 2221 2221
2303 2303 4606 4606 4606 4606 4606 4606 4606 2303 2303

Plastic Friction Accumulative Quantity SS SR Brass SA

3134 1567 Jan 7835 15670 6268 17237
3234 1617 Feb 15920 31840 12736 35024
3342 1671 Mar 24275 48550 19420 53405
3456 1728 Apr 32915 65830 26332 72413
3584 1792 May 41875 83750 33500 92125
3712 1856 Jun 51155 102310 40924 112541
3846 1923 Jul 60770 121540 48616 133694
3986 1993 Aug 70735 141470 56588 155617
4134 2067 Sep 81070 162140 64856 178354
4284 2142 Oct 91780 183560 73424 201916
4442 2221 Nov 102885 205770 82308 226347
4606 2303 Dec 114400 228800 91520 251680

Plastic Friction
0.288401404 4.9018028079
0.2815954774 4.8881909548
0.2793254377 4.8836508754
0.2781900349 4.8813800699
0.2775074627 4.8800149254
0.2770525853 4.8791051706
0.2767278262 4.8784556525
0.2764844137 4.8779688273
0.276295177 4.877590354
0.2761440401 4.8772880802
0.2760205569 4.8770411139
0.2759178322 4.8768356643
 Necess 1 2 3 4 5 6 7 8
Jan 2169 2119 2119 7915 4097 4190 2025 4050 4338

Feb 2239 2187 2187 8167 4228 4324 2090 4180 4477
Mar 2313 2260 2260 8440 4369 4468 2159 4318 4626
Apr 2392 2337 2337 8728 4518 4621 2233 4466 4784
May 2481 2424 2424 9051 4685 4792 2316 4632 4961
Jun 2569 2510 2510 9374 4853 4963 2398 4796 5138
Jul 2662 2601 2601 9713 5028 5142 2485 4970 5324
Aug 2759 2696 2696 10066 5211 5329 2575 5150 5517
Sep 2861 2796 2796 10440 5404 5527 2671 5342 5722
Oct 2965 2897 2897 10819 5600 5728 2768 5536 5930
Nov 3075 3004 3004 11218 5807 5939 2870 5740 6149
Dec 3188 3115 3115 11632 6021 6158 2976 5952 6376

Bearing Grease Plastic Friction

6268 3134 3134 1567 Line
12736 6368 6368 3184 L0
19420 9710 9710 4855 L1
26332 13166 13166 6583 L2
33500 16750 16750 8375
40924 20462 20462 10231 L3
48616 24308 24308 12154 L4
56588 28294 28294 14147 L5
64856 32428 32428 16214
73424 36712 36712 18356 L6
82308 41154 41154 20577 L7
91520 45760 45760 22880 L8
L9
L10
L11

L12
L13
L14
 9 10 11 12 13 14 15 16
4238 4189 3958 4050 4288 3827 2049 2095

4374 4323 4084 4179 4425 3949 2114 2162

4520 4467 4220 4318 4572 4081 2185 2234
4674 4620 4364 4466 4728 4220 2259 2310
4847 4791 4526 4631 4903 4377 2343 2396
5020 4962 4687 4796 5078 4533 2427 2481
5201 5141 4857 4969 5262 4696 2514 2571
5391 5328 5033 5150 5453 4867 2606 2664
5591 5526 5220 5342 5656 5048 2702 2763
5794 5726 5410 5535 5861 5231 2800 2863
6007 5937 5609 5740 6077 5424 2904 2969
6229 6157 5816 5951 6301 5624 3011 3079

Part Code PartCycle

Name Time (second/piece)
Monthly
Overall
Capacity
Equipment
(piece)Effectiveness
Utilization
Production
(%) Waste Rate (%)
FB_SET_00Front Brak 90 24000 85% 85% 2.5%
FB-008 Rotor Disc 90 24000 85% 85% 2.5%
FB-001 Hose Calipe 60 30000 85% 87% 3.0%
FB-002 Hose Calipe 60 3.5%
FB-005 Stainless B 45 34000 85% 88% 2.0%
FB-016 Tube Hyd Fr 40 45000 86% 87% 2.5%
FB-006 Caliper Dis 80 30000 90% 86% 2.4%
FB-007 Caliper Dis 80 2.6%
FB-009 Bearing In 50 35000 87% 85% 3.1%
FB-010 Bearing Ou 50 35000 87% 86% 4.5%
FB-003 Seal - Brass 35 57000 88% 90% 4.0%
FB-004 Retain Clip 20 100000 85% 90% 2.0%
FB-014 Cap Dust 25 84000 91% 90% 2.4%
FB-015 Pads - Disc 70 33000 90% 85% 3.0%

FB-011 Washer Ret 20 98000 90% 88% 2.7%

FB-012 Cotter Key 15 140000 86% 90% 2.5%
FB-013 Seal Grease 30 60000 85% 86% 2.5%
Bearing Steel
Orginal number of Number of units necessary
Month units necessary after improvement Price ($/pcs)
Jan 0 0 9.5
Feb 0 0 9.5
Mar 0 0 9.5
Apr 0 0 9.5
May 0 0 9.5
Jun 0 0 9.5
Jul 10646 10561 9.5
Aug 11034 10946 9.5
Sep 11444 11352 9.5
Oct 11859 11764 9.5
Nov 12296 12198 9.5
Dec 12750 12648 9.5
Total 70029 69469
6268 FB_002
12736 2168.8581315
19420 2238.0622837
26332 2312.8027682
33500 2391.6955017
40924 2480.2768166
48616 2568.8581315
56588 2661.5916955
64856 2758.4775087
73424 2860.899654
82308 2964.7058824
91520 3074.0484429
3187.5432526

Orginal number of Number of units necessary

Month units necessary after improvement Price ($/pcs)
Jan 8675 8606 9.5
Feb 8952 8881 9.5
Mar 9251 9177 9.5
Apr 9567 9490 9.5
May 9921 9842 9.5
Jun 10275 10193 9.5
Jul 10646 10561 9.5
Aug 11034 10946 9.5
Sep 11444 11352 9.5
Oct 11859 11764 9.5
Nov 12296 12198 9.5
Dec 12750 12648 9.5
Cost saving ($) Units saving (pcs)
0 0
0 0
0 0
0 0
0 0
0 0
809.12 85.17
838.58 88.27
869.71 91.55
901.27 94.87
934.51 98.37
969.01 102.00
5322.21 560
FB_003 FB_008 FB_009 FB_010
8675.43252595 4337.7162629758 4337.716 4337.716
8952.24913495 4476.124567474 4476.125 4476.125
9251.21107266 4625.6055363322 4625.606 4625.606
9566.78200692 4783.3910034602 4783.391 4783.391
9921.10726644 4960.553633218 4960.554 4960.554
10275.432526 5137.7162629758 5137.716 5137.716
10646.366782 5323.1833910035 5323.183 5323.183
11033.9100346 5516.955017301 5516.955 5516.955
11443.5986159 5721.7993079585 5721.799 5721.799
11858.8235294 5929.4117647059 5929.412 5929.412
12296.1937716 6148.0968858131 6148.097 6148.097
12750.1730104 6375.0865051903 6375.087 6375.087

Cost saving ($) Units saving (pcs)

659.33 69
680.37 71
703.09 74
727.08 77
754.00 79
780.93 82
809.12 85
838.58 88
869.71 92
901.27 95
934.51 98
8658.00 910