To
make decisions the manager has only information about the present
and the past. He knows the various parameters associated with
the system. He can observe the current inventory level, has
a record of the demands in each of the previous days and knows
the status of replenishment orders placed previously. He must
use this information to make decisions that will affect the
future.
Excel is useful for modeling and simulating such an iterative
system. We model each iteration as a row on an Excel worksheet.
A model of the inventory system for 50 days will consist of
50 rows. Any particular row holds a model of the various costs
and interactions for a single iteration. The model can include
formulas that reference current information (stored in cells
in the current row) and past information (stored in cells from
previous rows).
When all iterations have the same model it is easy to build
an analysis of an arbitrary number of iterations. We create
a model of the first day on one row or the worksheet. Then
we select the first row and any additional number of rows below
the first. We then select the "Fill Down" command
of Excel. This copies all the formulas from the first row into
all the rows below. Because the copy command creates relative
addresses in the formulas, the interactions modeled in the
first row are maintained for all the following rows. Using
the statistical functions of Excel, summary results for all
rows are easily obtained. The statistics can be used to experiment
with alternative parameters settings. For instance the inventory
manager might use them to find an optimum policy for choosing
the frequency and size of replenishments.
The simulation add-in builds and runs
multiline simulation models. When installed, the menu items
on the figure above appear under the OR_MM menu. We show
the menu with both the Random Variables and Simulation add-ins
installed. The former is often useful when the simulation
model includes random variables.
In the following pages, we describe
the operations of the add-in. The Build Sim item builds
and changes a structure on which a multiline simulation model
can be constructed and run. The Build Model item provides
access to some specific models that are constructed by the
add-in.
The Add Buttons and Remove
Buttons commands are useful for creating a template
or when moving models from one computer to another. Buttons
cause linking problems when a model created on one computer
is moved to another. Use the Remove Buttons command
to remove all the simulation buttons before saving. The Add
Buttons command puts them back. When a workbook uses
functions from the Random Variables add-in, use
the Relink Functions command to fix the links.
There are several other pages on this site where simulation
models are described.
Games can be constructed that use simulation with the control
option. Time advances in one period intervals and the user
(or game player) makes decisions.
|
