《Systems Engineering》 Experiments Guide
2003.12.20
Systems Engineering Experiment Ⅰ
1. Experiment Objectives:
(1) Learn to build the decision trees model on an Excel Spreadsheet;
(2) Learn to apply the TreePlan (an Excel add-in) to solve the decision problem.
2. Experiment Tools:
The Microsoft Excel (the TreePlan has been installed).
3. Experiment Tasks:
(1) Familiarize with the Excel Treeplan environment. (2) Solve the example problem using Treeplan.
Experiment Part Ⅰ:
Requirement: Familiarize with the Excel Treeplan environment. Procedure:
You can start a new tree in TreePlan either by hitting Ctrl-t or by selecting Tools, Decision
Tree from the menu bar. TreePlan then prompts弹出对话框 you with a dialog box with three options; choose New to begin a new tree. TreePlan will then draw a default initial 最初的decision tree with its upper left corner at the selected cell. For example, the figure below shows the initial tree when $B$2 is selected. (Note注意 that TreePlan writes over existing values in the spreadsheet电子表: begin your tree to the right of the area where your data is stored, and do not subsequently add or delete rows or columns in the tree-diagram area.) In Excel 97 (or later version) a terminal node is represented by a vertical bar instead of a triangle.
A12345678910BCDEFGHDecision 10010Decision 20000
Build up a tree by adding or modifying branches or nodes in the default tree. To change the
2
branch labels or probabilities概率, click on the cell containing the label or probability and type the new label or probability. To modify the structure of the tree (e.g., add or delete branches or nodes in the tree), select the node or branch in the tree to modify and select Tools, Decision Tree or hit Ctrl-t. TreePlan will then present a dialog box showing the available commands.
For example to add an event node to the top branch of the tree shown above, select the terminal node at the end of that branch ($G$5) and hit Ctrl-t. To select a terminal node in Excel 97 (or later version), select the square cell next to the vertical line at the end of a terminal branch. TreePlan then presents this dialog box.
To add an event node to the branch, we change the selected terminal node to an event node by selecting Change to event node in the dialog box, selecting the number of branches (here two), and hitting OK. TreePlan then redraws the tree with a chance node in place of the terminal node.
B345678910111213141516CDEFGHI0.5Event 30Decision 10000.5Event 4010Decision 2000000JKL The dialog boxes presented by TreePlan vary depending on what you have selected when you choose Tools, Decision Tree or hit Ctrl-t. The dialog box shown below is presented when you hit Ctrl-t with an event node selected; a similar dialog box is presented when you select a decision node. If you want to add a branch to the selected node, choose Add branch and hit OK. If you want to insert a decision or event node before the selected node choose Insert decision or Insert event and hit OK. To get a description of the available commands, click on the Help button.
3
The Copy subtree command is particularly useful when building large trees. If two or more parts of the tree are similar, you can copy and paste \each part separately. To copy a subtree, select the node at the root of the subtree and choose Copy subtree. This tells TreePlan to copy the selected node and everything to the right of it in the tree. To paste this subtree, select a terminal node and choose Paste subtree. TreePlan then duplicates the specified subtree at the selected terminal node.
Since TreePlan decision trees are built directly in Excel, you can use Excel's commands to format your tree. For example, you can use bold or italic fonts for branch labels: select the cells you want to format and change them using Excel's formatting commands. To help you, TreePlan provides a Select dialog box that appears when you choose Tools Decision Tree or hit Ctrl-t without a node selected. You can also bring up this dialog box by hitting the Select button on the Node dialog box. From here, you can select all items of a particular type in the tree. For example, if you choose Probabilities and hit OK, TreePlan selects all of the probabilities in the tree. You can then format all of the probabilities simultaneously using Excel's formatting commands. (Because of limitations in Excel, the Select dialog box will not be available when working with very large trees.)
Experiment Part Ⅱ:
Requirement: Learn how to build and solve the decision tree by following the example. An example of a TreePlan decision tree is shown below. In the example, a firm must decide whether to purchase an option买卖选择权，期权 on the defense market rights认股权 to a new flight safety system. The tree consists of decision nodes, event nodes and terminal nodes connected by branches. Each branch is surrounded by cells containing formulas, cell references, or labels pertaining to that branch. You may edit the labels, probabilities, and partial cash flows associated with each branch. The partial cash flows are the amount the firm \paid\to go down that branch. Here, the firm pays $125,000 if it decides to purchase the option and another $575,000 if it signs the license agreement.
4
ROLLBACK EVs: Equal to the expected value at this point in the tree.0.15Win First Contract$4,2000.71License Agreement-$575DECISION NODE: The number in the node indicates Purchase OptionEVENT -$125$1000.29No License Agreement1$100$0-$125$0-$700TERMINAL NODES -$125$1930.85No Contract-$700$5,250TERMINAL VALUES: Equal to sum of partial cash flows along path.$10,500$7,0000.75Sublicense$3,500$0$3,500$10,500PROBABILITIES: Enter numbers or formulas in these cell.PARTIAL CASH FLOWS: Enter numbers or formulas in these cell.0.25Win Second ContractReject Purchase$0$0BRANCH LABELS: Type text in these cells$0All dollar amounts are thousands ($000).Decision Tree based on AIL Example in Ulvila and Brown, \Harvard Business Review, September-October 1982 The trees are %using formulas embedded in the spreadsheet嵌入电子表格的宏. The terminal values sum all the partial cash flows along the path leading to that terminal node. The tree is then \maximizing at decision nodes; the rollback EVs appear next to each node and show the expected value at that point in the tree. The numbers in the decision nodes indicate which alternative is optimal for that decision. In the example, the \in the decision node indicates that it is optimal to \expected value ($100,000).
TreePlan has a few options that control the way calculations are done in the tree. To select these options, hit the Options button in any of TreePlan's dialog boxes. The first choice is whether to Use Expected Values or Use Exponential Utility Function for computing certainty equivalents. The default is to rollback the tree using expected values. If you choose to use exponential utilities, TreePlan will compute utilities of endpoint cash flows at the terminal nodes and compute expected utilities instead of expected values at event nodes. Expected utilities are calculated in the cell below the certainty equivalents. You may also choose to Maximize (profits) or Minimize (costs) at decision nodes; the default is to maximize profits. If you choose to minimize costs instead, the cash flows are interpreted as costs and decisions are made by choosing the minimum expected value or certainty equivalent rather than the maximum. See the Help file for details on these options.
5