Question

The Green Thumb Water Sprinkler Company was founded in 1948 by Arthur Green, an entrepreneur with an avid interest in home gardening. Today the successful company, known as Arthur Green, Inc., is run by Arthur’s descendants. Among other things Arthur Green produces a line of high-quality sprinklers targeted at upper-income hobbyists. The company’s products are considered very “retro” in design and are seen by customers as unique and harkening back to an earlier time. To reinforce this image company catalogs and websites feature photos of the late Arthur Green working in his garden and wearing a pith helmet, popular in the past with some gardeners. A stylized drawing of Arthurs’s pith helmet appears on the company’s logo, and one of the real Arthurian headgear is lovingly displayed behind glass at the company’s headquarters in Oconomowoc, Wisconsin.

One product made by Arthur Green is the Model #984 Basic Lawn Sprinkler, a mainstay of the company’s product line and little-changed since first introduced in 1953.

The #984 Sprinkler is very basic in design and easy to assemble. A product structure tree is given later in this problem as well as basic information about each item in the tree. Capacity is limited in that a maximum of 300,000 sprinklers may be shipped in any given week. Note especially the items Raw Aluminum Tubing and Bulk Paint. The tubing ½” in diameter and is purchased in 20-foot sections. 5000 of these 20-foot tubes can be shipped on a single semi flatbed trailer. The lengths of tubing are then cut to form the Frame Tube and the Sprinkler Tube. The Bulk Paint is purchased in semi tanker loads of 8500 gallons each.

Considering the Master Production Schedule and other information given on the following page:

a. Develop MRP reports for each of the ten items of interest in the #984 problem. For Raw Aluminum Tubing and Bulk Paint your report should show the number of truck/tanker loads to be ordered in each time bucket.

b. For the planning scenario in part a above you also need an estimate of scrap aluminum tubing to be sold for recycling on a weekly basis. Determine on a week-by-week basis the revenue that can come from selling scrap aluminum.

c. Mall-Mart Stores, Inc., a very good customer, has suddenly asked whether Arthur Green can ship an additional 200,000 #984 sprinklers in Week 4. This is on top of other orders reflected in the Master Production Schedule. Modify the Master Production Schedule such that Arthur Green can:

· Meet the new quantity from Mall-Mart as quickly as possible.

· Fulfill all other existing orders in the MPS.

· Incur no more than a 10% change in MPS quantities week-to-week.

· Week 4 shipments can be no more than (125,000)(1.1)=137,500 units.

Master Production Schedule Week 10 12 ipment Quantity emtn 125,000 100,000 145,000 175,000 200,000 220,000 220,00 230,000 235,000 Item Ordering Policy Lot-800 Beginning Inventory Scheduled Receipts Sprinkler Water Motor AssemblyAny Batch Size Hose Receptacle Assembly Lots 10,000 Frame Assembly Any Batch Size Any Batch Size Lot- 100,000 Lot- 1,000 Lot for Lot Lot- 250,000 Lot- 1,000,000 Lot-8500 gallons 0 10,000 Sprinkler Tube Metal Screw Water Motor Frame Tube 50,000 250,000 in Week 2 End Plate Bulk Paint 3200 gallons Raw Aluminum Tubing Lot 3500 2 Truckloads 5 Truckloads in Week 1 sections Additional Information The End Plate has a 2-week leadsime All other items have a lead sine of I week. The grade and gauge of aluminum tubing used here weighs 15 pounds per linear foot. The current market price for scrap aluminum tubing is $0.40 per pound. The vendor for the water motor has some significant quality issues. All water motors are inspected by Green Thumb on arrival from the vendor and 1 motor in 200 is found to be unusable. Of course, this must be taken into consideration in planning. The motors in beginning inventory have been inspected and all are useable.

Answer 1

Sprinkler	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement					125000	100000	145000	175000	200000	220000	220000	230000	235000
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	5000	5000	5000	5000	0	0	600	0	0	0	0	400	600
Net requirement		0	0	0	120000	100000	145000	174400	200000	220000	220000	230000	234600
Planned order receipt					120000	100000	145600	174400	200000	220000	220000	230400	235200
Planned order release	0	0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0

