Objectives

• To gain an understanding of queueing systems

• To be able to solve waiting line problems

Learning outcomes

By the end of this tutorial the student should be able to:

• understand what a queueing system is;

• understand how queueing models are useful;

• understand how the information can be graphically represented;

• understand how to read the information from tables;

• prepare a spreadsheet and use it to solve a waiting line problem.

First things first

•    Download the EXCEL file, “WaitingLines_StartFile ”, from canvas and load it onto your H/J Drive.

The problem statement

A manager at a bank is attempting to improve customer satisfaction by offering service such that (1) the average customer waiting time does not exceed 2 minutes and (2) the average queue length is 2 or fewer customers. The bank gets an average of 150 customers each day. Given the existing situation for service and arrival times, as shown in tables below, does the bank meet manager’s criteria?

Simulate  150  customer  arrivals,  their  wating  times,  and  queue  length.  Do  200  runs.  (2000  is best but  we  don’t  want  to  crash  the  labs computers). Does the bank meet the manager’s criteria?

Objective of the study

We would like to answer a number of questions about the queueing system from this study:

1.   What is the average waiting time of customers?

2.   What is the average queue length of customers?

The solution

1) Set 150 customers in column A, note that we start our simulation with a fictitious customer “0” that represents the time until the arrival of the first client.

2) Time between arrivals: The arrival time for the next customer is a random variable between 0 and 5. To generate a random number, we use  the formula RAND() which gives a number between 0 and 1. As example, if RAND() = 0.27, then the next arrival must be 2 (0.27 falls in the       range 0.25 and 0.35). Now, given a random number with RAND(), we need to find the corresponding outcome. To do so, we use the formula LOOKUP(). This formula looks for the first parameter in a range of values and gives as outcome the respective number from the desired range.

And the formula that wrap up this logic is: =LOOKUP(RAND(),$L$2:$L$7,$J$2:$J$7). Place this formula in cell B3 and then drag it down to fill in the time between arrivals of the 150 clients.

3) Arrival time: Is the time between arrivals + the arrival time of the previous client. In cell C3 place the formula =B3+C2 and drag it down until client 150.

4) Start Service: Customers may be served as they arrive or sometimes they have to wait in line. Therefore, the service for the current client      starts when he arrives and the server is free (no waiting time) or when he arrives and waits for the end of the service of the previous client. We use MAX() to get the maximum value between the End of Service of the previous client and the Arrival time of the current client.                        In cell D3 place the formula =max(F2,C3) and drag it down until client 150.

5) Service Time: Same as the Time Between Arrivals, we use the formula LOOKUP. In cell E3 place the formula

=LOOKUP(RAND(),$L$10:$L$13,$J$10:$J$13) and drag it down until client 150.

6) End Service: Start Service + Service Time. In cell F3 place the formula =D3+E3 and drag it down until client 150.

7) Wait Time: This is the difference between Start Time and Arrival Time. In cell G3 place the formula =D3-C3 and drag it down until client 150.

8) Queue Length: We use the formula MATCH. Match returns how many people have already started their service when the customers arrives. Then Customer # - MATCH returns how many people have not started their service and therefore are waiting in line. In the MATCH, if you use a -1, the list put into “match’ needs to be decreasing (ie: largest to smallest) In the MATCH, if you use a 1, the list put into “match’ needs to be increasing (ie: smallest to largest) In the MATCH, if you use a 0, you are looking for an exact match   In cell H3 place the formula =A3-MATCH(C3,D$3:D3,1) and drag it down until client 150. Let see an example. Customer number 9 arrives at min 14, one min later than customer 8. In the Start Service column, we can see that at the      time of 14 the last customer that got served was customer 6, who started his service at the time of 13. Therefore, when our current customer # 9 arrives at min 14, there are 2 customers in the line waiting for service (customer #7 and #8).

8) Average wait time Based on 1 Replication: In cell M16 place the formula =AVERAGE(G3:G152). (from customer# 1)

9) Average queue length Based on 1 Replication: In cell M17 place the formula =AVERAGE(H3:H152). (from customer# 1)