Simpler Simulation of Multi-Server Queues

This workbook comprises templates for learning about modeling of waiting lines. Using
them as reference examples, you can build your own spreadsheet queue simulation
models. Start small and slowly build up your skill and ability to model more complex
queue situations.
Simulated data for inter-arrival and service times can be obtained by either re-sampling
the raw data (giving the empirical distribution) or assuming the exponential distribution
(using the inverse exponential function). Examples of both are included.
The first is a 1-server model. This model can be extended by adding more columns to
become c-server models. For each value of c, a different worksheet has to be made,
thus a non-trivial tedious exercise. This approach is therefore not recommended and no
such examples are included here.

Worksheet "S
the modeling
to reach "Prot

A more scaleable alternative, the parametric c-server model can be built -- you need only
enter the value of c. This c-server model is derived from the 1-server model, changing
We can also s
only cells in its "Service Start" column.

to start figure
should be in e
model, thus c

Some issues to consider

Are utilization and traffic intensity the same?
Which is better, fewer faster servers or more slower servers?

Legend
Decision
Parameter
Historical
Intermediate
Result
Key Result

Output

Input

The remaining
hidden from v
unhidden onc
complete eno

[Link].ms_office/Home

Generating Queue Data

Empirical Distribution

Exponential Distribution
or

uses raw data to generate the required

random variates that mimic the observed situation

if the data follows the Exponential Distribution, we can use

Average

Average

04:48

11:00

Inter-Arrival

Service Time

01:22

18:22

mm:ss

mm:ss

or relevant inputs

Key F9 to simulate new values

Inter-Arrival

Service Time

07:25

09:33

mm:ss

mm:ss

Inter-Arrival Time Data

00:34
01:02
00:32
09:39
00:19
06:13
01:51
01:45
12:34
00:29
03:01
02:10
05:23
07:25
00:01
03:29
07:06
03:25
02:38
01:03
09:59
00:57
07:24
07:38
05:34
01:22
03:40
02:57
00:05
07:47

06:00
04:27
03:00
01:08
09:17
09:21
12:34
00:15
00:22
09:12
07:29
03:33
03:26
11:35
14:08

14:19
04:37
17:27
00:20
04:44
01:24
01:10
01:00
00:02
23:23
13:55
02:52
06:04
06:56
16:37

= Percentile(DataArray, Rand())

12:19
00:36
02:36
03:56
06:02
03:54
01:25
04:21
01:26
00:34
11:12
05:52
10:17
04:05
00:07

= -Average * Ln(Rand())

01:26
03:38
01:32
00:41
03:27
06:26
05:29
01:51
01:24
08:05
02:38
01:47
01:59
07:15
02:17

11:12
06:18
04:03
01:33
02:28
06:58
00:33
05:11
12:09
02:30
24:22
00:30
03:33
03:00
08:51

02:51
20:22
00:11
00:43
07:06
00:54
00:14
01:42
00:35
02:43
00:42
01:27
03:51
11:01
02:49

02:38
00:22
10:27
09:18
01:16
02:59
02:33
04:09
02:22
11:32
28:08
04:52
14:56
08:55
08:15

09:10
04:50
13:16
14:05
04:24
03:34
01:39
08:11
01:07
06:44
06:09
06:49
17:16
01:01
02:09

02:16
00:05
00:00
11:57
10:53
06:38
01:19
01:20
00:04
07:53
08:09
02:19
01:45
00:21
04:28

04:10
12:30
08:04
00:05
03:42
00:06
00:31
08:49
00:54
08:14
02:08
01:57
07:00
01:37
00:04

Service Time Data

13:43
55:39
07:51
01:39
12:55
24:37
01:34
09:38
20:46
30:40
20:15
09:44
22:05
16:28
05:12
01:20
00:09
25:32
06:20
01:06
11:17
15:36
00:13
02:55
38:25
39:42
22:36
11:15
09:33
25:55

08:40
29:50
01:01
00:24
11:28
09:34
06:58
10:14
07:34
08:11
58:03
09:06
13:18
03:58
24:07

12:40
18:20
01:20
06:29
00:11
03:21
00:59
02:32
03:59
13:30
11:30
00:38
13:03
03:23
05:33

08:46
02:21
07:42
05:26
10:49
06:36
10:32
04:30
06:37
14:38
01:17
10:51
05:04
21:04
13:36

03:30
09:05
17:13
14:47
27:31
13:53
01:25
02:35
19:15
02:00
00:39
07:06
09:05
05:44
21:24

04:58
31:35
21:26
10:37
01:57
23:07
35:36
28:03
37:09
02:14
05:23
00:30
19:55
24:54
00:08

07:57
06:47
20:41
13:10
11:20
06:02
00:13
00:54
14:25
14:47
05:19
11:27
00:01
04:32
24:57

