One unit of A is made of two units of B and one unit of C. B is made of three units of D and one unit of F. C is composed of three units of B, one unit of D, and four units of E. D is made of one unit of E. Item C has a lead time of one week; Items A, B, E, and F have two-week lead times; and Item D has a lead time of three weeks. Lot-for-lot (L4L) lot sizing is used for Items C, E, and F; lots of size 25, 50, and 185 are used for Items A, B, and D, respectively. Items A, B, D, and E have on-hand (beginning) inventories of 5, 10, 140, and 120, respectively; all other items have zero beginning inventories. We are scheduled to receive 10 units of A in Week 3, 20 units of B in Week 7, 50 units of F in Week 5, and 65 units of E in Week 2; there are no other scheduled receipts. If 20 units of A are required in Week 10, use the low-level-coded bill-of-materials (product structure tree) to find the necessary planned order releases for all components. (Leave the cells blank, whenever zero (0) is required.) Please use Excel spreadsheet:

on hand

1

2

3

4

5

6

7

8

9

10

a

gross requirements

oh:5

scheduled receipts

lt:2

projected on hand

ss:0

net requirements

q:25

planned order receipts

planned order releases

on hand

c

gross requirements

oh:0

scheduled receipts

lt:1

projected on hand

ss:0

net requirements

q:l4l

planned order receipts

planned order releases

on hand

b

gross requirements

oh: 10

scheduled receipts

lt: 2

projected on hand

ss: 0

net requirements

q: 50

planned order receipts

planned order releases

on hand

d

gross requirements

oh: 140

scheduled receipts

lt: 3

projected on hand

ss: 0

net requirements

q: 185

planned order receipts

planned order releases

on hand

f

gross requirements

oh: 0

scheduled receipts

lt: 2

projected on hand

ss: 0

net requirements

q: l4l

planned order receipts

planned order releases

on hand

e

gross requirements

oh: 120

scheduled receipts

lt: 2

projected on hand

ss: 0

net requirements

q: l4l

planned order receipts

planned order releases