County Beverage DriveThru Inc operates a chain of beverage s

County Beverage Drive-Thru, Inc. operates a chain of beverage supply stores in Northern Illinois. Each store has a single service lane; cars enter at one end of the store and exit at the other end. Customers pick up soft drinks, beer, snacks, and party supplies without getting out of their cars. When a new customer arrives at the store, the customer waits until the preceding customers order is complete and then drives into the store for service.
Typically three employees operate each store during peak periods; two clerks take and fill orders, and a third clerk serves as cashier and store supervisor. County Beverage is considering a revised store design in which computerized order-taking and payment are integrated with specialized warehousing equipment. Management hopes that the new design will permit operating each store with one clerk. To determine whether the new design is beneficial, management decided to build a new store using the revised design.
County Beverages new store will be located near a major shopping center. Based on experience at other locations, management believes that during the peak late afternoon and evening hours, the time between arrivals follows an exponential probability distribution with a mean of six minutes*. These peak hours are the most critical time period for the company; most of their profit is generated during these peak hours.
An extensive study of times required to fill orders with a single clerk led to the following probability of service times.
Service Time (minutes) Probability
2    0.24
3    0.20
4   0.15
5   0.14
6    0.12
7   0.08
8    0.05
9   0.02
Total 1.00
In case customer waiting times prove too long with just a single clerk, County Beverages management is considering an additional alternative: add a second clerk to help with bagging, taking orders and related tasks. With the second clerk helping with a single channel, service times will be reduced. The following probability distribution describes service times given that option.


*Note: Values from an exponential probability distribution with mean ? can be generated in Excel using the following function: = -?*LN(RAND()), where ?=inter-arrival time
Service Time (minutes) Probability
1 0.20
2 0.35
3 0.30
4 0.10
5 0.05
Total 1.00

County Beverages management would like you to develop a spreadsheet simulation model of the new system and use it to compare the operation of the system using the following two designs:
Design
A One channel, one clerk
B One channel, two clerks


Management is especially concerned with how long customers have to wait for service. Research has shown that 30 percent of the customers will wait no longer than 6 minutes and that 90 percent will wait no longer than 10 minutes. As a guideline, management requires the average waiting time to be less than 1.5 minutes.

Managerial Report

Prepare a report that discusses the general development of the spreadsheet simulation model, and make any recommendations that you have regarding the better store design and staffing plan for County Beverage.

1. List the information the spreadsheet simulation model should generate so that a decision can be made on the store design and the desired number of clerks.
2. Run the simulation for 1000 customers for each alternative considered. You may want to consider making more than one run with each alternative.
3. Be sure to note the number of customers County Beverage is likely to lose due to long customer waiting times with each design alternative.

Solution

xcel worksheets patterned after those used for the Black Sheep Scarves one- and two-server simulations inFigure 15.15 and 15.17 may be used to solve this case problem.We recommend using one workbook withthree 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-Thrusimulation is the treatment of the transient case. In the statistical summary, Black Sheep Scarves excludedthe first 100 customers since the simulation was intended to analyze the long-run effect of the qualityinspection systems. For County Beverage Drive-Thru, management wants to analyze the system onlyduring daily peak hours (4 PM to 10 PM). The analysis of the simulation results should include allcustomer arrivals in the first 360 minutes of the simulation. The logic of the spreadsheet should bemodified accordingly.Single-Server System Operated by 1 ClerkUse the format of the BlackSheep1 simulation model and set up the spreadsheet as follows:Cell formulas modified from BlackSheep1 are as follows:CellFormulaB20=$B$4 * -1 * LN(RAND())CP - 82

F20=VLOOKUP(RAND(),$A$8:$C$15,3,TRUE)C1022=COUNTIF(C20:C1019,\"<360\")C1023=COUNTIFS(E20:E1019,\">0\",C20:C1019,\"<360\")C1024=C1023/C1022C1025=AVERAGEIF(C20:C1019,\"<360\",E20:E1019)C1026=SUMIF(C20:C1019,\"<360\",F20:F1019)/360C1027=COUNTIFS(E20:E1019,\">6\",C20:C1019,\"<360\")C1028=C1027/C1022C1029=COUNTIFS(E20:E1019,\">10\",C20:C1019,\"<360\")C1030=C1029/C1022Cells 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 thefirst 360 minutes of the simulation are included (as these are the ones arriving between 4 PM and 10 PM)