In a normal decision tree, the outcomes of a chance node are treated as discrete events. For example, there is a .4 probability of X and a .6 probability of Y. This limited view of the potential outcomes can be improved by replacing these two discrete outcomes with a spectrum of outcomes. In other words, we treat the outcome as a random variable that can assume a range of values based on an underlying probability distribution.
Setting Up the Decision Tree
Using cell references, node values can be treated as random variables which can be used as inputs to a Monte Carlo simulation. By entering the SIMROLLBACK function into a cell in the same worksheet as the tree, the cell containing SIMROLLBACK can be simulated repeatedly.
The NODEOPTCOUNT worksheet function allows for counting the number of times a node is on the optimal path during the simulation.
For more information on entering worksheet functions, refer to the SIMROLLBACK and NODEOPTCOUNT tutorial.
A Monte Carlo simulation package can be used in conjunction with DTace to perform the simulation. In the example that follows, Vortarus Technologies’ Simulation Master add-in was used to perform the simulation.
The decision tree shown below has two end nodes that are random variables. End 1 is a normally distributed random variable with mean 4 and standard deviation of 1. End 2 is a triangular distributed random variable with minimum -1, most likely 2, and maximum 5.
It should be noted that DTace allows for one end node to emanate from a chance node with a probability of 1. Since the end node is a random variable, we can use only one end node from a chance node to cover all possibilities. This is different from a tree with discrete outcomes and discrete probabilities for each outcome.
To get the SIMROLLBACK cell value (cell AL3) to match the root node expected value, turn off Excel’s automatic calculation.
Each time Excel calculates the worksheet, the random variable cells will assume a new value and as a result the SIMROLLBACK cell will assume a new value as well. The data fields on the decision tree will not update. To update the data fields displayed on the tree, the Roll Back Tree button must be clicked on the ribbon.
Run the Monte Carlo Simulation
Once the spreadsheet is set up, the tree can be simulated. The SIMROLLBACK cell (AL3) is selected as the output. The Simulation Master result is shown below with a histogram of the root node expected value, and summary statistics.
Earlier, the NODEOPTCOUNT was entered in cell AG3 to count the number of times node 1.1 was on the optimal path during the simulation. The worksheet is shown below after the simulation was run. The count is now 7367. The count was at 1 when the simulation started so node 1.1 was on the optimal path 7366 times out of the 10,000 simulation trials.
Simulating Intermediate Nodes
Thus far, we have rolled the tree back to the root node during simulation. This can also be done to intermediate nodes. An intermediate node is one that isn't a root node or end node.
The set-up is similar to the procedure outlined earlier, except we use the SIMROLLBACK2 and NODEOPTCOUNT2 worksheet functions.