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 add-in 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 add-in 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 User-Defined function: =SIM_rand(-F15) The function is provided by the Simulation add-in 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 three-channel queue constructed by the Queuing add-in 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 add-in, 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 add-in. 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 user-defined function provided by the Random Variables add-in. That add-in 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 add-in 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 x-column and y-column 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 add-in 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.

Operations Research Models and Methods
Internet
by Paul A. Jensen