For the car wash simulation model (Example 12.18), use data tables to find the distributions for the maximum number in the queue, maximum waiting time, average waiting time per customer, and total idle time. Use 100 trials for each.

Data from Exercises 12.18

Mike and Judy operate a small car wash; Judy is in charge of finance, accounting, and marketing and Mike is in charge of operations. On a typical Saturday, customers arrive randomly between three and eight minutes apart. A standard wash takes four minutes (the service time), but may run as high as seven minutes for those cars that purchase extra services. The probabilities of the times between arrivals and service times are estimated from historical data. Although customers may complain a bit, they do not leave if they have to wait. 

To simulate this system on a spreadsheet, we need to generate the arrival times of customers and the time required for service. For arrivals, we could specify either the actual times that customers arrive for service or the times between successive arrivals. The second approach works better for simulation purposes because we need only know the time that the last customer arrived to generate the arrival time of the next customer. Therefore, we set the arrival time for a customer as the arrival time of the previous customer plus a random value. We use discrete distributions in column J–M for the time between arrivals and service time, and we use VLOOKUP functions to find these. 

Figure 12.27 shows the spreadsheet model (Excel file Car Wash Simulation). The model simulates 100 customers (roughly one day). We calculate the maximum number in the queue, maximum waiting time, average waiting time per customer, and total idle time. It does not make sense to average the number in the queue or idle time, because these are time-dependent, not customer-dependent. Because this model only simulates one day, you could use data tables to find distributions for these measures. We leave this as an exercise.