

Simulation 


General Model 


To build a general simulation model
choose the Build
Sim... item from the Simulation menu.
For this introduction
we are simulating a simple time series that represents the
demand for some product. The model for the time series is described
mathematically by the equation below.
The demand is based on a specified mean demand
and the standard deviation of the random variation term. The
variation is Normally distributed with a mean value of zero.
We require that the time series return a nonnegative integer
as indicated by the brackets in the top expression.


The Build
Sim option calls a
VBA program that presents the dialog shown below. The
dialog requires a name. We provide the name TS_1. The name
should be short and representative of the model. The name
must start with a letter, have no spaces and include no punctuation
except the underline symbol (_). The name cannot represent
a cell on the worksheet. The name must be unique to all other
names used in the workbook. The five fields under the name
determine the number of rows and columns of the display. 


Building the Simulation Form 

The worksheet constructed
by the addin is shown below. The sheet has the name TS_1.
Since several ranges on the sheet are given names prefaced
by the worksheet name, the name cannot be changed once the
sheet is constructed.
Notice that the display has three columns, the first is labeled
"RN. 1 ", and the other two have the labels "Base" and "Demand".
The first column is the Random
Number column. In general, there may be more than one.
The last two columns are the Simulation columns. In
general, there may be many of these. The number of columns
of each type is determined by the associated fields on the
dialog. The columns originally had default names that were
changed to represent the current problem.
At the very top of the display, we see parameters obtained
from the dialog box. They are colored yellow to indicate that
the user should not change them. Rows 10, 11 and
12 hold statistics for the simulation. Row 15 is the initial
row and holds numbers that initialize the formulas of the simulation.
Only one initial row is shown, but the Initial Rows field of
the dialog controls the number. Row 16 of the worksheet holds
row 1 of the simulation. This is an especially important row
as it contains the formulas that describe the situation being
modeled. The 24 rows starting with row 17 are the remaining
iterations of the simulation. The Sample Size field
of the dialog determines how many rows are in this category.
Clicking the Change button located in column A modifies most
of the structural features of the display. Then
the dialog above is again presented. Specifying new values
on the dialog can change all the fields on the dialog, except
the name. 


The contents of several
cells are expanded with the outlined text boxes in the figure
above.
Cell 
Description 
F10 
This cell is in a random number
column of the statistics display. Its content,"***",
indicates that statistics concerning this column are not
maintained. Generally, the addin does not maintain statistics
about random number columns. 
G10 
This cell holds the Excel
function:
=AVERAGE(G16:G40)
It computes the average, or the statistical
mean, of the simulated numbers in the Base column.
Cell H10 holds a similar function for the Demand column.
The contents of the cells in this area are controlled
by the Show Statistics area near the bottom left
of the dialog. Buttons are determine whether no statistics
are computed, only the mean or both the mean and standard
deviation. For a large simulation, computing the mean
and standard deviation of every column may be difficult
computationally. When a single column is to be specified,
there is a field on the dialog in which the column index
is to be specified. Note that the number used in this
field is the simulation column number. 
G11 
This cell holds the Excel
function that computes the standard deviation of the
simulated numbers in the Base column:
=STDEV(G16:G40)
A similar function is provided in cell
H11 for the Demand column. 
G12 
This cell is provided for
the user to specify some other statistical function than "AVERAGE" or "STDEV".
There are a number of statistics one might like to gather
and Excel has a variety of functions for this purpose. 
F13 
This is a "seed" for
the random numbers in the column below F13. The seed is
initially obtained using a random number generated by Excel,
but the user may change the seed. For a positive
seed, the sequence of random numbers will always be the
same. This is useful for some applications. If the Randomize
Seeds button is checked on the dialog, the program
randomly generates a new seed for every run of the simulation. 
F15 
When a simulation is run (by clicking the
Simulate button), the seed in F13 is transferred
into cell F15. This in turn controls the sequence of random
numbers below F15. 
F16 
Here we see a UserDefined function:
=SIM_rand(F15)
The function is provided by the Simulation
addin and has the purpose of generating a uniformly
distributed random number between 0 and 1. The function
uses the random number generator provided by Excel. Notice
that the function points to cell F15. This provides the
connection between the random number generated and the
seed. The function requires a negative argument, thus
we negate the reference to F15. 
F40 
This cell is in the last
row of the column and holds the function:
=SIM_rand(F39)
Thus the contents of cell F40 depend on the contents
of cell F39. All the cells from F16 through F40 have
the same relative reference, so all are controlled by
the seed at the top of the column. 
F15:H15 
This range is
the initial row, which is labeled row 0 in this
case. The random columns will hold the seeds in this
range. The simulation columns will hold initial values
for the simulation. For example, an inventory model would
require an "initial inventory"
quantity. This would be placed in an appropriate column
in the initial row. Some models will require more than
one initial row. 
F16:H16 
This important row holds the
formulas that describe the simulation model. The computer
fills the random columns, but the user must fill the simulation
columns. For some systems, the formulas in these cells
may be very complicated. The complexity of the situation
that can be modeled depends a great deal on the inventiveness
of the user and the user's skill at manipulating Excel
formulas. 