[Link].ms_office/Data

Single Server Queuing System

Inputs

Average

Average

05:00

04:00

mm:ss

mm:ss

Traffic Intensity

Utilization

Last

Last

Average

Average

Average

hh:mm

hh:mm

mm:ss

mm:ss

Customer

Inter-Arrival
Time

Service
Time

Arrival
Time

Service
Start
End

Wait
Time

System
Time

System
Length

mm:ss

mm:ss

hh:mm

hh:mm

mm:ss

mm:ss

Results

Working

hh:mm

9:00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

[Link].ms_office/Proto

Single Server Queuing System

Input

Key F9 to simulate

Average

Average

05:00

04:00

mm:ss

mm:ss

Traffic Intensity

Utilization

Last

Last

Average

Average

Average

80.0

76.0

13:49

13:53

06:19

10:47

2.1

hh:mm

hh:mm

mm:ss

mm:ss

Exponential

Exponential

Customer

Inter-Arrival
Time

Service
Time

Arrival
Time

Service
Start
End

Wait
Time

System
Time

System
Length

mm:ss

mm:ss

hh:mm

hh:mm

hh:mm

mm:ss

mm:ss

06:22
22:48
00:15
03:29
03:54
06:14
06:21
01:26
01:11
00:33
00:26
00:38
00:06
14:54
02:54
00:29
06:48
01:49
04:34
06:52
09:21
01:29
01:18
09:17
02:44
08:04
03:32
02:50
06:31
00:33
02:28
11:12
07:12
01:41
04:40
02:58
05:10
05:44
04:41
12:55
01:52

9:00
9:05
9:05
9:10
9:16
9:18
9:20
9:23
9:30
9:31
9:34
9:52
9:53
10:08
10:08
10:10
10:21
10:36
10:46
10:49
10:51
10:55
10:57
11:09
11:22
11:32
11:37
11:47
11:53
11:56
11:57
12:00
12:17
12:32
12:44
12:45
12:51
12:57
12:58
13:02
13:13
13:16

9:05
9:11
9:34
9:34
9:38
9:42
9:48
9:54
9:56
9:57
9:57
9:58
10:08
10:08
10:23
10:26
10:36
10:46
10:49
10:54
11:01
11:10
11:11
11:22
11:32
11:37
11:47
11:53
11:56
12:03
12:03
12:17
12:32
12:44
12:45
12:51
12:57
13:02
13:08
13:13
13:26

9:00
9:11
9:34
9:34
9:38
9:42
9:48
9:54
9:56
9:57
9:57
9:58
9:59
10:08
10:23
10:26
10:27
10:42
10:48
10:54
11:01
11:10
11:11
11:13
11:31
11:34
11:46
11:51
11:56
12:03
12:03
12:06
12:29
12:39
12:45
12:50
12:54
13:02
13:08
13:13
13:26
13:28

00:00
06:14
23:43
18:02
19:32
21:40
25:07
24:12
25:11
23:10
05:55
05:24
00:00
00:03
13:40
04:39
00:00
00:00
00:00
02:45
05:21
12:56
02:21
00:00
00:00
00:00
00:00
00:00
00:00
05:38
03:06
00:00
00:00
00:00
00:36
00:00
00:00
04:16
05:59
00:00
10:45

06:22
29:02
23:58
21:30
23:26
27:54
31:28
25:38
26:22
23:44
06:22
06:02
00:06
14:57
16:33
05:07
06:48
01:49
04:34
09:37
14:42
14:26
03:39
09:17
02:44
08:04
03:32
02:50
06:31
06:10
05:35
11:12
07:12
01:41
05:16
02:58
05:10
10:00
10:41
12:55
12:36

0
1
2
2
3
4
5
6
7
8
4
5
0
1
1
2
0
0
0
1
1
2
2
0
0
0
0
0
0
1
2
0
0
0
1
0
0
1
2
0
1

Result

Working

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

05:25
00:08
05:20
05:56
01:58
01:46
02:47
07:17
00:27
03:12
17:48
00:57
15:41
00:04
01:17
11:55
14:07
10:13
03:26
01:50
04:16
01:45
12:04
13:04
09:18
05:56
09:57
05:41
02:59
00:54
03:04
17:22
14:21
12:00
01:05
06:12
06:02
00:54
04:01
11:27
02:10

[Link].ms_office/1-Server

Working

Customer

Exponential

Exponential

Inter-Arrival
Time

Service
Time

Arrival
Time

Service
Start
End

Wait
Time

System
Time

System
Length

mm:ss

mm:ss

hh:mm

hh:mm

hh:mm

mm:ss

mm:ss

42
43
44
45
46
47
48
49
50

