Mathematics for Business Decisions, Part 1.5a

1
Mathematics for Business Decisions, Part 1.5a

• Managing ATM Queues

2
Background

• Branch office of People's Bank has 3 ATM's.
• Manager wants to determine what level of service
  to provide how many ATM's to open and how to
  configure the queues.

3
Queuing Models

• Standard Model Queue forms at each server, and
  customer at front of queue is routed to server
  when it becomes available.
• Serpentine Model Single queue forms, and
  customer at front of queue is routed to first
  available server.

4
Advertising Claims

• Manager is considering six possible advertising
  claims
• Mean waiting time is at most 1 minute.
• No one will wait more than 12 minutes.
• At most 5 of customers will be delayed.
• Mean number of people in queue will not exceed 8.
• At most 3 of customers will be irritated.
• Total number present will never exceed 10.

5
Queue Data.xls

• Data on ATM usage for 5 week period.
• Arrival times of customers during 9 a.m. hour on
  Fridays.
• Arrival times of customers during 9 p.m. hour on
  Fridays.
• Service times during first week.

6
Objective

• Based only on 9 a.m. hour on Fridays, how many
  ATMs should be opened and what queuing model
  should be used to validate each advertising claim
  during 9-10 a.m. period?
• Based only on 9 p.m. hour on Fridays, how many
  ATMs should be opened and what queuing model
  should be used to validate each advertising claim
  during 9-10 p.m. period?
• What is the expected cost of the gift certificate
  program? How would this change if it is
  estimated that only 60 of eligible will decide
  to claim the gift?

7
Assumptions

• No one is using an ATM or waiting for a machine
  at start of hour.
• Service times for each ATM have same distribution
  as that sampled in Week 1.
• Time until first arrival and times between
  arrivals of customers have same distribution.
• In standard model, arriving customers enter
  shortest of queues. If queues are same length,
  customer selects queue at random.

8
Random Variables

• A time (in minutes) between consecutive arrivals
  or until first arrival at 9 a.m. hour on Fridays.
• A exp(0.52) Why?
• B time (in minutes) between consecutive arrivals
  or until first arrival at 9 p.m. hour on Fridays.
• B exp(1.92) Why?
• S service time (in minutes)
• Distribution of service times is unknown.

9
Random Variables (continued)

• Let i 1, 2, or 3 ATMs
• Wi waiting time (in minutes) between the arrival
  of a customer during the 9 a.m. hour on Fridays
  and start of his/her service.
• Qi number of people being served, or waiting to
  be served, when a new customer arrives at the 9
  a.m. hour on Fridays.
• Ci total number of people present when a new
  customer arrives during the 9 a.m. hour on
  Fridays.

10
Random Variables (continued)

• Let i 1, 2, or 3 ATMs
• Ui waiting time (in minutes) between the arrival
  of a customer during the 9 p.m. hour on Fridays
  and start of his/her service.
• Ri number of people being served, or waiting to
  be served, when a new customer arrives at the 9
  p.m. hour on Fridays.
• Di total number of people present when a new
  customer arrives during the 9 p.m. hour on
  Fridays.

11
Simulation
My ATMs.xls
12
1 ATM

• Time of arrival of first customer (F35)
• Randomly selected observation from exp(0.52)
• Insert Graph
• -0.52LN(1-RAND())

13
1 ATM (continued)

• Length of service (G35)
• Randomly selected observation from sample of 7634
  service times for Week 1
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)
• Start of service (H35)
• Time of arrival
• F35
• End of service (I35)
• Start of service length of service
• H35G35

14
1 ATM (continued)

• Waiting time (J35)
• Start of service time of arrival
• H35-F35
• Delayed? (K35)
• If waiting time gt 5
• Then yes
• Else no
• IF(J35gt5,"yes","no")
• Number in queue (L35)
• 0

15
1 ATM (continued)

• Time of arrival of second customer (F36)
• Time of arrival of preceding customer randomly
  selected observation from exp(0.52)
• F35-0.52LN(1-RAND())
• Length of service (G36)
• Same as for preceding customer
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)
• Start of service (H36)
• Maximum of time of arrival of current customer
  and end of service for preceding customer
• MAX(F36,I35)

16
1 ATM (continued)

• End of service (I36)
• Same as for preceding customer
• H36G36
• Waiting time (J36)
• Same as for preceding customer
• H36-F36
• Delayed? (K36)
• Same as for preceding customer
• IF(J36gt5,"yes","no")

17
1 ATM (continued)

