系统工程实验课指导书

《Systems Engineering》 Experiments Guide

2003.12.20

Systems Engineering Experiment Ⅰ

1. Experiment Objectives:

(1) Learn to build the decision trees model on an Excel Spreadsheet;

(2) Learn to apply the TreePlan (an Excel add-in) to solve the decision problem.

2. Experiment Tools:

The Microsoft Excel (the TreePlan has been installed).

3. Experiment Tasks:

(1) Familiarize with the Excel Treeplan environment. (2) Solve the example problem using Treeplan.

Experiment Part Ⅰ:

Requirement: Familiarize with the Excel Treeplan environment. Procedure:

You can start a new tree in TreePlan either by hitting Ctrl-t or by selecting Tools, Decision

Tree from the menu bar. TreePlan then prompts弹出对话框 you with a dialog box with three options; choose New to begin a new tree. TreePlan will then draw 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 97 (or later version) a terminal node is represented by a vertical bar instead of a triangle.

A12345678910BCDEFGHDecision 10010Decision 20000

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

2

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 branch in the tree to modify and select Tools, Decision Tree or hit 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 terminal node at the end of that branch ($G$5) and hit Ctrl-t. To select a terminal node in Excel 97 (or later version), select the square cell next to the vertical line at the end of a terminal branch. 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 hitting OK. TreePlan then redraws the tree with a chance node in place of the terminal node.

B345678910111213141516CDEFGHI0.5Event 30Decision 10000.5Event 4010Decision 2000000JKL The dialog boxes presented by TreePlan vary depending on what you have selected when you choose Tools, Decision Tree or hit Ctrl-t. The dialog box shown below is presented when you hit 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 hit OK. If you want to insert a decision or event node before the selected node choose Insert decision or Insert event and hit OK. To get a description of the available commands, click on the Help button.

3

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 \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 hit Ctrl-t without a node selected. You can also bring up this dialog box by hitting 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 hit OK, TreePlan selects all of the 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.)

Experiment Part Ⅱ:

Requirement: Learn how to build and solve the decision tree by following the example. An example of a TreePlan decision tree is shown below. In the example, a firm must decide whether to purchase an option买卖选择权，期权 on the defense market rights认股权 to a new flight safety system. 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 \paid\to go down that branch. Here, the firm pays $125,000 if it decides to purchase the option and another $575,000 if it signs the license agreement.

4

ROLLBACK EVs: Equal to the expected value at this point in the tree.0.15Win First Contract$4,2000.71License Agreement-$575DECISION NODE: The number in the node indicates Purchase OptionEVENT -$125$1000.29No License Agreement1$100$0-$125$0-$700TERMINAL NODES -$125$1930.85No Contract-$700$5,250TERMINAL VALUES: Equal to sum of partial cash flows along path.$10,500$7,0000.75Sublicense$3,500$0$3,500$10,500PROBABILITIES: Enter numbers or formulas in these cell.PARTIAL CASH FLOWS: Enter numbers or formulas in these cell.0.25Win Second ContractReject Purchase$0$0BRANCH LABELS: Type text in these cells$0All dollar amounts are thousands ($000).Decision Tree based on AIL Example in Ulvila and Brown, \Harvard Business Review, September-October 1982 The trees are %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 \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 \in the decision node indicates that it is optimal to \expected value ($100,000).

TreePlan has a few options that control the way calculations are done in the tree. To select these options, hit 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.

5

A1234567891011121314BCDEFGH0.6High sales+$300Introduce product-$300+$100+$6000.4Low sales+$100-$200+$300=1-H1-$2001+$100Model Output CellDon't introduce$0$0$0IJKLModel Input Cell NO+$1000.000.100.200.300.400.500.600.700.800.901.00P=A10M1234567891011121314

M1234567891011121314NOP(High Sales)Exp. Value0.000.100.200.300.400.500.600.700.800.901.000000050100150200250300P 6

To solve the problem, operate as the following procedure step by step.

1. Construct a decision tree model or financial planning model. 2. Identify the model input cell (H1) and model output cell (A10).

3. Modify the model so that probabilities will always sum to one. (That is, enter the

formula =1-H1 in cell H6.) 4. Enter a list of input values in a column (N3:N13).

5. Enter a formula for determining output values at the top of an empty column on the right

of the input values (=A10 in cell O2). 6. Select the data table range (N2:O13).

7. From the Data menu choose the Table command.

8. In the Data Table dialog box, select the Column Input Cell edit box. Type the model

input cell (H1), or point to the model input cell (in which case the edit box displays $H$1). Click OK. 9. The Data Table command substitutes each input value into the model input cell,

recalculates the worksheet, and displays the corresponding model output value in the table. 10. Optional: Change the formula in cell O2 to =CHOOSE(B9,”Introduce”,”Don’t”).

4. Finish the experimentation report.

7

Systems Engineering Experiment Ⅱ

