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

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