• Number in queue (L36)
• Number of end of service times that are greater
  than or equal to time of arrival of current
  customer
• DCOUNT(I34I35,,Y35Y36)
• Caution regarding formulas

Y
34
35 (F36ltI35)
18
Summary Statistics

• Mean waiting time
• Maximum waiting time
• Percent delayed
• Mean queue length
• Percent irritated
• Maximum total number present maximum number in
  queue

19
2 ATM's

• Time of arrival of first customer (F35)
• Randomly selected observation from exp(0.52)
• -0.52LN(1-RAND())
• Length of service (G35)
• Randomly selected observation from sample of 7634
  service times for Week 1
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)
• Number in queue for ATM 1 (H35)
• 0
• Number in queue for ATM 2 (K35)
• 0

20
2 ATM's (continued)

• Start of service at ATM 1 (I35)
• If ATM 1 selected
• Then time of arrival
• Else blank
• IF(RANBETWEEN(1,2)1,F35,"")
• End of service at ATM 1 (J35)
• If ATM 1 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(I35),I35G35,0)

21
2 ATM's (continued)

• Start of service at ATM 2 (L35)
• If ATM 1 selected
• Then blank
• Else time of arrival
• IF(ISNUMBER(I35)"",F35)
• End of service at ATM 2 (M35)
• If ATM 2 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(L35),L35G35,0)

22
2 ATM's (continued)

• Waiting time (N35)
• 0
• Delayed? (O35)
• If waiting time gt 5
• Then yes
• Else no
• IF(N35gt5,"yes","no")
• Number in queue (P35)
• Minimum of numbers in queues
• MIN(H35,K35)
• Total number present (R35)
• Sum of numbers in queues
• H35K35

23
2 ATM's (continued)

• Time of arrival of second customer (F36)
• Time of arrival of preceding customer randomly
  selected observation from exp(0.52)
• F35-0.52LN(1-RAND())
• Length of service (G36)
• Same as for preceding customer
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)

24
2 ATM's (continued)

• Number in queue for ATM 1 (H36)
• Number of end of service times at ATM 1 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(J34J35,,AG35AG36)
• Number in queue for ATM 2 (K36)
• Number of end of service times at ATM 2 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(M34M35,,AJ35AJ36)

25
2 ATM's (continued)

• Start of service at ATM 1 (I36)
• If in queue for ATM 1 lt in queue for ATM 2
• Then max. of time of arrival of current
  customer and
• end of service for preceding customer
• Else If in queue for ATM 1 in queue
  for ATM 2
• Then If ATM 1 selected
• Then max. of time
  of arrival of current
• customer and
  end of service for
• preceding
  customer
• Else blank
• Else blank
• If(H36ltK36,MAX(J35J35,F36),IF(H36K36,IF(RANDB
  ETWEEN(1,2)1,MAX(J35J35,F36),""),""))

26
2 ATM's (continued)

• End of service at ATM 1 (J36)
• Same as for preceding customer
• IF(ISNUMBER(I36),I36G36,0)
• Start of service at ATM 2 (L36)
• If ATM 1 selected
• Then blank
• Else max. of time of arrival of current
  customer and end
• of service for preceding customer
• IF(ISNUMBER(I36)"",MAX(M35M35,F36))
• End of service at ATM 2 (M36)
• Same as for preceding customer
• IF(ISNUMBER(L36),L36G36,0)

27
2 ATM's (continued)

• Waiting time (N36)
• Start of service end service at ATM selected
• IF(ISNUMBER(I36),I36-F36,L36-F36)
• Delayed? (O36)
• Same as for preceding customer
• IF(N36gt5,"yes","no")
• Number in queue (P36)
• Same as for preceding customer
• MIN(H36,K36)
• Total number present (R36)
• Same as for preceding customer
• H36K36

28
3 ATM's

• Time of arrival of first customer (F35)
• Randomly selected observation from exp(0.52)
• -0.52LN(1-RAND())
• Length of service (G35)
• Randomly selected observation from sample of 7634
  service times for Week 1
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)
• Number in queue for ATM 1 (H35)
• 0
• Number in queue for ATM 2 (K35)
• 0
• Number in queue for ATM 3 (N35)
• 0

29
3 ATM's (continued)

• Start of service at ATM 1 (I35)
• If ATM 1 selected
• Then time of arrival
• Else blank
• IF(RANBETWEEN(1,3)1,F35,"")
• End of service at ATM 1 (J35)
• If ATM 1 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(I35),I35G35,0)

30
3 ATM's (continued)

