Question

One unit of A is made of three units of B, one unit of C, and two units of D. B is composed of two units of E and one unit of D. C is made of one unit of B and two units of E. E is made of one unit of F.

Items B, C, E, and F have one-week lead times; A and D have lead times of two weeks.

Assume that lot-for-lot (L4L) lot sizing is used for Items A, B, and F; lots of size 50, 50, and 200 are used for Items C, D, and E, respectively. Items C, E, and F have on-hand (beginning) inventories of 10, 50, and 150, respectively; all other items have zero beginning inventory. We are scheduled to receive 10 units of A in Week 2, 50 units of E in Week 1, and also 50 units of F in Week 1. There are no other scheduled receipts. If 30 units of A are required in Week 8, use the low-level-coded bill-of-materials to find the necessary planned order releases for all components.

Develop an MRP planning schedule showing gross and net requirements and order release and order receipt dates. (Leave no cells blank - be certain to enter "0" wherever required.)

	Period	1	2	3	4	5	6	7	8
Item A OH = 0 LT = 2 SS = 0 Q = L4L	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases
Item C OH = 10 LT = 1 SS = 0 Q = 50	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases
Item B OH = 0 LT = 1 SS = 0 Q = L4L	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases
Item D OH = 0 LT = 2 SS = 0 Q = 50	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases
Item E OH = 50 LT = 1 SS = 0 Q = 200	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases
Item F OH = 150 LT = 1 SS = 0 Q = L4L	Gross requirements
	Scheduled receipts
	Projected available balance
	Net requirements
	Planned order receipts
	Planned order releases

Answer 1

Below is the screenshot of the MRP schedule -

XI 5 = mos - Excel Dynamics AX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri T 11 AA Wrap Text General * Cut EE Copy Format Painter PM Paste BIU 三三 А Merge & Center $ - % 40 00 .00 0 Inser Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Г Number A1 : X ✓ fx A A B J - с On Hand 1 D 1 0 6 O 2 3 + 5 6 E 2 0 10 10 0 0 0 F 3 0 0 10 0 0 0 G 4 0 0 10 0 0 0 H 5 O O 10 0 0 0 0 K 8 30 0 0 20 20 0 O 10 0 0 20 0 0 0 0 7 0 0 10 0 0 0 7 60 9 10 11 12 13 14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10 0 0 50 0 -40 50 10 70 -30 100 70 0 0 0 -30 30 0 0 0 0 -30 30 0 0 0 0 0 0 10 10 16 17 18 19 20 21 Gross Requirement A Scheduled Receipts LT 2 Projected on Hand Q-L4L Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement Scheduled Receipts B LT1 Projected on Hand Net Requirements Q = L4L Planned Order Receipts Planned Order Releases Gross Requirement Scheduled Receipts С Projected on Hand LT1 Q = 50 Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement D Scheduled Receipts Projected on Hand LT 2 Q = 50 Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement E Scheduled Receipts Projected on Hand LT1 Q = 200 Net Requirements Planned Order Receipts Planned Order Releases 0 0 10 0 0 0 0 0 0 0 10 0 0 0 0 0 40 0 0 0 0 0 40 0 0 0 0 0 0 0 O 0 0 0 0 0 0 0 0 0 0 0 10 0 0 0 0 0 0 0 0 50 0 0 100 0 0 0 0 0 10 0 0 50 70 0 20 30 50 0 20 0 40 10 50 0 40 0 30 20 50 0 23 24 25 26 27 28 0 10 0 40 10 50 50 0 0 30 0 0 0 0 0 30 0 0 0 30 50 31 32 33 34 35 ఎర్ 0 50 100 0 0 0 50 0 0 100 0 0 0 20 0 80 0 n 200 240 0 40 160 200 0 0 0 40 0 0 0 0 0 40 0 0 0 0 40 0 0 0 0

x5 – mos - Excel Dynamics AX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri T 11 A A Wrap Text General A * Cut ED Copy Format Painter Paste BIU 至 7 Inser $ - % Merge & Center - 4.0 .00 .00 0 Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number A69 : X ✓ fic A - B C E F G H J K 37 0 150 39 40 F LT1 Q = L4L Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 0 50 200 0 0 0 0 0 200 0 0 0 0 0 200 0 0 0 200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 42 2

Below is the screenshot of the formula applied -

