B 9 0 1 3 Great Tech (GT) is a regional company specializing in the manufacturing of smart home devices. The company is well-known for its innovative products, efficient production processes, and exceptional customer service. The production of a particular smart home device requires various components (e.g., microcontrollers, sensors, actuators). The manager has designated the product X and its components as Items Y, Z, W, V, and U, respectively. Item X is assembled using 1 unit of Item Y, 2 units of Item Z, and 2 units of Item W. Each Item Y consists of 2 units of Item Z and 2 units of Item V. Each Item Z requires 1 unit of Item W, 2 units of Item V, and 1 unit of Item U. The table below shows the lead times and quantities on hand at week 0 (the current week) for Items X, Y, Z, W, V, and U, respectively. Items X, Y, and Z are produced in-house, while the rest are purchased from suppliers. GT's master production schedule for Item X indicates gross requirements of 50 units, 70 units, and 120 units at weeks 8, 11, and 13, respectively. Assume there are no scheduled receipts (i.e., pending orders to arrive) for any items during next 13 weeks. So, "scheduled receipts" is omitted in the MRP schedule for the purpose of simplicity. 2 For the following questions Q1 & Q2, present the MRP and lot sizing schedules using the templates provided below for next 13 weeks (~ 3 months). For each item, the schedule indicates the week and quantity that the item should be ready (in the row of "Gross requirement"), and the week and quantity that the assembly or purchase order should be released (in the row of "Order released"). Fill your numbers in the cells within Q1's and Q2's templates. Each incorrect number costs you 0.2 points. If there are 30 or more incorrect numbers together within these two templates, you will receive no points for this Excel work. Blank cell will be 5❘ considered as containing 0. No Excel formula is required for this Excel assignment. 4 6 7 Q1: GT uses the lot-for-lot approach for Items X, Y, Z, V and U. However, to lower the number of purchase orderes from the suppliers of item W overseas, GT decides to follow a different approach of lot sizing for purchasing W: 8 9 -- Place the purchase order only when necessry to avoid immediate stockouts, OR when the beginning inventory on hand of the week drops to 20 units or lower (i.e., ROP = 20 units); 0 -- The purchase order quantity should be the multiple of 30 units (e.g., 30, 60, 90, 120, etc.) and this quantity should be kept as low as possible. 1 Stockouts should not occur at any times. 2 3 4 Table Item Lead time (weeks) Quantity on hand at Week 0 5 (units) 6 X 1 25 7 Y 2 5 8 Z 2 45 10 11 12 13 9 W 3 25 Item Week 0 V 2 65 Gross requirement 1 U 1 50 Projected on hand at the beginning 35 60 60 50 50 30 5 25 35 25 5 20 20 40 40 35 25 X 2 Net requirements 3 Planned order receipts 4 Planned order released 30 0 0 0 0 30 0 0 0 30 0 0 0 5 Gross requirement 6 Projected on hand at the beginning 0 20 20 25 25 20 0 20 35 10 10 35 35 20 0 5 Y 7 Net requirements 8 Planned order receipts 9 Planned order released 0 30 0 0 0 30 0 0 30 0 0 0 30 -0 Gross requirement -1 Projected on hand at the beginning 25 ज 45 30 20 5 30 50 35 15 20 40 40 25 5 -2 Z Net requirements -3 Planned order receipts 4 Planned order released 30 0 0 0 0 30 0 0 0 30 0 0 0 -5 Gross requirement Different lot -6 Projected on hand at the beginning 15 25 15 65 70 50 20 40 0 25 25 120 100 90 90 60 60 sizing than lot -7 W Net requirements for lot 8 Planned order receipts -9 Planned order released 0 30 0 30 0 0 0 0 120 0 0 0 0 0 Gross requirement 1 Projected on hand at the beginning 5 20 20 40 40 35 25 25 0 20 40 20 5 30 35 25 2 V Net requirements 3 Planned order receipts 4 Planned order released 0 30 0 0 0 0 30 0 0 0 30 0 0 5 Gross requirement 6 Projected on hand at the beginning 35 55 40 20 40 20 10 15 60 60 80 60 60 60 45 35 10 30 30 U -7 Net requirements 8 Planned order receipts 9 Planned order released 30 0 0 0 0 30 30 0 0 0 0 0 30 0