150 5 Multiple-Stage Single-Product Factory Models
Appendix
The appendix of Chap. 3 presented a relatively easy method for simulating a single
workstation containing one processor. In addition the appendix also discussed the
use of Excel in solving linear systems of equations. In this chapter, we extend these
concepts to networks of workstations.
Simulation for a Network of Single-Server Workstations. The use of Excel
for simulating a network of single-server workstations will be demonstrated using
Example 5.3. The concept of the network simulation is to use the equation for the
queueing time delay (Eq. 3.22) and include specific times for arrivals and departures.
Thus, our spreadsheet model is very similar to the spreadsheet example on Page 99
with some extra columns. In the formulas used below, note that all times are in terms
of minutes and the data are the same as used for Example 5.3, namely, a factory with
a serial topology of three workstations is to be simulated.
ABC
1 Inter Arrival-1 Arrive Time-1 Service Time-1
2 00=GAMMAINV(RAND(),0.5,24)
3 =GAMMAINV(RAND(),1.3333,11.25) =B2+A3 =GAMMAINV(RAND(),0.5,24)
DEF G
1 Que Delay-1 Depart Time-1 Inter Arrive-2 Service Time-2
2 0 =B2+C2+D2 =E2 =GAMMAINV(RAND(),1.4286,6.3)
3 =MAX(0,D2+C2-A3) =B3+C3+D3 =E3-E2 =GAMMAINV(RAND(),1.4286,6.3)
HIJK
1 Que Delay-2 Depart Time-2 Inter Arrive-3 Service Time-3
2 0 =E2+G2+H2 =I2 =-13.2
*
LN(RAND())
3 =MAX(0,H2+G2-F3) =E3+G3+H3 =I3-I2 =-13.2
*
LN(RAND())
LMN
1 Que Delay-3 Depart Time-3 System Cycle Time
2 0 =I2+K2+L2 M2-B2
3 =MAX(0,L2+K2-J3) =I3+K3+L3 =M3-B3
Notice that exponential random variates are used for the service times in the third
workstation (Column K) since a gamma distribution with a coefficient of variation
of 1.0 is an exponential distribution. Also, the spreadsheet can be made slightly
more compact by using “Wrap Text” in the first row, and increasing the height of
the first row. As before, the cells A3:N3 should be copied down several thousands
of rows to simulate the system. Finally the average of the values in Column N will
yield the associated estimate for the system mean cycle time.
Equation Generation using Excel. The use of Properties 5.7 and 5.9 is straight
forward, but it can be tedious to implement because the matrix Q and vector b of
Property 5.9 involve several terms. If Excel is going to be used for determining the
inverse, then it can also be used to help generate the coefficients. The Excel example
that follows is the solution to Example 5.7. In order to clearly identify the various
matrices and vectors in the spreadsheet, we label each matrix by placing its identifier