Question

Can you help me with CH 16 Case Problem 3 "County Beverage Drive Thru" from Quantitative Analysis for Business, Edition 13?

Answer 1

1-

Excel worksheets patterned after those used for the Black Sheep Scarves one- and two-server simulations in Figure 15.15 and 15.17 may be used to solve this case problem. We recommend using one workbook with three worksheets, one for each of the following Drive-Thru designs: single-server with one clerk, single-server with two clerks, and two-server with 2 clerks.One difference between the Black Sheep Scarves simulation and the County Beverage Drive-Thru simulation is the treatment of the transient case. In the statistical summary, Black Sheep Scarves excluded the first 100 customers since the simulation was intended to analyze the long-run effect of the quality inspection systems. For County Beverage Drive-Thru, management wants to analyze the system only during daily peak hours (4 PM to 10 PM). The analysis of the simulation results should include all customer arrivals in the first 360 minutes of the simulation. The logic of the spreadsheet should be modified accordingly.

The simulation models track the number of customers arriving between 4 PM and 10 PM, the probability that a customer waits, the average waiting time, the clerk utilization, the probability of waiting more than six minutes, and the probability of waiting more than 10 minute.

2-Single-Server System Operated by 1 Clerk

Use the format of the BlackSheep1 simulation model and set up the spreadsheet as follow-

Cell formulas modified from BlackSheep1 are as follows:

Formula

B20 =$B$4 * -1 * LN(RAND())

F20 =VLOOKUP(RAND(),$A$8:$C$15,3,TRUE)

C1022 =COUNTIF(C20:C1019,"<360")

C1023 =COUNTIFS(E20:E1019,">0",C20:C1019,"<360")

C1024 =C1023/C1022

C1025 =AVERAGEIF(C20:C1019,"<360",E20:E1019)

C1026 =SUMIF(C20:C1019,"<360",F20:F1019)/360

C1027 =COUNTIFS(E20:E1019,">6",C20:C1019,"<360")

C1028 =C1027/C1022

C1029 =COUNTIFS(E20:E1019,">10",C20:C1019,"<360")

C1030 =C1029/C1022

Cells B20 and F20 can be copied to fill columns B and F.

Note that in the computation of the single-run summary statistics, only the customers that arrive within the

first 360 minutes of the simulation are included (as these are the ones arriving between 4 PM and 10 PM).

-Single-Server System Operated by 2 Clerks

Similar to the single-server system with one clerk, we use the format of the BlackSheep1 simulation model

(only the service time distribution differs from the single-server with one clerk case). For instance, in cell

F17, the service time is now computed by “=VLOOKUP(RAND(),$A$8:$C$12,3,TRUE)”

Two-Server System Operated by 2 Clerks

We use the format of the BlackSheep2 simulation model. Customer interarrival times and service times are

the same as shown for the single-server operated by one clerk design.

This part of the case is optional in that significant spreadsheet modeling skills are required to duplicate the

2-server simulation model. Selected cell formulas area as follows:

Cell Formula

I20 =G16

J20 =0

I21 =IF(I20=MIN(I20,J20),G21,I20)

J21 =IF(J20=MIN(I20,J20),G21,J20)

Cells I21 and J21 can be copied to fill columns I and J.

3-

The single-server system with 1 clerk system appears unacceptable. The mean waited time is over

5 minutes which exceeds the company guideline of 1.5 minutes. In addition, 33% of customers

waited over 6 minutes and 19% waited over 10 minutes. The expected number of unsatisfied

customers is then:((0.33−0.19)×60 ×0.3)+(0.19× 60 ×0.9)=12.78.The company must do something to improve the service characteristics of its drive-thru operation or face the loss of substantial business.

The single-server system with 2 clerks appears to be the most appropriate design. The mean

waiting time of less than 1 minute is with the company’s guideline of 1.5 minutes. Very few

customers experienced wait times exceeding 6 or 10 minutes. The expected number of unsatisfied

customers is then:(0 ×60 × 0.3)+(0 ×60 × 0.9)=0The performance of the two-server system with 2 clerks is the best overall, but the added cost may not justify the expansion to the two server operation. The mean waiting time of less than 1 minute is with the company’s guideline of 1.5 minutes. Very few customers experienced wait times exceeding 6 or 10 minutes. . The expected number of unsatisfied customers is then:(0 ×60 × 0.3)+(0 ×60 × 0.9)=0

.

*hope above answer helps you.