This project requires you to make an excel model using the data provided Please mention your ability to use Excel in the bid so I know you read this. 1. Using the information given in the background section, you are to conduct a cost-benefit analysis by developing a spreadsheet model using Microsoft Excel that can be used to help the strategic direction of the company in dealing with the remote sensor problem. (15 points). The model should accurately represent the current situation and allow you to evaluate the different costs, labor costs versus the return costs, and provide information to assist Mr. Appleby to address the issues below: a. Perform descriptive statistics analysis for the given data. b. Provide the total labor cost from the sample? (Hint: you are to calculate the labor cost for each item first). c. In a summary table, provide the total number of each model, average functional test for each model, average sensor test for each model and the average labor cost for each model (Hint: you are to use AVERAGEIF function here). d. Using the results from the historical data given in the background, you are to develop an Excel worksheet that will simulate the cost of not inspecting the remote sensor for a sample of 200 trials for the criteria given below: Trial Type Return cost Probability of failure Probable cost Trial = number of trials simulated Type = randomly generate numbers between Type 1 and Type 4 Return cost = calculation based on mean = $27.95, standard deviation = $12.50. Probability of failure = calculation based on failure rate between 1% and 4%. Probable cost = product of return cost and probability of failure rate e. From the simulation results, in a table, report the probable cost for each model plus the total probable cost.
