Return to Index
Operations Research Models and Methods
Computation Section
Decision Analysis
 - Data Worksheet

Pressing the OK button on the Model dialog causes the data worksheet to be constructed. Part of the worksheet is shown in the figure below after the data has been entered for the example problem. On the remainder of this page, we describe the contents of the data worksheet.

Along the top of the display starting in column F, the yellow areas hold the parameters of the model. These are determined by buttons in the Model dialog. The values are colored yellow to indicate that these should not be changed directly by the user. Some of parameters may be changed by clicking the Change Structure button. Others cannot be changed once the model worksheet is constructed.



Arc Data

  The range starting in cell D9 and going downward and to the right holds the arc data. Each arc has an index that is automatically provided by the program. Columns E through H are filled by the user to describe the particular model. Generally, cells with white backgrounds and maroon borders are for user data entry. Every arc passes from one node to another, called respectively the from node and the to node. The indices of these two nodes are placed in columns E and F. The Arc Cost column is included when the Arc Value button is clicked in the Model dialog. The Arc Probability column is always present. Arcs leaving decision nodes should always have a 1 probability for the Function solver option. For the Algorithm option, optimum arcs have a 1 probability, while non-optimum arcs have 0. The probability values for arcs leaving decision nodes are set by the program.

Arcs leaving chance nodes have positive probabilities that must sum to 1 for a given chance node. Both arc costs and arc probabilities can be determined by Excel functions. If cells outside the arc display are referenced in a function, the function should use absolute references.

Columns I and J hold the names of the from and to nodes. These are provided so that an arc can be easily identified by its end points. These two columns hold Excel formulas. Their yellow color indicates that the user should not change them.

Column K holds the names of the arcs. Arcs are originally given the same name as the name of the to node. The pink color of this column means that the user may change arc names. A name can easily be changing by typing a text name over the formula.

Column L holds the solution values associated with the arcs and are used by the solution procedure. These are determined by formulas in these cells. The formulas should not be changed.

The data shown in the arc data table is for the example problem.


Node Data


The node data is found starting in cell N9 and continuing downward and to the right. For the example we have placed data that is specific to the repair problem in rows 2 through 5. Values and formulas in the arc and node arrays reference these data values with absolute addresses.

Considering rows 9 and below we see in column N the node indices assigned sequentially by the program. These numbers may change for a particular node if the node data is sorted or new nodes are inserted in the list. Column O gives the node names for the example. These are provided by the user with the names representing the meaning of the nodes. Column P lists the type of each node, D for decision, C for chance, and T for terminal. Column Q holds the node values. In this case only terminal nodes have values.

When the model is optimized, column R holds the optimum value for each node. In the case of a decision node, it is the value of the objective if the optimum decisions are followed from that node and all subsequent nodes. In the case of a chance node, it is the expected value at that node when following the optimum decisions in all subsequent nodes. For a terminal node, column R holds the value assigned to the node. Column S holds the arc index of the optimum decision for a decision node and is left blank for chance and terminal nodes. Column T holds the arc name associated with the index in column S.

Columns U and V hold the Level and Depth for each node. These are used to determine the placement of the nodes in the graphical representation. They are colored green to indicate that the program has placed numerical values into these cells. The cell values may be changed by the user when using the manual node placement option.


Change Structure Button

We see in column A of the worksheet a series of buttons that can be used to change features of the model or initiate the procedures of the add-in. We describe the purpose of these buttons on the remainder of the page.

Clicking the Change Structure button presents the dialog below. A button or field shown in gray cannot be changed. For example, the name of the model cannot be changed once the worksheet is constructed. Similarly, if a utility function is not initially specified, it cannot be changed.

The fields and buttons on the dialog allow other features of the model to be changed. For example, the direction of optimization, the solver option and the graphic options can be changed by clicking the appropriate buttons. Nodes and arcs can added or removed by clicking the appropriate button and filling the fields for the Arcs and Nodes areas.

