decan-20180619-1753 - treeplan.com · nch and press hoose insert d ands, click on plan event di ee...
TRANSCRIPT
Decision Trees Using TreePlan 16
16.1 TREEPLAN OVERVIEW TreePlan is a decision tree add-in for Microsoft Excel 2007 & 2010 & 2013 & 2016 (Windows) and Microsoft Excel 2011 & 2016 (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+Command+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, TreePlan 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 & 2016, 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).
With Mac Excel 2016, you can start TreePlan by pressing Option+Cmd+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
20
itscethsu
F
Blaortr(o
Foseth
F
Tsean
00 Chapter 16
s upper left corell C3 is selectehe spreadsheet:ubsequently ad
igure 16.1 Tre
uild up a tree babels or probabr probability. Tee), select the n
or Option+Cm
or example, to elect the trianghis dialog box.
igure 16.2 Tre
o add an eventelecting Changnd pressing OK
6 Decision Trees
rner at the seleed before creat begin your tre
dd or delete row
eePlan Initial D
by adding or mbilities, click onTo modify the snode or the cel
md+t). TreePlan
add an event nle shape, and p
eePlan Termina
t node to the brge to event nodK. TreePlan the
Using TreePlan
cted cell. For eting the new treee to the right ows or columns
Default Decisio
modifying brancn the cell contastructure of thell containing thn will then pres
node to the toppress Ctrl+Shi
al Node dialog
ranch, we chande in the dialogen redraws the
example, the fiee. (Note that Tof the area whein the tree-diag
on Tree
ches or nodes iaining the labele tree (e.g., addhe node in the tsent a dialog b
branch of the ft+T (or Optio
box
nge the selectedg box, selectintree with a cha
igure below shTreePlan writeere your data is
agram area.)
in the default trl or probabilityd or delete brantree to modify,
box showing th
tree shown aboon+Cmd+t). T
d terminal nodeng the number oance node in p
ows the initial es over existings stored, and d
ree. To changey and type the nnches or nodes , and press Ctr
he available com
ove, select cellTreePlan then p
e to an event nof branches (he
place of the term
tree when g values in
do not
e the branch new label in the
rl+Shift+T mmands.
l H5 or presents
node by ere two), minal node.
F
Thprbeprchseav
Fi
ThofseThthsp
Siyoto
igure 16.3 Init
he dialog boxeress Ctrl+Shifelow is presentresented when hoose Add braelected node, cvailable comm
igure 16.4 Tree
he Copy subtrf the tree are sieparately. To chis tells TreeP
his subtree, selepecified subtree
ince TreePlan our tree. For exo format and ch
tial Decision T
es presented byft+T (or Optioted when you pyou select a de
anch and presshoose Insert dands, click on
ePlan Event Di
ree command iimilar, you canopy a subtree, lan to copy theect a terminal ne at the selecte
decision trees axample, you cahange them usi
Tree Diagram
y TreePlan varyn+Cmd+t). Wpress Ctrl+Shiecision node. Is OK. If you wdecision or Insthe Help butto
ialog Box
is particularly un copy and past
select the nodee selected nodenode and choosed terminal nod
are built directan use bold or iing Excel's form
16.2
y depending onWith an event no
ift+T (or Optiof you want to a
want to insert a sert event and on.
useful when bute "subtrees" rae at the root of e and everythinse Paste subtrde.
tly in Excel, yoitalic fonts for matting comma
2 Building a Deci
n what you havode selected, thon+Cmd+t); aadd a branch todecision or evepress OK. To
uilding large trather than buildf the subtree anng to the right oree. TreePlan th
ou can use Excbranch labels: ands. To help y
cision Tree in Tre
ve selected whehe dialog box sa similar dialogo the selected nent node beforget a descripti
rees. If two or mding up each p
nd choose Copyof it in the treehen duplicates
cel's commandsselect the cell
you, TreePlan
eePlan 201
en you shown g box is node, re the on of the
more parts part y subtree. . To paste the
s to format s you want provides a
20
SeseboPthoftr
1Awcobrpewbrprel
F
ThthbyEthinsele
02 Chapter 16
elect dialog boelected. You caox. From here,robabilities an
hen format all of limitations inees.)
6.3 ANATAn example of awhether to prepaontract. The treranches. Each bertaining to tha
with each branchranch. For the repare the proplectronic metho
igure 16.5 Nod
he trees are "sohe partial cash fy computing exVs appear next
he decision nodn the first decisecond decisioneads to a higher
6 Decision Trees
ox that appears an also bring up you can select
nd press OK, Tof the probabili
n Excel, the Sel
TOMY OFa TreePlan decare a proposal ee consists of dbranch is surroat branch. You h. The partial cscenario with t
posal, receives od, and spends
des, Labels, Ca
olved" using foflows along thexpected valuest to each node ades indicate whsion node indic node indicatesr expected valu
Using TreePlan
when you presp this dialog bot all items of a
TreePlan selectities simultanelect dialog box
F A TREEcision tree is shfor a possible c
decision nodes,ounded by cells
may edit the lacash flows are terminal value $250,000 up fr $120,000 on t
ash Flows, and
ormulas embede path leading
s at event nodesand show the e
hich alternativecates that it is os the firm shouue, $90,000, th
ss Ctrl+Shift+ox by pressingparticular type
ts all cells contously using Ex
x may not be av
EPLAN DEhown below. Incontract and (2, event nodes as containing foabels, probabilthe amount the$30,000, the fi
front if awardedthe mechanical
d Probabilities
dded in the spreto that terminas and by maximexpected valuee is optimal foroptimal to prepuld try the electhan the mechan
+T (or Option+g the Select bute in the tree. Fotaining probabixcel's formattinvailable when w
ECISION n the example, 2) which methoand terminal noormulas, cell relities, and partie firm "gets pafirm pays $50,0d the contract, l method if the
eadsheet. The tal node. The tremizing at decise at that point inr that decision. are the propostronic method
nical method, $
+Cmd+t) withtton on the Nodor example, if ilities in the treng commands. working with v
TREE a firm must deod to use to satodes connectedeferences, or labial cash flows aaid" to go down000 if it decidespends $50,00 electronic met
terminal valueee is then "rollsion nodes; then the tree. The In the examplal, and the "2" because that al
$80,000.
hout a node de dialog you choose ee. You can (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
F
TropUThTrutbedecava
igure 16.6 Ter
reePlan has a fptions, press th
Use Expected Vhe default is toreePlan will cotilities instead elow the certaiecision nodes; ash flows are inalue or certain
rminal Values,
few options thahe Options butValues or Use Eo rollback the trompute utilitiesof expected van equivalents. the default is tnterpreted as cequivalent rath
Rollback EVs
at control the wtton in any of TExponential Uree using expes of endpoint calues at event nYou may also
to maximize prosts, and decisher than the ma
16.3
s, and Choice In
way calculationTreePlan's dialoUtility Functiocted values. If
cash flows at thnodes. Expected
choose to Maxrofits. If you chsions are made aximum. See th
Anatomy of a Tr
ndicators
ns are done in tog boxes. The on for computinf you choose tohe terminal nodd utilities are cximize (profithoose to minimby choosing th
he Help file fo
reePlan Decision
the tree. To selfirst choice is ng certain equi
o use exponentides and compucalculated in thts) or Minimizmize costs instehe minimum ex
or details on the
n Tree 203
lect these whether to ivalents. ial utilities,
ute expected he cell ze (costs) at ead, the xpected ese options.
204 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
Optional
Cash Flow
Probability
Terminal NodeEndpoint Value
Event NodeRollback Value
Decision NodeChoice Indicator
Decision NodeRollback Value
RT (Risk Tolerance)
Location Formula
Endpoint Valueat Terminal Node
SUM of branch cash flows
Rollback Valueat Event Node
IF probabilities SUM to approximately one, then SUM the products of probabilities times subsequent rollback values
Rollback Valueat Decision Node
MAX or MIN of subsequent rollback values
Choice Indicatorat Decision Node
Nested IF functions
16.5 Step-by-Step TreePlan Tutorial 205
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.
206 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.
F
Tbrth
B
F
igure 16.10 Dr
o build the decranch name, brhe left side of e
Building the
1. Start wis open
2. Select cDecisioWindowSubseqTree di
igure 16.11 Tr
Prepare propo
Don't prepare
riveTek Decisi
cision tree, youranch cash floweach branch. As
e Tree Diag
with a new workn, choose Insert
cell A1. In Excon Tree; in Maws version, pre
quent instructioialog box, click
reePlan New T
0.5Aw
osal
0.5Not
proposal
ion Tree Diagr
u use TreePlan’w, and branch ps you build the
gram
ksheet. (If no wt > Worksheet.
cel 2007 & 201ac Excel 2011, ess Ctrl+Shift+ons refer only tk the New Tree
Tree Dialog Bo
arded contract
t awarded contract
ram
’s dialog boxesprobability (fore tree diagram,
workbook is op.)
10 & 2013 & 2choose Tools >
+T. In Mac Excto the Windowe button. A dec
x
Use mechan
Try electronic
Try magnetic
16.5 Step-by-S
s to develop thr an event) in tTreePlan ente
pen, choose Fil
2016, choose A> TreePlan Decel 2011 & 201s shortcut key.cision node wit
ical method
0.5Ele
c method
0.5Ele
0.7Ma
c method
0.3Ma
Step TreePlan Tu
e structure. Yothe cells aboveers formulas in
le > New. If a w
Add-ins > TreePecision Tree. O16, press Optio. In the TreePlath two branche
5ectronic success
5ectronic failure
7agnetic success
3agnetic failure
Tutorial 207
ou enter a e and below
other cells.
workbook
Plan r, in any
on+Cmd+t. an New es appears.
$80,000
$150,000
$30,000
$120,000
$0
-$50,000
$0
20
F
F
F
08 Chapter 16
igure 16.12 In
3. Select cD7, and
igure 16.13 Br
4. Select cChange
igure 16.14 Tr
123456789
A B
10
123456789
A B
20
6 Decision Trees
nitial Decision T
cell D2, and end enter Don't p
ranch Names a
cell F3. Press Ce To Event No
reePlan Termin
C D
Alternative 1
0
Alternative 2
0
C D
Prepare propos
-50000
Don't prepare p
0
Using TreePlan
Tree Diagram
nter Prepare pprepare propo
and Cash Flow
Ctrl+Shift+T. Ide, select Two
nal Node Dialo
E F G
0
0
E F G
sal-500
-50000
proposal
0
proposal. Selecosal.
In the TreePlano Branches, and
og Box
0
0
000
0
ct cell D4, and
n Terminal Nod click OK. Th
enter –50000.
de dialog box, he tree is redraw
Select cell
select wn.
16.5 Step-by-Step TreePlan Tutorial 209
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.
1234567891011121314
A B C D E F G H I J K0.5Outcome 3
-50000Prepare proposal 0 -50000
-50000 -50000 0.5Outcome 4
-500002 0 -50000
0
Don't prepare proposal0
0 0
1234567891011121314
A B C D E F G H I J K0.5Awarded contract
200000Prepare proposal 250000 200000
-50000 75000 0.5Not awarded contract
-500001 0 -50000
75000
Don't prepare proposal0
0 0
210 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.
123456789
101112131415161718192021222324
A B C D E F G H I J K L M N O
Alternative 5200000
0 200000
0.5Awarded contract Alternative 6
1 200000250000 200000 0 200000
Prepare proposal Alternative 7200000
-50000 75000 0 200000
0.5Not awarded contract
1 -5000075000 0 -50000
Don't prepare proposal0
0 0
123456789
101112131415161718192021222324
A B C D E F G H I J K L M N O
Use mechanical method80000
-120000 80000
0.5Awarded contract Try electronic method
2 150000250000 150000 -50000 150000
Prepare proposal Try magnetic method120000
-50000 50000 -80000 120000
0.5Not awarded contract
1 -5000050000 0 -50000
Don't prepare proposal0
0 0
16.5 Step-by-Step TreePlan Tutorial 211
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.
123456789
1011121314151617181920212223242526272829
A B C D E F G H I J K L M N O P Q R S
Use mechanical method80000
-120000 80000
0.5Outcome 8
0.5 150000Awarded contract Try electronic method 0 150000
2250000 150000 -50000 150000 0.5
Outcome 9150000
0 150000Prepare proposal
-50000 50000 Try magnetic method120000
-80000 120000
0.51 Not awarded contract
50000 -500000 -50000
Don't prepare proposal0
0 0
123456789
1011121314151617181920212223242526272829
A B C D E F G H I J K L M N O P Q R S
Use mechanical method80000
-120000 80000
0.5Electronic success
0.5 150000Awarded contract Try electronic method 0 150000
3250000 120000 -50000 90000 0.5
Electronic failure30000
-120000 30000Prepare proposal
-50000 35000 Try magnetic method120000
-80000 120000
0.51 Not awarded contract
35000 -500000 -50000
Don't prepare proposal0
0 0
212 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.
123456789
10111213141516171819202122232425262728293031323334
A B C D E F G H I J K L M N O P Q R S
Use mechanical method80000
-120000 80000
0.5Electronic success
1500000.5 Try electronic method 0 150000Awarded contract
3 -50000 90000 0.5250000 120000 Electronic failure
30000-120000 30000
0.5Outcome 10
Prepare proposal 120000Try magnetic method 0 120000
-50000 35000-80000 120000 0.5
Outcome 11120000
0 120000
1 0.535000 Not awarded contract
-500000 -50000
Don't prepare proposal0
0 0
16.5 Step-by-Step TreePlan Tutorial 213
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
123456789
10111213141516171819202122232425262728293031323334
A B C D E F G H I J K L M N O P Q R S
Use mechanical method80000
-120000 80000
0.5Electronic success
1500000.5 Try electronic method 0 150000Awarded contract
2 -50000 90000 0.5250000 90000 Electronic failure
30000-120000 30000
0.7Magnetic success
Prepare proposal 120000Try magnetic method 0 120000
-50000 20000-80000 84000 0.3
Magnetic failure0
-120000 0
1 0.520000 Not awarded contract
-500000 -50000
Don't prepare proposal0
0 0
21
secoab
F
Thm
F
14 Chapter 16
econd branch (nontract, DriveTbout interpretat
Formatting
he following stmay choose to u
13. From thMove Odialog b
14. On sheCtrl+Shwith PrAlign L
igure 16.24 Tr
15. Select coption cash floWith thCells d(zero) fNumbe
6 Decision Trees
numbered 1, 2Tek should try tion.
the Tree D
teps show howuse other forma
he Edit menu, Or Copy from box, check the
eet Original (2)hift+T. In the Trobabilities is sLeft button.
reePlan Select
cell H12. Pressbutton for Cellow cells selecthose cells still dialog box, clicfor Decimal Plers. Click OK.
Using TreePlan
, and 3, from tothe electronic m
Diagram
w to use TreePlats for your ow
choose Move othe shortcut m
e Create A Cop
, if the probabiTreePlan Selecselected, and cl
Dialog Box
s Ctrl+Shift+Tls with Partial ed, if the cash selected, choosk the Number
laces; select $ i
op to bottom) imethod. A sub
an and Excel fwn tree diagram
or Copy Sheet menu). In the lowpy box, and clic
ilities are not lect dialog box, vlick OK. With
. In the TreePlCash Flows is flows are not lse Home > Fortab. In the Catin the Symbol l
is the optimal cbsequent chapte
features to formms.
(or right-clickwer left cornerck OK.
eft-aligned, selverify that the oall probability
lan Select dialoselected, and c
left-aligned, clirmat > Format tegory list box,list box; select
choice. If awarer provides mo
mat the tree dia
k the sheet tab ar of the Move O
lect cell H9. Proption button f
y cells selected,
og box, verify tclick OK. Withick the Align LCells. In the F
, choose Current -$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
F
igure 16.25 Ex
16. Select coption rollbacabove.
17. Select coption value cabove.
xcel Format Ce
cell I12. Press button for Cellk cells selected
cell S3. Press Cbutton for Cell
cells selected, c
ells Dialog Box
Ctrl+Shift+T. ls with Rollbacd, choose Form
Ctrl+Shift+T. Ils with Terminchoose Format
x
In the TreePlack EVs/CEs is
mat > Cells. Re
In the TreePlannal Values is se
> Cells. Repea
16.5 Step-by-S
an Select dialogselected, and c
epeat the Curre
n Select dialogelected, and cliat the Currency
Step TreePlan Tu
g box, verify thclick OK. With
ency formatting
g box, verify thick OK. With ay formatting of
Tutorial 215
hat the h all g of step 16
hat the all terminal f step 16
216 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.
123456789
10111213141516171819202122232425262728293031323334
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.5Electronic success
$150,0000.5 Try electronic method $0 $150,000Awarded contract
2 -$50,000 $90,000 0.5$250,000 $90,000 Electronic failure
$30,000-$120,000 $30,000
0.7Magnetic success
Prepare proposal $120,000Try 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
F
Esevadothfofo
P
igure 16.27 Ex
xplanation: A eparated by semalues, and text,oes not displayhe cell, but its rormat without hormats are save
21. Select coption rollbacCells >list box
22. Doublefrom th
Printing the
23. In the N
24. To prin& 2010Office the Pagthe optthe Heaapprop
xcel Format Ce
custom numbemicolons, and t, in that order. y positive numbresult is not dishaving to entered with the wor
cell A27. Pressbutton for Cellk values select
> Number. In thx, and select th
e-click the Formhe shortcut men
e Tree Diag
Name Box list
nt the tree diagr0 & 2013 & 20Button or File
ge tab; for Orieion button for ader list box seriate headers a
ells Dialog Box
er format has fothey define theWhen you spebers, negative nsplayed. Later, r the formula arkbook unless
s Ctrl+Shift+Tls with Rollbacted, choose Forhe Category lise three-semico
matted (2) sheenu), and enter M
gram
box, select Tre
ram from Exce016, choose Pag
> Print Previeentation click thFit To 1 Page W
elect None, andand footers). Cl
x
our sections of e formats for poecify three semnumbers, zero if you want togain. Editing ayou explicitly
. In the TreePlck EVs/CEs is rmat > Cells > st box, select Colon entry. Clic
et tab (or right-Model Inputs
eeDiagram (or
el, with the treege Layout > Prw > Page Setuhe option buttoWide By 1 Pagd in the Footer lick the Sheet t
16.5 Step-by-S
f format codes. ositive number
micolons withouvalues, or text
o display the rean existing formdelete a forma
lan Select dialoselected, and cNumber or Ho
Custom. Scroll ck OK.
-click the sheet. Save the wor
select cells A1
e diagram rangrint Area > Set
up. In the Page on for Landscage Tall. Click tlist box select
tab; clear the c
Step TreePlan Tu
The sections ars, negative numut format codest. The formula esult, you can cmat does not deat.
og box, verify tclick OK. Withome > Format to the bottom o
t tab and choosrkbook.
1:S34).
ge selected, in Et Print Area. ThSetup dialog b
ape, and for Scathe Header/FooNone (or selec
check box for G
Tutorial 217
are mbers, zero s, Excel remains in
change the elete it. All
that the h all > Format of the Type
se Rename
Excel 2007 hen choose box, click aling click oter tab; in ct other
Gridlines,
218 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 & 2016, 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.5Electronic success
$150,0000.5 Try electronic method $0Awarded contract
-$50,000 0.5$250,000 Electronic failure
$30,000-$120,000
0.7Magnetic success
Prepare proposal $120,000Try magnetic method $0
-$50,000-$80,000 0.3
Magnetic failure$0
-$120,000
0.5Not awarded contract
-$50,000$0
Don't prepare proposal$0
$0
F
1Trevutfo
F
FoU
FoU
igure 16.29 Al
6.6 EXPOreePlan's defauvent node and mtilities in TreePormulas for com
igure 16.30 Tr
or the MaximizU = A–B*EXP(
or the MinimizU = A-B*EXP(X
Prepare
-$50,000
Don't pre
$0
lternative Driv
ONENTIAult is to rollbacmaximum of sPlan's Options mputing the uti
reePlan Option
ze option with (-X/RT) and CE
ze option with X/RT) and CE
0.5Awa
$250
proposal
0
0.5Not
$0
epare proposal
veTek Decision
AL UTILITYck the tree usinuccessor valuedialog box, Trility and certain
ns Dialog Box
exponential utE = -LN((A-EU
exponential uti= LN((A-EU)
arded contract
0,000
awarded contract
n Tree
Y AND TRg expected val
es at a decisionreePlan will redn equivalent at
tility, the rollbaU)/B)*RT, whe
ility, the formu/B)*RT.
Use mechanical method
-$120,000
Try electronic method
-$50,000
Try magnetic method
-$80,000
16.6 Exponenti
REEPLANlue (probabilityn node. If you cdraw the decisit each node.
ack formulas aere X and EU a
ulas are
0.5Electronic success
$0
0.5Electronic failure
$0
0.7Magnetic success
$0
0.3Magnetic failure
$0
ial Utility and Tre
N y-weighted avechoose to use eion tree diagram
are are cell referen
Use mechanical 1
-$120,000
Use mechanical 1
-$120,000
eePlan 219
erage) at an exponential m with
nces.
$80,000
$150,000
method$30,000
$120,000
method$0
-$50,000
$0
220 Chapter 16 Decision Trees Using TreePlan
TreePlan uses the name RT to represent the risk tolerance parameter of the exponential utility function. The names A and B determine scaling. When you choose to use exponential utility, if the names A, B, and RT do not already exist in the worksheet or workbook, they are initially defined as A=1, B=1, and RT=999999999999.
In Windows Excel you can change the values of defined names using Formulas > Name Manager.
In Mac Excel 2016 you can change the values of defined names by choosing (from the topmost menu) Insert > Name > Define Name. In the Define Name dialog box, after you select a name in the “Names in workbook” list box, enter a new value in the “Select the range of cells” edit box, and click the OK button.
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–2016.