What

1
Whats in the Focus fileand how do we edit it?

• Project 2, 115a

2
Update Files

• http//business.math.arizona.edu/MBD/mbd.html
• Click on Update Your Files in the upper right-
  hand corner.

3
Update Your Files

• Release 1.5a, 2005
• Are you a licensed user of Release 1.5a, and do
  you still accept the terms of that license
  agreement? YES
• Queuing Files Click and save, then double-click
  queue_focus.exe to extract files.
• You are now ready to begin editing the Queue
  Focus.xls file.

4
1 ATM
Mean Time Between Arrivals Mean Time Between Arrivals Waiting Time Cut-Off (in Minutes) Waiting Time Cut-Off (in Minutes)
0.52 0.52 5 5  

Allowable Customers Allowable Customers Customers and Service Customers and Service Customers and Service Customers and Service Customers and Service
Index Random Number for Simulation Customer Number Time of Arrival After Start of Hour Length of Service Start of Service After Start of Hour End of Service After Start of Hour
1 0.66292292 1 0.57 1.21 0.57 1.78
2 0.77727999 2 1.35 0.78 1.78 2.56
Mean Time Between arrivals, and the cut-ff for
Waiting Time (as weve defined it) in B30E31
for easy reference.

• The Monte Carlo Method (for simulating times
  between arrivals) is broken down into 2 steps in
  the simulation. First, in Column C we just
  generate a probability--a random number between 0
  and 1, using RAND().

5
1 ATM
Customer Number Time of Arrival After Start of Hour
IF(ISNUMBER(F35),B35,"") IF(-B31LN(1-C35)lt60,-B31LN(1-C35),"")
IF(ISNUMBER(F36),B36,"") IF(ISNUMBER(F35),IF(F35-B31LN(1-C36)lt60,F35-B31LN(1-C36),""),"")

• Customer Number
• If we generate a time of arrival before the hour
  is up (in column F), then well give the
  arriviving customer the index number in column B.
• If he arrives after the hour, we wont note him
  at all, and well leave it blank

6
1 ATM
Customer Number Time of Arrival After Start of Hour
IF(ISNUMBER(F35),B35,"") IF(-B31LN(1-C35)lt60,-B31LN(1-C35),"")
IF(ISNUMBER(F36),B36,"") IF(ISNUMBER(F35),IF(F35-B31LN(1-C36)lt60,F35-B31LN(1-C36),""),"")

• Time of Arrival After Start of the Hour
• 1st Customer Using the inverse cdf for our time
  between arrivals distribution,
  , if this number is within the
  first hour, well use this value as his time of
  arrival, otherwise well leave his time blank.
• Subsequent Customers If the previous customer
  arrived before the end of the hour, then well
  try simulating another time between arrivals.
  Well add this time to the previous customers
  time of arrival. If this new time is within the
  first hour, well note this value. Otherwise
  well leave the cell blank.

7
1 ATM
Length of Service
IF(ISNUMBER(F35),VLOOKUP(RANDBETWEEN(1,7634),Data!G45Data!H7678,2),"")
IF(ISNUMBER(F36),VLOOKUP(RANDBETWEEN(1,7634),Data!G45Data!H7678,2),"")

• Bootstrapping
• For each customers length of service, well use
  the bootstrapping method, sampling from our
  records of past service times (Why not use Monte
  Carlo?)
• VLOOKUP and RANDBETWEEN

8
1 ATM
Start of Service After Start of Hour End of Service After Start of Hour
IF(ISNUMBER(F35),F35,"") IF(ISNUMBER(F35),H35G35,"")
IF(ISNUMBER(F36),MAX(F36,I35),"") IF(ISNUMBER(F36),H36G36,"")

• Time of Start of Service (after the hour)
• 1st customer By assumption, nobody is already at
  the ATM when the 1st customer arrives. He may
  start using the ATM as soon as he arrives (IF he
  arrives within 60 minutes of the start of the
  hour).
• Subsequent customers They will either start
  using the ATM as soon as the previous customer
  leaves (end of service time) or when they arrive
  (their time of arrival) if nobody is currently
  using the ATM. They will start the later of the
  two (MAX(F36,I35)). Of course, if they dont
  arrive before 60 minutes though, well leave it
  blank.
