Decision Trees in Excel
DTace is an add-in that allows you to create and analyze decision trees in Excel. You can develop a detailed plan, and understand the potential risks and opportunities of your situation.
When used in conjunction with Simulation Master, you can replace discrete outcomes with a probability distribution and use Monte Carlo simulation to get a more complete risk profile.
DTace features a simple form based user interface. It also allows for easy sharing since it resides within the Excel environment.
Free Trial Version
The DTace trial version has all the functionality of the full version except that it is limited to 7 nodes. There is no time limit on the trial version.
Building and Analyzing a Decision Tree
Create a New Tree DTace creates a new worksheet that is formatted for a DTace decision tree.
Add Nodes Add nodes to the decision tree by using the add node form. Depending on the node type, a node name, probability and value is entered. You can easily edit nodes later. There are three node types: decision, chance, and end nodes.
A decision node is represented by a square, and denotes a point in the tree where a decision is made. A chance node is represented by a circle, and denotes a branching of the tree where two or more outcomes are possible with each outcome having a probability. When using Monte Carlo simulation, one outcome of a chance node is possible since all probabilities are covered by the probability distribution associated with the outcome. An end node is represented by a triangle, and denotes the end of a path through the decision tree.
Roll Back Once the decision tree nodes have been added, roll back the tree to calculate the expected monetary value, expected utility or certainty equivalent of the root node. Using the "choose maximum" or "choose minimum" decision rules, you can rollback so the maximum value or minimum value is chosen at each decision node.
Sensitivity Analysis DTace has a sensitivity analysis tool to vary payoffs and probabilities to find what factors have the most impact on expected value or utility. You set the amount of change as a percentage of the base case values, and the software automatically generates a tornado chart showing sensitivity. All reports and charts are native to Excel so you can easily change formatting to meet your needs.
Decision Tree Features
Referenced Data DTace can reference tree data to spreadsheet cells. For example, if you have calculated payoff values in the workbook, just reference the cell where the payoff is located.
Tree Layout Auto arrange feature will automatically arrange tree in either a horizontal or vertical orientation. Nodes can also be manually moved to arrange the tree any way you want.
Node Hiding DTace has node hiding options to only show important features of the decision tree. You can show only the nodes on optimal paths, while hiding non-optimal nodes. Also, you can hide by node level. Finally, a base node can be selected and all nodes that are not children of the base node will be hidden.
Text Visibility Data fields and their labels can be toggled on and off. Text can be located above/below nodes or left/right.
Click to Edit When the click to edit setting is on, clicking on a node will open the edit node form to quickly edit nodes.
Connector Types Three connector types are available: straight, elbow, and curved.
Node Sorting When new nodes are added to an existing tree, the node sorting tool will rearrange nodes so they are next to their parent node.
Roll Back You can use expected value, exponential utility, certainty equivalent, or define your own utility function.
Probability Check The probability check tool verifies that outcomes from each chance node have a total probability of one. As a result, there are fewer probability errors.
Sensitivity Analysis A sensitivity analysis tool that varies payoffs and probabilities to find the factors that have the most impact on expected value or utility. A sensitivity report can be sent to a new worksheet or workbook. Tornado and spider charts can be included in the sensitivity report.
Selection Tools Selection tools that are useful for mass copying and formatting. Using these tools allows you to easily use Excel native formatting tools. Selection types: select everything, select all nodes, select by node type, select all text, select all connectors.
Saved Arrangement The saved arrangement tool saves a tree arrangement to allow for temporary changes to layout. As a result, tree layout can be restored to saved arrangement later. For example you can arrange the tree for a presentation, and save this arrangement. You can auto arrange the tree for the most compact arrangement, and then restore the presentation layout any time.
Reporting DTace generates node data reports in a new worksheet or workbook. It also has several data filtering and sorting functions to only get the data you want.
Monte Carlo Simulation Compatible with Simulation Master The SIMROLLBACK worksheet function allows Monte Carlo simulation of expected value. Using Simulation Master, chance node outcomes can be treated as random variables for simulation.
Excel: 2007 to 2019, Office 365. 32 and 64 bit versions.
Windows: Vista to Windows 10. 32 and 64 bit versions.
Excel for Mac not supported at this time.
Excel is a registered trademark of Microsoft Corporation.