A savings bank will open several new branch banks during the coming year. Each n

Question

A savings bank will open several new branch banks during the coming year. Each new branch is designed to have one automated teller machine (ATM). A concern is that during busy periods several customers may have to wait to use the ATM. This concern has led the bank to undertake a study of the ATM waiting line system. The bank’s vice president wants to determine whether one ATM will be sufficient. The bank has established service guidelines for its ATM system stating that the average customer waiting time for an ATM should be one minute or less.

One probabilistic input to the ATM simulation model is the arrival times of customers who want to use the ATM. In waiting line simulations, arrival times are determined by randomly generating the time between two successive arrivals, referred to as the inter-arrival time. For the branch bank being analyzed, the customer inter-arrival times are uniformly distributed between 0 and 5 minutes. The handout, Spreadsheet Simulation, shows that the continuous uniform distribution has the random number generator

a + (b-a)*Rand()

where a<b and a and b represent the minimum and maximum bounds of the distribution, respectively. For the branch bank being studied the inter-arrival times are uniformly distributed with a minimum value of 0 and a maximum value of 5.

Assume that the simulation run begins at time = 0. The inter-arrival time is randomly generated using the formula above for the continuous uniform distribution with a = 0 and b = 5. That value represents the arrival time of the first customer. Let’s assume that value is 1.4. Thus, the first customer arrives 1.4 minutes after the simulation begins. The second number generated from the continuous uniform function represents how long until the second customer arrives. Suppose this number is 1.3. This means that customer 2 arrives 1.3 minutes after customer 1 arrives. Thus, customer 2 arrives at 1.4 + 1.3 = 2.7 minutes after the simulation begins.

Another probabilistic input in the ATM simulation is the service time, which is the time the customer spends using the ATM. The service time does not include waiting time. Past data from similar ATMs indicates that a normal probability distribution with a mean of 2 minutes and a standard deviation of 0.5 minutes can be used to describe service times. In Excel, the function = NORM.INV(Rand(),2,0.5) is used to generate the service times.

The probabilistic inputs are the inter-arrival times and the service times. The controllable input is the number of ATMs used. The output will consist of the various operating characteristics such as the probability of waiting, the average waiting time, the maximum waiting time and so on.

Set up and run a simulation analysis of the bank’s ATM study. Run your simulation for 1,000 customers. Use the following layout for your model.

Customer

Inter-arrival Time

Arrival Time

Service Start Time

Waiting Time

Service Time

Completion Time

Time In System

1

generated

generated

The Inter-arrival times and the service times are generated using the random number generators. The other cells are calculations. You will need to use some If statements in some of them. Hint: Waiting time is equal to starting time minus arrival time.

Once you have run the simulation for 1,000 customers compute the following statistics:

(Treat the first 100 customers as a warm-up period to reach steady state. That is, don’t include them in your summary calculations.)

Helpful Hint for Simulating ATM Service Time. Make an input section. Two inputs will be the mean service time, µ, and the standard deviation of service time, . The service time for each customer to the ATM is modeled using the formula:

=NORM.INV(rand(), µ,).

You can enter the rand() function directly into the NORM.INV function. Note the first input to the NORM.INV function is a probability. Because rand() returns a value between 0 and 1 it is essentially our probability. In this way we can sample from a normal distribution of service times.

Customer

Inter-arrival Time

Arrival Time

Service Start Time

Waiting Time

Service Time

Completion Time

Time In System

1

generated

generated

Explanation / Answer

1

0.26

0.26

0.00

1.68

Note : Complete table not provided due to limitation of characters

Summary Calculations :

Customer Inter-arrival Time Arrival Time Service Start Time Waiting Time Service Time Completion Time Time In System

1

0.26

0.26

0.26

0.00

1.68