Water motor assembly	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	0	0	0	0	0	0	0	0	0	0	0	0	0
Net requirement		0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Planned order receipt				120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Planned order release	0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0

HR Assembly	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	0	0	0	0	0	4400	0	0	0	0	9600	4400	4400
Net requirement		0	0	120000	100000	145600	170000	200000	220000	220000	230400	225600	0
Planned order receipt				120000	100000	150000	170000	200000	220000	220000	240000	230000
Planned order release	0	0	120000	100000	150000	170000	200000	220000	220000	240000	230000	0	0

Frame Assembly	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	0	0	0	0	0	0	0	0	0	0	0	0	0
Net requirement		0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Planned order receipt				120000	100000	145600	174400	200000	220000	220000	230400	235200	0
Planned order release	0	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0

Bulk paint	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	3562.5	2968.75	4322.5	5177.5	5937.5	6531.25	6531.25	6840	6982.5	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	3200	3200	0	5531.25	1208.75	4531.25	7093.75	562.5	2531.25	4191.25	5708.75	5708.75	5708.75
Net requirement		0	362.5	2968.75	0	3968.75	1406.25	0	5968.75	4308.75	2791.25	0	0
Planned order receipt				8500		8500	8500		8500	8500	8500
Planned order release	0	0	8500	0	8500	8500	0	8500	8500	8500	0	0	0

Sprinkler tube	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	10000	10000	0	0	0	0	0	0	0	0	0	0	0
Net requirement		0	110000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Planned order receipt			110000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Planned order release	0	110000	100000	145600	174400	200000	220000	220000	230400	235200	0	0	0

Metal screw	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	720000	600000	873600	1046400	1200000	1320000	1320000	1382400	1411200	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	50000	50000	30000	30000	56400	10000	10000	90000	70000	87600	76400	76400	76400
Net requirement		0	670000	570000	843600	990000	1190000	1310000	1230000	1312400	1323600	0	0
Planned order receipt			700000	600000	900000	1000000	1200000	1400000	1300000	1400000	1400000
Planned order release	0	700000	600000	900000	1000000	1200000	1400000	1300000	1400000	1400000	0	0	0

Water motor	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	122449	102040.8	148571.4	177959.2	204081.6	224489.8	224489.8	235102	240000	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	50000	50000	551	510	939	980	898	408	918	816	816	816	816
Net requirement		0	72448.98	101489.8	148061.2	177020.4	203102	223591.8	224081.6	234183.7	239183.7	0	0
Planned order receipt			73000	102000	149000	178000	204000	224000	225000	235000	240000	0	0
Planned order release	0	73000	102000	149000	178000	204000	224000	225000	235000	240000	0	0	0

Frame tube	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	0	0	0	0	0	0	0	0	0	0	0	0	0
Net requirement		0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Planned order receipt			120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Planned order release	0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0	0

Plastic cap	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	360000	300000	436800	523200	600000	660000	660000	691200	705600	0	0
Scheduled receipt		0	0	250000	0	0	0	0	0	0	0	0	0
Projected on-hand	1000000	1000000	640000	590000	153200	130000	30000	120000	210000	18800	63200	63200	63200
Net requirement		0	0	0	0	370000	470000	630000	540000	481200	686800	0	0
Planned order receipt			0	0		500000	500000	750000	750000	500000	750000	0	0
Planned order release	0	0	0	0	500000	500000	750000	750000	500000	750000	0	0	0

End Plate	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		0	120000	100000	145600	174400	200000	220000	220000	230400	235200	0	0
Scheduled receipt		0	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	350000	350000	230000	130000	984400	810000	610000	390000	170000	939600	704400	704400	704400
Net requirement		0	0	0	15600	0	0	0	0	60400	0	0	0
Planned order receipt			0	0	1000000					1000000		0	0
Planned order release	0	0	1000000	0	0	0	0	1000000	0	0	0	0	0

