Queue Simulation

[Hardcastle]

My first time to the boards. Let me introduce myself. I'm a TA for a high school excel based computer science class, and I was asked to model the following problem:

A small store is considering hiring a second checker to join its existing checker. An analysis of the store''s traffic reveals that for each five-minute interval there is a 10% chance of no customers arriving, a 15% chance of one customer arriving, a 15% chance of two customers arriving, a 25% chance of three customers arriving, a 15% chance of four customers arriving, a 10% chance of five customers arriving and a 10% of chance of six customers arriving. Cells G15 and H15 should contain the number of customers the first checker and the second checker can process respectively during a five-minute interval.

Assume that the customers arrive one at a time at the beginning of the five-minute interval and that each customer chooses the shortest line. How long will each line be after 8 hours, and how many customers will each checker process?

I know it's a lot, and here is my worksheet:

I'm doing the following:

1. Creating a listing of times in column A in 5 minute intervals
2. Generating a random number in column B
3. In column C I'm using VLOOKUP to investigate a table I've created in J25:K31 to return the number of customer's arriving each minute.
4. I've created columns for each checker, and the number of customers they can serve in a 5 minute period.
5. The number of customers each checker can serve is in A19 & B19, respectively
6. The number of customers that each checker CANNOT serve goes into a queue (Columns F & G)

I'm having a little difficulty adapting the following into my worksheet:

"Assume that the customers arrive one at a time at the beginning of the five-minute interval and that each customer chooses the shortest line."

Do I need to integrate an IF statement somewhere in my current setup? Once I find that, I can easily determine the line length at the end of 8 hours and the total number of customers served with no problem.

How would I go about setting up my worksheet so that the randomly generated customers go to the shortest line, etc. and the ones that are not served go into a queue?

I truly appreciate your help.

 

[BrianB]

I wonder if you need to worry about the length of each queue.
I would basically focus on the number of customers coming in and being served, and how many in the shop at a particular time.

A bit like my bank balance. There is a starting balance with additions and subtractions.

So for each period the formula is Previous Balance + Incoming - Served. Using your numbers, a single checker would give results like :-

Arrived     Served      In Shop
3           2           1
3           2           2
2           2           2
6           2           6
3           2           7
1           2           6
1           2           5