• Start of service at ATM 2 (L35)
• If ATM 1 selected
• Then blank
• Else If ATM 2 selected
• Then time of arrival
• Else blank
• IF(ISNUMBER(I35)"",IF(RANDBETWEEN(1,2)1,F35,"")
• End of service at ATM 2 (M35)
• If ATM 2 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(L35),L35G35,0)

31
3 ATM's (continued)

• Start of service at ATM 3 (O35)
• If ATM's 1 or 2 selected
• Then blank
• Else time of arrival
• IF(OR(ISNUMBER(I35),ISNUMBER(L35)),""F35)
• End of service at ATM 3 (P35)
• If ATM 3 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(O35),LO5G35,0)

32
3 ATM's (continued)

• Waiting time (Q35)
• 0
• Delayed? (R35)
• If waiting time gt 5
• Then yes
• Else no
• IF(Q35gt5,"yes","no")
• Number in queue (S35)
• Minimum of numbers in queues
• MIN(H35,K35,N35)
• Total number present (T35)
• Sum of numbers in queues
• H35K35N35

33
3 ATM's (continued)

• Time of arrival of second customer (F36)
• Time of arrival of preceding customer randomly
  selected observation from exp(0.52)
• F35-0.52LN(1-RAND())
• Length of service (G36)
• Same as for preceding customer
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)

34
3 ATM's (continued)

• Number in queue for ATM 1 (H36)
• Number of end of service times at ATM 1 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(J34J35,,AJ35AJ36)
• Number in queue for ATM 2 (K36)
• Number of end of service times at ATM 2 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(M34M35,,AM35AM36)
• Number in queue for ATM 3 (N36)
• Number of end of service times at ATM 3 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(P34P35,,AP35AP36)

35
3 ATM's (continued)

• Start of service at ATM 1 (I36)
• If in queue for ATM 1 lt min. of in queue for
  ATM 2 and in queue for ATM 3
• Then max. of time of arrival of current
  customer and end of service for
• preceding customer
• Else If in queue for ATM 1 gt min. of in
  queue for ATM 2 and in
• queue for ATM 3
• Then blank
• Else If in queue for ATM 2
  in queue for ATM 3
• Then If ATM 1
  selected
• Then
  max. of time of arrival of current customer
• 
  and end of service for preceding customer
• Else
  blank
• Else If ATM 1
  selected
• Then
  max. of time of arrival of current customer
• 
  and end of service for preceding customer
• Else
  blank

36
3 ATM's (continued)

• Start of service at ATM 1 (I36) (continued)
• IF(H36ltMIN(K36,N36),MAX(J35J35,F36),IF(H36gtMIN
  (K36,N36),"",IF(K36N36,IF(RANDBETWEEN(1,3)1,MAX(
  J35J35,F36),""),IF(RANDBETWEEN(1,2)1,MAX(J35
  J35,F36),""))))
• End of service at ATM 1 (J36)
• Same as for preceding customer
• IF(ISNUMBER(I36),I36G36,0)

37
3 ATM's (continued)

• Start of service at ATM 2 (L36)
• If ATM 1 is selected
• Then blank
• Else If in queue for ATM 2 lt in queue
  for ATM 3
• Then max. of time of arrival of
  current customer
• and end of service for
  preceding customer
• Else If in queue for ATM 2
  in queue for ATM 3
• Then If ATM 2 is
  selected
• Then
  max. of time of arrival of current
• 
  customer and end of service for
• 
  preceding customer
• Else
  blank
• Else blank

38
3 ATM's (continued)

• Start of service at ATM 2 (L36) (continued)
• IF(ISNUMBER(I36),"",IF(K36ltN36,MAX(M35M35,F36)
  ,IF(K36N36,IF(RANDBETWEEN(1,2)1,MAX(M35M35,F3
  6),""),"")))
• End of service at ATM 2 (M36)
• Same as for preceding customer
• IF(ISNUMBER(L36),L36G36,0)

39
3 ATM's (continued)

• Start of service at ATM 3 (O36)
• If ATM's 1 or 2 selected
• Then blank
• Else max. of time of arrival of current
  customer and end
• of service for preceding customer
• IF(OR(ISNUMBER(I36),ISNUMBER(L36)),"",MAX(P35P
  35,F36))
• End of service at ATM 3 (P36)
• Same as for preceding customer
• IF(ISNUMBER(O36),O36G36,0)

40
3 ATM's (continued)

• Waiting time (Q36)
• Start of service end service at ATM selected
• IF(ISNUMBER(I36),I36-F36,IF(ISNUMBER(L36),L36-F36
  ),O36-F36))