• Time of End of Service (after the hour)
• Simply the start of service plus the length of
  service, for all customers.

9
1 ATM
Waiting Time Delayed
IF(ISNUMBER(F35),H35-F35,"") IF(ISNUMBER(F35),IF(J35gtD31,"yes","no"),"")
IF(ISNUMBER(F36),H36-F36,"") IF(ISNUMBER(F36),IF(J36gtD31,"yes","no"),"")

• Waiting Time
• Simply the time between the start of service
  (Column H) and time of arrival (Column F), if the
  customer arrives in the first hour.
• Delayed?
• If the wait time is greater than the cut-off
  (D31), then yes, the customer is delayed.
  Otherwise well note no.

10
1 ATM
Ranges
 
(F36ltI35)
 
(F37ltI35)
Number in Queue
0
IF(ISNUMBER(F36),DCOUNT(I34I35,,Y35Y36),"")
IF(ISNUMBER(F37),DCOUNT(I34I36,,Y37Y38),"")

• Number in Queue
• 1st customer No one is at the ATM when the first
  customer arrives. Therefore, the number in queue
  0
• Subsequent customers If the end of service time
  of previous customers is greater than the time of
  arrival of current customer, then the current
  customer will have to wait. This function counts
  how many End of Service times are after the
  time of arrival.

11
Modifications

• DO NOT ADD OR DELETE CELLS, ROWS, OR COLUMNS! It
  messes up the macros.
• Make note of Queue Focus.xls values
• range of cells for Service Times Data (7,634
  values in Data!G45H7678)
• simulation accommodates 160 customers
• note Excel row number of 161st customer (last row)

12
Modifications

1. Copy your teams Service Times data into
   Data!G45H???? make note of your last service
   time, and clear any extraneous data (i.e. if you
   have 7,600 times, there are 34 more times in the
   original data set. Clear these valuesDO NOT
   DELETE CELLS!)
2. Change your teams average time between arrivals
   in cell B31 of the Simulation sheet.

13
Modifications

• Cell G35 (Length of Service) makes reference to
  the list of service times data. Modify this
  formula to make reference to the range of cells
  that you modified in Step 1.
• Copy this down the column (to row 194).

14
Modifications

• Select the last two full rows, from B193L194.
  Drag the fill handle down until you see that the
  bottom index number is at the number of customers
  you need to accommodate from your initial data.

15
Modifications

• For example, your teams data may look like the
  excerpt above. This specifies that the
  simulation must accommodate 200 arrivals per
  hour. Remember the Focus file only accommodates
  160. You will therefore need to add 40 rows.
  (i.e. whereas customer 160 was in Row 194 of
  Excel, customer 200 should be in Row 234)

16
Modifications

• All formulas from column B to column K should
  fill with appropriate cell references. Column L
  needs extra modifications though.

L
IF(ISNUMBER(F190),DCOUNT(I34I189,,Y343Y344),"")
IF(ISNUMBER(F191),DCOUNT(I34I190,,Y345Y346),"")
IF(ISNUMBER(F192),DCOUNT(I34I191,,Y347Y348),"")
IF(ISNUMBER(F193),DCOUNT(I34I192,,Y349Y350),"")
IF(ISNUMBER(F194),DCOUNT(I34I193,,Y351Y352),"")

• Note the pattern in the cell references
• Y343Y344, Y345Y346, Y347Y348, Y349Y340,

Notice the first cell always starts with an odd
number, the next is an even number. There is no
overlap in these cell ranges. When you copy this
formula down, Excel will just increment each cell
reference by 1 value, not two Y353Y354,
Y354Y355, Y355Y356, . You will need to edit
the rows youve added. Just make sure you are
consistent with the original pattern.
17
Modifications

• Modify the criteria in column Y
• Select the last 4 rows of column Y. Note that
  these were in rows 349-352. Drag the fill
  handle down until you have added 2 times the
  number of rows you had to add before (in our
  example we added 40 rows, so now we need to add
  80. Therefore we should end on row 432.)

 
