single server queue simulation in excel

Leong: Simpler Spreadsheet Simulation of Multi-Server Queues

176 INFORMS Transactions on Education mann sucht frau über 50 7(2), pp. 172–177, ©2007 INFORMS

worksheets are usually hidden, using Excel’s work-

sheet hide feature (by activating Format/Sheet/Hide

in the menu).

Students are to use worksheet “Scratch” (which is

just a blank worksheet) to start placing basic infor-

mation and laying out the table headers. The desired

result of this is as shown in worksheet “Proto.”

Setting aside their various attempts, the class then

continues on using the “Proto” worksheet provided.

I work interactively with students to develop the for-

mulas to fill the cells for customer 1, taking care

to alter the cell referencing (to absolute, relative or

mixed as needed) so that the customer 1’s row can

later be copied and pasted down to complete the sim-

ulation model.

Some explanation on the generation of random

variates is needed before the inter-arrival time and

service time cells can be filled. The top portion of

worksheet “Data” provides the necessary notes and

formulas. Two simple methods are presented there:

re-sampling and inverse function. These would have

been taught in an earlier class that covers random

variates and Monte-Carlo simulation. I usually use

worksheet “Data” to help review the concepts and

reinforce learning.

The first model to complete is the single-server

model. After evaluating the effect of different traffic

intensities on the queue performance, I proceed to

show how to change the formula in the service-start

cell to make the single-server model into a parametric

multi-server model. I usually take a row of students

in class to role play the queue and explain why the

logic of the service-start time formula is correct. The

students, which should number more than servers,

are assumed to be arranged in order of their arrival.

Students who are supposedly being served would,

one-at-a-time but not necessarily in arrival order, be

asked to leave the system, to emulate the end of their

service. Taking the role of an incoming new arrival,

I would then talk through with the class to establish

the time point at which service should start for me.

Once completed, students are required to tidy up

their work to our prescribed common layout and for-

mat (as shown in Figure 1) and complete the doc-

umentation of the model (as shown in Figure 2)to

improve readability and reduce risk of spreadsheet

errors. The result is a high quality professional work-

able “Decision-Support System.” Students can finally

unhide the last two worksheets and evaluate their

solutions against them.

6. Final Comments

Though the original intent of this paper was to provide

simpler alternative formulations to Hora’s G/G/c

formulation, the model constructed is not restricted

to modeling systems with independent identical dis-

tributions for the arrival and service processes. For

example, the model could be used for trace-driven

simulations, where historical inter-arrival and service

times recorded from real systems are used. In Berger

and Whitt (1992)’s notation, the model presented here

can be characterized as the more general A/A/c/8

queue: arbitrary arrival process, arbitrary service pro-

cess, c servers, and infinite waiting buffer.

Multiple replications of the simulation model

can be done using either a spreadsheet simula-

tion add-in (e.g. XLSim kennenlernen kennen lernen rechtschreibung http://www.analycorp.com/,

Crystal Ball http://www.decisioneering.com/, and

@Risk http://www.palisade.com/) or Excel’s Data-

Table feature. I prefer to do the latter as it does

not incur additional cost for students. Queue perfor-

mance results (like average wait-time, probability dis-

tribution of system length etc.) can be collected for

the numerous simulation runs, under different traffic

intensities and number of servers. In interpreting the

results of multiple replications one needs to keep in

mind that each replication terminates when some pre-

specified number of customers has completed service,

as opposed to when a certain amount of time has

passed.

With the simpler proposed formulation, my col-

leagues and I have for several academic terms con-

ducted classes on spreadsheet queuing simulation

(in an interactive manner, along with modeling of

other business processes) for second-year business,

accountancy, economics, information systems, and

social science undergraduates. Using only native Excel

features, these undergraduates can build reasonably

sophisticated process-driven spreadsheet queuing

models, learn to interpret implications of simulation

results, apply them to concrete real-world situations

and have fun doing it.

Acknowledgments

I have benefited much and the paper is vastly improved by

the many constructive comments and suggestions given by

the associate editor and anonymous referees. Their effort in

providing “developmental review” of this paper is much

appreciated.

Appendix

LARGE

LARGE(range, k) returns the kth largest value in the data

set specified by the range. For example, LARGE(A1:A20, 1)

returns the largest value in the data set contained in

cells A1 through A20 (same result as MAX(A1:A20)),

LARGE(A1:A20, 2) returns the second largest value,

LARGE(A1:A20, 3) returns the third largest value, and so on.

When used against a column with running serial numbers

1, 2, 3,.., it can be used to sort a set of numbers in descending

order.