Please use Excel to calculate all answers and show work/formulas to cells.

Question 1 ( 25 points) ECO Auto Repair fixes about 1,300 of a certain transmission part annually. ECO purchases each part for $55. They use 22% annual interest rate for holding costs. The ordering cost (or setup cost) for each order is estimated as $33 based on labor and shipping costs. Supplier lead time is 6 weeks. Currently the company places an order to buy this transmission part once every 4 months, but they are not sure if this is optimal and would like you as a systems engineer to make a recommendation? a) (5 points) What is the optimal number of these transmission car parts ECO should purchase each time an order is placed? b) (4 points) What is the reorder point? c) (4 points) What is the order cycle length? d) (4 points) How many orders would ECO place in a year? e) (4 points) How much is ECO expected to spend on inventory related costs annually? f) (4 points) How does the optimal ordering policy compare to the current policy in terms of annual costs? What would be the saving through the optimal policy? Question 2 (25 points) ATA Manufacturing Company produces transmission parts and sells to different Auto Repair shops 6000 units per year. ATA can produce these transmission parts at a rate of 70 units/hour. Machine setup time to produce these parts is 3.5 hours. Labor for this work is estimated at a rate of $60/ hour, and manufacturing plant idle time during setups is estimated to cost the firm around $220 per hour in lost profit. ATA uses a 22% annual interest rate for holding cost estimation. Each transmission part costs ATA $25 to produce; they are sold for \$55 each to auto repair shops. ATA works one shift of tenhours per day Monday through Saturday. a) (7 points) What is the optimal production quantity for this transmission part that minimizes inventory related costs? b) (6 points) What is the maximum expected level of on-hand inventory for these transmission parts? c) (6 points) What is the uptime and down time during each production cycle? d) (6 points) What is the total annual expected cost for the optimal production policy? What is the expected annual profit