FALSE
 
FALSE
18
Modifications
 
(F191ltI35)
 
(F192ltI35)
 
(F193ltI35)
 
(F194ltI35)
Of course, when you do this, Excel will again
increment the row values as it sees fit. NOTE
THE PATTERN IN THE ORIGINAL CELLS!

• The first cell reference is an absolute reference
  and counts up by one number every other space.
• The second cell reference stays at I35. This is
  not an absolute reference though.

19
Modifications

• The simulation for one hour is complete for your
  data now. However the Simulation worksheet is
  not complete. We want to generate 2,000 of these
  hours. We do this by simulating 100 hours,
  tallying the results, then run this 20 times and
  tally all these results. We have 20 ? 100
  simulations, in essence giving us 2,000 simulated
  hours.

20
Modifications
Number That Arrive Sum of Waiting Times Maximum Waiting Time
IF(MAX(E35E195)161,"Overflow",MAX(E35E195)) SUM(J35J194) MAX(J35J194)
Number Delayed Sum of Numbers in Queue Maximum Number in Queue
COUNTIF(K35K194,"yes") SUM(L35L194) MAX(L35L194)

1. Modify the summary cells for 100 hours (cells
   N35S35). Whats needs to be changed to account
   for your data?

21
Modifications

1. Click ToolsgtgtMacrogtgtMacrosgtgtOne_ATM then click
   RUN. This should take 8-12 minutes (roughly)
   depending on how fast your computer is. If you
   see in your summary results that you had an
   Overflow, then you need to troubleshoot your
   modifications. Start by making sure you
   accommodated the correct number of customers!

22
Modifications

• At this point, if you do not understand why you
  entered a particular formula into a cell, ASK.
  Although we will most likely not ask you to
  create a simulation from start to finish, you
  should understand the logic involved in each
  column of the spreadsheet!

23
Modifications

1. Modify appropriate cells in 2 ATMs, run the macro
   Two_ATMs.
2. Modify appropriate cells in 3 ATMs, run the macro
   Three_ATMs.
3. Modify appropriate cells in the worksheet 3
   ATMs Serpentine, and run the macro
   Three_ATMs_Serpentine.

24
Modifications

• You will be running 4 simulations (1 ATM- 3 ATMs
  Serpentine), for both hours, giving you a total
  of 8 simulations so far.
• IMPORTANT Create a folder for the first hour
  Delayed simulation. Save this Queue Focus.xls
  file into it. COPY this file into another folder
  for the second hour Delayed simulation. The only
  difference between these two will be the average
  time between arrivals in cell B31. The reason
  for separate folders is that the macros will not
  work if you rename the file itself. At the same
  time, you want to save your work for each hour
  separately so that if you need to re-do one of
  the simulations, you dont have to start over
  again.

25
Modifications

• You may have noticed that the summary cells for
  the 100 and 2,000 hours of simulation only keep
  track of whether or not the customer was delayed,
  average wait time, number in queue, and number
  present. You also have a claim for the number
  irritated. You need a new simulation for this.
  This means that you need 4 more simulations for
  the 1st hour, and 4 more for the 2nd hour.

26
Modifications

• Create new folders for 1st hour Irritated and 2nd
  hour Irritated. Copy your Queue Focus.xls file
  into the 1st hour only. (Well modify this one
  first for the Irritated claim, then copy this
  file into the 2nd hour Irritated folder.)

27
Modifications

• Change from Delayed to Irritated
• Change headers in cells E30, K34, Q34, Q38, V34,
  and V38 to reflect that youre keeping track of
  number of people irritated. (The Cut-Off is now
  in people, not minutes!)
• Cell K35 says IF(ISNUMBER(F35),IF(J35gtD31,"yes
  ","no"),"")
• It is comparing the waiting time to the cut-off
  for Delayed. You want it to compare the number
  in queue (L35) to the Irritated cut-off, which is
  now in D31.

28
Modifications

• Make a summary table like the one in the Focus
  section in the text

29
Modifications

• Well discuss the Gift Certificate program on
  Monday.
• Good luck!