One of the key techniques for evaluating a decision tree is to determine the likelihood of reaching each terminal node in the tree. In this video, learn how to calculate the probability of reaching a node in your decision tree.
- [Instructor] One of the key techniques for evaluating a decision tree is to determine the likelihood of reaching each terminal, or leaf node, in the tree. Once you have the probability you can add up the accumulated values and multiply that total by the probability you reach each node. Adding up the expected value of all terminal nodes gives you the expected value for the process. In this movie I will show you how to calculate the probability of reaching a node in your decision tree. My sample file is DecisionTree03 and you can find it in the Chapter04 folder of the Exercise Files collection.
The Excel table that I have created summarizes the data that is contained within the decision tree Smart Art diagram at the bottom. So, for example, I see that I have node number 4, which is on level number 2, springs forth from node number 1, it is an EndNode, it has a value of minus 500, and the probability of reaching that node from the previous step is .4, or 40%.
What I'd like to do though is to find the total probability of reaching an individual node. Let's follow a path on the decision tree itself to find the probability of reaching an individual node. Let's go with the top path. I have the probability of reaching node number 1 as .7, then if I want to go to node 4 that will happen with a probability of .4. So I would multiply 0.7 by 0.4 and find the total probability of reaching node 4 would be .28.
So what I need to do is to capture that logic within my Excel table. I'll do that by typing in cell G1 the new column header TotalProb for total probability and press Enter. Because I typed it just to the right of an existing Excel table header Excel included that header in the table and gave me cells in the column that I can fill values into. Instead of values I'm going to type in formulas.
The total probability of reaching node 0 at the start of the process is 1, so I will type 1 in the total probability cell and press Enter. Now I do need to start calculating. So what I need to do is to find the probability of reaching the parent node, which is why I have the Parent column, and then multiply that by the probability of taking the next step, which is included in the Probability column. The first thing I'll do is to type an = sign, I'm in cell G3.
And I want to multiply the probability of reaching an individual node on the last step by the total probability of reaching its parent. I'll start by clicking in cell F3 after the = sign, and I will multiply the probability of taking the step to this individual node. Now I need to look up the total probability of reaching node number 1's parent node. Well, as I see from column C, the Parent column, its parent is 0.
What I can do is use this table as a lookup table and say, okay, my Parent node is 0, what is its total probability? In other words, what is the total probability of reaching that node? So I can look using the value 0 in the Node column to find the total probability, which is 1. And it'll work all the way down the column. So I'm multiplying the probability of taking this step by the probability of reaching the parent node, which is 0.
I'll use the VLOOKUP formula, so I'll type VLOOKUP. The lookup value is in the Parent column, so I'll click the Parent column for this row, then a ,. The table array is my table, so I'll select that, and I get Table1. You could just type in Table1 if you want. Then a ,. The column index number, so counting from the left we have one, two, three, four, five, six, seven, we're looking at total probability, that's in column 7, then a ,.
And we want to force an exact match, it also allows us to detect any errors, so I'll use the down arrow key to highlight FALSE, then press Tab, type a ), and Enter. So what I see is that the probability of reaching node number 1 is 0.7. And if I look at the Smart Art diagram I can verify that yes, we will always start at node 0 probability 1, and then .7 of the time we will go to node 1, and .3 of the time we'll go to node 2.
So if I click cell G3 and then drag the fill handle down to cover the remaining cells in the column then I get a series of calculations. Let's take a look at our previous example to get to node 6. So we have probability 1 at node 0, then .7 times 0.2, which is .14, times 0.5, or half of that, which is .07, and if I look at node 6 in my table I see that in fact the value is 0.07, so the calculation is correct.
When you create this calculation always be sure that the total probability for node 0 is 1 and that is not determined by a formula. Once you have that as your base you can copy the formula that we created here to the rest of the cells in the column and that will give you the information you need. In the next movie we will calculate the expected value for each terminal node and for the tree as a whole.
- Finding target values using Goal Seek
- Finding a solution using Solver
- Tuning investment portfolios
- Organizing worksheets
- Creating objective and control formulas
- Experimenting with different constraints
- Optimizing resource placement
- Defining decision trees