Excel建模插件treeplan_treeplan免费下载,treeplan下载资源-CSDN文库

共3个文件

xla：1个

pdf：1个

xls：1个

treeplan

4星 · 超过85%的资源需积分: 50 120 浏览量 2008-11-17 13:46:24 上传评论 6 收藏 439KB RAR 举报

资源推荐

资源详情

资源评论

收起资源包目录

TreePlan.rar （3个子文件）

TreePlan

treeplan.xla 189KB

treeplan.pdf 478KB

treesamp.xls 128KB

Decision Trees Using TreePlan

Add-In for Microsoft Excel

Contents

Chapter 1 Quick Start........................................................................................2

TreePlan Installation.............................................................................................2

Occasional Use....................................................................................................2

Selective Use.......................................................................................................2

Steady Use..........................................................................................................2

Building a Decision Tree in TreePlan.....................................................................3

The Anatomy of a TreePlan Decision Tree............................................................5

Chapter 2 Step-by-Step TreePlan Tutorial ....................................................6

DriveTek Problem................................................................................................6

Nodes and Branches............................................................................................7

Terminal Values...................................................................................................7

Building the Tree Diagram....................................................................................8

Interpreting the Results.......................................................................................13

Formatting the Tree Diagram..............................................................................14

Displaying Model Inputs .....................................................................................16

Printing the Tree Diagram..................................................................................17

Alternative Model..............................................................................................19

Chapter 3 Decision Tree Solution.................................................................20

Strategy.............................................................................................................20

Payoff Distribution.............................................................................................20

DriveTek Strategies ...........................................................................................20

Strategy Choice .................................................................................................25

Certainty Equivalent...........................................................................................25

Rollback Method................................................................................................27

Optimal Strategy................................................................................................28

Chapter 4 Sensitivity Analysis........................................................................30

One-Variable Sensitivity Analysis using an Excel data table ..................................30

Two-Variable Sensitivity Analysis using an Excel data table..................................32

Chapter 1 Quick Start

TreePlan is an Excel add-in that allows you to build decision trees in Excel. It was developed by Professor

Michael R. Middleton at the University of San Francisco and modified for use at Fuqua (Duke) by

Professor James E. Smith.

TreePlan Installation

All of TreePlan’s functionality is in a single file, TreePlan.xla. Depending on your preference, there are

three ways to install TreePlan. (These instructions also apply to the other Decision ToolPak add-ins:

SensIt.xla and RiskSim.xla.)

Occasional Use

If you plan to use TreePlan on an irregular basis, simply use Excel’s File | Open command to load

TreePlan.xla each time you want to use it. You may keep the TreePlan.xla file on a floppy disk, your

computer’s hard drive, or a network server.

Selective Use

You can use Excel’s Add-In Manager to install TreePlan. First, copy TreePlan.xla to a location on your

computer’s hard drive. Second, if you save TreePlan.xla in the Excel or Office Library subdirectory, go to

the third step. Otherwise, run Excel, choose Tools | Add-Ins; in the Add-Ins dialog box, click the Browse

button, use the Browse dialog box to specify the location of TreePlan.xla, and click OK. Third, in the Add-

Ins dialog box, note that TreePlan is now listed with a check mark, indicating that its menu command will

appear in Excel, and click OK.

If you plan to not use TreePlan and you want to free up main memory, uncheck the box for TreePlan in

the Add-In Manager. When you do want to use TreePlan, choose Tools | Add-Ins and check TreePlan’s

box.

To remove TreePlan from the Add-In Manager, use Windows Explorer or another file manager to delete

TreePlan.xla from the Library subdirectory or from the location you specified when you used the Add-In

Manager’s Browse command. The next time you start Excel and choose Tools | Add-Ins, a dialog box will

state “Cannot find add-in … treeplan.xla. Delete from list?” Click Yes.

Steady Use

If you want TreePlan’s options immediately available each time you run Excel, use Windows Explorer or

another file manager to save TreePlan.xla in the Excel XLStart directory. Alternatively, in Excel you can