XJ 5 mos - Excel ? FILE Abhishek Rai fx Calculate Now HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Dynamics AX Σ ? A e Define Name FX Use in Formula AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Used Time Reference Trig. Functions Manager Create from Selection Function Library Defined Names Insert Function Trace Precedents 5 Show Formulas Trace Dependents Error Checking Remove Arrows Fx Evaluate Formula Formula Auditing Gó Watch Window Calculation Calculate Sheet Options Calculation B1 : X fic A B 1 2 3 4 5 6 7 9 10 11 12 13 14 16 17 18 19 20 21 Gross Requirement д Scheduled Receipts LT 2 Projected on Hand Q L4L Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement Scheduled Receipts B LT1 Projected on Hand Q = L4L Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement С Scheduled Receipts LT1 Projected on Hand Q = 50 Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement Scheduled Receipts D LT2 Projected on Hand Q = 50 Net Requirements Planned Order Receipts Planned Order Releases Gross Requirement Scheduled Receipts E LT1 Projected on Hand Q = 200 Net Requirements Planned Order Receipts Planned Order Releases C D E F G H On Hand 1 2 13 4 5 O 0 0 0 0 O 10 0 0 0 0 =C4+06+03-02 =D4+E6+E3-E2 =E4+F6+F3-F2 =F4+G6+G3-G2 =G4+H6+H3-HZ =IF((C4+D3-D2)>0,0,(C4+D3-D2)*(-1)) =IF((D4+E3-E2)>0,0,(D4+E3-E2)*(-11) =IF((E4+F3-F2)>0,0,(E4+F3-F2)"(-1)) =IF((F4+G3-G2)>0,0,(F4+G3-G2)"(-1) =IF((G4+H3-H2)>0,0,(G4+H3-H2)"(-1) 0 0 0 0 0 O 0 0 0 0 =D7'3+D21'1 =E7'3+E211 =F7'3+F211 |=G7'3+G211 =H7'3+H211 10 0 0 10 0 0 =C11+13+D10-09 =D11+E13+E10-E9 =E11+F13+F10-F9 =F11+G13+G10-G9 =G11+H13+H10-H9 =IF((C11+010-09)>0,0,(C11+010-09)*(-1)) = IF((D11+E10-E9)>0,0,(D11+E10-E9)*(-1)) = IF((E11+F10-F9)>0,0,(E11+F10-F9)"(-1)) =IF((F11+G10-G9)>0,0,1F11+G10-G9)"(-1)) =IF((G17+H10-H9)>0,0,(G11+H10-H9)*(-1)) 0 0 0 0 10 In 0 0 10 70 =D7'1 =E71 =F71 =G71 =H7' 1 0 0 0 0 10 =C18+D20+D17-016 =D18+E20+E17-E16 =E18+F20+F17-F16 =F18+G20+G17-G16 =G18+H2O+H17-H16 = IF((C18+D17-D16)>0,0,1C18+D17-D16)"(-1)) = IF((D18+E17-E16)>0,0,1018+E17-E16)*(-11) = IF((E18+F17-F16)>0,0,(E18+F17-F16)*(-11) = IF((F18+G17-G16)>0,0,(F18+G17-G16)*(-1)) = IF((G18+H17-H16)>0,0,1G18+H17-H16)*(-1)) 0 0 0 0 0 10 0 0 0 50 =D7'2+D14'1 =E7' 2+E14'1 =F72+F14'1 =G7'2+G14'1 =H7' 2+H14'1 0 0 0 0 0 0 =C25+D27+D24-023 =D25+E27+E24-E23 -E25+F27+F24-F23 =F25+G274G24-G23 =G25+H27+H24-H23 =IF((C25+D24-D23)>0,0,(C25+D24-D23)*(-1)) = IF((D25+E24-E23)>0,0,(D25+E24-E23)*(-1) =IF((E25+F24-F23)>0,0,(E25+F24-F231*(-1) =IF((F25+G24-G23)>0,0,(F25+G24-G23)*(-1) =IF((G25+H24-H23)>0,0,(G25+H24-H23)*(-1) 10 10 10 50 50 0 50 50 50 10 =D14'2+D212 =E14'2+E212 =F14'2+F212 =G14'2+G212 =H14'2+H212 50 0 0 0 0 50 =C32+D34+D31-030 =D32+E34+E31-E30 =E32+F34+F31-F30 =F32+G34+G31-G30 =G32+H34+H31-H30 =IF((C32+D31-030)>0,0,(C32+D31-D30)*(-1) =IF((D32+E31-E30)>0,0,(D32+E31-E301*(-1)) =IF((E32+F31-F30)>0,0,(E32+F31-F30)*(-1)) =IF((F32+G31-G30)>0,0,(F32+G31-G30)*(-1)) =IF((G32+H31-H30)>0,0,(G32+H31-H30)*(-1)) 0 0 0 0 200 10 0 0 200 0 23 24 25 26 27 28 30 31 32 33 34 35 30

XUS ? mos - Excel Dynamics AX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Abhishek Rai fx Σ Calculate Now Insert Function ? A e Define Name Trace Precedents 5 Show Formulas FX Use in Formula Trace Dependents Error Checking AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Used - Time Reference Trig Functions Manager Create from Selection Remove Arrows fx Evaluate Formula Function Library Defined Names Formula Auditing GÒ Watch Window Calculation Calculate Sheet Options Calculation D63 : X A B C D E F H 150 37 38 39 40 41 42 43 Gross Requirement Scheduled Receipts F LT1 Projected on Hand Net Requirements Q = L4L Planned Order Receipts Planned Order Releases =D35'1 =E35'1 =F35'1 =G35'1 =H35'1 50 0 0 0 0 =C39+041+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+D38-D371*(-1) =IF((D39+E38-E37)>0,0,039+E38-E37]*(-1) =IF((E39+F38-F37)>0,0,(E39+F38-F371*(-1) =IF((F39+G38-G37)>0,0,(F39+G38-G371*(-1) =IF((G39+H38-H37)>0,0,(G39+H38-H37]"(-1) 0 0 0 0 0 0 0 0 0 0