Use a spreadsheet to set up the master production schedule for item A and the MRP inventory status records for all the dependent items. The needed data are given below.

Bill of Materials Make a printout of the MRP inventory records for each of the following cases:

a. The given master schedule and inventory status data.

b. Production for 300 units of A is added to week 6 for item A.

c. Item A production quantity of 300 units is moved up by 1 week from week 7 to week 6;
the scheduled receipt for item C of 200 units in week 4 is delayed to week 5; and the on-hand inventory for item E is found to be only 1,700.
Suggested Steps for the MRP Assignment The net requirement row of the inventory status record is set up in two stages. First, an intermediate row is set up by using the “on-hand” row. Then both the “on-hand”
row and the intermediate row are used to set up the “net requirements” row. An example follows.

Stage |
Set up the master schedule, bill-of-materials data, and on-hand inventory data at the top of the worksheet.
Stage Il 1. Set up a status record as shown above.
2. Set up the needed formulas for the “on-hand” row.
3. In cell c267—if(€254. In cell d26: =if(d25>=0,0,@if(c25=0,—d25,c25—d25)))
5. Copy the formula in cell d26 into the corresponding cells of columns E through the last week in the inventory record.
6. Copy this typical status record as many times as the number of dependent items.
Stage Ill Repeat the following for each item:
1. Enter a formula for the actual on-hand quantity (use the cell in column B for this).
Then enter the scheduled receipt quantities.
2. For each item set up the “planned order” row of each item.
3. Set up the gross requirement formula for the dependent items. Repeat this step in the order of “low-level” code.

Master Schedule for Item A WEEK 5 6 7 8 Begin Production 400 0 300 450 Inventory Status ITEM ON HAND B 200 C 300 D 400 E 2,000 F 300 G 200 SCHEDULED RECEIPT 600 (2), 400(4), 200(6) 100(1), 200(4), 100(7) 1,200 (5) 5,000 (3), 5,000 (5), 5,000 (8) 700(3) 1,000 (3), 500(7) Numbers in parentheses indicate week of receipt. 2B (LT= 1) 1C (LT= 1) 3D (LT=4) 4E (LT = 2) 6E 1G (LT= 1) 3B 3F (LT= 1) 6E 1G