Milestone 2

DAT520TreePlan184Guide.pdf

Home >Information Systems homework help >Milestone 2

Decision Trees Using TreePlan 16

16.1 TREEPLAN OVERVIEW TreePlan is a decision tree add-in for Microsoft Excel 2007–2013 (Windows) and Microsoft Excel 2011 (Macintosh).

TreePlan helps you build a decision tree diagram in an Excel worksheet using dialog boxes. Decision trees are useful for analyzing sequential decision problems under uncertainty. Your decision tree model may include various controllable alternatives (e.g., whether to introduce a new product, whether to bid on a new project) and uncontrollable uncertainties (e.g., possible demand for a product, whether you're awarded a contract), arranged in chronological order. TreePlan automatically includes formulas for summing cash flows to obtain outcome values and for calculating rollback values for determining the optimal strategy.

To use TreePlan, you (1) open a new worksheet, (2) press Ctrl+Shift+T (or Option+Cmd+T) to build a new decision tree diagram, (3) select a node to change the structure of your decision tree, (4) enter branch names, cash flows, and probabilities, and (5) determine the optimal strategy from TreePlan's results.

All of TreePlan’s functionality, including its built-in help, is a part of the TreePlan XLAM file. There is no separate setup file or help file. When you use TreePlan on a Windows computer, it does not create any Windows Registry entries (although Excel may use such entries to keep track of its add-ins).

16.2 BUILDING A DECISION TREE IN TREEPLAN With Windows Excel 2007 & 2010 & 2013, you can start TreePlan either by choosing Add-ins > TreePlan Decision Tree or by pressing Ctrl+Shift+T (hold down the Ctrl and Shift keys and press T).

With Mac Excel 2011, you can start TreePlan either by choosing Tools > TreePlan Decision Tree from the menu or by pressing Option+Cmd+T (hold down the Option and Command keys and press T).

If the worksheet doesn't have a decision tree, TreePlan prompts you with a dialog box with three options; choose New Tree to begin a new tree. TreePlan draws a default initial decision tree with its upper left corner at the selected cell. For example, the figure below shows the initial tree when cell C3 is selected before creating the new tree. (Note that TreePlan writes over existing values in

th su

B la or tr (o

Fo (c O

T se an

92 Chapter 16

he spreadsheet: ubsequently ad

igure 16.1 Tre

uild up a tree b abels or probab r probability. T ee), select the n

or Option+Cm

or example, to cell G4) next to

Option+Cmd+T

igure 16.2 Tre

o add an event electing Chang nd pressing OK

6 Decision Trees

begin your tre dd or delete row

eePlan Initial D

by adding or m bilities, click on To modify the s node or the cel

md+T). TreePla

add an event n o the vertical li T). TreePlan th

eePlan Termina

t node to the br ge to event nod K. TreePlan the

Using TreePlan

ee to the right o ws or columns

Default Decisio

modifying branc n the cell conta structure of the ll containing th an will then pre

node to the top ne at the end o hen presents th

al Node dialog

ranch, we chan de in the dialog en redraws the

of the area whe in the tree-diag

on Tree

ches or nodes i aining the label e tree (e.g., add he node in the t esent a dialog b

branch of the of a terminal br his dialog box.

box

nge the selected g box, selectin tree with a cha

ere your data is agram area.)

in the default tr l or probability d or delete bran tree to modify, box showing th

tree shown abo ranch and press

d terminal node ng the number o ance node in p

s stored, and d

ree. To change y and type the n nches or nodes , and press Ctr he available co

ove, select the s Ctrl+Shift+T