04:11
04:42
01:22
07:08
05:28
02:50
02:57
00:14
04:16

02:38
03:02
00:37
05:23
03:19
08:15
00:10
00:51
00:12

13:20
13:24
13:26
13:33
13:38
13:41
13:44
13:44
13:49

13:28
13:31
13:34
13:34
13:40
13:43
13:51
13:52
13:52

13:31
13:34
13:34
13:40
13:43
13:51
13:52
13:52
13:53

08:26
06:21
08:01
01:31
01:26
01:56
07:14
07:10
03:45

11:03
09:23
08:39
06:54
04:45
10:11
07:24
08:01
03:57

2
3
4
2
1
1
1
2
3

[Link].ms_office/1-Server

to simulate

Documentation
Average Inter-Arrival Time C4 <Input>
Average Service Time D4 <Input>
Opening Time G14 <Input>, also Customer 0 "Service-End" Time
Traffic Intensity C8 =D4/C4*100
Utilization D8 =SUM(D15:D999)/(G8-G14)*100
Last Customer Arrival Time E8 =MAX(E14:E999)
Last Customer Service-End Time G8 =MAX(G14:G999)
Average Time Customer waited H8 =AVERAGE(H15:H999)
Ave Time Customer in System I8 =AVERAGE(I15:I999)
Average # Customer in system J8 =SUMPRODUCT(((J15:J998+1)+J16:J999)/2,C16:C999)/(E8-E14)
Inter-Arrival Time
Service Time
Customer Arrival Time
Service-Start Time
Service-End Time

C20
D20
E20
F20
G20

=-$C$4*LN(RAND())
=-$D$4*LN(RAND())
=E19+C20
=MAX(E20,G19)
=F20+D20

Customer Wait Time H20 =F20-E20

Customer in system Time I20 =G20-E20
# of Customer in system J20 =COUNTIF($G$14:G19,">"&E20)

[Link].ms_office/1-Server

[Link].ms_office/1-Server

Multi-Server Queuing System

Input

Key F9 to simulate

Average

Average

Servers

04:48

11:00

mm:ss

mm:ss

Traffic Intensity

Utilization

Last

Last

Average

Average

Average

76.4

63.6

12:30

12:59

05:23

14:30

3.2

hh:mm

hh:mm

mm:ss

mm:ss

Empirical

Empirical

Customer

Inter-Arrival
Time

Service
Time

Arrival
Time

Service
Start
End

Wait
Time

System
Time

System
Length

mm:ss

mm:ss

hh:mm

hh:mm

hh:mm

mm:ss

mm:ss

12:58
02:04
31:50
13:28
00:02
01:20
02:14
13:36
14:21
10:50
07:33
16:43
00:58
02:00
04:33
10:46
01:43
01:01
13:00
05:22
09:18
05:06
00:23
31:10
08:14
01:25
55:45
00:21
09:33
05:32
16:24
08:01
00:11
07:30
01:04
06:02
03:33
12:29
13:54
02:15
10:50

9:00
9:01
9:02
9:03
9:07
9:19
9:26
9:28
9:34
9:36
9:43
10:00
10:01
10:06
10:06
10:08
10:20
10:29
10:30
10:30
10:30
10:56
10:58
11:04
11:07
11:18
11:31
11:31
11:32
11:32
11:33
11:34
11:37
11:38
11:40
11:40
11:40
11:42
11:43
11:52
11:52
12:05

9:01
9:02
9:03
9:07
9:19
9:26
9:28
9:34
9:36
9:43
10:00
10:01
10:06
10:07
10:08
10:20
10:29
10:30
10:31
10:31
10:56
10:58
11:04
11:07
11:18
11:31
11:31
11:32
11:32
11:38
11:42
11:44
11:52
11:52
11:58
12:00
12:00
12:03
12:06
12:16
12:18

9:00
9:14
9:05
9:35
9:21
9:19
9:27
9:30
9:48
9:51
9:54
10:08
10:18
10:07
10:09
10:12
10:31
10:31
10:31
10:44
10:36
11:05
11:03
11:05
11:38
11:27
11:32
12:26
11:32
11:42
11:44
11:58
11:52
11:52
12:00
12:00
12:06
12:03
12:16
12:19
12:18
12:29

00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:53
00:00
00:00
00:00
00:00
01:00
01:12
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:00
00:15
05:34
07:45
07:02
13:54
12:30
18:50
19:23
17:57
20:03
13:31
23:27
13:12

12:58
02:04
31:50
13:28
00:02
01:20
02:14
13:36
14:21
10:50
07:33
16:43
00:58
02:52
04:33
10:46
01:43
01:01
14:00
06:35
09:18
05:06
00:23
31:10
08:14
01:25
55:45
00:21
09:48
11:06
24:10
15:03
14:05
20:01
19:54
25:24
21:29
32:32
27:25
25:43
24:02

