TreePlan.xla key functions summary (continued)

To download TreePlan.xla: Go to P:\Course-Files\Spring\T03\MDM to download TreePlan.xla, an Excel add-in for decision analysis. The following is a summary on the key functions of TreePlan. For a more detailed tutorial, please refer to the Excel example, “TreeSamp.xls”, at

The following is a summary on the key functions of TreePlan. The best way to benefit from this quick summary is to 1) download and install TreePlan to your Excel; 2) print out this summary; 3) follow the instructions step by step to open a new worksheet and start a new decision tree.

The decision tree picture below may help you get started.The basic idea for that tree is to evaluate whether a decision maker should (or should not) invest in a new project, where the investment required is $500, and there is 50%-50% chance for the “good case” (which brings in an income of $2000) and the “bad case” (which leads to a $500 loss) to occur.

A decision tree built using TreePlan:

Branch NamesPartical Cash FlowsProbabilitiesRollback EVsTerminal Values

  1. To start a new tree: Run Excel, and use Excel's File Open command (or Tools/Addins/Browze) to open TreePlan.xla at where it has been downloaded. This will create a Decision Tree row in the Tools menu. In a new excel sheet, click “Tools” menu, and then click “Decision Tree” to start a new tree (it’s usually a good idea to start the tree at about cell B15).You will see a default tree with one decision node and two branches attached to it. It fits our case perfectly, since there are only two possible decisions – “To invest” or “not to”.
  1. To “grow” a tree: click the target cell, where a decision node (denoted by a square) or an event node (denoted by a circle) is supposed to be, and then hit “Ctrl-T”. Select whether you want a decision node or an event node, and how many branches should be attached to it. In our case, let’s use the upper decision branch for the “to invest” decision. Click the cell at the right end of this branch and then hit “Ctrl-T”. Select “Event node” and “2 branches” on the pop-up window. You will see an event node (a circle) with two event branches (for “good case” and “bad case”) “grown” from the upper decision branch.
  1. Important details for any tree branch:

-After a decision node, you need to type in Branch Name (e.g., “To invest in the new project” or “Not to invest” as in the example shown on the previous page), and Partial Cash Flow (e.g., “-500” in case you decide to invest; or “0” if you decide “not to invest”).

-After an event node, you need to type in Branch Name (e.g. “Good case” or “Bad case”), Probability (e.g. “0.5”each for the good and the bad case as with our example), and the Partial Cash Flow [e.g. an income of “2000” in the good case, and “-500” (loss) in the bad case).

-The Rollback EV(expected value) and Terminal Valueswill be automatically calculated.

-Rollback EV: are the expected values calculated by TreePlan, given the judgments on partial cash flows and probabilities that the decision maker/tree builder has made.

-Terminal Value: are the values that you are supposed to get after going through the decision and event paths that lead you from the root/beginning node to the terminal node. By going through different decision and/or event paths, you will end up getting different terminal values. In our example, if you decide not to invest, your terminal value will be 0; if you decide to invest, the terminal value will be $1500 if the “good case” occurs; in case the “bad case” occurs after you invest, the terminal value would be -$1000.

-What is the value that matters the most to you? It’s the rollback value beside the “root” node that starts the whole tree. This is the expected value that TreePlan calculates for you after factoring in all the possible decisions and subsequent outcomes (as defined by you, and with the probabilities that you assigned to them).

-Before you input any value, the default values for all the above-mentioned cells are set to be 0. When in doubt of where to type in what, select the related cell, and hit “Ctrl-T” to find out cell definition.

  1. To modify certain part of a tree: click the decision or event node cell that you want to modify, and hit “Ctrl-T”. You will see choices, such as “Add branch”, “Copy subtree”, “Change to Event node”, and “Remove Branch” etc.. Make appropriate choice and hit “OK”.

`

  1. To read trees built by others: click the cells that contain numbers or words, hit “Ctrl-T”, and then you will be able to see the cell definitions. The most common ones are listed as follows, and they have all been explained in our summary.

-Branch names

-Probabilities

-Partial cash flows

-Rollback EV

-Terminal Value

  1. Other HELPFUL notes:

1)Don’t forget to check the “Options” choices. You will be able to see default options by hitting “Ctrl-T” and then clicking the “Options” button in the window that pops up. Change default settings if you don’t want to “Use expected values” or “Maximize (profits)”.

2)If you want to copy a tree (to use it as a base for a similar tree, for instance), simply copy the whole Excel worksheet and then revise the tree in it. The TreePlan add-in will NOT allow you to select the tree itself and copy it to another location.

3)Building two or more trees in one same Excel worksheet is currently not possible. So if you want to group relevant trees, just keep on adding worksheets to build a workbook that contain all the trees.