e to an event n of branches (he

place of the term

do not

e the branch new label in the

rl+Shift+T ommands.

square cell T (or

node by ere two), minal node.

Th pr pr pr ch se av

Th of se Th th sp

Si yo to Se se bo P

igure 16.3 Init

he dialog boxe ress Ctrl+Shif ress Ctrl+Shif resented when hoose Add bra elected node, c vailable comm

igure 16.4 Tree

he Copy subtr f the tree are si eparately. To c his tells TreeP

his subtree, sele pecified subtree

ince TreePlan our tree. For ex o format and ch elect dialog bo elected. You ca ox. From here, robabilities an

tial Decision T

es presented by ft+T (or Optio ft+T (or Optio you select a de

anch and press hoose Insert d ands, click on

ePlan Event Di

ree command i imilar, you can opy a subtree, lan to copy the ect a terminal n e at the selecte

decision trees a xample, you ca hange them usi ox that appears an also bring up you can select

nd press OK, T

Tree Diagram

y TreePlan vary n+Cmd+T). T n+Cmd+T) w ecision node. I s OK. If you w decision or Ins the Help butto

ialog Box

is particularly u n copy and past

select the node e selected node node and choos ed terminal nod

are built direct an use bold or i ing Excel's form when you pres

p this dialog bo t all items of a

TreePlan select

16.2

y depending on The dialog box

with an event no f you want to a

want to insert a sert event and on.

useful when bu te "subtrees" ra e at the root of e and everythin se Paste subtr de.

tly in Excel, yo italic fonts for matting comma ss Ctrl+Shift+ ox by pressing particular type

ts all cells cont

2 Building a Deci

n what you hav shown below

ode selected; a add a branch to decision or eve press OK. To

uilding large tr ather than build f the subtree an ng to the right o ree. TreePlan th

ou can use Exc branch labels: ands. To help y

+T (or Option+ g the Select but e in the tree. Fo taining probabi

cision Tree in Tre

ve selected whe is presented w similar dialog

o the selected n ent node befor get a descripti

rees. If two or m ding up each p

nd choose Copy of it in the tree hen duplicates

cel's commands select the cell

you, TreePlan +Cmd+T) with tton on the Nod or example, if ilities in the tre

eePlan 193

en you when you

box is node, re the on of the

more parts part y subtree. . To paste the

s to format s you want provides a hout a node de dialog you choose ee. You can

th of tr

1 A w co br pe w br pr el

Th th by E th in se le

94 Chapter 16

hen format all o f limitations in ees.)

6.3 ANAT An example of a whether to prepa ontract. The tre ranches. Each b ertaining to tha

with each branch ranch. For the repare the prop lectronic metho

igure 16.5 Nod

he trees are "so he partial cash f y computing ex Vs appear next

he decision nod n the first decis econd decision eads to a higher

6 Decision Trees

of the probabili n Excel, the Sel

TOMY OF a TreePlan dec are a proposal ee consists of d branch is surro at branch. You h. The partial c scenario with t

posal, receives od, and spends

des, Labels, Ca

olved" using fo flows along the xpected values t to each node a des indicate wh sion node indic node indicates r expected valu

Using TreePlan

ities simultane lect dialog box

F A TREE cision tree is sh for a possible c

decision nodes, ounded by cells

may edit the la cash flows are terminal value $250,000 up fr $120,000 on t

ash Flows, and

ormulas embed e path leading

s at event nodes and show the e

hich alternative cates that it is o s the firm shou ue, $90,000, th

ously using Ex x may not be av

EPLAN DE hown below. In contract and (2 , event nodes a s containing fo abels, probabil the amount the $30,000, the fi

front if awarded the mechanical

d Probabilities

dded in the spre to that termina s and by maxim expected value e is optimal for optimal to prep uld try the elect han the mechan

xcel's formattin vailable when w

ECISION n the example, 2) which metho and terminal no ormulas, cell re lities, and parti e firm "gets pa firm pays $50,0 d the contract, l method if the

eadsheet. The t al node. The tre mizing at decis e at that point in r that decision. are the propos tronic method

nical method, $

ng commands. working with v

TREE a firm must de od to use to sat odes connected eferences, or lab ial cash flows a aid" to go down 000 if it decide spends $50,00 electronic met

terminal value ee is then "roll sion nodes; the n the tree. The In the exampl al, and the "2" because that al

$80,000.

