A metropolitan school system consists of three districts – north, south, and central.

A metropolitan school system consists of three districts – north, south, and central. The north district contains 25% of all students, the south district contains 40%, and the central district contains 35%. A minimum-competency test was given to all students: 10% of the north district students failed. 15% of the south district students failed, and 5% of the central district students failed. a. Develop a probability tree showing all marginal, conditional, and joint probabilities. b. Develop a joint probability table. c. What is the probability that a student selected at random failed the test?

Decision Trees.doc Page 1 of 7 Decision Trees The Decision Tree module in Excel OM (and in POM for Windows) acts differently than all other modules because rather than creating a table of data it creates a graphical tree. We will use Example 3 in Chapter A5 from Heizer & Render’s Operations Management textbook for our example. After selecting the Decision Tree Module the screen will appear as in Figure 1 below. Figure 1: The Initial Decision Tree Screen Notice the Decision Tree Creation Window on the right. This is used to perform all of the work of constructing the tree. The initial screen has 1 starting node (Node 1) which can be seen in cell A6. The first step is to add branches from this node. The default setting is to Add 2 Decision branches. The type of branch is selected by the choice of “Add” buttons that is selected and the number of branches is selected by the textbox/scrollbar combination. For this example, there are three options – Purchase CAD, Hire Engineers or Do Nothing. Therefore we will change the number of branches to 3 and click on the “Add Decisions” button. This yields the screen as displayed in Figure 2. Decision Trees.doc Page 2 of 7 Figure 2: The First Three Branches At this point the data can be entered into the shaded green cells. For decision branches, the data consists of the name of the branch and possibly a profit or cost. If the branch is at the end of the tree then the profit gets entered. If the branch is not at the end then any intermediate profits or costs can be entered. Costs are entered as negative numbers. We will display the data in the next screen. We have entered the data as displayed in Figure 3. Decision Trees.doc Page 3 of 7 Figure 3: The First Three Branches with Data The names have been entered and the costs have been entered as negative numbers. Notice that the costs were placed on the far right side of each branch. These are the profits of the branch and will be more meaningful after a little more explanation. Also notice that numbers appear above Node 1. The 0 in cell A9 represents the best profit available so far while the number below the node, the 4 in A11, indicates whih direction to take (go to node 4) in order to achieve this profit. For either the CAD system or for hiring engineers there could be a favorable market or an unfavorable market with probabilities of .4 and .6, respectively. Therefore we need to add two event (chance or probabilistic) branches to both node 2 and node 3. The selected node is node 2 (see the top line in the Decision Tree Creation window above. We can click on a node to select it or we can use the scrollbar to select the node. We will leave the node at node 2 and click on the “Add Events button” yielding the screen displayed in Figure 4. Decision Trees.doc Page 4 of 7 Figure 4: Adding Event Nodes Again, we need to enter the data. The default probabilities are set to 1/n when n event nodes are entered but these can be changed. It is not necessary to change the probability of the last event because this is set so that the probabilities sum to one. In this example, we simply need to change the .5 for event 1 to .4 and the .5 for event 2 will automatically change. In cell F7 we will enter the formula for the profits under CAD for a FAVORABLE market (25,000 processors sold at $100 each which cost $40 each). In cell F11 we will enter the formula for the profits under CAD for an UNFAVORABLE favorable market (8,000 processors sold at $100 each which cost $40 each). The screen appears as in Figure 5. Decision Trees.doc Page 5 of 7 Figure 5: Data for Consequences of Purchasing the CAD Notice that the profit for a favorable market is $1,500,000 while the profit for an unfavorable market is $480,000. Above node 2 is $888,000 which is the weighted average of these two profits. Above node 1 is the best profit (so far) of $388,000 given by subtracting the $500,000 cost of the CAD from the profit of $888,000 that is expected if the CAD is selected. Also, notice that the number below node 1 is 2 because that is the choice that should be made – node 2. Finally, notice that he numbers on the right of nodes 5 and 6 represent the total profit from the beginning of the tree to the end of the branch. The $1,000,000 and the -$20,000 match the diagram in Figure 5.14 of the Heizer and Render textbook. We need to add the two event (favorable or unfavorable markets) branches for Hiring Engineers. We could repeat the process that we used for adding the branches to Purchasing the CAD but instead we will demonstrate how to copy and paste subtrees. Select node 2. We have already done this as you can see from the Selected Node at the top of the Decision Tree Creation Window. Notice that in the middle of Creation Window there is a frame named “Other actions at node.” The third button says “Copy subtree at node 2”. Click here to copy everything to the right of node 2. Now click on node 3, go to the “Other actions at node” frame and click on the button that say “Paste subtree at node 3”. This will copy the entire structure, including numbers to node 3 as shown in Figure 6 below. Decision Trees.doc Page 6 of 7 Figure 6: The Results of Copying and Pasting a Subtree While this saves a great deal of work we are not finished. The numbers form above were copied. This is alright for the probabilities above the branches because they stay the same in this example but the profits below the branches change so we must change the formulas to match the example. In cell F15 we will enter the formula for the profits under HIRING for a FAVORABLE market (25,000 processors sold at $100 each which cost $50 each). In cell F11 we will enter the formula for the profits under CAD for an UNFAVORABLE favorable market (8,000 processors sold at $100 each which cost $50 each). The final screen appears as in Figure 7. Decision Trees.doc Page 7 of 7 Essentially, this screen matches Figure 5.14. The placement of the costs for Cad and Hiring is considered in the middle of our Excel workbook while it has been considered at the ends of the branches in the textbook. The results are the same. The best profit as displayed above node 1 is $388,000 and the way to achieve this profit by s