BUILDING A DECISION TREE IN TREEPLAN

TreePlan is a decision tree add-in for Microsoft Excel 2000–2010 (Windows) and Microsoft Excel 2004 (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.

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). 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 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.)

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. 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. 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 pressing OK. TreePlan then redraws the tree with a chance node in place of the 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 press Ctrl+Shift+T without a node selected. You can also bring up this dialog box by pressing 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 press OK, TreePlan selects all cells containing 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 may not be available when working with very large trees.)