use Tools | Options | General to specify an alternate startup file location and use a file manager to save

TreePlan.xla there. When you start Excel, it tries to open all files in the XLStart directory and in the

alternate startup file location.

For additional information visit “TreePlan FAQ” at www.treeplan.com.

After opening TreePlan.xla in Excel, the command "Decision Tree" appears at the bottom of the Tools

menu (or, if you have a customized main menu, at the bottom of the sixth main menu item).

Building a Decision Tree in TreePlan

You can start TreePlan either by choosing Tools | Decision Tree from the menu bar or by pressing

Ctrl+t (hold down the Ctrl key 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 $B$2 is selected. (Note that TreePlan writes over existing values in the

spreadsheet: begin your tree to the right of the area where your data is stored, and do not subsequently

add or delete rows or columns in the tree-diagram area.) In Excel 5 and 95 a terminal node is

represented by a triangle instead of a vertical bar.

A B C D E F G H I

Decision 1

0 0

Decision 2

0 0

Build up a tree by adding or modifying branches or nodes in the default tree. To change the branch labels

or probabilities, click on the cell containing the label or probability and type the new label or probability. To

modify the structure of the tree (e.g., add or delete branches or nodes in the tree), select the node or the

cell containing the node in the tree to modify, and choose Tools | Decision Tree or press Ctrl+t.

TreePlan will then present a dialog box showing the available commands.

For example, to add an event node to the top branch of the tree shown above, select the square cell (cell

G4) next to the vertical line at the end of a terminal branch and press Ctrl+t.. TreePlan then presents this

dialog box.

To add an event node to the branch, we change the selected terminal node to an event node by selecting

Change to event node in the dialog box, selecting the number of branches (here two), and pressing OK.

TreePlan then redraws the tree with a chance node in place of the terminal node.

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

0.5

Event 3

Decision 1 0 0

0 0 0.5

Event 4

1 0 0

Decision 2

0 0

The dialog boxes presented by TreePlan vary depending on what you have selected when you choose

Tools | Decision Tree or press Ctrl+t. The dialog box shown below is presented when you press Ctrl+t

with an event node selected; a similar dialog box is presented when you select a decision node. If you

want to add a branch to the selected node, choose Add branch and press OK. If you want to insert a

decision or event node before the selected node, choose Insert decision or Insert event and press OK.

To get a description of the available commands, click on the Help button.

The Copy subtree command is particularly useful when building large trees. If two or more parts of the

tree are similar, you can copy and paste "subtrees" rather than building up each part separately. To copy a

subtree, select the node at the root of the subtree and choose Copy subtree. This tells TreePlan to copy

the selected node and everything to the right of it in the tree. To paste this subtree, select a terminal node

and choose Paste subtree. TreePlan then duplicates the specified subtree at the selected terminal node.

Since TreePlan decision trees are built directly in Excel, you can use Excel's commands to format your

tree. For example, you can use bold or italic fonts for branch labels: select the cells you want to format

and change them using Excel's formatting commands. To help you, TreePlan provides a Select dialog box

that appears when you choose Tools Decision Tree or press Ctrl+t without a node selected. You can

also bring up this dialog box by pressing the Select button on the Node dialog box. From here, you can

select all items of a particular type in the tree. For example, if you choose Probabilities and press OK,

TreePlan selects all cells containing probabilities in the tree. You can then format all of the probabilities

simultaneously using Excel's formatting commands. (Because of limitations in Excel, the Select dialog box

will not be available when working with very large trees.)

The Anatomy of a TreePlan Decision Tree

An example of a TreePlan decision tree is shown below. In the example, a firm must decide (1) whether

to prepare a proposal for a possible contract and (2) which method to use to satisfy the contract. The tree

consists of decision nodes, event nodes and terminal nodes connected by branches. Each branch is

surrounded by cells containing formulas, cell references, or labels pertaining to that branch. You may edit

the labels, probabilities, and partial cash flows associated with each branch. The partial cash flows are

the amount the firm "gets paid" to go down that branch. Here, the firm pays $50,000 if it decides to

