Question

Download HW 15 (Excel download) and perform the necessary MRP calculations. Then enter selected values below.

What is the Parent's Net Requirements for Period 5?

What is the Parent's Net Requirements for Period 8?

What is the Parent's Planned Order Receipts for Period 6?

What is Part 1's Net Requirements for Period 8?

What is the Part 1's Net Requirements for Period 5?

What is the Part 1's Planned Order Release for Period 5?

What is Part 2's Gross Requirements for Period 7?

What is Part 2's Projected On-Hand Inventory for Period 2?

What is Part 2's Net Requirements for Period 6?

What is Part 2's Planned Order Receipt for Period 6?

Management Instructions
		Beginning Inventory	Lot Size	Lead Time	Quantity per Parent	Safety Stock
						α	μ	σ	Amount
	Parent	403	12	1		93.0%	5,300	40
	Part 1	64	12	2	9	50.0%	1,900	30
	Part 2	-	200	1	7	80.0%	675	5
Material Requirements Plan
	Period	1	2	3	4	5	6	7	8
	Demand	-	-	-	134	143	60	-	49
Parent
	Gross Requirements
	Scheduled Receipts								14
	Projected On-Hand (at beginning of period)
	Net Requirements
	Planned Order Receipts
	Planned Order Releases
Part 1
	Gross Requirements
	Scheduled Receipts
	Projected On-Hand (at beginning of period)
	Net Requirements
	Planned Order Receipts
	Planned Order Releases
Part 2
	Gross Requirements
	Scheduled Receipts			85			85
	Projected On-Hand (at beginning of period)
	Net Requirements
	Planned Order Receipts
	Planned Order Releases

Answer 1