(Because very large

ecide (1) tisfy the d by abels associated n that es to 00 to try the thod fails.

es sum all led back" e rollback

numbers in le, the "1" in the

lternative

Tr op U Th Tr ut be de ca va

igure 16.6 Ter

reePlan has a f ptions, press th

Use Expected V he default is to reePlan will co tilities instead elow the certai ecision nodes; ash flows are in alue or certain

rminal Values,

few options tha he Options but Values or Use E o rollback the tr ompute utilities of expected va n equivalents. the default is t nterpreted as c equivalent rath

Rollback EVs

at control the w tton in any of T Exponential U ree using expe s of endpoint c alues at event n You may also

to maximize pr osts, and decis her than the ma

16.3

s, and Choice In

way calculation TreePlan's dialo Utility Functio cted values. If

cash flows at th nodes. Expected

choose to Max rofits. If you ch sions are made aximum. See th

Anatomy of a Tr

ndicators

ns are done in t og boxes. The on for computin f you choose to he terminal nod d utilities are c ximize (profit hoose to minim by choosing th

he Help file fo

reePlan Decision

the tree. To sel first choice is ng certain equi

o use exponenti des and compu calculated in th ts) or Minimiz mize costs inste he minimum ex

or details on the

n Tree 195

lect these whether to ivalents. ial utilities,

ute expected he cell ze (costs) at ead, the xpected ese options.

196 Chapter 16 Decision Trees Using TreePlan

16.4 TREEPLAN INPUTS AND FORMULAS

Figure 16.7 Influence Chart of TreePlan Inputs and Formulas

Figure 16.8 Explanation of Formulas

16.5 STEP-BY-STEP TREEPLAN TUTORIAL A decision tree can be used as a model for a sequential decision problems under uncertainty. A decision tree describes graphically the decisions to be made, the events that may occur, and the outcomes associated with combinations of decisions and events. Probabilities are assigned to the events, and values are determined for each outcome. A major goal of the analysis is to determine the best decisions.

Decision tree models include such concepts as nodes, branches, terminal values, strategy, payoff distribution, certain equivalent, and the rollback method. The following problem illustrates the basic concepts.

Branch Inputs Cell Formulas Defined Name

Optional

Cash Flow

Probability

Terminal Node Endpoint Value

Event Node Rollback Value

Decision Node Choice Indicator

Decision Node Rollback Value

RT (Risk Tolerance)

Location Formula

Endpoint Value at Terminal Node

SUM of branch cash flows

Rollback Value at Event Node

IF probabilities SUM to approximately one, then SUM the products of probabilities times subsequent rollback values

Rollback Value at Decision Node

MAX or MIN of subsequent rollback values

Choice Indicator at Decision Node

Nested IF functions

16.5 Step-by-Step TreePlan Tutorial 197

DriveTek Problem

DriveTek Research Institute discovers that a computer company wants a new storage device for a proposed new computer system. Since the computer company does not have research people available to develop the new storage device, it will subcontract the development to an independent research firm. The computer company has offered a fee of $250,000 for the best proposal for developing the new storage device. The contract will go to the firm with the best technical plan and the highest reputation for technical competence.

DriveTek Research Institute wants to enter the competition. Management estimates a cost of $50,000 to prepare a proposal with a fifty-fifty chance of winning the contract.

However, DriveTek's engineers are not sure about how they will develop the storage device if they are awarded the contract. Three alternative approaches can be tried. The first approach is a mechanical method with a cost of $120,000, and the engineers are certain they can develop a successful model with this approach. A second approach involves electronic components. The engineers estimate that the electronic approach will cost only $50,000 to develop a model of the storage device, but with only a 50 percent chance of satisfactory results. A third approach uses magnetic components; this costs $80,000, with a 70 percent chance of success.

DriveTek Research can work on only one approach at a time and has time to try only two approaches. If it tries either the magnetic or electronic method and the attempt fails, the second choice must be the mechanical method to guarantee a successful model.

The management of DriveTek Research needs help in incorporating this information into a decision to proceed or not.

[Source: The storage device example is adapted from Spurr and Bonini, Statistical Analysis for Business Decisions, Irwin.]

Nodes and Branches

Decision trees have three kinds of nodes and two kinds of branches. A decision node is a point where a choice must be made; it is shown as a square. The branches extending from a decision node are decision branches, each branch representing one of the possible alternatives or courses of action available at that point. The set of alternatives must be mutually exclusive (if one is chosen, the others cannot be chosen) and collectively exhaustive (all possible alternatives must be included in the set).

There are two major decisions in the DriveTek problem. First, the company must decide whether or not to prepare a proposal. Second, if it prepares a proposal and is awarded the contract, it must decide which of the three approaches to try to satisfy the contract.

An event node is a point where uncertainty is resolved (a point where the decision maker learns about the occurrence of an event). An event node, sometimes called a "chance node," is shown as a circle. The event set consists of the event branches extending from an event node, each branch representing one of the possible events that may occur at that point. The set of events must be mutually exclusive (if one occurs, the others cannot occur) and collectively exhaustive (all possible events must be included in the set). Each event is assigned a subjective probability; the sum of probabilities for the events in a set must equal one.

The three sources of uncertainty in the DriveTek problem are: whether it is awarded the contract or not, whether the electronic approach succeeds or fails, and whether the magnetic approach succeeds or fails.

198 Chapter 16 Decision Trees Using TreePlan

In general, decision nodes and branches represent the controllable factors in a decision problem; event nodes and branches represent uncontrollable factors.

Decision nodes and event nodes are arranged in order of subjective chronology. For example, the position of an event node corresponds to the time when the decision maker learns the outcome of the event (not necessarily when the event occurs).

The third kind of node is a terminal node, representing the final result of a combination of decisions and events. Terminal nodes are the endpoints of a decision tree, shown as the end of a branch on hand-drawn diagrams and as a triangle on computer-generated diagrams.

The following table shows the three kinds of nodes and two kinds of branches used to represent a decision tree.

Figure 16.9 Nodes and Symbols Type of Node Written Symbol Computer Symbol Node Successor Decision square square decision branches Event circle circle event branches Terminal endpoint triangle or bar terminal value

Terminal Values

Each terminal node has an associated terminal value, sometimes called a payoff value, outcome value, or endpoint value. Each terminal value measures the result of a scenario: the sequence of decisions and events on a unique path leading from the initial decision node to a specific terminal node.

To determine the terminal value, one approach assigns a cash flow value to each decision branch and event branch and then sum the cash flow values on the branches leading to a terminal node to determine the terminal value. In the DriveTek problem, there are distinct cash flows associated with many of the decision and event branches. Some problems require a more elaborate value model to determine the terminal values.

The following diagram shows the arrangement of branch names, probabilities, and cash flow values on an unsolved tree.

T br th

igure 16.10 Dr

o build the dec ranch name, br he left side of e

Building the

1. Start w is open

2. Select c in Mac press C refer on New T

igure 16.11 Tr

Prepare propo

Don't prepare

riveTek Decisi

cision tree, you ranch cash flow each branch. As

e Tree Diag

with a new work n, choose Insert

cell A1. In Exc c Excel 2011, c Ctrl+Shift+T. In nly to the Wind ree button. A d

reePlan New T

0.5 Aw

osal

0.5 Not

proposal

ion Tree Diagr

u use TreePlan’ w, and branch p s you build the

gram

ksheet. (If no w t > Worksheet.

cel 2007 & 201 choose Tools > n Mac Excel 2 dows shortcut decision node w

Tree Dialog Bo

arded contract

t awarded contract

ram

’s dialog boxes probability (for e tree diagram,

workbook is op .)

10 & 2013, cho TreePlan Dec

011, press Opt key. In the Tre with two branc

Use mechan

Try electronic

Try magnetic

16.5 Step-by-S

s to develop th r an event) in t TreePlan ente

pen, choose Fil

oose Add-ins > ision Tree. Or, tion+Cmd+T. S eePlan New Tr ches appears.

ical method

0.5 Ele

c method

0.5 Ele

0.7 Ma

c method

0.3 Ma

Step TreePlan Tu

e structure. Yo the cells above ers formulas in

le > New. If a w

> TreePlan Dec , in any Windo Subsequent ins ree dialog box,

5 ectronic success

5 ectronic failure

7 agnetic success

3 agnetic failure

Tutorial 199

ou enter a e and below

other cells.

workbook

cision Tree; ows version, structions click the

$80,000

$150,000

$30,000

$120,000

-$50,000

00 Chapter 16

igure 16.12 In

3. Select c D7, and

igure 16.13 Br

4. Select c Change

igure 16.14 Tr

1 2 3 4 5 6 7 8 9

A B

1 0

1 2 3 4 5 6 7 8 9

A B

2 0

6 Decision Trees

nitial Decision T

cell D2, and en d enter Don't p

ranch Names a

cell F3. Press C e To Event No

reePlan Termin

C D

Alternative 1

Alternative 2

C D

Prepare propos

-50000

Don't prepare p

Using TreePlan

Tree Diagram

nter Prepare p prepare propo

and Cash Flow

Ctrl+Shift+T. I de, select Two

nal Node Dialo

E F G

sal -500

-50000

proposal

proposal. Selec osal.

In the TreePlan o Branches, and

og Box

000

ct cell D4, and

n Terminal No d click OK. Th

enter –50000.

de dialog box, he tree is redraw

Select cell

select wn.

16.5 Step-by-Step TreePlan Tutorial 201

Figure 16.15 Initial Event Branches

5. Select cell H2, and enter Awarded contract. Select cell H4, and enter 250000. Select cell H7, and enter Not awarded contract.

Figure 16.16 Event Branches With Names and Cash Flow

6. Select cell J3. Press Ctrl+Shift+T. In the TreePlan Terminal Node dialog box, select Change To Decision Node, select Three Branches, and click OK. The tree is redrawn.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

A B C D E F G H I J K 0.5 Outcome 3

-50000 Prepare proposal 0 -50000

-50000 -50000 0.5 Outcome 4

-50000 2 0 -50000

Don't prepare proposal 0

0 0

1 2 3 4 5 6 7 8 9 10 11 12 13 14

A B C D E F G H I J K 0.5 Awarded contract

200000 Prepare proposal 250000 200000

-50000 75000 0.5 Not awarded contract

-50000 1 0 -50000

75000

Don't prepare proposal 0

0 0

202 Chapter 16 Decision Trees Using TreePlan

Figure 16.17 Subsequent Decision Branches

7. Select cell L2, and enter Use mechanical method. Select cell L4, and enter –120000. Select cell L7, and enter Try electronic method. Select cell L9, and enter –50000. Select cell L12, and enter Try magnetic method. Select cell L14, and enter –80000.

Figure 16.18 Subsequent Decision Branches With Names and Cash Flows

8. Select cell N8. Press Ctrl+Shift+T. In the TreePlan Terminal Node dialog box, select Change To Event Node, select Two Branches, and click OK. The tree is redrawn.

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

A B C D E F G H I J K L M N O

Alternative 5 200000

0 200000

0.5 Awarded contract Alternative 6

1 200000 250000 200000 0 200000

Prepare proposal Alternative 7 200000

-50000 75000 0 200000

0.5 Not awarded contract

1 -50000 75000 0 -50000

Don't prepare proposal 0

0 0

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

A B C D E F G H I J K L M N O

Use mechanical method 80000

-120000 80000

0.5 Awarded contract Try electronic method

2 150000 250000 150000 -50000 150000

Prepare proposal Try magnetic method 120000

-50000 50000 -80000 120000

0.5 Not awarded contract

1 -50000 50000 0 -50000

Don't prepare proposal 0

0 0

16.5 Step-by-Step TreePlan Tutorial 203

Figure 16.19 Branches for Electronic Method Uncertainty

9. Select cell P7, and enter Electronic success. Select cell P12, and enter Electronic failure. Select cell P14, and enter –120000.

Figure 16.20 Branch Names and Cash Flows for Electronic Method Uncertainty

10. Select cell N18. Press Ctrl+Shift+T. In the TreePlan Terminal Node dialog box, select Change To Event Node, select Two Branches, and click OK. The tree is redrawn.

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

A B C D E F G H I J K L M N O P Q R S

Use mechanical method 80000

-120000 80000

0.5 Outcome 8

0.5 150000 Awarded contract Try electronic method 0 150000

2 250000 150000 -50000 150000 0.5

Outcome 9 150000

0 150000 Prepare proposal

-50000 50000 Try magnetic method 120000

-80000 120000

0.5 1 Not awarded contract

50000 -50000 0 -50000

Don't prepare proposal 0

0 0

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

A B C D E F G H I J K L M N O P Q R S

Use mechanical method 80000

-120000 80000

0.5 Electronic success

0.5 150000 Awarded contract Try electronic method 0 150000

3 250000 120000 -50000 90000 0.5

Electronic failure 30000

-120000 30000 Prepare proposal

-50000 35000 Try magnetic method 120000

-80000 120000

0.5 1 Not awarded contract

35000 -50000 0 -50000

Don't prepare proposal 0

0 0

204 Chapter 16 Decision Trees Using TreePlan

Figure 16.21 Branches for Magnetic Method Uncertainty

11. Select cell P16, and enter .7. Select cell P17, and enter Magnetic success. Select cell P21, and enter .3. Select cell P22, and enter Magnetic failure. Select cell P24, and enter –120000.

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

A B C D E F G H I J K L M N O P Q R S

Use mechanical method 80000

-120000 80000

0.5 Electronic success

150000 0.5 Try electronic method 0 150000 Awarded contract

3 -50000 90000 0.5 250000 120000 Electronic failure

30000 -120000 30000

0.5 Outcome 10

Prepare proposal 120000 Try magnetic method 0 120000

-50000 35000 -80000 120000 0.5

Outcome 11 120000

0 120000

1 0.5 35000 Not awarded contract

-50000 0 -50000

Don't prepare proposal 0

0 0

16.5 Step-by-Step TreePlan Tutorial 205

Figure 16.22 Complete Diagram Before Formatting

12. Double-click the sheet tab (or right-click the sheet tab and choose Rename from the shortcut menu), and enter Original. Save the workbook.

Interpreting the Results

The $30,000 terminal value on the far right of the diagram in cell S13 is associated with the following scenario:

Figure 16.23 Evaluation of a Scenario Branch Type Branch Name Cash Flow Decision Prepare proposal –$50,000 Event Awarded contract $250,000 Decision Try electronic method –$50,000 Event Electronic failure (Use mechanical method) –$120,000

Terminal value $30,000

TreePlan put the formula =SUM(P14,L11,H12,D20) into cell S13 for determining the terminal value.

Other formulas, called rollback formulas, are in cells below and to the left of each node. These formulas are used to determine the optimal choice at each decision node.

In cell B26, a formula displays 1, indicating that the first branch is the optimal choice. Thus, the initial choice is to prepare the proposal. In cell J11, a formula displays 2, indicating that the

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

A B C D E F G H I J K L M N O P Q R S

Use mechanical method 80000

-120000 80000

0.5 Electronic success

150000 0.5 Try electronic method 0 150000 Awarded contract

2 -50000 90000 0.5 250000 90000 Electronic failure

30000 -120000 30000

0.7 Magnetic success

Prepare proposal 120000 Try magnetic method 0 120000

-50000 20000 -80000 84000 0.3

Magnetic failure 0

-120000 0

1 0.5 20000 Not awarded contract

-50000 0 -50000

Don't prepare proposal 0

0 0

se co ab

Th m

06 Chapter 16

econd branch (n ontract, DriveT bout interpretat

Formatting

he following st may choose to u

13. From th Move O dialog b

14. On she Ctrl+Sh with Pr Align L

igure 16.24 Tr

15. Select c option cash flo With th Cells d (zero) f Numbe

6 Decision Trees

numbered 1, 2 Tek should try tion.

the Tree D

teps show how use other forma

he Edit menu, Or Copy from box, check the

eet Original (2) hift+T. In the T robabilities is s Left button.

reePlan Select

cell H12. Press button for Cell ow cells select hose cells still dialog box, clic for Decimal Pl ers. Click OK.

Using TreePlan

, and 3, from to the electronic m

Diagram

w to use TreePl ats for your ow

choose Move o the shortcut m

e Create A Cop

, if the probabi TreePlan Selec selected, and cl

Dialog Box

s Ctrl+Shift+T ls with Partial ed, if the cash selected, choos k the Number

laces; select $ i

op to bottom) i method. A sub

an and Excel f wn tree diagram

or Copy Sheet menu). In the low py box, and clic