• Delayed? (R36)
• Same as for preceding customer
• IF(Q36gt5,"yes","no")
• Number in queue (S36)
• Same as for preceding customer
• MIN(H36,K36,N36)
• Total number present (T36)
• Same as for preceding customer
• H36K36N36

41
3 ATM's Serpentine

• Time of arrival of first customer (F35)
• Randomly selected observation from exp(0.52)
• -0.52LN(1-RAND())
• Length of service (G35)
• Randomly selected observation from sample of 7634
  service times for Week 1
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)

42
3 ATM's Serpentine (continued)

• Number ahead at ATM 1 (H35)
• 0
• Start of service at ATM 1 (I35)
• If ATM 1 selected
• Then time of arrival
• Else blank
• IF(RANBETWEEN(1,3)1,F35,"")
• End of service at ATM 1 (J35)
• If ATM 1 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(I35),I35G35,0)

43
3 ATM's Serpentine (continued)

• Number ahead for ATM 2 (K35)
• 0
• Start of service at ATM 2 (L35)
• If ATM 1 selected
• Then blank
• Else If ATM 2 selected
• Then time of arrival
• Else blank
• IF(ISNUMBER(I35)"",IF(RANDBETWEEN(1,2)1,F35,"")
• End of service at ATM 2 (M35)
• If ATM 2 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(L35),L35G35,0)

44
3 ATM's Serpentine (continued)

• Number ahead at ATM 3 (N35)
• 0
• Start of service at ATM 3 (O35)
• If ATM's 1 or 2 selected
• Then blank
• Else time of arrival
• IF(OR(ISNUMBER(I35),ISNUMBER(L35)),""F35)
• End of service at ATM 3 (P35)
• If ATM 3 selected
• Then start of service length of service
• Else 0
• IF(ISNUMBER(O35),LO5G35,0)

45
3 ATM's Serpentine (continued)

• Waiting time (Q35)
• 0
• Delayed? (R35)
• If waiting time gt 5
• Then yes
• Else no
• IF(Q35gt5,"yes","no")
• Number in queue (S35)
• Minimum of 0 and sum of numbers ahead 2
• IF(MIN(H35,K35,N35)0,0,SUM(H35,K35,N35)-2)
• Total number present (T35)
• Sum of numbers ahead
• H35K35N35

46
3 ATM's Serpentine (continued)

• Time of arrival of second customer (F36)
• Time of arrival of preceding customer randomly
  selected observation from exp(0.52)
• F35-0.52LN(1-RAND())
• Length of service (G36)
• Same as for preceding customer
• VLOOKUP(RANDBETWEEN(1,7634),Table_array,2)
• Number ahead at ATM 1 (H36)
• Number of end of service times at ATM 1 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(J34J35,,AJ35AJ36)

47
3 ATM's Serpentine (continued)

• Start of service at ATM 1 (I36)
• If min. of end of service for all preceding
  customers gt time of arrival of current customer
• Then If end of service for preceding
  customer at ATM 1 lt min. of end of service
• for preceding customers at ATM's 2
  3
• Then end of service for
  preceding customer at ATM 1
• Else blank
• Else If end of service for preceding
  customer at ATM 1 gt time of arrival of current
• customer
• Then blank
• Else If min. of end of
  service for preceding customers at ATM's 2 3 gt
• time of arrival of
  current customer
• Then time of
  arrival of current customer
• Else If max. of
  end of service for preceding customers at ATM's 2
• 3 lt
  time of arrival of current customer
• Then
  If ATM 1 selected
• 
  Then time of arrival of current customer
• 
  Else blank
• 
  Else If ATM 1 selected
• 
  Then time of arrival of current

48
3 ATM's Serpentine (continued)

• Start of service at ATM 1 (I36) (continued)
• IF(MIN(MAX(J35J35),MAX(M35M35),MAX(P35P3
  5))gtF36,IF(MAX(J35J35)ltMIN(MAX(M35M35),MAX(
  P35P35)),MAX(J35J35),""),IF(MAX(J35J35)gtF
  36,"",IF(MIN(MAX(M35M35),MAX(P35P35))gtF36,F3
  6,IF(MAX(MAX(M35M35),MAX(P35P35))ltF36,IF(RA
  NDBETWEEN(1,3)1,F36,""),IF(RANDBETWEEN(1,2)1,F36
  ,"")))))
• End of service at ATM 1 (J36)
• Same as for preceding customer
• IF(ISNUMBER(I36),I36G36,0)

