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).