Queue Simulation

Hardcastle

New Member
Joined
Nov 10, 2005
Messages
2
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:

Queue.gif


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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top