Question

The demand for subassembly S is 80 units in week 7. Each unit of S requires 2 units of T and 1 unit of U. Each unit of T requires 2 units of V, 1 unit of W, and 1 unit of X. Finally, each unit of U requires 2 units of Y and 2 units of Z. One firm manufactures all items. It takes 2 weeks to make S, 1 week to make T, 2 weeks to make U, 2 weeks to make V, 3 weeks to make W, 1 week to make X, 2 weeks to make Y, and 1 week to make Z. Click the icon to view the product structure and the time-phased product structure. Click the icon to view the on-hand inventory Suppose that in addition to 80 units of S, there is also a demand for 20 units of U, which is a component of S. The 20 units of U are needed for maintenance purposes. These units are needed in week 6. Additionally, a new order has been received for 80 units of S due in week 8. Modify the net material requirements plan to reflect these changes. Construct a new net material requirements plan (enter your responses as whole numbers). Item Week 4 5 Lead Time (weeks) 1 2 3 6 7 8 S Gross req On hand Net req Order receipt Order release Gross req On hand Net req Order receipt Order release Gross req On hand Net req Order receipt Order release Gross req On hand Net req Order receipt Order relance V

Answer 1

Below is the screenshot of the MRP schedule -

mos - Excel ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - * Time Reference Trigº Functions Manager Function Library fx ? A .l le - Tell me what you want to do Define Name - Trace Precedents Show Formulas f Use in Formula - Trace Dependents ! Error Checking Create from Selection Remove Arrows - Evaluate Formula Defined Names Formula Auditing Watch Window A1 0 F G - On Hand 1 2 0 7 80 S IT? Q=L4L 20 20 20 0 Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 20 0 20 0 20 0 20 1 0 0 800 60 60 0 80 80 0 0 0 60 6-1-1-111-61 0 0 0 120 1800 20 0 20 0 202020 0 Gross Requirement Scheduled Receipts Projected on Hand T Net Requirements Planned Order Receipts Planned Order Releases LT1 Q=L4L 0 0 100 1800 100 180 0 18000 00100 celelel-lee leela--- lelolel-lee leelel--8| 0 80-818lolololololol lolololololol lololololo 000 60 800 11 30 30 0 Gross Requirement Scheduled Receipts Projected on Hand L Net Requirements Planned Order Receipts Planned Order Releases Q = L4L 30 30 30 000 00030800 30 800 0 30 0 0 113| 6013314 000 000 0 200 3600 20 LT2 Q = L4L Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 20 0 0 2020 000 00180 36000 00 180 36000 180 360 0 0 0 0 1 0 0 0

mos - Excel ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - Time - Reference Trigº Functions Manager Function Library fx F ? A .l le - Tell me what you want to do Define Name - Trace Precedents Show Formulas f Use in Formula - Trace Dependents ! Error Checking Create from Selection Remove Arrows - Evaluate Formula Defined Names Formula Auditing Watch Window A61 H I J K 0 0 0 30 30 30 LT 3 0 Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 18000 0 0 0 000 18000 180 30 0 70 1800 0 00100 0 25 25 LT1 Gross Requirement Scheduled Receipts 1 Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 25 0 180 000 0 0 0 0 0 0 0 0 180 0 0 0 180 0 0 0 0 0 75 75 180 Q=L4L 0 0 1-1880-31-85-8530-31-731-08 0060 160 000 60 0 Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 60 0 0 60 0 0 0 0 0 160 000 160 0 0 0 000 1600 0 0 0 40 Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 0 40 0 0 40 0 0 20 20 20 160 160 160 0 0 0 0 0 0 0 0 0 0 0

Below is the screenshot of the formula applied -