49
3 ATM's Serpentine (continued)

• Number ahead at ATM 2 (K36)
• Number of end of service times at ATM 2 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(M34M35,,AM35AM36)
• Start of service at ATM 2 (L36)
• If ATM 1 is selected
• Then blank
• Else If min. of end of service for preceding
  customers at ATM's 2 3 gt time of
• arrival of current customer
• Then If end of service for
  preceding customer at ATM 2 lt end of service
• for preceding customer
  at ATM 3
• Then end of service
  for preceding customer at ATM 2
• Else blank
• Else If end of service for
  preceding customer at ATM 2 gt time of arrival of
• current customer
• Then blank
• Else If end of
  service at ATM 3 lt time of arrival of current
• customer
• Then If
  ATM 2 selected
• 
  Then time of arrival of current customer

50
3 ATM's Serpentine (continued)

• Start of service at ATM 2 (L36) (continued)
• IF(ISNUMBER(I36),"",IF(MIN(MAX(M35M35),MAX(P3
  5P35))gtF36,IF(MAX(M35M35)ltMAX(P35P35),MAX(
  M35M35),""),IF(MAX(M35M35)gtF36,"",IF(MAX(P
  35P35)ltF36,IF(RANDBETWEEN(1,2)1,F36,""),F36))))
  
• End of service at ATM 2 (M36)
• Same as for preceding customer
• IF(ISNUMBER(L36),L36G36,0)

51
3 ATM's Serpentine (continued)

• Number ahead at ATM 3 (N36)
• Number of end of service times at ATM 3 that are
  greater than or equal to time of arrival of
  current customer
• DCOUNT(P34P35,,AP35AP36)
• Start of service at ATM 3 (O36)
• If ATM's 1 or 2 selected
• Then blank
• Else max. of time of arrival of current
  customer and end
• of service for preceding customer
• IF(OR(ISNUMBER(I36),ISNUMBER(L36)),"",MAX(P35P
  35,F36))
• End of service at ATM 3 (P36)
• Same as for preceding customer
• IF(ISNUMBER(O36),O36G36,0)

52
3 ATM's Serpentine (continued)

• Waiting time (Q36)
• Start of service end service at ATM selected
• MAX(I36,O36,L36)-F36
• Delayed? (R36)
• Same as for preceding customer
• IF(Q36gt5,"yes","no")
• Number in queue (S36)
• Same as for preceding customer
• IF(MIN(H36,K36,N36)0,0,SUM(H36,K36,N36)-2)
• Total number present (T36)
• Same as for preceding customer
• H36K36N36

53
Results
54
Results (continued)
Claim Number Required
Mean Wait Mean waiting time is at most 1 min. 3 ATMs
Maximum Wait No one will wait more than 12 minutes. 3 ATMs Serpentine
Percent Delayed At most 5 will be delayed. 3 ATMs
Mean Queue Mean number of people in the queue will not exceed 8. 2 ATMs
Percent Irritated At most 2 will be irritated. 3 ATMs
Maximum Present The total number present will never exceed 10. Cannot be achieved
55
Results (continued)
56
Results (continued)
Claim Number Required
Mean Wait Mean waiting time is at most 1 min. 2 ATMs
Maximum Wait No one will wait more than 12 minutes. 2 ATMs
Percent Delayed At most 5 will be delayed. 1 ATM
Mean Queue Mean number of people in the queue will not exceed 8. 1 ATM
Percent Irritated At most 2 will be irritated. 2 ATMs
Maximum Present The total number present will never exceed 10. 2 ATM's
57
Results (continued)
Our simulations can now be used to find the
expected cost of the mangers proposed gift
certificate plan. With a mean time of 0.52
minutes between arrivals, we would expect to
average approximately 60/0.52 ? 115.4 customers
per hour. Our estimate for the probability that
a customer will be delayed is 0.003. Hence, the
expected number of delayed customers per hour is
115.4?0.003 0.3462. Since the gift
certificates are worth 25, the expected cost of
gift certificates per hour is 25?0.3462 ? 8.66.
Adding the 20 hourly cost for the up-graded
slip dispenser, we find that the expected hourly
cost of the gift certificate program is 28.66.
Experience shows that not everyone who is
delayed and is entitled to a certificate will
actually claim the 25 that is offered. If we
suppose that only 60 of those who are delayed
will redeem their certificates, then the expected
number of customers who will receive and use a
certificate is 0.3462?0.6 ? 0.2077 per hour.