Operations Research Models and Methods / Computation

Decision Analyis

With an empty Excel worksheet and the Decision Add-in loaded, we create this first decision node by selecting Add Decision from the OR/MS menu. During the node definition stage of the process, make the Excel calculation mode Manual, otherwise you will receive error messages. The dialog box defining the decision is below.

The Cell entry provides the location of the upper left corner of the range that will define the decision on the worksheet. The content of the cell is the current cursor location, but it may be changed. Type indicates whether the goal of the decision is to maximize or minimize. The Name identifies the range holding the decision information on the worksheet. It must be different than any other range names used. The program automatically adds the preface D_ to names of decision nodes. The bottom field indicates the number of alternative decisions for the node.

Pressing OK, puts the decision definition on the worksheet as shown below. The definition is not complete until we provide information about the decisions in the last two rows of the display. We'll do that later after all the nodes of the decision tree are defined.

The next step in building the tree is to add node 3, the chance node X. A chance node describes the possible outcomes of an uncertain experiment. Obviously we can't know the result of test X before it is run, but we can estimate probabilities of results. For the example, we note that the test has three possible outcomes: x 1 (failure probably due to A or B), x 2 (failure probably due to C or D), or x 3 (an indication to replace the motherboard). To represent the node on the worksheet, we select the Add Chance item from the menu. The dialog box is shown below. Again it provides only structure information.

The chance node dialog places the chance definition on the worksheet as shown below. We choose not to fill in the information concerning the realizations until all the nodes of the decision tree are on the worksheet.


The remaining chance and decision nodes of the tree are created in a similar manner. D2is the decision to either replace the motherboard or perform test Y to further isolate the failed component. Chance node Y describes the probabilities and costs associated with the outcome of this test. Decision D3 is the decision to replace the motherboard or proceed with test Z. Chance node Z describes the probabilities and costs associated with this test. Once all the nodes are constructed on the worksheet, the data concerning the various nodes and the information that links the nodes are added. The completed worksheet is shown below.

Worksheet before linking for the example problem.

In constructing this representation of the decision tree, terminal nodes, the black circles in the figure, are not explicitly defined but do appear in the decision and chance node data. The next state columns give the names of the subsequent state reached by the decisions or realizations. The names are preceded by a C or D to indicate chance or decision respectively. If the state is a terminal state, the next state cell is left blank. The total value column remains blank at this stage.

Linking the Nodes

Return to Menu

Operations Research Models and Methods
by Paul A. Jensen and Jon Bard, University of Texas, Copyright by the Authors