mos - Excel Abhishek Rai - 0 ASH ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used , * Time Reference Trigº Functions Manager Function Library fx ? A .l le - Calculate Now Tell me what you want to do Define Name f Use in Formula - Create from Selection Defined Names Trace Precedents Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options - Calculation F H TO On Hand 1 Gross Requirement 0 Scheduled Receipts Projected on Hand 20 -C4+06+D3-D2 Net Requirements I =IF((C4+D3-D2)>0.0.104403-0211-m Planned Order Receipt 0 Planned Order Release 0 0 |=D4+E6+E3-E2 =IF((D4+E3-E2)>0,0,(D4+E3-E2)-1) lo =F4+G6+G3-G2 =IF((F4+G3-G2)>0,0,1F4+G3-G2)*(-1) =G4+H6+H3-H2 = IF((G4+H3-H2)>0,0,(G4+H3-H2) (-1) =E4+F6+F3-F2 =IF((E4+F3-F2)>0,0,(E4+F3-F2)*(-1) 10 In 10 60 =D72 =E7'2 =G72 10 10 T LT1 Q | L4L Gross Requirement T Scheduled Receipts Projected on Hand 20 Net Requirements Planned Order Receip Planned Order Release =F7'2 0 =E11+F13+F10-F9 =IF((E11+F10-F9)>0,0,(E11+F10-F9)*(-1)) =C11+13+D10-09 =IF((C11+D10-D9)>0,0,(C11+010-09)*(-1)) 12 | =D11+E13+E10-E9 = IF((D11+E10-E9)>0,0,(D11+E10-E9) (-1)) lo |=F11+G13+G10-G9 =IF((F11+G10-G9)>0,0,(F11+G10-G9)"(-1) lo 100 o =G11+H13+H10-H9 =IF((G11+H10-H9)>0,0,(G11+H10-H9)*(-1)) 1100 180 o =D7'1 =G71 =H7'1 10 Gross Requirement U Scheduled Receipts LT2 Projected on Hand 30 = NE Net Requirements L4L Planned Order Planned Order Receip Planned Order Release =E71 0 =D18+E20+E17-E16 =IF((D18+E17-E16)>0,0,(D18+E17-E16)"(-1)) =C18+D20+017-016 |-IF((C18+D17-D16)>0,0,(C18+D17-016)*(-1)) In |=E18+F20+F17-F16 =IF((E18+F17-F16)>0,0,(E18+F17-F16)"(-1)) =F18+G20+G17-G16 = IF((F18+G17-G16)>0,0, (F 18+G17-G16)"(-1) =G18+H2O+H17-H16 =IF((G18+H17-H16)>0,0,(G18+H17-H161"-1)) 20 o 30 0 0 30 80 =H14'2 25 26 27 LT 2 Q= L4L Gross Requirement Scheduled Receipts Projected on Hand 20 IN DO T Planned Order Receip Planned Planned Order Release I=D14'2 I=E14'2 =F14'2 =G14'2 0 =C25+D27+D24-D23 =D25+E27+E24-E23 =E25+F27+F24-F23 =F25+G27+G24-G23 I FIC 54024-02313001025+024-D2311-11=F(D25+E24-E23)>0,0,(D25+E24-E23)*(-1) =IF((E25+F24-F23)>0,0,(E25+F24-F23)"(-1|=IF((F25+G24-G23)>0,0,(F25+G24-G231"(- 180 180 360 =G25+H27+H24-H23 -IF[[G25+H24-H23)U1G25+H24-h25W 360 10

mos - Excel Abhishek Rai - 0 ASH ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - - Time Reference Trigº Functions Manager Function Library fx ? A .l le - Calculate Now Tell me what you want to do Define Name f Use in Formula - Create from Selection Defined Names Trace Precedents Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation A61 в F =D14'1 =E1401 =F1401 =G14'1 =H14'1 10 Gross Requirement 31W Scheduled Receipts LT 3 Projected on Hand 30 33 Q = Net Requirements 34 L4L Planned Order Receipt Planned Order Release =C32+D34+D31-D30 =IF((C32+D31-D30)>0,0,(C32+D31-D30)'(-1)) 0 70 =D32+E34+E31-E30 =E32+F34+F31-F30 =F32+G34+G31-G30 =IF((D32+E31-E30)>0,0,(D32+E31-E30) (-1)) =IF((E32+F31-F30)>0,0,(E32+F31-F30)'(-1) =IF((F32+G31-G30)>0,0,(F32+G31-G30)'(-1) =G32+H34+H31-H30 =IF((G32+H31-H30)>0,0,(G32+H31-H30)'(-1)) 180 0 70 1180 0 T=D141 =G14'1 =H14'1 Gross Requirement 38 X Scheduled Receipts 39 LT1 Projected on Hand 25 40 Q- Net Requirements I 41L4L Planned Order Receipt Planned Order Release =C39+D41+D38-037 =D39+E41+E38-E37 =E39+F41+F38-F37 =F39+G41+G38-G37 =G39+H41+H38-H37 =IF((C39+D38-D37)>0,0,(C39+038-D37)"(-1)-IF((D39+E38-E37)>0,0,(D39+E38-E37)*(-1)) =IF((E39+F38-F37)>0,0,(E39+F38-F37)*(-1) - IF((F39+G38-G37)>0,0,(F39+G38-G37)*(-1)=IF((G39+H38-H37)>0,0. (G39+H38-H371"(-10) 0 lo 75 180 o 75 180 44 45 46 47 48 Gross Requirement Y Scheduled Receipts LT 2 Projected on Hand 60 Q = Net Requirements L4L Planned Order Receipt Planned Order Release =D212 T-E212 =F212 =G212 =H212 In =C46+D48+D45-144 =D46+E48+E45-E44 =E46+F48+F45-F44 =F46+G48+G45-G44 =G46+H48+H45-H44 =IF((C46+D45-D44)>0,0,(C46+D45-D44)*(-1) =IF((D46+E45-E44)>0,0,(046+E45-E44)'(-1) =IF((E46+F45-F44)>0,0,(E46+F45-F44)*(-1) =IF((F46+G45-G44)>0,0,(F46+G45-G44)*(-1) =IF((G46+H45-H44)>0,0,(G46+H45-H44)*(-1)) 160 o 0 =D212 =E212 =F212 =G212 I=H212 Gross Requirement I Scheduled Receints | Projected on Hand 40 |Net Requirements I Planned Order Receip! Planned Order Release =C53+D55+052-051 =D53+E55+E52-E51 =E53+F55+F52-F51 =F53+G55+G52-G51 =IF((C53+D52-D51)>0,0,(C53+D52-D51)'(-1)) =IF(D53+E52-E51)>0,0,(D53+E52-E51)"(-1)) =IF((E53+F52-F51)>0,0,(E53+F52-F51)"(-1)) -IF((F53+G52-G51)>0,0,(F53+G52-G51)"(-1) 160 o 20 =G53+H55+H52-H51 = IF((G53+H52-H51)>0.0 (G53+H52-H511"(-1)) L4L 20 160