Quantitative Research Methods and Data Analytics

Guide for Decision Analysis with TreePlan
TreePlan is an Excel add-in that enables you to create decision trees in an Excel spreadsheet. This
guide to using TreePlan consists of the following parts:
1. Getting Started
2. Adding Nodes
3. Copying Subtrees
4. Interpreting the Results
We use the TreePlan software to build a decision tree for the first part of the Oil Drilling case. This
part of the case concerns the decision as to whether or not to drill. The cost of drilling the site is
£70m. If the well is “dry”, there will be no revenue. If it is “wet”, revenue will be £220m. If “soaking”,
revenue will be £670m. If there is underlying lime-shale rising into a flat dome shape, the chances of
finding oil increase. However, we don’t know yet if there is a dome. There is an estimate of 6
chances in 10 of finding a dome on the current site. The following table gives conditional
probabilities of the drilling result, given the existence or otherwise of the dome. For example, 85% is
the probability of the well being dry, given that there is no underlying dome structure.
Dome No Dome
Dry Well 0.60 0.85
Wet Well 0.25 0.125
Soaking Well 0.15 0.025
1 1
1. Getting Started
To get started with a new decision tree, open a new empty Excel spreadsheet, and position the
cursor in cell A1. Click Add-ins and select TreePlan Student Decision Tree.
In the following dialog box, select New Tree:
The following simple tree appears:
[BUS229] Quantitative Research Methods and Data Analytics Dr Eun-Seok Kim
2
Click on cell D2, which contains the label “Alternative 1” for the top branch. Type “Drill”. Click on cell
D7, which contains the label “Alternative 2” for the lower branch. Type “No Drill”.
In TreePlan, costs and payouts can be specified throughout the tree. Indicate the cost of drilling by
typing “-70” in cell D4. You should now have the following tree:
Make sure to save your spreadsheet frequently as you build the tree.
2. Adding Nodes
Now we need to include a chance node following the Drill branch of the tree. This chance node must
have two branches representing whether or not there is a dome.
Click on cell F3, which contains the end (triangular) node of the Drill branch. Click Add-ins and select
TreePlan Student Decision Tree. In the ensuing dialog box, select Change to even node, and specify
the number of Branches as Two, and click OK.
The tree should appear as:
3
Change the branch labels “Outcome 3” and “Outcome 4”, in cells H2 and H7, to “Dome” and “No
Dome”, respectively. Change the probability on the “Dome” and “No Dome” branches (from 0.5) to
0.6 and 0.4, respectively. Leave the costs on these two branches as 0.
The tree should be of the following form:
Next, we need to add a new chance node to the end node of both the Dome and No Dome branches.
This is new chance node represents the three possible drilling results, Dry, Wet and Soaking.
Click on cell J3, which contains the end node of the Dome branch. Click Add-ins and select TreePlan
Student Decision Tree. In the ensuing dialog box, select Change to event node, and specify the
number of Branches as Three, and click OK.
For the chance node that follows the Dome branch:
i. change the branch labels to “Dry”, “Wet” and “Soaking”;
ii. change the probabilities on the “Dry”, “Wet” and “Soaking” branches (from 0.333333) to 0.6,
0.25 and 0.15, respectively; and
iii. change the values on the “Dry”, “Wet” and “Soaking” branches (from 0) to 0, 220 and 670,
respectively.

4
The new tree is of the form:
3. Copy subtrees
To the end node of the No Drill branch of the tree, we now need to add a similar Drilling Results
chance node to the one that ne hate just created. A convenient way to do this is to copy the chance
node that we have just created, and then edit appropriately the probabilities in the new node.
To copy the chance node that we have just created, click on cell J8, which contains that node. Click
Add-ins and select TreePlan Student Decision Tree. In the ensuing dialog box, select Copy subtree,
and click OK.
Now click on cell J18, which contains the end node of the “No Dome” branch of the tree. Click Addins and select TreePlan Student Decision Tree. In the ensuing dialog box, select Paste subtree, and
click OK.
For the chance node that follows the No Dome branch, change the probabilities on the “Dry”, “Wet”
and “Soaking” branches to 0.85, 0.125 and 0.025, respectively. Do not change the labels or values on
these branches.
The completed tree should be of the form:
[BUS229] Quantitative Research Methods and Data Analytics Dr Eun-Seok Kim
5
4. Interpreting the Results
TreePlan automatically calculates the cumulative value for each path through the tree. These are
displayed at the extreme right end of the tree. Expected values are presented just below each
chance and decision node.
The optimal course of action at the decision node, according to an expected value criterion, is
indicated with a number inside this node. For the oil drilling decision tree in this guide, the number
in the node is 1, indicating that the optimal decision corresponds to the first branch coming from the
decision node. The optimal course of action is to drill. and this has an expected value of 41.
For the oil drilling decision tree in this guide, the optimal decision is the one that maximises the
expected value (profit). For some other trees, it can be appropriate to minimise expected value
(costs). The criterion used to evaluate the tree can be changed in TreePlan by first clicking on an
empty cell in the spreadsheet containing the decision tree. Then click “Add-ins” and select “TreePlan
Student Decision Tree”. In the ensuing dialog box, select Options. The next dialog box allows you to
select Maximize (profits) or Minimize (costs) for the expected value (EV).