5 In-house or outsource ? (20 points for all questions) As a production engineer working for an auto maker, you have been tasked with determining the best way to acquire the plastic instrument panel of the most recent vehicle model. Specifically, you must advise upper management as to whether it is best to produce the panel in-house or to outsource its fabrication to a supplier. The instrument panel is to be manufactured by injection molding. Although there is sufficient expertise in your company to produce the panel in-house, an injection molding machine would need to be purchased and a manufacturing area setup specifically for thispurpose at a total cost (purchase and installation) of $400,000. In addition, the machine would entail monthly operating and maintenance costs of $1,000. Based on your estimates, the total cost of materials and labour to produce the instrument panel for one vehicle in-house would be $60. The production process would inevitably lead to defective panels being produced and needing to be thrown away (at a cost of $60 each). Based on information acquired from the injection moldingindustry, the rate of occurrence of defects can be expected to behave as a normal distribution withan average of 3% and a standard deviation of 0.5%. If the instrument panels are outsourced, the only expenditures are the purchase cost per panel as provided by prospective suppliers. Based on received quotes, these costs would vary with the production level as indicated in Table 3. Table 3 - Cost per outsourced instrument panel based on production level. Production level per month (units) Cost per panel 1 to 24 $150 25 to 49 $140 50 to 99 $125 100 to 249 $115 250 to 499 $105 500 to 999 $85 1,000 to 1,999 $75 2,000 and 2,999 $70 3.000 and over $50 16Based on sales projections for the vehicle, you estimate that the monthly production level willbe at least 300 units, at most 5,500 units and most likely 2,800 units. Your company's MARR is 10% compounded monthly and your analysis is to be based on a 5 years study period. To simplify your analysis, you may assume that all costs occur at the end of their respective months. All calculations must be performed in MS Excel. Answers to all questions should appear in the final version of your spreadsheet. QUESTIONS: I. Draw a cash flow diagram to represent the in-house production option. (1 point) II. Based on a nominal production level of 2,800 units and a nominal defect rate of 3%, compute the average cost per instrument panel based on production in-house. (1 point) III. Based on a nominal production level of 2,800 units, determine the cost per instrument panel based on outsourcing the production. Your worksheet should usea lookup table to obtain the appropriate cost from the data in Table 3 (i.e., the cost per panel should change automatically according to the specified nominal production level). (2 points) IV. For the specified nominal production level and defect rate, compare the costs per instrument panel for the in- house production and outsourcing options. Which alternative appears to be preferable at this point? (1 point) V. Prepare a sensitivity graph showing the variation in costs per unit for each production option as a function of production level (keep the defect rate at its nominal value). Hint: Use a data table to compute the required data and vary the production level from 300 to 5,500 units in 25 units increments. (2 points) By examining the sensitivity graph, what is the approximate range of production levels for which the in-house option is best? (1 point) Based on the information available to you from your analysis thus far, what recommendation should be given to upper management given the expected nominal values ? Include a graph in your report (1 point). VI. On a separate worksheet, implement a Monte Carlo simulation to determine the expected costs per unit of both the in-house and outsource production options. (5 points) 17Hint: To save time, copy pertinent cells from previous questions and then add/modify as required. The following general procedure may be followed: (i) Based on the estimates provided for the minimum, maximum and most likely production levels, program your spreadsheet to generate a random production level based on the beta distribution. (ii) Based on the estimated average and standard deviation of the defect rate, program your spreadsheet to generate a random defect rate based on the normal distribution. (iii) Calculate the average cost per instrument panel resulting from an in-house production based on the randomly generated production level and defect rate. (iv) Using a data table, perform a Monte Carlo simulation with a total of 1000 trials. VII. Compute the average costs per instrument panel for each production option based on the results from the Monte Carlo simulation. (1 point) VIII. Plot a histogram to illustrate the probability distribution of the cost per instrument panel for the in-house production option. Use bins ranging from $63 to $77 in $1 increments. (2 points) IX. Based on the data generated by the Monte Carlo simulation, what is the probability that the cost per unit for internal production will be less than $65? (2 points) X. Based on the data you have accumulated, comment on the level of risk associated to each production option. What recommendation should be given to upper management? (1 point)