Given excel data:

kindly someone answer this question its very urgent

BACKGROUND An auto maintenance shop suddenly has more customers than it can handle in a given day. The shop currently has one maintenance bay and is considering adding additional bays. The current options are to continue operations with 1 bay or expand to 2, 3, or 4 bays. Each additional bay costs $10,000 per day. There is no cost for the existing bay. Each day a random number of cars arrive and the single bay serves as many cars as possible. The number of cars the bay can handle each day is also random. If there are 10 or more cars waiting to be served, the night crew is hired for $12,000 to work on cars overnight. 10 of the cars are sent to the overnight crew, all cars above 10 are sent to a competitor resulting in no cost or revenue. Any of the 10 cars the night crew cannot handle are added to the list for the next day. Revenues are generated for each car served by the day crew and the night crew. Example: 45 cars arrive, the bay services 20 cars resulting in 25 cars waiting to be serviced. 15 of the 25 are sent away and the remaining 10 are left for the night crew. If the night crew works on 7, the final 3 cars are left for the next day. GOAL a. Use the supplied data and information above to create a simulation to find the optimal number of additional bays the company should build. The answer will be 0, 1, 2 or 3 additional bays for a total of 1, 2, 3 or 4 bays. Please include the overall profit the company would make for each of the four options and clearly identify which option your team suggests based on the optimal solutions. Your report should include charts or graphs to support your decision. b. Using your answer in part a, evaluate how the system will perform with the number of bays your team suggests if the number of cars that arrived each day was based on a normal distribution with a mean that ranged from 100 to 125 and a standard deviation of 10. Show how the profit will change over the range of the mean for the fixed number of bays you chose in part a. RUBRIC a. Report: 25 points: Your report will be graded on the clarity of your explanation, how well you support your decision, and how you discuss the benefits to the customer of the suggested number of bays. Presentation and neatness will also be a factor in grading the report. b. Spreadsheet: 75 points: Your spreadsheet will be graded on the accuracy of your model, how well the model works (lack of errors), and how well you incorporated all aspects of the data to include relationships and distributions. 1 2 4 5 6 7 8 9 10 11 Daylcars arrived per day 1 87 95 80 92 80 87 111 86 81 84 89 70 103 82 88 88 71 69 80 97 97 79 90 74 85 98 75 79 72 2 3 4 5 6 7 8 9 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 2 3 4 Day cars served per day 1 2 3 4 5 6 5 6 8 7 g 8 10 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 9 45 45 38 36 46 49 42 45 35 39 34 48 46 43 40 43 30 38 40 42 40 40 37 43 35 0 30 35 40 47 1 2 3 4 2282385838 5 6 7 6 7 9 8 10 9 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 20 21 22 24 23 24 25 26 27 28 29 21 26 27 29 Days Serviced at Night 1 8 2 6 3 7 4 10 5 10 10 10 10 9 7 9 10 10 9 10 9 8 10 9 8 10 7 8 10 9 10 7 7 8 30 - - - - 1 Day 1 2 3 4 5 6 7 9 10 11 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 $ 27 26 28 27 29 28 29 30 8 35 30 31 234567890 10 Revenue per car per day 233.45 285.72 334.70 409.74 204.46 366.35 405.14 260.00 515.22 192.00 380.30 213.43 277.49 727.12 124.26 399.00 376.69 364.05 151.38 375.67 465.87 626.89 184.08 160.03 362.45 397.90 341.08 344.87 434.12 466.28 BACKGROUND An auto maintenance shop suddenly has more customers than it can handle in a given day. The shop currently has one maintenance bay and is considering adding additional bays. The current options are to continue operations with 1 bay or expand to 2, 3, or 4 bays. Each additional bay costs $10,000 per day. There is no cost for the existing bay. Each day a random number of cars arrive and the single bay serves as many cars as possible. The number of cars the bay can handle each day is also random. If there are 10 or more cars waiting to be served, the night crew is hired for $12,000 to work on cars overnight. 10 of the cars are sent to the overnight crew, all cars above 10 are sent to a competitor resulting in no cost or revenue. Any of the 10 cars the night crew cannot handle are added to the list for the next day. Revenues are generated for each car served by the day crew and the night crew. Example: 45 cars arrive, the bay services 20 cars resulting in 25 cars waiting to be serviced. 15 of the 25 are sent away and the remaining 10 are left for the night crew. If the night crew works on 7, the final 3 cars are left for the next day. GOAL a. Use the supplied data and information above to create a simulation to find the optimal number of additional bays the company should build. The answer will be 0, 1, 2 or 3 additional bays for a total of 1, 2, 3 or 4 bays. Please include the overall profit the company would make for each of the four options and clearly identify which option your team suggests based on the optimal solutions. Your report should include charts or graphs to support your decision. b. Using your answer in part a, evaluate how the system will perform with the number of bays your team suggests if the number of cars that arrived each day was based on a normal distribution with a mean that ranged from 100 to 125 and a standard deviation of 10. Show how the profit will change over the range of the mean for the fixed number of bays you chose in part a. RUBRIC a. Report: 25 points: Your report will be graded on the clarity of your explanation, how well you support your decision, and how you discuss the benefits to the customer of the suggested number of bays. Presentation and neatness will also be a factor in grading the report. b. Spreadsheet: 75 points: Your spreadsheet will be graded on the accuracy of your model, how well the model works (lack of errors), and how well you incorporated all aspects of the data to include relationships and distributions. 1 2 4 5 6 7 8 9 10 11 Daylcars arrived per day 1 87 95 80 92 80 87 111 86 81 84 89 70 103 82 88 88 71 69 80 97 97 79 90 74 85 98 75 79 72 2 3 4 5 6 7 8 9 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 2 3 4 Day cars served per day 1 2 3 4 5 6 5 6 8 7 g 8 10 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 9 45 45 38 36 46 49 42 45 35 39 34 48 46 43 40 43 30 38 40 42 40 40 37 43 35 0 30 35 40 47 1 2 3 4 2282385838 5 6 7 6 7 9 8 10 9 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 20 21 22 24 23 24 25 26 27 28 29 21 26 27 29 Days Serviced at Night 1 8 2 6 3 7 4 10 5 10 10 10 10 9 7 9 10 10 9 10 9 8 10 9 8 10 7 8 10 9 10 7 7 8 30 - - - - 1 Day 1 2 3 4 5 6 7 9 10 11 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 $ 27 26 28 27 29 28 29 30 8 35 30 31 234567890 10 Revenue per car per day 233.45 285.72 334.70 409.74 204.46 366.35 405.14 260.00 515.22 192.00 380.30 213.43 277.49 727.12 124.26 399.00 376.69 364.05 151.38 375.67 465.87 626.89 184.08 160.03 362.45 397.90 341.08 344.87 434.12 466.28