Topics: Decision tree, Spreadsheet, Decision theory Pages: 22 (5875 words) Published: February 4, 2013
Decision Trees Using TreePlan

16

16.1 TREEPLAN OVERVIEW
TreePlan is a decision tree add-in for Microsoft Excel 2000–2010 (Windows) and Microsoft Excel 2004 & 2011 (Macintosh). TreePlan helps you build a decision tree diagram in an Excel worksheet using dialog boxes. Decision trees are useful for analyzing sequential decision problems under uncertainty. Your decision tree model may include various controllable alternatives (e.g., whether to introduce a new product, whether to bid on a new project) and uncontrollable uncertainties (e.g., possible demand for a product, whether you're awarded a contract), arranged in chronological order. TreePlan automatically includes formulas for summing cash flows to obtain outcome values and for calculating rollback values for determining the optimal strategy. To use TreePlan, you (1) open a new worksheet, (2) press Ctrl+Shift+T (or Options+Cmd+T) to build a new decision tree diagram, (3) select a node to change the structure of your decision tree, (4) enter branch names, cash flows, and probabilities, and (5) determine the optimal strategy from TreePlan's results. All of TreePlan’s functionality, including its built-in help, is a part of the TreePlan XLA file. There is no separate setup file or help file. When you use TreePlan on a Windows computer, it does not create any Windows Registry entries (although Excel may use such entries to keep track of its add-ins).

16.2 BUILDING A DECISION TREE IN TREEPLAN
On Windows computers, you can start TreePlan either by choosing Tools | Decision Tree from the menu bar (Excel 2003 and earlier versions), by choosing Add-ins | Decision Tree (Excel 2007 or 2010), or by pressing Ctrl+Shift+T (hold down the Ctrl and Shift keys and press T). On Mac Excel 2011, you can start TreePlan either by choosing Tools | Decision Tree from the menu bar or by pressing Options+Cmd+T (hold down the Options and Command keys and press T). If the worksheet doesn't have a decision tree, TreePlan prompts you with a dialog box with three options; choose New Tree to begin a new tree. TreePlan draws a default initial decision tree with its upper left corner at the selected cell. For example, the figure below shows the initial tree when cell C3 is selected before creating the new tree. (Note that TreePlan writes over existing values in

186

Chapter 16 Decision Trees Using TreePlan

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.) Figure 16.1 TreePlan Initial Default Decision Tree

Build up a tree by adding or modifying branches or nodes in the default tree. To change the 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 the cell containing the node in the tree to modify, and press Ctrl+Shift+T (or Options+Cmd+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 square cell (cell G4) next to the vertical line at the end of a terminal branch and press Ctrl+Shift+T (or Options+Cmd+T). TreePlan then presents this dialog box. Figure 16.2 TreePlan Terminal Node 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 pressing OK. TreePlan then redraws the tree with a chance node in place of the terminal node.

16.2 Building a Decision Tree in TreePlan

187

Figure 16.3 Initial Decision Tree Diagram

The dialog boxes presented by TreePlan vary depending on what you have selected when you press Ctrl+Shift+T (or Options+Cmd+T). The dialog box shown below is presented when you press Ctrl+Shift+T (or...