0
1
2
2
2
1
1
1
1
2
0
1
2
3
2
0
1
2
3
4
0
1
1
0
1
1
2
2
3
3
4
5
6
6
7
8
9
9
8
8
5

Result

Working

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

01:33
01:24
00:19
04:27
11:32
06:59
01:59
06:16
02:24
06:37
16:56
01:20
05:04
00:06
01:25
12:17
09:10
00:21
00:01
00:01
26:12
01:50
06:27
02:58
11:13
12:17
00:04
01:26
00:06
00:29
01:34
02:31
01:09
01:35
00:05
00:32
01:28
01:26
09:00
00:05
12:30

[Link].ms_office/c-Server

Working

Customer

Empirical

Empirical

Inter-Arrival
Time

Service
Time

Arrival
Time

Service
Start
End

Wait
Time

System
Time

System
Length

mm:ss

mm:ss

hh:mm

hh:mm

hh:mm

mm:ss

mm:ss

42
43
44
45
46
47
48
49
50

06:11
03:27
02:08
01:03
02:58
06:40
00:04
01:48
00:41

13:08
07:13
00:18
11:29
01:01
24:54
21:26
00:21
00:27

12:11
12:14
12:16
12:17
12:20
12:27
12:27
12:29
12:30

12:19
12:26
12:29
12:29
12:33
12:34
12:34
12:40
12:41

12:33
12:34
12:29
12:40
12:34
12:59
12:55
12:41
12:41

08:39
12:12
12:17
11:31
12:12
06:32
06:32
11:30
11:10

21:47
19:25
12:34
23:01
13:13
31:26
27:57
11:52
11:38

5
6
6
7
6
6
7
7
7

[Link].ms_office/c-Server

to simulate

Question
Why would the following not work properly?
Service Start-time F20 =MAX(E20, LARGE($G$14:G19,$E$4))

Documentation
Average Inter-Arrival Time
Average Service Time
Number of Servers
Opening Time

C4
D4
E4
G14

=AVERAGE(Data!B13:K27)
=AVERAGE(Data!B30:K44)
<Input>, validated as whole numbers > 0
<Input>, also Customer 0 "Service-End" Time

Traffic Intensity C8 =(D4/E4)/C4*100

Utilization D8 =SUM(D15:D999)/(E4*(G8-G14))*100
Last Customer Arrival Time E8 =MAX(E14:E999)
Last Customer Service-End Time G8 =MAX(G14:G999)
Average Time Customer waited H8 =AVERAGE(H15:H999)
Ave Time Customer in System I8 =AVERAGE(I15:I999)
Average # Customer in System J8 =SUMPRODUCT(((J15:J998+1)+J16:J999)/2,C16:C999)/(E8-E14)
Inter-Arrival Time
Service Time
Customer Arrival Time
Service-Start Time
Service-End Time

C20
D20
E20
F20
G20

=PERCENTILE(Data!$B$13:$K$27, RAND())
=PERCENTILE(Data!$B$30:$K$44,RAND())
=E19+C20
=MAX(E20, IF(B20>=$E$4,LARGE($G$14:G14,$E$4),0))
=F20+D20

Customer Wait Time H20 =F20-E20

Customer in System Time I20 =G20-E20
# of Customer in System J20 =COUNTIF($G$14:G19,">"&E20)

Another formulation for F20

Service-Start Time F20 =IF(J20>=$E$4,LARGE($G$14:G19,$E$4),E20)

[Link].ms_office/c-Server

[Link].ms_office/c-Server

Number of Customers in the System vs Time

0

47

48

49

50

J64
J62

J16

Customer

Customers in System

J63

J17

J15
E14

E15
C15

E16
C16

E17
C17

E62

E62
C62

E63
C63

E64
C64

Arrival Times
Inter-Arrival Times

Area under the curve (shaded grey) SUMPRODUCT((J15:J63+1)+J16:J64)/2,C16:C64))

Since
Area under the curve between customer 1 and 2 arrivals ((J15 + 1) + J16)/2 * C16
Area under the curve between customer 2 and 3 arrivals ((J16 + 1) + J17)/2 * C17
Area under the curve between customer 48 and 49 arrivals ((J62 + 1) + J63)/2 * C63
Area under the curve between customer 49 and 50 arrivals ((J63 + 1) + J64)/2 * C64
Dividing the area under the curve by (E64 - E14) gives the required results. Simplifying by setting cell E8 = E64 and
extending to more rows gives
Average number of customers in the system SUMPRODUCT((J15:J998+1)+J16:J999)/2,C16:C999))/(E8-E14)

[Link].ms_office/Notes