1 Management Instructions Safety Stock Beginning Invento Lead Quantity Time Lot Size Amount er Parent 40 59 93% 50% 80% 5300 1900 403 Parent Part 1 Part 2 12 12 200 30 6 8 Materials Requirements Plan Period Demand 134 143 49 10 12 Parent 49 14 80 134 143 14 15 16 17 18 19 20 Part 1 Gross Requirements Scheduled Receipts Projected On-Hand (at begi Net Requirements Planned Order Receipts Planned Order Releases 403 403 403 403 269 Gross Requirements Scheduled Receipts Projected On-Hand (at begi Net Requirements Planned Order Receipts Planned Order Releases 64 25 26 28 Part 2 29 30 Gross Requirements Scheduled Receipts Projected On-Hand (at begi 0 Net Requirements Planned Order Receipts Planned Order Releases 885 1170 1170 1170 200 32 200 200 200 200 200 200 200 200 200 200 35

EXCEL FORMULAS:

Management Instructions
		Beginning Inventory	Lot Size	Lead Time	Quantity per Parent	Safety Stock
						α	μ	σ	Amount
	Parent	403	12	1		0.93	5300	40	=NORMSINV(G4)*I4*SQRT(E4)
	Part 1	64	12	2	9	0.5	1900	30	=NORMSINV(G5)*I5*SQRT(E5)
	Part 2		200	1	7	0.8	675	5	=NORMSINV(G6)*I6*SQRT(E6)
Materials Requirements Plan
	Period	1	2	3	4	5	6	7	8
	Demand				134	143	60		49
Parent
	Gross Requirements	=C10	=D10	=E10	=F10	=G10	=H10	=I10	=J10
	Scheduled Receipts								14
	Projected On-Hand (at beginning of period)	=C4+C14+C17	=C15-C13+D14+D17	=D15-D13+E14+E17	=E15-E13+F14+F17	=F15-F13+G14+G17	=G15-G13+H14+H17	=H15-H13+I14+I17	=I15-I13+J14+J17
	Net Requirements	=MAX(0,C13-C15)	=MAX(0,D13-(C15-C13+D14))	=MAX(0,E13-(D15-D13+E14))	=MAX(0,F13-(E15-E13+F14))	=MAX(0,G13-(F15-F13+G14))	=MAX(0,H13-(G15-G13+H14))	=MAX(0,I13-(H15-H13+I14))	=MAX(0,J13-(I15-I13+J14))
	Planned Order Receipts		=IFERROR(1*OFFSET(D18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(E18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(F18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(G18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(H18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(I18,0,-1*$E$4,1,1),0)	=IFERROR(1*OFFSET(J18,0,-1*$E$4,1,1),0)
	Planned Order Releases	0	0	0	0	0	0	0	0
Part 1
	Gross Requirements	=C18*$F$5	=D18*$F$5	=E18*$F$5	=F18*$F$5	=G18*$F$5	=H18*$F$5	=I18*$F$5	=J18*$F$5
	Scheduled Receipts
	Projected On-Hand (at beginning of period)	=C5	=C23-C21+D22+D25	=D23-D21+E22+E25	=E23-E21+F22+F25	=F23-F21+G22+G25	=G23-G21+H22+H25	=H23-H21+I22+I25	=I23-I21+J22+J25
	Net Requirements	=MAX(0,C21-C23)	=MAX(0,D21-(C23-C21+D22))	=MAX(0,E21-(D23-D21+E22))	=MAX(0,F21-(E23-E21+F22))	=MAX(0,G21-(F23-F21+G22))	=MAX(0,H21-(G23-G21+H22))	=MAX(0,I21-(H23-H21+I22))	=MAX(0,J21-(I23-I21+J22))
	Planned Order Receipts		=IFERROR(1*OFFSET(D26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(E26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(F26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(G26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(H26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(I26,0,-1*$E$5,1,1),0)	=IFERROR(1*OFFSET(J26,0,-1*$E$5,1,1),0)
	Planned Order Releases	=ROUNDUP((OFFSET(C24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(D24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(E24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(F24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(G24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(H24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(I24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5	=ROUNDUP((OFFSET(J24,0,$E$5,1,1)+$J$5)/$D$5,0)*$D$5
Part 2
	Gross Requirements	=C26*$F$6	=D26*$F$6	=E26*$F$6	=F26*$F$6	=G26*$F$6	=H26*$F$6	=I26*$F$6	=J26*$F$6
	Scheduled Receipts			85			85
	Projected On-Hand (at beginning of period)	=C6	=C31-C29+D30+D33	=D31-D29+E30+E33	=E31-E29+F30+F33	=F31-F29+G30+G33	=G31-G29+H30+H33	=H31-H29+I30+I33	=I31-I29+J30+J33
	Net Requirements	=MAX(0,C29-C31)	=MAX(0,D29-(C31-C29+D30))	=MAX(0,E29-(D31-D29+E30))	=MAX(0,F29-(E31-E29+F30))	=MAX(0,G29-(F31-F29+G30))	=MAX(0,H29-(G31-G29+H30))	=MAX(0,I29-(H31-H29+I30))	=MAX(0,J29-(I31-I29+J30))
	Planned Order Receipts		=IFERROR(1*OFFSET(D34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(E34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(F34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(G34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(H34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(I34,0,-1*$E$6,1,1),0)	=IFERROR(1*OFFSET(J34,0,-1*$E$6,1,1),0)
	Planned Order Releases	=ROUNDUP((OFFSET(C32,0,$E$6,1,1)+$J$6)/$D$6,0)*$D$6	=ROUNDUP((OFFSET(D32,0,$E$6,1,1)+$J$6)/$D$6,0)*$D$6	=ROUNDUP((OFFSET(E32,0,$E$6,1,1)+$J$6)/$D$6,0)*$D$6	=ROUNDUP((OFFSET(F32,0,$E$6,1,1)+$J$6)/$D$6,0)*$D$6	=ROUNDUP((OFFSET(G32,0,$E$6,1,1)+$J$6)/$D$6,0)*$D$6	0	0	0