An auto maintenance shop suddenly has more customers than it can handle in a given day. The shop currently has one maint

[answerhappygod]

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.

An Auto Maintenance Shop Suddenly Has More Customers Than It Can Handle In A Given Day The Shop Currently Has One Maint 1 (33.73 KiB) Viewed 30 times

Excel Data:

An Auto Maintenance Shop Suddenly Has More Customers Than It Can Handle In A Given Day The Shop Currently Has One Maint 2 (6.17 KiB) Viewed 30 times

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 9 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 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