Return to Index
Operations Research Models and Methods
Computation Section

simulation menu

Often systems operate in an iterative manner. Say the manager of a store maintains an inventory holding an expensive product. The manager looks at the inventory at the end of each day to see if a replenishment order should be placed. If the inventory level is low, he will place an order for some number of the product. The manager knows that even if an order is placed, it will not arrive right away. Rather there is some lead-time during which demand may exceed the available inventory and customers will be disappointed.

There are a variety of contradictory considerations that affect the manager's decisions about replenishment orders. First there is a cost for placing an order that discourages too frequent replenishments. Second there is a cost of holding inventory that discourages ordering too many units in a single replenishment. Third there is a cost associated with the customer who is disappointed when there is no product in inventory when the customer wants it.


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.


Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved

Next Page