MRP Problem. The Music company produces bass guitars in Caguas, PR The production levels are set up to match predicted demand as closely possible. One of the instruments produced is the part number 500 bass guitar. The figure below shows the part number 500 bass guitar and its subassemblies and components. The information of the product structure is represented in the indented bill of materials table. The first step in the assembly process is that the bridges are installed into the body, and the pegs are installed into the neck. Then, the body and the neck are assembled together. Finally, the strings are installed to get the final assembly. To procure the bridges from suppliers takes 1 week of lead time and 2 weeks lead time is required to complete the body. There are 5 bodies on hand. The delivery lead time of the strings is 2 weeks. The production manager wants to protect its strings inventory because of low performance supplier and decided to maintain a safety stock of 20 units each week, starting in week 7. The neck requires 2 weeks to produce and the pegs have a lead time of 5 weeks. Bridges Strings Body Neck MANUFACTURING BILL OF MATERIAL PARTNUMBER 500 BASS GUITAR Pegs Part Description Quantity UOM Number Required 200 Body 1 cach 205 Bridge 2 each 300 String 4 each 400 Neck 1 each 40% POR 4 cach It takes 1 week to produce a bass guitar once all materials are available. Suppose that predicted demands (gross requirements) for part number 500 is as follows: Week 8 9 10 11 12 Demand 85 28 15 8 12 These forecasts represent the number of bass guitars that the company would like to have ready to ship in the specified week. Based on the current anticipated returns, the company expects the following scheduled receipts to the inventory: Week 8 9 10 11 Scheduled Receipts 3 7 7 B There are 23 bass guitars in inventory at the end of the week 7. The MPS for the bass guitars is now obtained by netting out the inventory on hand at the end of week 7 and scheduled receipts in order to obtain net predicted demand. a. (15 points). Complete the MRP record for part number 500, part number 300, part number 200 & part number 205 following a lot-for-lot rule and showing all element of a MRP record. If submitted in Excel, make sure to show the functions or formulas used, not values)