1.94 1.68 2 1.53 1.79 1.94 0.15 1.04 2.98 1.19 3 0.39 2.17 2.98 0.81 1.73 4.70 2.53 4 2.27 4.45 4.70 0.26 1.11 5.81 1.36 5 4.42 8.87 8.87 0.00 2.89 11.76 2.89 6 2.45 11.31 11.76 0.44 1.76 13.52 2.20 7 3.76 15.07 15.07 0.00 1.91 16.98 1.91 8 4.71 19.79 19.79 0.00 2.38 22.17 2.38 9 1.45 21.24 22.17 0.93 2.19 24.36 3.12 10 2.17 23.41 24.36 0.94 3.01 27.36 3.95 11 0.48 23.89 27.36 3.47 2.83 30.19 6.30 12 1.77 25.66 30.19 4.53 2.32 32.51 6.85 13 3.98 29.63 32.51 2.87 2.61 35.12 5.48 14 2.59 32.22 35.12 2.90 1.98 37.10 4.87 15 3.20 35.42 37.10 1.68 1.50 38.59 3.17 16 3.04 38.46 38.59 0.13 1.95 40.55 2.09 17 2.99 41.44 41.44 0.00 1.52 42.97 1.52 18 3.74 45.18 45.18 0.00 1.61 46.80 1.61 19 0.12 45.30 46.80 1.50 2.05 48.84 3.54 20 1.36 46.66 48.84 2.19 1.44 50.28 3.62 21 0.19 46.85 50.28 3.43 2.73 53.01 6.16 22 2.50 49.35 53.01 3.65 2.05 55.06 5.70 23 2.13 51.49 55.06 3.57 1.22 56.27 4.79 24 1.98 53.47 56.27 2.80 0.92 57.19 3.72 25 3.42 56.90 57.19 0.30 1.89 59.09 2.19 26 1.96 58.85 59.09 0.23 2.48 61.56 2.71 27 2.82 61.67 61.67 0.00 2.33 64.01 2.33 28 3.30 64.98 64.98 0.00 1.93 66.91 1.93 29 2.80 67.78 67.78 0.00 1.83 69.61 1.83 30 3.51 71.28 71.28 0.00 2.26 73.54 2.26 31 0.18 71.46 73.54 2.08 2.47 76.01 4.56 32 2.14 73.60 76.01 2.42 1.36 77.37 3.77 33 2.80 76.40 77.37 0.97 1.89 79.27 2.87 34 1.28 77.68 79.27 1.59 2.48 81.75 4.07 35 1.57 79.26 81.75 2.50 1.40 83.15 3.90 36 4.61 83.86 83.86 0.00 2.38 86.25 2.38 37 2.92 86.78 86.78 0.00 3.32 90.10 3.32 38 0.84 87.62 90.10 2.48 1.97 92.06 4.44 39 0.76 88.38 92.06 3.68 2.87 94.94 6.55 40 0.69 89.07 94.94 5.86 1.60 96.54 7.47 41 2.01 91.08 96.54 5.46 2.88 99.41 8.34 42 1.70 92.78 99.41 6.63 1.81 101.23 8.44 43 3.38 96.16 101.23 5.07 2.14 103.37 7.21 44 3.36 99.52 103.37 3.84 2.86 106.23 6.71 45 4.03 103.55 106.23 2.68 2.44 108.67 5.12 46 0.79 104.34 108.67 4.33 2.00 110.67 6.33 47 4.81 109.15 110.67 1.52 2.28 112.96 3.81 48 0.56 109.71 112.96 3.25 2.25 115.21 5.50 49 0.32 110.03 115.21 5.17 2.10 117.31 7.28 50 1.20 111.23 117.31 6.08 1.25 118.56 7.33 51 1.91 113.14 118.56 5.42 1.96 120.52 7.38 52 0.72 113.86 120.52 6.66 0.63 121.16 7.30 53 1.06 114.92 121.16 6.24 2.84 124.00 9.08 54 3.46 118.38 124.00 5.61 2.12 126.11 7.73 55 2.41 120.79 126.11 5.32 1.87 127.98 7.19 56 0.21 121.00 127.98 6.98 2.94 130.92 9.92 57 1.05 122.06 130.92 8.87 2.57 133.50 11.44 58 3.77 125.83 133.50 7.66 2.71 136.21 10.38 59 3.36 129.19 136.21 7.02 1.79 137.99 8.81 60 3.49 132.67 137.99 5.32 2.32 140.32 7.64 61 2.57 135.24 140.32 5.08 1.81 142.13 6.89 62 1.15 136.39 142.13 5.74 1.91 144.04 7.64 63 2.72 139.12 144.04 4.92 1.34 145.38 6.27 64 4.06 143.18 145.38 2.20 2.43 147.81 4.63 65 0.29 143.47 147.81 4.34 1.70 149.51 6.04 66 3.19 146.66 149.51 2.85 1.80 151.31 4.65 67 0.29 146.95 151.31 4.35 1.57 152.88 5.93 68 2.36 149.32 152.88 3.56 1.46 154.34 5.03 69 1.10 150.42 154.34 3.93 2.85 157.20 6.78 70 2.34 152.75 157.20 4.44 2.14 159.34 6.58 71 1.97 154.72 159.34 4.61 1.40 160.74 6.02 72 3.48 158.21 160.74 2.53 1.80 162.54 4.33 73 3.83 162.04 162.54 0.50 1.08 163.62 1.58 74 2.25 164.29 164.29 0.00 2.18 166.47 2.18 75 3.78 168.07 168.07 0.00 1.85 169.92 1.85 76 0.78 168.85 169.92 1.08 2.27 172.19 3.34 77 1.63 170.48 172.19 1.71 2.09 174.28 3.80 78 1.32 171.80 174.28 2.47 2.08 176.36 4.55 79 4.52 176.32 176.36 0.03 1.40 177.76 1.43 80 3.50 179.83 179.83 0.00 0.89 180.72 0.89 81 2.17 181.99 181.99 0.00 2.05 184.04 2.05 82 2.76 184.76 184.76 0.00 2.37 187.12 2.37 83 3.50 188.26 188.26 0.00 2.28 190.54 2.28 84 4.35 192.61 192.61 0.00 2.37 194.98 2.37 85 3.17 195.78 195.78 0.00 2.01 197.79 2.01 86 3.48 199.26 199.26 0.00 2.98 202.24 2.98 87 2.15 201.42 202.24 0.83 1.80 204.05 2.63 88 0.07 201.48 204.05 2.56 1.94 205.98 4.50 89 3.66 205.14 205.98 0.84 1.86 207.84 2.70 90 1.09 206.23 207.84 1.61 1.76 209.60 3.37 91 1.10 207.33 209.60 2.27 1.23 210.83 3.50 92 1.02 208.34 210.83 2.48 1.85 212.68 4.34 93 3.41 211.76 212.68 0.92 1.78 214.47 2.71 94 1.16 212.92 214.47 1.55 2.52 216.99 4.08 95 1.40 214.32 216.99 2.67 1.85 218.84 4.53 96 2.95 217.27 218.84 1.57 2.72 221.57 4.30 97 0.79 218.06 221.57 3.51 1.97 223.54 5.48 98 4.33 222.39 223.54 1.15 1.77 225.31 2.92 99 1.57 223.96 225.31 1.35 2.24 227.54 3.59 100 1.73 225.69 227.54 1.86 2.56 230.10 4.42 101 1.65 227.34 230.10 2.77 1.74 231.84 4.51 102 0.13 227.47 231.84 4.37 1.81 233.65 6.18 103 4.06 231.53 233.65 2.12 1.51 235.16 3.63 104 4.31 235.84 235.84 0.00 0.75 236.59 0.75 105 4.54 240.38 240.38 0.00 1.55 241.93 1.55 106 2.19 242.57 242.57 0.00 1.73 244.30 1.73 107 2.84 245.40 245.40 0.00 2.53 247.93 2.53 108 1.80 247.20 247.93 0.72 2.17 250.10 2.89 109 4.67 251.87 251.87 0.00 1.22 253.09 1.22 110 1.78 253.66 253.66 0.00 2.00 255.65 2.00 111 0.78 254.43 255.65 1.22 2.39 258.04 3.61 112 2.81 257.25 258.04 0.79 1.63 259.67 2.42 113 1.96 259.21 259.67 0.46 1.92 261.58 2.37 114 4.35 263.56 263.56 0.00 2.14 265.70 2.14 115 3.41 266.97 266.97 0.00 1.62 268.59 1.62 116 2.91 269.88 269.88 0.00 1.95 271.82 1.95 117 0.84 270.72 271.82 1.11 1.53 273.35 2.63 118 0.83 271.55 273.35 1.80 1.45 274.80 3.25 119 2.82 274.37 274.80 0.43 1.77 276.57 2.20 120 3.46 277.83 277.83 0.00 1.85 279.68 1.85 121 1.77 279.59 279.68 0.09 3.27 282.95 3.36 122 3.58 283.18 283.18 0.00 0.98 284.16 0.98 123 3.21 286.38 286.38 0.00 2.05 288.43 2.05 124 0.65 287.03 288.43 1.40 2.28 290.71 3.68 125 2.36 289.39 290.71 1.32 2.08 292.79 3.40 126 3.71 293.10 293.10 0.00 1.65 294.75 1.65 127 1.92 295.01 295.01 0.00 1.17 296.19 1.17 128 2.95 297.97 297.97 0.00 2.29 300.25 2.29 129 3.13 301.09 301.09 0.00 2.51 303.61 2.51 130 4.43 305.53 305.53 0.00 2.16 307.69 2.16 131 2.45 307.97 307.97 0.00 1.81 309.78 1.81 132 2.80 310.77 310.77 0.00 2.40 313.17 2.40 133 0.54 311.31 313.17 1.86 2.45 315.62 4.30 134 2.57 313.89 315.62 1.73 2.18 317.80 3.91 135 3.24 317.13 317.80 0.67 1.78 319.58 2.45 136 0.78 317.90 319.58 1.68 3.07 322.65 4.75 137 2.56 320.46 322.65 2.19 1.88 324.53 4.07 138 2.53 322.99 324.53 1.54 2.21 326.74 3.75 139 3.73 326.72 326.74 0.02 1.82 328.55 1.83 140 3.13 329.85 329.85 0.00 2.07 331.93 2.07 141 4.29 334.15 334.15 0.00 2.46 336.60 2.46 142 4.94 339.09 339.09 0.00 1.72 340.81 1.72 143 1.55 340.63 340.81 0.18 2.19 343.00 2.37 144 0.82 341.46 343.00 1.54 2.01 345.01 3.55 145 3.94 345.40 345.40 0.00 1.22 346.62 1.22 146 4.51 349.91 349.91 0.00 2.64 352.55 2.64 147 2.22 352.12 352.55 0.43 1.04 353.59 1.47 148 0.93 353.05 353.59 0.54 2.58 356.17 3.13 149 1.69 354.74 356.17 1.43 1.99 358.16 3.42 150 3.93 358.67 358.67 0.00 2.58 361.25 2.58 151 0.39 359.06 361.25 2.19 2.11 363.36 4.30 152 3.69 362.75 363.36 0.61 1.54 364.90 2.15 153 1.68 364.43 364.90 0.47 1.57 366.47 2.04 154 3.62 368.05 368.05 0.00 2.49 370.54 2.49 155 3.89 371.94 371.94 0.00 1.97 373.91 1.97 156 1.02 372.96 373.91 0.95 3.19 377.10 4.14 157 4.71 377.67 377.67 0.00 1.38 379.05 1.38 158 4.14 381.80 381.80 0.00 1.10 382.91 1.10 159 4.50 386.30 386.30 0.00 1.93 388.23 1.93 160 4.60 390.90 390.90 0.00 1.67 392.58 1.67 161 1.94 392.84 392.84 0.00 3.11 395.95 3.11 162 0.89 393.73 395.95 2.22 1.38 397.33 3.60 163 2.01 395.74 397.33 1.59 2.46 399.79 4.05 164 4.57 400.31 400.31 0.00 2.32 402.63 2.32 165 2.02 402.33 402.63 0.30 1.40 404.03 1.70 166 3.16 405.49 405.49 0.00 1.78 407.27 1.78 167 0.26 405.75 407.27 1.52 1.72 408.99 3.24 168 1.90 407.65 408.99 1.34 2.54 411.53 3.88 169 2.07 409.72 411.53 1.81 0.99 412.52 2.80 170 1.95 411.67 412.52 0.86 2.61 415.13 3.47 171 1.69 413.36 415.13 1.78 3.11 418.24 4.89 172 0.37 413.73 418.24 4.51 2.59 420.84 7.11 173 4.95 418.68 420.84 2.15 1.65 422.49 3.80 174 3.70 422.39 422.49 0.10 1.38 423.87 1.48 175 4.33 426.71 426.71 0.00 1.99 428.70 1.99 176 1.28 428.00 428.70 0.71 2.06 430.77 2.77 177 3.12 431.12 431.12 0.00 2.46 433.58 2.46 178 4.46 435.57 435.57 0.00 2.24 437.81 2.24 179 2.51 438.09 438.09 0.00 2.41 440.49 2.41 180 1.86 439.95 440.49 0.55 2.09 442.58 2.63 181 1.62 441.56 442.58 1.02 2.12 444.70 3.13 182 1.35 442.92 444.70 1.78 1.81 446.51 3.59 183 0.37 443.29 446.51 3.22 1.71 448.21 4.93 184 0.51 443.79 448.21 4.42 2.35 450.56 6.77 185 0.47 444.26 450.56 6.30 2.18 452.74 8.48 186 1.20 445.46 452.74 7.29 2.47 455.21 9.75 187 4.37 449.83 455.21 5.38 2.47 457.68 7.85 188 4.69 454.52 457.68 3.16 1.43 459.11 4.59 189 4.05 458.57 459.11 0.54 1.54 460.65 2.08 190 2.94 461.51 461.51 0.00 2.03 463.54 2.03 191 3.92 465.43 465.43 0.00 1.77 467.20 1.77 192 4.85 470.28 470.28 0.00 2.61 472.89 2.61 193 2.61 472.89 472.89 0.00 2.58 475.47 2.58 194 3.74 476.64 476.64 0.00 2.75 479.38 2.75 195 1.38 478.02 479.38 1.37 1.24 480.62 2.61 196 4.95 482.97 482.97 0.00 1.90 484.86 1.90 197 3.61 486.58 486.58 0.00 1.70 488.28 1.70 198 0.55 487.13 488.28 1.15 1.91 490.19 3.06 199 0.39 487.52 490.19 2.67 1.79 491.98 4.46 200 3.12 490.64 491.98 1.34 2.42 494.40 3.76

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.