Accounting / Business Excel Problem and Solutions

168 ? Test Item File Chapter Nine ? 167 Excel Problems A local bank has a single drive-in teller window. Customers arrive at the drive-in window following a Poisson distribution at an average rate of 20 per hour. Assume that service is provided at the drive-in window at a rate of 30 customers per hour and that service time follows an exponential distribution. Moreover, assume that the population size is infinite and the queue length is unrestricted. To determine the efficiency of operations, the bank wishes to examine several queue operating characteristics. a. What is the utilization rate of this service system? b. What is the average number of customers in line? c. What is the average time that each customer spends in the queue? d. What is the average time that each customer spends in the system? e. What is the probability that the drive-in window will be idle? Answer: a. 67%; b. 1.33; c. 4 minutes; d. 6 minutes; e. 33.33% A college cafeteria has a single check out lane manned by a single cashier. Students arrive at the cash register at the rate of 2 per minute during peak lunch hour. It takes about 20 seconds to check out each student’s tray. Assume that arrival rate follows a Poisson distribution and service time follows an exponential distribution. To determine the efficiency of operations, the cafeteria manager wishes to examine several queue operating characteristics. a. What is the utilization rate of this service system? b. What is the average number of students in line? c. What is the average time that each student spends in the queue? d. What is the average time that each student spends in the queue and being checked out? e. What is the probability that the cashier will be idle? Answer: a. 67%; b. 1.33; c. 0.67 minutes; d. 1 minute; e. 33.33% A small suburban post office is frequented by customers at the rate of 15 per hour. The post office has a single employee who typically processes each customer’s request at an average rate of 3 minutes per transaction. Assume that arrival rate follows a Poisson distribution and service time follows an exponential distribution. To determine the efficiency of operations, the postmaster wishes to examine several queue operating characteristics. a. What is the utilization rate of this service system? b. What is the average number of customers in line? c. What is the average time that each customer spends in the queue? d. What is the average time that each customer spends in the queue and being serviced? e. What is the probability that the post office employee will be idle? Answer: a. 75%; b. 2.25; c. 9 minutes; d. 12 minutes; e. 25% 4. Refer to problem 3. The postmaster has been receiving complaints from customers about long waiting times. The postmaster is considering hiring an additional employee who will also be able to process each customer’s request at the same rate of 3 minutes per transaction. Assume that the post office pays its employees $12 per hour and that the cost of customer waiting time, in terms of customer dissatisfaction and goodwill, is $10 per hour. Assume that arrival rate follows a Poisson distribution and service time follows an exponential distribution. Should the postmaster hire an additional employee? Compare the total costs of this 2-employee system with the old single-server queuing system. Answer: Total cost with 1 employee = ($10)(3)+($12)(1)=$42/hour Total cost with 2 employees = ($10)(0.8727)+($12)(2)=$32.73/hour Postmaster should higher an additional employee. 5. A fast food restaurant currently has 2 cashiers. Upon arrival, customers form a single line and place their food order at the next available register. Assume that customers arrive at the rate of 35 per hour. It takes an average of 3 minutes to place and process each customer’s order. Assume that arrival rate follows a Poisson distribution and service time follows an exponential distribution. To determine the efficiency of operations, the cafeteria manager wishes to examine several queue operating characteristics. Based upon the analysis, the manager believes that if the average waiting time per customer in the system is greater than 5 minutes, then a request for an additional employee should be made. Should the manager make a request to hire an additional employee? Answer: Yes, current waiting time in the system is 12.8 minutes. 6. Refer to problem 5. Assume that each employee earns an hourly wage of $6.50/hour. Also, assume that the cost of customer waiting time is $10/hour. What would be the impact of hiring an additional employee on customers’ waiting time and total costs? Answer: Wq with 3 employees = 0.80 minutes W with 3 employees = 3.8 minutes. Total cost with 2 employees = ($10)(7.4667)+($6.50)(2)=$87.67/hour Total cost with 3 employees = ($10)(2.2171)+($6.50)(3)=$$41.67/hour 7. Cars arrive at Sparkling Clean automatic car wash at the rate of 15 per hour and follow the Poisson distribution. It takes exactly 3 minutes for each car to go through the automated process of wetting, soaping, scrubbing, and drying. a. What is the average time that each car spends in the queue? b. What is the average time that each car spends in the queue and gets washed? c. What is the probability that the next car that arrives will not have to wait? Answer: a. 4.5 minutes; b. 7.5 minutes; c. 25% 8. Refer to problem 7. Assume that the owners of Sparkling Clean decide that in order to remain competitive in the automatic car wash business, each car should not spend more than 6 minutes in the system. The company that had installed the car wash system told the owners that they would be happy to tweak the timing of the automated washing process to achieve the desired goal of the owners. What should the service rate be? Hint: use Excel’s Goal Seek function. Answer: The service rate should be 21.457 cars/hour or approximately 2.8 minutes per car. In other words, the owners should tell the installing company to shave off approximately 12 seconds off the original 3 minutes per car service time. 9. Cars arriver at Speedy Lube at an average rate of 4 per hour. Arrivals can be assumed to follow the Poisson distribution. The sole mechanic who works at Speedy Lube spends an average of 12 minutes changing each car’s oil and filter. The standard deviation of service time is 2 minutes and service time distribution is arbitrary. Calculate the operating characteristics of the queuing system at Speedy Lube. What is the probability that an arriving customer will have to wait for service? Answer: Probability that an arriving customer will have to wait for service is 80%. 10. Refer to problem 9. Assume that Speedy Lube has been acquired by a new owner who is planning an advertisement campaign that claims that each car would spend an average of 20 minutes in the shop inclusive of waiting time and service time. What should the mechanic’s service rate be so as not to accuse the new owner of false advertising? Hint: use Excel’s Goal Seek function. Answer: The service rate should be 6.046257 cars/hour or approximately a service time of about 10 minutes per car. 11. A technician who works in a computer lab at a local college has the responsibility of maintaining 30 desktop computers. Past records indicate that each desktop needs repair after about 40 hours of use. Breakdowns have been found to be Poisson distributed. The one technician on duty can repair a desktop in an average of 1 hour, following an exponential distribution. a. How many desktops are waiting for service (i.e., in the repair queue), on average? b. What is the average waiting time in the queue? c. What is the average wait in the system? d. What is the probability that all desktops are in good working condition and not needing any repairs? Answer: a. 1.27; b. 1.81 hours; c. 2.07 hours; d. approximately 30%