Creating Simulated Spreadsheets You will conduct a cost-benefit analysis to quantify the cost of inspecting ski bindings versus the additional costs of not inspecting bindings and having skis returned from customers. As with the ski itself, the majority of the inspection cost with bindings is the labor. The average labor rate for the binding inspectors is $39 per hour. Historical data has shown that ski bindings fail at rates between 2% and 4%. The additional costs incurred if a ski binding fails after purchase are approximately $50 to $90. Based on all of this introductory information, complete the following: 1. Open the workbook named Cost Benefit Analysis from the appropriate link in Canvas. Save the file as Cost_Analysis_Your First Name_Your Last Name. 2. Format cell E2 to accept both text and values where the value can be used in a formula. Use the format: $0.00 "/hr-labor". 3. Enter 39 in cell E2 and ensure that your format rule applies correctly to the cell. 4. Based on the information in the introductory paragraphs, calculate the cost to test each ski binding and the total cost over the two-hour period. Remove any error indicators that may occur from omitting adjacent cells. Format all values to the Accounting format. Remove the currency symbol from all except the first value in this column. 5. Insert a new blank worksheet into the workbook and name the sheet Simulate. appropriately and format to look professionally appealing. You are going to be asked to simulate the cost of not inspecting the bindings for a sample of 100 skis. 7. In the Type column, write a formula to simulate a ski binding type assuming bindings are equally likely to be any of Types 1 through 5. Remember that you need 100 simulated values. 8. In the Return Cost column, write a formula to simulate the return cost based on the historical data given in the introductory paragraphs, assuming equal probability of any value within the range. 9. in the Probability of Failure column, write a formula to simulate the probability that the ski binding will fail, based on the historical data. Assume equal probability within the range will occur. failing, rounded to the nearest cent. 11. In cell G1 of the Simulate worksheet, create the following table: 12. Create a formula to calculate the expected costs by binding type and the total of all types. 13. Recalculate the data five times and record the totals below the summary table. 14. Assume that labor rates for inspection have decreased to $25 per hour. What is the revised cost to inspect? Will the new result effect your cost benefit analysis totals? Record the total below the table on the Inspection Cost Sheet. Return the inspection cost back to $39. 15. Calculate the hourly labor cost that would be needed to obtain a total cost of inspection of $150. Accept the revised hourly labor cost. 16. Update the properties in the workbook to make it personal. 17. Add the file name to the footer of the both sheets. 18. Save the file and upload to the appropriate link in Canvas. Ski Bindings Labor Cost Analysis Creating Simulated Spreadsheets You will conduct a cost-benefit analysis to quantify the cost of inspecting ski bindings versus the additional costs of not inspecting bindings and having skis returned from customers. As with the ski itself, the majority of the inspection cost with bindings is the labor. The average labor rate for the binding inspectors is $39 per hour. Historical data has shown that ski bindings fail at rates between 2% and 4%. The additional costs incurred if a ski binding fails after purchase are approximately $50 to $90. Based on all of this introductory information, complete the following: 1. Open the workbook named Cost Benefit Analysis from the appropriate link in Canvas. Save the file as Cost_Analysis_Your First Name_Your Last Name. 2. Format cell E2 to accept both text and values where the value can be used in a formula. Use the format: $0.00 "/hr-labor". 3. Enter 39 in cell E2 and ensure that your format rule applies correctly to the cell. 4. Based on the information in the introductory paragraphs, calculate the cost to test each ski binding and the total cost over the two-hour period. Remove any error indicators that may occur from omitting adjacent cells. Format all values to the Accounting format. Remove the currency symbol from all except the first value in this column. 5. Insert a new blank worksheet into the workbook and name the sheet Simulate. appropriately and format to look professionally appealing. You are going to be asked to simulate the cost of not inspecting the bindings for a sample of 100 skis. 7. In the Type column, write a formula to simulate a ski binding type assuming bindings are equally likely to be any of Types 1 through 5. Remember that you need 100 simulated values. 8. In the Return Cost column, write a formula to simulate the return cost based on the historical data given in the introductory paragraphs, assuming equal probability of any value within the range. 9. in the Probability of Failure column, write a formula to simulate the probability that the ski binding will fail, based on the historical data. Assume equal probability within the range will occur. failing, rounded to the nearest cent. 11. In cell G1 of the Simulate worksheet, create the following table: 12. Create a formula to calculate the expected costs by binding type and the total of all types. 13. Recalculate the data five times and record the totals below the summary table. 14. Assume that labor rates for inspection have decreased to $25 per hour. What is the revised cost to inspect? Will the new result effect your cost benefit analysis totals? Record the total below the table on the Inspection Cost Sheet. Return the inspection cost back to $39. 15. Calculate the hourly labor cost that would be needed to obtain a total cost of inspection of $150. Accept the revised hourly labor cost. 16. Update the properties in the workbook to make it personal. 17. Add the file name to the footer of the both sheets. 18. Save the file and upload to the appropriate link in Canvas. Ski Bindings Labor Cost Analysis