ilities are not le ct dialog box, v lick OK. With

. In the TreePl Cash Flows is flows are not l se Home > For tab. In the Cat in the Symbol l

is the optimal c bsequent chapte

features to form ms.

(or right-click wer left corner ck OK.

eft-aligned, sel verify that the o all probability

lan Select dialo selected, and c

left-aligned, cli rmat > Format tegory list box, list box; select

choice. If awar er provides mo

mat the tree dia

k the sheet tab a r of the Move O

lect cell H9. Pr option button f

y cells selected,

og box, verify t click OK. With ick the Align L Cells. In the F

, choose Curren t -$1,234 for N

rded the ore details

agram. You

and choose Or Copy

ress for Cells , click the

that the h all partial Left button. Format ncy; type 0

Negative

igure 16.25 Ex

16. Select c option rollbac above.

17. Select c option value c above.

xcel Format Ce

cell I12. Press button for Cell k cells selected

cell S3. Press C button for Cell

cells selected, c

ells Dialog Box

Ctrl+Shift+T. ls with Rollbac d, choose Form

Ctrl+Shift+T. I ls with Termin choose Format

In the TreePla ck EVs/CEs is

mat > Cells. Re

In the TreePlan nal Values is se

> Cells. Repea

16.5 Step-by-S

an Select dialog selected, and c

epeat the Curre

n Select dialog elected, and cli at the Currency

Step TreePlan Tu

g box, verify th click OK. With

ency formatting

g box, verify th ick OK. With a y formatting of

Tutorial 207

hat the h all g of step 16

hat the all terminal f step 16

208 Chapter 16 Decision Trees Using TreePlan

Figure 16.26 Complete DriveTek Decision Tree

18. Double-click the Original (2) sheet tab (or right-click the sheet tab and choose Rename from the shortcut menu), and enter Formatted. Save the workbook.

Displaying Model Inputs

When you build a decision tree model, you may want to discuss the model and its assumptions with co-workers or a client. For such communication it may be preferable to hide the results of formulas that show rollback values and decision node choices. The following steps show how to display only the model inputs.

19. From the Edit menu, choose Move or Copy Sheet (or right-click the sheet tab and choose Move Or Copy from the shortcut menu). In the lower left corner of the Move Or Copy dialog box, check the Create A Copy box, and click OK.

20. On sheet Formatted (2), select cell B1. Press Ctrl+Shift+T. In the TreePlan Select dialog box, verify that the option button for Columns with Nodes is selected, and click OK. With all node columns selected, choose Format > Cells > Number or Home > Format > Format Cells > Number. In the Category list box, select Custom. Select the entry in the Type edit box, and type ;;; (three semicolons). Click OK.

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

A B C D E F G H I J K L M N O P Q R S

Use mechanical method $80,000

-$120,000 $80,000

0.5 Electronic success

$150,000 0.5 Try electronic method $0 $150,000 Awarded contract

2 -$50,000 $90,000 0.5 $250,000 $90,000 Electronic failure

$30,000 -$120,000 $30,000

0.7 Magnetic success

Prepare proposal $120,000 Try magnetic method $0 $120,000

-$50,000 $20,000 -$80,000 $84,000 0.3

Magnetic failure $0

-$120,000 $0

1 0.5 $20,000 Not awarded contract

-$50,000 $0 -$50,000

Don't prepare proposal $0

$0 $0

E se va do th fo fo

igure 16.27 Ex

xplanation: A eparated by sem alues, and text, oes not display he cell, but its r ormat without h ormats are save

21. Select c option rollbac Cells > list box

22. Double from th

Printing the

23. In the N

24. To prin & 2010 Button tab; for option Header approp

xcel Format Ce

custom numbe micolons, and t , in that order. y positive numb result is not dis having to enter ed with the wor

cell A27. Press button for Cell k values select

> Number. In th x, and select th

e-click the Form he shortcut men

e Tree Diag

Name Box list

nt the tree diagr 0 & 2013, choo or File > Print

r Orientation cl button for Fit T

r list box select riate headers a

ells Dialog Box

er format has fo they define the When you spe bers, negative n splayed. Later, r the formula a rkbook unless

s Ctrl+Shift+T ls with Rollbac ted, choose For he Category lis e three-semico

matted (2) shee nu), and enter M