1. Experiment Objectives

From OR Courseware Recall the following contents: (1) The interpretation of the Decision Trees

(2) The procedure of building decision trees by Treeplan

2. Experiment Tasks

Problem :

The GOFERBROKE COMPANY owns a tract of land that may contain oil. A consulting

geologist has reported to management that she believes there is 1 chance in 4 of oil. Because of this prospect, another oil company has offered to purchase the land for $90,000. However, Goferbroke is considering holding the land in order to drill for oil itself. The cost of drilling is $100,000. If oil is found, the resulting expected revenue will be $800,000, so the company's expected profit (after deducting the cost of drilling) will be $700,000. A loss of $100,000 (the drilling cost) will be incurred if the land is dry (no oil).

How to approach the decision of whether to drill or sell based just on these data. However, before deciding whether to drill or sell, another option is to conduct a detailed seismic survey of the land to obtain a better estimate of the probability of finding oil.

Status of Land Alternative Oil $700,000 $90,000 1 in 4 Payoff Dry -$100,000 $90,000 3 in 4 Drill for Oil Sell the Land Chance of Status Questions:

(a) Formulate the decision tree model for this problem. (b) Build the model on an Excel spreadsheet. (c) Use the Excel Treeplan to solve the model.

3. Finish the experimentation report.

8

Systems Engineering Experiment Ⅲ

1. Experiment Objectives:

Learn how to manage project using Project 2000

2. Experiment Tasks:

Formulate and solve the following project management problems using Project 2000. Problem:

The RELIABLE CONSTRUCTION COMPANY has just made the winning bid of $5.4 million to

construct a new plant for a major manufacturer. The manufacturer needs the plant to go into operation within a year. Therefore, the contract includes the following provisions:

? A penalty of $300,000 if Reliable has not completed construction by the deadline 47 weeks

from now. ? To provide additional incentive for speedy construction, a bonus of $150,000 will be paid to

Reliable if the plant is completed within 40 weeks. The following table shows the list of the various activities. The third column provides important additional information for coordinating the scheduling of the crews.

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

When a given activity has more than one immediate predecessor, all must be finished before

Activity Description Excavate Lay the foundation Put up the rough wall Put up the roof Install the exterior plumbing Install the interior plumbing Put up the exterior siding Do the exterior painting Do the electrical work Put up the wallboard Install the flooring Do the interior painting Install the exterior fixtures Install the interior fixtures Immediate Predecessors → A B C C E D E,G C F,I J J H K,L Estimated Duration 2 weeks 4 weeks 10 weeks 6 weeks 4 weeks 5 weeks 7 weeks 9 weeks 7 weeks 8 weeks 4 weeks 5 weeks 2 weeks 6 weeks 9

the activity can begin. Adding up these times gives a grand total of 79 weeks, which is far beyond the deadline for the project. Fortunately, some of the activities can be done in parallel, which substantially reduces the project completion time.

Given all the information in the above table, develop answers to the following questions using project 2000.

1. What is the total time required to complete the project if no delays occur?

2. When do the individual activities need to start and finish (at the latest) to meet this project

completion time? 3. When can the individual activities start and finish (at the earliest) if no delays occur? 4. Which are the critical bottleneck activities where any delays must be avoided to prevent

delaying project completion? 5. For the other activities, how much delay can be tolerated without delaying project

completion? 6. What is the Critical Path?

3. Finish the experimentation report.

10

Systems Engineering Experiment Ⅳ

1. Experiment Objectives:

Recall project management operation and have a quiz in class

2. Experiment Tasks:

Task 1: Solve the following problem:

Construct the project network for a project with the following activities list. Please calculate

the engineering time, and find the critical path.

Series Numbers 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Activity A B C D E F G H I J K L M N Immediate Predecessors - A B B B C D, E F G, H I I J K L Estimated Duration 1 Month 2 Months 4 Months 3 Months 2 Months 3 Months 5 Months 1 Month 4 Months 2 Months 3 Months 3 Months 5 Months 4 Months

Task 2: A quiz

Open file of ORtest2.htm and give the answer to each problem, submit the answer and get the score.

11

Systems Engineering Experiment Ⅳ

1. Experiment Objectives:

Recall project management operation and have a quiz in class

2. Experiment Tasks:

Task 1: Solve the following problem:

Construct the project network for a project with the following activities list. Please calculate

the engineering time, and find the critical path.

Series Numbers 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Activity A B C D E F G H I J K L M N Immediate Predecessors - A B B B C D, E F G, H I I J K L Estimated Duration 1 Month 2 Months 4 Months 3 Months 2 Months 3 Months 5 Months 1 Month 4 Months 2 Months 3 Months 3 Months 5 Months 4 Months

Task 2: A quiz

Open file of ORtest2.htm and give the answer to each problem, submit the answer and get the score.

11

本文来源：https://www.bwwdw.com/article/q34g.html