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