2 4 6 GreatTech (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 2, 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. 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 considered as containing 0. No Excel formula is required for this Excel assignment. 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 8 purchasing W: 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 Table Item Quantity on Lead time (weeks) hand at Week 0 (units) Gross requirement 5 6 1 25 7 Y 2 5 8 Z 2 45 9 W 3 Item Week 0 V 2 65 1 U 1 x 2 3 4 5 6 Y Projected on hand at the beginning Net requirements Planned order receipts Planned order released Gross requirement Projected on hand at the beginning Net requirements Planned order receipts Planned order released Gross requirement Projected on hand at the beginning Z Net requirements Planned order receipts 9 0 1 2 3 4 5 -6 Different lot sizing than lot W 7 Net requirements for lot 8 0 1 2 3 4 5 6 -7 8 9 0 Planned order released Gross requirement Projected on hand at the beginning Planned order receipts Planned order released Gross requirement Projected on hand at the beginning V Net requirements Planned order receipts Planned order released Gross requirement Projected on hand at the beginning Net requirements Planned order receipts Planned order released 1Q2: GT's management has identified an error in the above table: W's quantity on hand in week 0 should be 125, not 25. They have also revised their approach to lot sizing when purchasing W from a U.S. distributor 2 (instead of overseas), adopting the following guidelines: 3 Place purchase orders only when necessary to avoid immediate stockouts; 4-Keep order quantities consistent. That is, each order should be for the same quantity. However, this quantity should be as small as possible (to prevent overly large orders) due to the current supply situation with this -5 distributor. 6 Stockouts should not occur at any times. -7 8 How should the above template for W's lot sizing be revised? 9 0 1 2 4 S 6 7 Gross requirement Projected on hand at the beginning W Net requirements Planned order receipts Planned order released 11 12 13