prepare the proposal, receives $250,000 up front if awarded the contract, spends $50,000 to try the

electronic method, and spends $120,000 on the mechanical method if the electronic method fails.

Use mechanical method

$80,000

0.5 -$120,000 $80,000

Awarded contract

2 0.5

$250,000 $90,000 Electronic success

$150,000

Try electronic method $0 $150,000

Prepare proposal -$50,000 $90,000 0.5

Electronic failure

-$50,000 $20,000 $30,000

-$120,000 $30,000

0.5

1 Not awarded contract

$20,000 -$50,000

$0 -$50,000

Don't prepare proposal

$0 $0

BRANCH LABELS:

Type text in these

cells.

DECISION NODES: TreePlan formula

for which alternative is optimal.

PARTIAL CASH FLOWS:

Enter numbers or

formulas in these cells.

PROBABILITIES: Enter numbers

or formulas in these cells.

ROLLBACK EVs: TreePlan formula for

expected value at this point in the tree.

TERMINAL NODES

EVENT NODES

TERMINAL VALUES: TreePlan formula for

sum of partial cash flows along path.

The trees are "solved" using formulas embedded in the spreadsheet. The terminal values sum all the

partial cash flows along the path leading to that terminal node. The tree is then "rolled back" by computing

expected values at event nodes and by maximizing at decision nodes; the rollback EVs appear next to

each node and show the expected value at that point in the tree. The numbers in the decision nodes

indicate which alternative is optimal for that decision. In the example, the "1" in the first decision node

indicates that it is optimal to prepare the proposal, and the "2" in the second decision node indicates the

firm should try the electronic method because that alternative leads to a higher expected value, $90,000,

than the mechanical method, $80,000.

TreePlan has a few options that control the way calculations are done in the tree. To select these options,

press the Options button in any of TreePlan's dialog boxes. The first choice is whether to Use Expected

Values or Use Exponential Utility Function for computing certainty equivalents. The default is to

rollback the tree using expected values. If you choose to use exponential utilities, TreePlan will compute

utilities of endpoint cash flows at the terminal nodes and compute expected utilities instead of expected

values at event nodes. Expected utilities are calculated in the cell below the certainty equivalents. You

may also choose to Maximize (profits) or Minimize (costs) at decision nodes; the default is to

maximize profits. If you choose to minimize costs instead, the cash flows are interpreted as costs, and

decisions are made by choosing the minimum expected value or certainty equivalent rather than the

maximum. See the Help file for details on these options.

评论收藏

内容反馈

wxwj2008

2013-05-16

用EXCEL做决策支持是EXCEL应用比较高端的层次，代表了EXCEL在管理中运用的最高层次，学习下
umwelt99

2012-08-14

很好，2010也可以用，树形结构胡报表真有点难度。
silent_light

2012-11-26

不错，简单易用
高兴的123

2020-10-29

加载不上,365没法用
li_yuh

2011-11-23

不错可用！ 2003 2007都可兼容 2010的应该也可以

前往

页

hxygsh

粉丝: 1
资源: 4

Excel 建模插件 treeplan

最新资源

Excel 建模插件 treeplan

Excel 决策插件Treeplan

treeplan.xla

Excel插件Treeplan决策树

TREEPLAN.XLA

TreePlan_Toolkit

treeplan 决策树

Treeplan 正式版

treeplan Excel决策树

决策树excel插件

EXCEL决策树文件

excel财务建模实例excel财务建模实例

MAYA建模插件合集

CAD山地建模插件

unity建模插件ProBuilder

Excel决策树插件

excel树控件

BYTreePlan.xlam

risksim和treeplan.rar

excel 百宝箱 4.0.xla

Decision Tree Excel 插件，决策树

PHStat 4.1 著名的EXCEL统计插件

3DSMAX Nurbs 建模插件POWER_NURBS

六边形蜂窝自动建模插件

Abaqus中蜂窝结构快速建模插件

excel 数据建模实例文件

UG齿轮快速精确建模插件

excel建模线性规划.ppt

excel 统计建模

Revit基于Excel建模.pdf

最新资源