5. A scatter-plot showing the back-up for 1 hour. Back up 25 20 15 Number of Backup Cars 10 0 10 20 30 40 50 60 Minutes Screenshots from Microsoft@ Excel@. Used by permission of Microsoft Corporation. 6. Assume each tollbooth will cost $1 million to construct. Each day there are 5 peak hours of traffic, with an average fare of $2.20 per car, and a cost of $45 per booth per hour for the operator. a. Compute the amount of money you can expect to make (fares - operator cost) during 1 peak hour of traffic for the given number of booths. b. Compute how many days it will take to pay off the construction of your tollbooths. Assume during non-peak hours the costs and fares cancel. 7. A textbox with your recommendation for the number of tollbooths to construct with explanation. Whatever number you recommend, you must explain why you did not choose the number above and below... This project requires applications of the logic functions, IF and VLOOKUP, to a random simulation. Using a probability distribution to determine the number of cars that will arrive at an exit every minute, students have to determine the appropriate number of toll booths to build. They must factor in cost, back-up, and revenue generated in making their decision. This project epitomizes the "informed decision making" that we strive to cultivate in the QR class. Students definitely are wowed by the ability of a spreadsheet to simulate this real world scenario. The model we create is relatively simple, yet powerful enough to help us generate insights into the amount of back-up we can expect for different numbers of toll booths. Number of Booths Constructed: Bins Cutoffs Outputs Number of Cars Processed in 1 minute per booth: 0...12 0 Number of Cars Processed in 1 minute total: 28 .12..49 0.12 23 49..80 D.49 29 .8..94 0.8 33 94.1 0.94 34 Minutes New Arrivals Total Cars Departures |Back-up 23 23 Back up a moo Number of Backup Cars 20 10 50 Minutes Screenshots from Microsoft@ Excel@. Used by permission of Microsoft Corporation