gram

box, select Tre

ram from Exce ose Page Layou t Preview > Pa lick the option To 1 Page Wid t None, and in and footers). Cl

our sections of e formats for po ecify three sem numbers, zero if you want to gain. Editing a you explicitly

. In the TreePl ck EVs/CEs is rmat > Cells > st box, select C olon entry. Clic

et tab (or right- Model Inputs

eeDiagram (or

el, with the tree ut > Print Area ge Setup. In th button for Lan

de By 1 Page T the Footer list lick the Sheet t

16.5 Step-by-S

f format codes. ositive number

micolons withou values, or text

o display the re an existing form delete a forma

lan Select dialo selected, and c Number or Ho

Custom. Scroll ck OK.

-click the sheet . Save the wor

select cells A1

e diagram rang a > Set Print Ar he Page Setup d ndscape, and fo Tall. Click the H

box select Non tab; clear the c

Step TreePlan Tu

The sections a rs, negative num ut format codes t. The formula esult, you can c mat does not de at.

og box, verify t click OK. With ome > Format to the bottom o

t tab and choos rkbook.

1:S34).

ge selected, in E rea. Then choo dialog box, clic or Scaling click Header/Footer ne (or select ot

check box for G

Tutorial 209

are mbers, zero s, Excel remains in

change the elete it. All

that the h all > Format of the Type

se Rename

Excel 2007 ose Office ck the Page k the tab; in the

ther Gridlines,

210 Chapter 16 Decision Trees Using TreePlan

and clear the check box for Row And Column Headings. Click OK. Choose File > Print and click OK.

25. To print the tree diagram from Word, clear the check boxes for Gridlines and for Row And Column Headings on Excel’s Page Setup dialog box Sheet tab. Select the tree diagram range. In Excel 2007 & 2010 & 2013, choose Home > Paste > As Picture > Copy as Picture. In the Copy Picture dialog box, click the option button As Shown When Printed, and click OK. In Word select the location where you want to paste the tree diagram and choose Edit > Paste.

Figure 16.28 DriveTek Without Rollback Values

Alternative Model

If you want to emphasize that the time constraint forces DriveTek to use the mechanical approach if they try either of the uncertain approaches and experience a failure, you can change the terminal nodes in cells R13 and R23 to decision nodes, each with a single branch.

Use mechanical method $80,000

-$120,000

0.5 Electronic success

$150,000 0.5 Try electronic method $0 Awarded contract

-$50,000 0.5 $250,000 Electronic failure

$30,000 -$120,000

0.7 Magnetic success

Prepare proposal $120,000 Try magnetic method $0

-$50,000 -$80,000 0.3

Magnetic failure $0

-$120,000

0.5 Not awarded contract

-$50,000 $0

Don't prepare proposal $0

1 Tr ev ut fo

Fo an ex

Tr fu w R co

igure 16.29 Al

6.6 EXPO reePlan's defau vent node and m tilities in TreeP ormulas for com

igure 16.30 Tr

or the Maximiz nd CE = -LN(( xponential utili

reePlan uses th unction. The na

worksheet when RT=999999999 ommand.

Prepare

-$50,000

Don't pre

lternative Driv

ONENTIA ult is to rollbac maximum of s Plan's Options mputing the uti

reePlan Option

ze option with A-EU)/B)*RT ity, the formula

he name RT to ames A and B d n you choose to 999. You can c

0.5 Awa

$250

proposal

0.5 Not

epare proposal

veTek Decision

AL UTILITY ck the tree usin uccessor value dialog box, Tr ility and certain

ns Dialog Box

exponential ut T, where X and as are U = A-B

represent the r determine scal o use exponent change the valu

arded contract

0,000

awarded contract

n Tree

Y AND TR g expected val

es at a decision reePlan will red n equivalent at

tility, the rollba EU are cell re

B*EXP(X/RT)

risk tolerance p ing. If the nam

tial utility, they ues of the nam

Use mechanical method

-$120,000

Try electronic method

-$50,000

Try magnetic method

-$80,000

16.6 Exponenti

REEPLAN lue (probability n node. If you c draw the decisi t each node.

ack formulas a eferences. For t

and CE = LN(

parameter of th mes A, B, and R y are initially d

mes using the In

0.5 Electronic success

0.5 Electronic failure

0.7 Magnetic success

0.3 Magnetic failure

ial Utility and Tre

N y-weighted ave choose to use e ion tree diagram

are U = A–B*E the Minimize o ((A-EU)/B)*RT

he exponential RT don't exist o defined as A=1 nsert > Name >

Use mechanical 1

-$120,000

Use mechanical 1

-$120,000

eePlan 211

erage) at an exponential m with

EXP(X/RT) option with T.

utility on the , B=1, and

> Define

$80,000

$150,000

method $30,000

$120,000

method $0

-$50,000

212 Chapter 16 Decision Trees Using TreePlan

16.7 ACKNOWLEDGEMENT TreePlan was developed by Professor Michael R. Middleton at the University of San Francisco (using the Excel 4 XLM macro programming language) and modified for use at Fuqua (Duke University) by Professor James E. Smith. Mike Middleton rewrote portions using VBA during 2008-2014.