Raw Al tubing	Week	1	2	3	4	5	6	7	8	9	10	11	12
Gross requirement		5.17	4.43	6.45	7.73	8.87	9.75	9.75	10.21	10.43	0	0	0
Scheduled receipt		5	0	0	0	0	0	0	0	0	0	0	0
Projected on-hand	2	1.83	0	0	0	0	0	0	1	1	1	1	1
Net requirement		0	2.60	6.26	7.69	8.86	9.51	9.46	9.88	9.80	0	0	0
Planned order receipt			2.8	6.3	7.7	9.1	9.8	9.8	10.5	10.5	0	0	0
Planned order release	0	2.8	6.3	7.7	9.1	9.8	9.8	10.5	10.5	0	0	0	0

Computations

2 Sprinkler Week 1 Gross requirement 4Scheduled receipt Projected on-hand Net requirement 5000 MAX(0,C5+D4+D7-D3) -MAX(0,D5+E4+E7-E3 -MAX(0,E3-D5-E4 -MAX(0,E5+F4+F7-F3 -MAX(0,F3-E5-F4 -MAX(0,F5+G4+G7-G3) -MAX(0,G3-F5-G4) -MAX(0,D3-CS-D4) 7 Planned order receipt Planned order release D7 10 11 Gross requirement 12 Scheduled receipt 13 Projected on-hand Water motor assembl Week 1 -F8 -MAX(0,F13+G12+G15-G11) -MAX(0,G11-F13-G12) -G14 H15 MAX(0,C13+D12+D15-D11) -MAX(0,D11-C13-D12) MAX(0,D13+E12+E15-E11) -MAX(0,E11-D13-E12 -MAX(0,E13+F12+F15-F11) -MAX(0,F11-E13-F1 -F14 -G15 Net requirement 15 Planned order receipt 16 Planned order releaseD15 E15 17 18 HR Assembly -F15 Week 1 Gross requirement -F8 20 Scheduled receipt 21 Projected on-hand Net requirement MAX(0,C21+D20+D23-D19) -MAX(0,D19-C21-D20) MAX(0,D21+E20+E23-E19) MAX(0,E19-D21-E20) MAX(0,F21+G20+G23-G19) -MAX(0,G19-F21-G20 MAX(0,E21+F20+F23-F19) -MAX(0,F19-E21-F20) -F22 G23 23 Planned order receipt 24 Planned order release D23-E23 H23

25 26 27 28 Scheduled receipt 29 Projected on-hand 30 31 Planned order receipt 32 Planned order release D31 E31 Frame Assemb Week 1 Gross requirement -F8 MAX(0,F29+G28+G31-G27) -MAX(0,G27-F29-G28) -G30 H31 -MAX(0,C29+D28+D31-D27) -MAX(0,D27-C29-D28) MAX(0,D29+E28+E31-E27) MAX(0,E27-D29-E28) MAX(0,E29+F28+F31-F2 MAX(0,F27-E29-F28) Net requirement -F30 -G31 F31 Week 1 34 Bulk paint 35 36 37 Projected on-hand 38 39 Planned order receipt -3.8 D16/128 -3.8 E16/128 -3.8*F16/128 Gross requirement Scheduled receipt -3.8*G16/128 3200 MAX(0,C37+D36+D39-D35) MAX(0,D37+E36+E39-E35) MAX(0,E35-D37-E36) -MAX(0,E37+F36+F39-F35) -MAX(0,F35-E37-F36) MAX(0,F37+G36+G39-G35) -MAX(0,G35-F37-G36) Net requirement -MAX(0,D35-C37-D36) Planned order release D39 E39 G39 H39 Week 1 42 Sprinkler tube 43 44 Scheduled receipt 45 Projected on-hand Gross requirement -D16 -E16 -F16 -G16 MAX(0,F45+G44+G47-G43) -MAX(0,G43-F45-G44) 10000 MAX(0,C45+D44+D47-D43) MAX(0,D45+E44+E47-E43 -MAX(0,E43-D45-E44) -E46 -F47 MAX(0,E45+F44+F47-F43) -MAX(0,F43-E45-F44) Net requirement -MAX(0,D43-C45-D44) 47 Planned order receipt Planned order release D47 -E47 G47

49 Metal screw Week 1 4 3 (D32+D16) -3 (E32+E16) 3 (F32+F16) 3 (G32+G16) 51 52 Scheduled receipt 53 Projected on-hand 54 Net requirement 55 Planned order receipt Gross requirement 50000MAX(0,C53+D52+D55-D51 MAX(0,D53+E52+E55-E51) MAX(o,E51-D53-E52) MAX(0,E53+F52+F55-F51) MAX(0,F51-E53-F52) MAX(0,F53+G52+G55-G51) MAX(0,G51-F53-G52) =MAX(0,051-53-D52) Planned order release :055 -E55 -H55 57 Water motor Week 1 4 =D16/0.98 -F16/0.98 59 60 Scheduled receipt 61 Projected on-hand Gross requirement -E16/0.98 -G16/0.98 50000 =MAX(0,C61+D60+D63-D59) -MAX(0,D59-C61-D60) MAX(0,D61+E60+E63-E59 MAX( 0,E59-D61-E60) MAX(0,E61+F60+F63-F59) -MAX(0,F59-E61-F60) MAX(0,F61+G60+G63-G59) -MAx(0,G59-F61-G60) 149000 H63 Net requirement 63 Planned order receipt Planned order release :0бЗ 66 Frame tube 67 Gross requirement 68 Scheduled receipt 69 Projected on-hand Week1 -D32 -F32 -G32 MAX(0,C69+D68+D71 -MAX(0,D67-C69-D68) -MAX(0,D69+E68+E71-E67) -MAX(0,E67-D69-E68) -E70 MAX(0,E69+F68+F71-F6 -MAX(0,F67-E69-F68) -F70 -G71 MAX(0,F69+G68+G71-667) -MAX(0,G67-F69-G68) -G70 H71 0 Net requirement 71 Planned order receipt 72 Planned order release D7 E71

73 74 Plastic cap 75 76 Scheduled receipt 77 Projected on-hand 78 Net requirement 79 Planned order receipt 80 Planned order release D79 E79 81 82 83 Gross requirement 84 Scheduled receipt 85 Projected on-hand 86 Net requirement 87 Planned order receipt 88 Planned order release E87 F87 89 90 Raw Al tubing 91 Gross requirement 92 Scheduled receipt 93 Projected on-hand 94 95 Planned order receipt 96 Planned order release D95 E95 Week 1 Gross requirement -3 D32 -3*E32 -3 F32 -3 G32 1000000 MAX(0,C77+D76+D79-D75) -MAX(0,D77+E76+E79-E75) -MAX(o,E75-D77-E76) -MAX(0,E77+F76+F79-F75) MAX(0,F75-E77-F76) -MAX(0,F77+G76+G79-G75 MAX(0,D75-C77-D76) MAX(0,G75-F77-G76) =F79 -G79 -H79 End Plate Week 1 D32 -E32 -F32 -G32 350000 -MAX(0,C85+D84+D87-D83 -MAX(0,D85+E84+E87-E83) -MAX(0,E85+F84+F87-F83) -MAX(0,F83-E85-F84 -MAX(0,F85+G84+G87-G83) MAX(0,D83-C85-D84) MAX(0,E83-D85-E84) MAX(0,G83-F85-G84) -H87 Week 1 =((D72*35.2+D48*18/12)/(5000*20) =((E72*35.2+E48*18)/12)/(5000*20) 弌(F72"35. 2+F48*18)/12)/(5000*20) =((G72*35.2+648" 18)/12)/(5000*20) -MAX(0,C93+D92+D95-D91) -MAX(0,D91-C93-D92 MAX(0,D93+E92+E95-E91) -MAX(0,E91-D93-E92) 2.8 -MAX(0,E93+F92+F95-F91) -MAX(0,F91-E93-F92) б. 3 -G95 MAX(0,F93+G92+G95-G91 -MAX(0,G91-F93-G92) 7.7 Net requirement