Using Spreadsheets to Perform Sensitivity analysis on Decision Trees使用电子表格来对决策树进行敏感性分析.docVIP

IndE 311: Stochastic Models and Decision AnalysisWinter 2007 Lab 1: Using spreadsheets to build and analyze decision trees The objective of the lab is to familiarize you with two Excel add-ins (TreePlan Sensit) to build and analyze decision trees. Part 0: Get ready Go to class web page (/inde311), then select “Labs”. Download the two add-ins (TreePlan and SensIt): Right-click on each link, select ‘Save Target as…’ and save the two files on the desktop. Open Microsoft Excel, select Tools - Add-Ins… Select Browse and point to the two add-ins (on the desktop now) one by one. Verify that the add-ins are installed by checking if your Tools menu contains “Decision Tree” and “Sensitivity Analysis”, i.e. as below:  Part 1: Create a decision tree using TreePlan Consider the first Goferbroke Co. problem in the text book (no seismic survey) Step 1: In Microsoft Excel, select decision tree from the Tools menu and click on New Tree. See Figure 1.1. Figure 1.1 This creates the default decision tree as shown in figure 1.2 with a single (square) decision node with two branches. Figure 1.2 Note: To change the type of any node in TreePlan, select the cell containing the node (B5 in figure 2) and choose Decision Tree from the Tools menu. This brings up a dialogue box that allows you to change the types of node. Step 2: Click on the cells to change the labels. Change labels for “Decision 1” and “Decision 2” (cells D2 D7 in figure 2) to “Drill” and “Sell” respectively. Step 3: Click on the cell containing the terminal node at the end of the drill branch (F3 in Figure 1.2), and choose Decision Tree from the Tools menu. This brings up the TreePlan dialog as shown in figure 1.3 Figure 1.3 Choose the “change to event node” option on the left and select two branches on the right then click OK. This results in the decision tree with the nodes and branches shown in Figure 1.4 (after replacing the default labels “Event 1” and “Event 2” with “Oil” and “Dry”, respectively)