Filling Row 1 

The simulation columns
of row 1 hold formulas that define the iterative simulation model.
The model can be very complex with as many columns as Excel allows.
For example, a multiline simulation model of a threechannel
queue constructed by the Queuing
addin has 19 columns. Below we show the contents of row
1 for the example time series model. 


The model for the
time series example is placed in two cells in row 1, cells
G16 and H16, Cell E16 holds the row index, which is also computed
with a formula.
Cell 
Description 
E16 
This cell holds the row index. The cell
holds the simple expression
=E15+1
The index is one greater than the contents
of the row above. Since the index of the initial row
is 0, the index of this first row is 1. This formula
is automatically provided by the addin, but the user
can change it. For example if the simulation was to show
10 day intervals, the formula could be
=E15+10. 
G16 
This cell computes the value of
Notice that in range (B15:C18) we have
defined the random variable "Base". The
cells in this range were filled by the Add RV command
of the Random Variables addin. The distribution
is specified as a Normal distribution with mean 25 and
standard deviation 5. Simulating from this distribution
is accomplished in cell G16 with the function:
=RV_sim(Base,F16)
RV_sim is a userdefined function
provided by the Random Variables addin. That addin
must be installed in order for this function to be available.
The example illustrates the arguments of the function.
The first argument is the random variable name and the
second is a seed. The seed for the cell G16 points to
F16, the cell just to the left.
If the mean or standard deviation stored
in cells C17 and C18 are changed, the number in cell
G16 changes accordingly. The Random Variables addin
provides a large variety of discrete and continuous probability
distributions. 
G17 
This cell completes the computation of
the required time series value for t=1.
The Excel expression in cell G17 is
=MAX(ROUND(G16,0),0)
where the ROUND function converts the contents
of G16 to an integer and the MAX functions assures that
it is nonnegative.
The contents of cells G16 and G17 could
have been combined into a single column for this simple
example. 

Performing the Simulation 

Clicking the Simulate button
copies the formulas in row 1 into rows 2 through 25. Since relative
addresses are preserved, each row computes the values for one
iteration. The simulation statistics are computed in rows 10
and 11 for the sample. 

Chart 

To make a chart of
the simulation results, select the Chart button at
the top of the page. The dialog is presented below. The xcolumn
and ycolumn numbers determine the columns of the simulation
to be charted. The default values are the first column (holding
the index) and the last column. Various features of the chart
are set in the fields of the dialog.
The chart for the example shows the time series
for 25 periods. (The Line option does not work at this
writing.) The chart shows the simulated time series. 


Larger Simulation Runs 

When developing a
model, it is good practice to keep the sample size small. Here
we have used 25 iterations. During the development, the user
often will add and delete columns. This is easily accomplished
with the Change button. When columns are inserted they
are added in a manner that does not invalidate functions or
references already created.
When the model is perfected however, a larger sample size
is necessary to obtain statistically valid results. Again,
this is easily accomplished by changing the sample size in
the dialog (reached with the Change button). We have increased
the sample size to 1000.
The addin automatically
extends the model by inserting rows and filling the rows with
the expressions in row 1. The figure below shows the results
for 1000 iterations. We have hidden several hundred rows. Since
Excel allows very many rows in the model, the sample size can
be very large. Of course the time required for the simulation
depends on the complexity of the model and the sample size. 


A simulation model created
by this procedure is dynamic. Cells are linked to each other
by active formulas. When a random number seed or a problem parameter
is changed, the entire simulation responds. This allows a variety
of analysis procedures not usually available with a simulation
model. We review these possibilities more fully on a later page. 


