The stochastic inventory problem
is an example of decision making under
uncertainty. The problem is simply stated and the model of
the game is well known. Still finding a good sequence of ordering
decisions is not easy. Although it is not too hard to obtain
a solution for a specific instance, we need a policy that
describes a set of rules that specifies when an order should
be placed and how much should be ordered. The policy should
be a function of the current and past data concerning the inventory
system. The most general policy would also be a function of
the parameters of the system. Inventory theory provides insights
to this question, but how do we implement a policy on our worksheet
The simplest way to do this is to enter formulas in the control
(green) areas of the solution table. The formulas can address
any cells on the worksheet, but to be realistic, they should
only use information from the current or previous periods.
To enter a formula, the worksheet must be Unprotected. The
commands for Protection are found on the Tools menu
of Excel. Once the formulas are entered, the simulation button
can test the quality of the policy with repeated simulations.
When testing a policy do not click the Restart button.
That button erases the contents of the control column.
The method we have just described is hindered because many
of the rows and columns of the worksheet have been hidden to
make the game easier to play. Also many details of computation
are hidden, rather than confuse the user. For full access to
the contents of the worksheet. Select the Relink command
from the Simulation add-in menu.
The command reveals all the hidden rows and columns
and replaces the buttons with those directly linked to the Simulate
add-in. This is indicated by the red color for the buttons
in the figure below. To understand the workings of this worksheet
refer to the instructions for the Simulation
The worksheet is unprotected and provides much more detailed
information about the situation. Policies can easily be described
by formulas in the control column and tested with the Simulate button.
The backorder model is illustrated, but a similar
worksheet underlies the lost sales game.