On clicking the OK button, the worksheet is modified to incorporate the requested changes.


Change Node/Arc Button

  When this button is pressed, the dialog below is presented. It allows nodes and arcs to be added and features associated with particular nodes and arcs to be changed. The specific case below shows the options associated with changing an arc. The user can change the start and/or end node of the arc, as well as its probability, value, parameter and name. If a feature is not present, as arc parameter for the example, the field is shown in gray.

If New is specified as the end node, a new node is created and placed at the bottom of the node data list.

  The figure below shows the dialog for adding an arc. When this option is chosen, the number field at the far right is enabled. This allows several arcs to be added with one step. This is particularly useful to build a model in an incremental manner.

With the change node option chosen, only the node area at the left is enabled. The type, value, parameter and name of the node can then be changed.

In every case changes are accomplished by clicking the Change button. This causes the data areas for the nodes and arcs to be adjusted without leaving the dialog. This is helpful when making a series of related changes. The dialog may be moved on the display so that the current and changed values can be easily observed.


Solve Button

  Clicking the Solve button causes the program to check the network for correctness. Some rules that must be satisfied are: the network must contain no cycles, decision and chance nodes must have at least one leaving arc, the probabilities for the arcs leaving a chance node must sum to 1, terminal nodes must have no leaving arcs. Violation of these and other programmed rules cause warning messages to be issued.

With the Algorithm solution option, the solution algorithm is initiated by the Solve button and the optimum solution is placed on the worksheet.


Graphics Button

  Clicking this button creates a new worksheet for the graphics presentation, if it does not already exist. Nodes are placed on the worksheet using the Level and Depth values. These are automatically determined if the Autoplace option has been selected.


Sort Network Button

  When building a network by adding nodes and arcs, the nodes and arcs are often placed in a non-intuitive sequence. Building and maintaining a network is easier if the nodes and arcs are sorted. Clicking this button causes the node data arrays to be sorted first by level and then by node name. After a node sort, the nodes are renumbered so the node indices are consecutive and increasing. This changes the indices associated with the nodes. Since the from and to nodes for the arcs are specified as node indices, the indices defining an arc must be adjusted to reflect the sorted node values. This is done automatically by the program.

Arcs are also sorted, first by from node index and second by arc name.

When fields of the node and arc data arrays contain formulas that refer to cells outside the data arrays, care must be taken so that these references are not invalidated by the sorting. This is accomplished by using absolute references in formulas. For example, a relative reference to cell A1 is simply A1. An absolute reference is $A$1. When absolute references are used, sorting cannot disturb them. Absolute references are also obtained by referring to cells by name. Using named references for formulas is a powerful feature of Excel.


Changes on the Graphic Worksheet


When a graphic representation of the network is created a Change Node/Arc button is placed on the graphic worksheet. This allows the changes to be made while the user looks at the graphical representation. When the Change button is pressed the graphic is redrawn.



New Functions

The add-in introduces three new functions available to Excel when the add-in is loaded. Excel calls these User Defined Functions. The three functions are listed below with a definition of their parameters.
  • MinIF(A, k, B): for the set of entries in the array A that are equal to k, find the minimum of the corresponding set of entries in the array B.
  • MaxIF(A, k, B): for the set of entries in the array A that are equal to k, find the maximum of the corresponding set of entries in the array B.
  • MatchIF(A, k, B, m): for the set of entries in the array A that are equal to k, find the index of the entry in array B whose value is the same as m. Return the corresponding index of B.
  • SumProductIF(A, k, B, C): for the set of entries in the array A that are equal to k, find the corresponding sum of the products of the elements of B and C. Return the sum of the products.

In each case A and B (and C for SumProductIF) are equal length arrays on the worksheet. k and m are numbers, letters or references to a cells. The functions are like the SUMIF Excel function. The functions are used in the Optimum Value column and the Optimum Arc column of the node data. They are also used to find the maximum and minimum of the terminal values when utility functions are used.


Return to Top

tree roots

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

next page