Tutorial for Preparation of Assignment 2 and for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Table of Content Task 2-1: Payoff Table Preparation & Simplified Decision Tree Task 2-2: EMV Calculations Task 2-3: Sensitivity Analysis Task 2-4: Decision Tree in Excel Add-In ‘TreePlan’ BU MET AD 715 Quantitative & Qualitative Decision-Making © 2018 Boston University Metropolitan College Slide 1 Example of Decision Tree Analysis Problem Description: A business owner is considering whether to open a new shop in City A. There are three decision alternatives: (1) Open a small shop (2) Open a medium-sized shop (3) Do not open a shop The amount of payoffs (profit) depends on the following market conditions: (1) Good Market (2) Average Market (3) Bad Market The probabilities for Good Market, Average Market, and Bad Market are predefined. For the purpose of the sensitivity analysis, the probability of Bad Market condition is given. © 2018 Boston University Metropolitan College Slide 2 Payoff Table: Template Task 2-1 Payoff Table Preparation & Simplified Decision Tree The estimated payoffs and probabilities are shown as follows: States of Nature Alternatives Good Market Average Market Bad Market Small Shop $40,000 $20,000 -$15,000 Medium-sized shop $70,000 $30,000 -$50,000 No shop $0 $0 $0 Probability 0.3 0.4 0.3 Probability for Sensitivity Analysis P 0.6 - P i = 0.4 0.6 © 2018 Boston University Metropolitan College Slide 3 Payoff Table: Template Task 2-1 Payoff Table Preparation & Simplified Decision Tree The estimated payoffs and probabilities are shown as follows: States of Nature Alternatives Good Market Average Market Bad Market Small Shop $40,000 $20,000 -$15,000 Medium-sized shop $70,000 $30,000 -$50,000 No shop $0 $0 $0 Probability 0.3 0.4 0.3 © 2018 Boston University Metropolitan College Slide 4 Task 2-1: Payoff Table Solution Task 2-1 Payoff Table Preparation & Simplified Decision Tree (continued) Based on the payoff table, we can draw the simplified decision tree (without probabilities and EMVs), where 1 is a decision node, and 2, 3, and 4 are chance nodes: © 2018 Boston University Metropolitan College Slide 5 Task 2-2: EMV Calculation Task 2-2: EMV Calculation Based on the payoff table, the EMV for each decision alternative could be calculated as below: (1) EMV (Small Shop) = ($40,000)*0.3 + ($20,000)*0.4+(-$15,000)*0.3 =$15,500 (2) EMV (Medium-Sized Shop) = ($70,000)*0.3+($30,000)*0.4+(-$50,000)*0.3 =$18,000 (3) EMV (No Shop) = $0*0.3+$0*0.4+$0*0.3 =$0 © 2018 Boston University Metropolitan College Slide 6 Task 2-2: Solution – EMV Calculation Task 2-2: EMV Calculation (continued) Payoff Table Results: States of Nature Alternatives EMV Good Market Average Market Bad Market Small Shop $40,000 $20,000 -$15,000 $15,500 Medium-sized shop $70,000 $30,000 -$50,000 $18,000 No shop $0 $0 $0 $0 Probability 0.3 0.4 0.3 © 2018 Boston University Metropolitan College Slide 7 Task 2-2: Solution Task 2-2 Solution: The best EMV is EMV (Medium-sized shop) States of Nature Alternatives EMV Node -$15,000 $15,500 2 $30,000 -$50,000 $18,000 3 $0 $0 $0 $0 4 0.3 0.4 0.3 Good Market Average Market Bad Market Small Shop $40,000 $20,000 Medium-sized shop $70,000 No shop Probability Final Decision EMV Node Medium-sized Shop $18,000 1 © 2018 Boston University Metropolitan College Slide 8 Task 2-3: Sensitivity Analysis Task 2-3: Sensitivity Analysis: Let’s assume that the probability of Bad Market condition is i = 0.4 Use sensitivity analysis to determine the probability range for Good Market which would change the business owner's decision. Draw a sensitivity chart and find the probability for the cross points. © 2018 Boston University Metropolitan College Slide 9 Task 2-3: Sensitivity Analysis The given information is that the probability of Bad Market is 0.4. Therefore, the total probability of Good Market and Average Market is 0.6. If we set P as the probability of Good Market, the probability of Average Market would be (0.6-P). States of Nature Alternatives Good Market Average Market Bad Market Small Shop $40,000 $20,000 -$15,000 Medium-sized shop $70,000 $30,000 -$50,000 No shop $0 $0 $0 Probability P 0.6 - P 0.4 © 2018 Boston University Metropolitan College Slide 10 Task 2-3: Sensitivity Analysis States of Nature Alternatives Small Shop Medium-sized shop No shop Probability EMV (No shop) EMV Good Market Average Market Bad Market $40,000 $20,000 -$15,000 $20,000*P+$6,000 $70,000 $30,000 -$50,000 $40,000*P-$2,000 $0 $0 $0 $0 P 0.6-P 0.4 = $0*P+$0*(0.6-P)+$0*0.4 = $0 EMV (Medium-sized shop)= ($70,000)*P+($30,000)*(0.6-P)+(-$50,000)*0.4 = $40,000*P-$2,000 EMV(Small Shop) = ($40,000)*P + ($20,000)*(0.6-P)+(-$15,000)*0.4 = $20,000*P+$6,000 © 2018 Boston University Metropolitan College Slide 11 Task 2-3: Sensitivity Analysis In summary, we have the equations for the three EMVs (in dollars), where P is the probability of Good Market: EMV (Small Shop)=$20,000*P + $6,000 EMV (Medium-sized shop)=$40,000*P - $2,000 EMV (No shop)=0 When P = 0: EMV(Small Shop)=$6,000 EMV(Medium-sized Shop)=-$2,000 EMV(No shop)=$0 When P = 0.6: EMV(Small Shop)=$18,000 EMV(Medium-sized Shop)=$22,000 EMV(No shop)=$0 Now, we can use these points to draw the lines for EMVs to do sensitivity analysis. © 2018 Boston University Metropolitan College Slide 12 Task 2-3: Sensitivity Analysis Based on the previous equations, here are the three lines representing the EMVs for each decision alternatives respectively: $40,000*P - $20,000*P = = $6,000 + $2,000 Sensitivity Analysis EMV $40,000*P - $2,000 = =$20,000*P + $6,000 $25,000 EMV (Medium-sized Shop) $20,000 $15,000 $20,000*P = $8,000 EMV (Small Shop) $10,000 Cross Point 2: P = 0.4 P = $8,000/$20,000 = 0.4 $5,000 Cross Point 1: P = 0.05 $40,000*P-2000 = 0 EMV (No Shop) $0 0 P = 2,000/$40,000 = 0.05 ($5,000) © 2018 Boston University Metropolitan College 0.1 0.2 0.3 0.4 0.5 0.6 P (Good Market) Slide 13 Task 2-3: Solution From the sensitivity chart, it’s obvious to see the cross points: When P = 0.05 EMV (Medium-sized Shop) = EMV (No Shop) = $0; When P = 0.4 EMV (Small Shop) = EMV (Medium-sized Shop) = $14,000 Probability ranges for Good Market: When 0 < P(Good Market) < 0.4 EMV (Small Shop) is the highest EMV, opening a small shop is the best choice. When 0.4 < P (Good Market) < 0.6 EMV (Medium-sized Shop) is the highest EMV, therefore opening a medium-sized shop is the best choice. © 2018 Boston University Metropolitan College Slide 14 Task 2-4: Decision Tree in Excel Add-In ‘TreePlan’ Use the following Tutorial for Building a Decision Tree in Excel Add-in ‘TreePlan’: A Step-By-Step Approach Step 1: Getting Started Step 2: Adding Branch Step 3: Naming Alternatives Step 4: Adding chance node Step 5: Naming Alternatives Step 6: Copy Subtree Step 7: Paste Subtree Step 8: Inserting Values © 2018 Boston University Metropolitan College Slide 15 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ What is ‘TreePlan’? TreePlan = The Decision Tree Microsoft Excel Add-In ‘TreePlan’ helps you build a decision tree diagram in an Excel worksheet. How to Get Access to Decision Tree using Excel Add-in ‘TreePlan’? Open V-Lab: http://www.bu.edu/metit/hw-and-sw/virtual-labs/ and log in to your BU account Click excel 2016 and open an new excel worksheet. Choose Add-Ins  Decision Tree. © 2018 Boston University Metropolitan College Slide 16 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 1: Getting Started. In a new worksheet, select cell A1. From Add-Ins tab choose Decision Tree from the Menu Commands group, then click New Tree © 2018 Boston University Metropolitan College Slide 17 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 2: Adding Branch. Now the decision tree has two alternatives. Click cell B5, from Add-Ins tab choose Decision Tree from the Menu Commands group, click Add Branch and click OK. © 2018 Boston University Metropolitan College Slide 18 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 3: Naming Alternatives. Entering “Open a small shop” in cell D2, “Open a medium-sized shop” in cell D7, and “No shop” in cell D12 instead of ‘Alternative 1’, ‘Alternative 2’ and ‘Alternative 3’. © 2018 Boston University Metropolitan College Slide 19 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 4: Adding chance node. Click cell F3, choose Decision Tree from the Menu Commands group. Then select Change to event node. Select Three in the Branches section and Click Ok. © 2018 Boston University Metropolitan College Slide 20 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 5: Naming Alternatives. Entering “Good Market” in cell H2, “Average Market” in cell H6, and “Bad Market” in cell H11 instead of ‘Outcome 1’, ‘Outcome 2’ and ‘Outcome 3’. © 2018 Boston University Metropolitan College Slide 21 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 6: Copy Subtree. Click on cell F8, choose Decision Tree from the Menu Commands group. Select Copy Subtree. © 2018 Boston University Metropolitan College Slide 22 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 7: Paste Subtree. Click on cell F18, choose Decision Tree from the Menu Commands group, then select Paste subtree. © 2018 Boston University Metropolitan College Slide 23 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 7 (Continued): Click on cell F8, Copy Subtree, and click cell F33 and Paste the Subtree, the decision tree will be shown as the following: © 2018 Boston University Metropolitan College Slide 24 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 8: Inserting Values. List the values for each branch based on the given payoff tables. o In cell H1, H11, H16, H26, H31 and cell H41, input 0.3; o In cell H6, H21 and cell H36, input 0.4. o In cell K3, K8 and cell K13, input value $40,000, $20,000, -$15,000 respectively. o In cell K18, cell K23, and cell K28, input value $70,000, $30,000, -$50,000 respectively. The EMV of the decision tree will be calculated automatically by Excel. The best EMV is $18,000, which is opening a medium-sized shop. © 2018 Boston University Metropolitan College Slide 25 Tutorial for Building a Decision Tree in Microsoft Excel Add-In ‘TreePlan’ Step 8 (Continued): EMV Calculation by Excel • Excel Add-in ‘TreePlan’ will automatically calculate the EMV for each chance node and give the best EMV of the each decision node. • The best EMV is $18,000. Therefore, open a medium-sized shop is the best choice. © 2018 Boston University Metropolitan College Slide 26 Running Head: MET AD 715 Assignment 2 Case Problem "Real Estate Development: Select a New Project" Assignment 2 AD715 Quantitative & Qualitative Decision-Making Table of Content 1 Running Head: MET AD 715 Assignment 2 2 1. Executive summary ……………………………………………………………3 2. Managerial Report…….……………………………………..………………...4 2.1 Expected Monetary Value..........................................................................4 2.2 Sensitivity Analysis…………………………………………………………5 3. 1 Appendices..........................................................................................................6 Executive summary The real estate company is considering investing a new project out of three Running Head: MET AD 715 Assignment 2 3 option, an apartment building or an office building or a warehouse building. In preparation for the decision, the company is considering hiring a business analyst. If the company hires the analyst, it would increase a survey fee for the decision regarding which project to develop. For more precise decision-making, we use the support tools to assist us which are decision tree, a sensitivity analysis, and a sensitivity diagram. After inputting all the necessary information and possible outcomes, the analysis of the support tools showed that it is in the real estate company’s best interest to build the warehouse and not to hire the business analysis. The probability of report results positive does not outweigh the probability of report results negative from the outcome of the business analyst. If the company does hire the business analyst, the expected monetary value also does not outweigh the expected monetary value of choosing to build the warehouse without hiring the analyst. From the Sensitivity Analysis, it concludes that if probability of report results positive (p) smaller than 0.8337, do not hire the business analyst; if p over 0.8337, hire the business analyst meanwhile the company could utilize the analysis gaining a profit greater than $328,200. 2 Managerial Report 2.1 Expected Monetary Value Running Head: MET AD 715 Assignment 2 4 For assessing the Expected Monetary Value (EMV) of not hiring the analyst, we calculate each option with the probability of optimistic realistic and pessimistic condition. For the results we get apartment $281,600, office $208,300, and warehouse $328,200. Next, subtract the fee for survey $10,000 regarding the report is positive or negative, calculate each potion with the probability of optimistic realistic and pessimistic condition. The results come out as apartment $315,300, office $258,400, and warehouse $359,100 when the report is positive; apartment $122,900, office $62,700, and warehouse $173,300 when the report is negative. From the value we get above we can know that the optimal EMV of report results positive is $359,100; negative is $173,300. Therefore, we calculate both with probability of report results positive 0.48 and report results negative 0.52. Then we can get the EMV of hiring a business analyst $262,484. In the conclusion, compare to the EMV of not hiring the analyst $328,200, hiring has a lower EMV $262,484 which means the real estate company would gain a greater profit if they chose the project of build the warehouse and not to hire an analyst. 2.2 Sensitivity Analysis From the EMV estimate, we get the optimal result as build the warehouse and do Running Head: MET AD 715 Assignment 2 5 not hire the analyst. However, we want to know under what circumstance could we hire the analyst. From the sensitivity analysis, we set a line of do not hire an analyst fixed at $328,200 and a variation line of probability of report result positive (p). We assume that probability is P and probability of report result negative is 1-P. Assuming P equal 0, then we get the point at $173,300 and assuming P equal 1, then we can get the point at $359,100. From two points, we can get a line which cross the fixed line at $328,200 and the formulate is 173300+185800P=328200. The cross point (p) is 0.83369. Which means that when the probability of report result positive is 0.83369 the outcomes of both hire and not hire is the same as $328,200; when the probability is under 0.83369 the outcomes would lower than $328,200; when the probability is above 0.83369 the outcomes would greater than $328,200. In the nutshell, if probability of report results positive (p) smaller than 0.83369, company should not hire the business analyst; if (p) equal to 0.83369, both hire or not hire have the same result as $328,200; if (p) over 0.83369, company should hire the business analyst meanwhile it could utilize the analysis gaining a profit greater than $328,200. 3 Appendices Running Head: MET AD 715 Assignment 2 3.1 6 Decision Tree 0.27 Optmistic Conditions 410000 410000 0.6 Realistic Conditions Apartment Building 300000 281600 300000 0.13 Pessimistic Conditions -70000 -70000 0.27 Optmistic Conditions 350000 350000 Do not hire a business analyst 0.6 Realistic Conditions Office Building 3 220000 328200 208300 220000 0.13 Pessimistic Conditions -140000 -140000 0.27 Optmistic Conditions 430000 430000 0.6 Realistic Conditions Warehouse 360000 328200 360000 0.13 Pessimistic Conditions -30000 -30000 0.6 Optmistic Conditions 400000 400000 0.29 Realistic Conditions Apartment Building 290000 315300 1 290000 0.11 Pessimistic Conditions 328200 -80000 -80000 0.6 Optmistic Conditions 350000 350000 0.48 Report Results Possitive 0.29 Realistic Conditions Office Building 3 220000 359100 258400 220000 0.11 Pessimistic Conditions -140000 -140000 0.6 Optmistic Conditions 430000 430000 0.29 Realistic Conditions Warehouse 360000 359100 360000 0.11 Pessimistic Conditions -30000 Hire a business analyst -30000 262484 0.23 Optmistic Conditions 400000 400000 0.25 Realistic Conditions Apartment Building 290000 122900 290000 0.52 Pessimistic Conditions -80000 -80000 0.23 Optmistic Conditions 350000 350000 0.52 Report Results Negative 0.25 Realistic Conditions Office Building 3 220000 173300 62700 220000 0.52 Pessimistic Conditions -140000 -140000 0.23 Optmistic Conditions 430000 430000 0.25 Realistic Conditions Warehouse 360000 173300 360000 0.52 Pessimistic Conditions -30000 -30000 3.2 Payoff Table, Sensitivity Analysis and Diagram Running Head: MET AD 715 Assignment 2 7 1. If the company does not hire a business analyst: States of Nature Alternatives Realistic Pessimistic Conditions Conditions EMV Optimistic Conditions Apartment Building 410000 300000 -70000 281,600 Office Building 350000 220000 -140000 208,300 Warehouse 430000 360000 -30000 328,200 Probability 0.27 0.6 0.13 2.1 If the analysis report is positive: States of Nature Alternatives EMV Optimistic Realistic Pessimistic Conditions Conditions Conditions Apartment Building $400,000 $290,000 -$80,000 315,300 Office Building $350,000 $220,000 -$140,000 258,400 Warehouse $430,000 $360,000 -$30,000 359,100 Probability 0.6 0.29 0.11 2.2 If the analysis report is negative: States of Nature Alternatives EMV Optimistic Realistic Pessimistic Conditions Conditions Conditions Apartment Building $400,000 $290,000 -$80,000 122,900 Office Building $350,000 $220,000 -$140,000 62,700 Warehouse $430,000 $360,000 -$30,000 173,300 Probability 0.23 0.25 0.52 Running Head: MET AD 715 Assignment 2 8 Sensitivity Analysis 400000 EMV(Do not hire a business analyst) 350000 300000 Cross point P=0.83369214 250000 200000 150000 EMV(Hire a business analyst) 100000 50000 0 0 0.2 0.4 0.6 0.8 1 1.2 Assignment 2: Case Problem "Real Estate Development: Select a New Project" Problem Description A real estate company is considering the development of one of the following three possible projects: (1) an apartment building; (2) an office building; (3) a warehouse. The amount of payoff (profit) that could be earned by selling the estate depends on the economic conditions, specified as: optimistic, realistic and pessimistic. The estimated payoffs and probabilities under optimistic, realistic and pessimistic conditions are shown as follows: States of Nature Alternatives Optimistic Conditions Realistic Conditions Apartment Building A B Office Building D E Warehouse G H Probability x y In preparation for a final decision, the company is considering the hiring of a business analyst. If the company hires the analyst, the decision regarding which project to develop will not be made until the analyst presents a survey. However, the analyst is requesting an upfront payment for the survey in the amount of Z. The probabilities of the survey results to be positive or negative are i and k. Summary tables, in case the company hires a business analyst: Fee for Survey Z Probability of survey results positive i Probability of survey results negative k (1) If the survey results are positive: States of Nature Alternatives Optimistic Conditions Realistic Conditions Apartment Building A-Z B-Z Office Building D-Z E-Z Warehouse G-Z H-Z Probability d e (2) If the survey results are negative: States of Nature Alternatives Optimistic Conditions Realistic Conditions Apartment Building A-Z B-Z Office Building D-Z E-Z Warehouse G-Z H-Z Probability g h Assignment 2: Starting Conditions Each student will receive from the instructor an excel file with a different dataset for payoffs (A to I) and probabilities (x,y,z,i,k,d,e,f,g,h,n). You have to prepare and submit a managerial report where you should answer the question: Which one of the development projects should be selected? And based on your estimates, should the company hire the business analyst? # Content per Tasks Task 2-0 Students should structure and present their Assignment 2 in the form of a Managerial Report. The expected length of the main body (tasks 2-1 to 2-4) is up to 3 pages APA format, excluding cover page, table of content, executive summary (task 2-5), and appendices (screenshots of the Payoff Table, EMV Table, Sensitivity Analysis Diagram, TreePlan Diagram of the Decision Tree). Submission requirements: Managerial Report (word file), and excel file with completed worksheets (iii) to (vi). Task 2-1 Prepare payoff tables and develop a decision tree for this problem (without probabilities and EMVs). Task 2-2 Given the probability of all three economic conditions and using expected monetary values (EMVs), calculate EMVs for each node and answer the questions: (1) What's the EMV for not hiring a business analyst and the EMV for hiring a business analyst? (2) What is your recommendation: to hire or not to hire a business analyst? Task 2-3 Use sensitivity analysis to define the probability range with respect to the survey results which might affect the decision to hire or not to hire a business analyst, draw the sensitivity chart, and find the probability for their cross point. Task 2-4 Apply a software tool for the construction of a decision tree with payoffs, probabilities, and EMVs. The recommended tool is TreePlan: a Microsoft Excel Add-Ins (it is preinstalled on all V-PCs of the MET V-LABs) Task 2-5 Prepare an executive summary List of Worksheets in the Excel File (it should be used as a reference for different tasks of the ma (i) A2 Text (ii) Payoff Table - Template (iii) Payoff Table - Solution and a sketch of a decision tree (without probability and EMVs) --> Needed (iv) EMV Calculation: use EMV as a decision criterion for each decision nodes and states of nature nod node, and recommend whether to hire/not to hire a business analyst --> Needed for Task 2-2 (v) Sensitivity Analysis Diagram: compute the probability of survey results and define the range of pro estate company would hire or not hire a business analyst (including the probability of the cross po (vi) TreePlan Diagram of the Decision Tree: use BU MET V-LAB for Excel Add-In TreePlan --> Needed f rojects: (1) an apartment building; (2) an the estate depends on the economic re shown as follows: ure Pessimistic Conditions C F I z If the company hires the analyst, the ey. However, the analyst is requesting an e positive or negative are i and k. ure Pessimistic Conditions C-Z F-Z I-Z f ure Pessimistic Conditions C-Z F-Z I-Z n A to I) and probabilities (x,y,z,i,k,d,e,f,g,h,n). Which one of the development projects st? Grading Points Per: Tasks Assignment asks nt 2 in the form of a Managerial Report. The up to 3 pages APA format, excluding cover and appendices (screenshots of the Payoff an Diagram of the Decision Tree). e), and excel file with completed his problem (without probabilities and and using expected monetary values questions: (1) What's the EMV for not ness analyst? (2) What is your lyst? 10 1 2 2 with respect to the survey results which ess analyst, draw the sensitivity chart, and 2 n tree with payoffs, probabilities, and EMVs. Add-Ins (it is preinstalled on all V-PCs of the 2 1 as a reference for different tasks of the managerial report) (without probability and EMVs) --> Needed for Task 2-1 ach decision nodes and states of nature nodes, calculate the EMV for each usiness analyst --> Needed for Task 2-2 of survey results and define the range of probability values that the real yst (including the probability of the cross point) --> Needed for Task 2-3 LAB for Excel Add-In TreePlan --> Needed for Task 2-4 1. If the company does not hire a business analyst: Alternatives Apartment Building Office Building Warehouse Probability 2. If the company hires a business analyst: Fee for Survey 2.1 If the analysis report is positive: Optimistic Conditions $220,000 $280,000 $220,000 0.32 Pessimistic Conditions -$220,000 -$60,000 -$130,000 0.11 States of Nature Realistic Conditions $126,000 $146,000 $156,000 0.4 Pessimistic Conditions -$234,000 -$74,000 -$144,000 0.1 States of Nature Realistic Conditions $126,000 $146,000 $156,000 0.27 Pessimistic Conditions -$234,000 -$74,000 -$144,000 0.54 $14,000 Alternatives Apartment Building Office Building Warehouse Probability States of Nature Realistic Conditions $140,000 $160,000 $170,000 0.57 Optimistic Conditions $206,000 $266,000 $206,000 0.5 2.2 If the analysis report is negative: Alternatives Apartment Building Office Building Warehouse Probability Optimistic Conditions $206,000 $266,000 $206,000 0.19 3. Sketch of a Decision Tree (without probability and EMVs): Do not hire a business analyst #REF! 0 #REF! #REF! #REF! hire a business analyst 0 #N/A Optimistic Conditions 0 Apartment Building 220000 Realistic Conditions 0 #REF! 0 140000 Pessimistic Conditions 0 -220000 Optimistic Conditions 0 office Building 0 280000 Realistic Conditions #N/A 0 160000 Pessimistic Conditions 0 -60000 Optimistic Conditions 0 warehouse 0 220000 Realistic Conditions #N/A 0 170000 Pessimistic Conditions 0 -130000 Optimistic Conditions 0 Apartment Building Realistic Conditions 0 0 #N/A Pessimistic Conditions 0 Optimistic Conditions 0 positive analysis report office Building Realistic Conditions #N/A 0 #N/A 0 #N/A 0 Pessimistic Conditions 0 Optimistic Conditions 0 warehouse 0 Realistic Conditions #N/A 0 Pessimistic Conditions 0 Optimistic Conditions 0 Apartment Building Realistic Conditions 0 0 #N/A Pessimistic Conditions 0 Optimistic Conditions 0 negative analysis report office Building Realistic Conditions #N/A 0 #N/A 0 #N/A 0 Pessimistic Conditions 0 Optimistic Conditions 0 warehouse 0 Realistic Conditions #N/A 0 Pessimistic Conditions 0 220000 140000 -220000 280000 160000 -60000 220000 170000 -130000 istic Conditions 206000 206000 ic Conditions 126000 126000 mistic Conditions -234000 -234000 istic Conditions 266000 266000 ic Conditions 146000 146000 mistic Conditions -74000 -74000 istic Conditions 206000 206000 ic Conditions 156000 156000 mistic Conditions -144000 -144000 istic Conditions 206000 206000 ic Conditions 12000 126000 mistic Conditions -234000 -234000 istic Conditions 266000 266000 ic Conditions 146000 146000 mistic Conditions -74000 -74000 istic Conditions 206000 206000 ic Conditions 156000 156000 mistic Conditions -144000 -144000 1. If the company does not hire a business analyst: Alternatives Apartment Building Office Building Warehouse Probability 2. If the company hires a business analyst: Fee for Survey States of Nature Realistic Conditions $140,000 $160,000 $170,000 0.57 Pessimistic Conditions -$220,000 -$60,000 -$130,000 0.11 Optimistic Conditions $206,000 $266,000 $206,000 0.5 States of Nature Realistic Conditions $126,000 $146,000 $156,000 0.4 Pessimistic Conditions -$234,000 -$74,000 -$144,000 0.1 Optimistic Conditions $206,000 $266,000 $206,000 0.19 States of Nature Realistic Conditions $126,000 $146,000 $156,000 0.27 Pessimistic Conditions -$234,000 -$74,000 -$144,000 0.54 Optimistic Conditions $220,000 $280,000 $220,000 0.32 EMV Node 126,000 174,200 153,000 3 4 5 EMV Node 130,000 184,000 151,000 9 10 11 EMV Node -53,200 50,000 3,500 12 13 14 $140,000 2.1 If the analysis report is positive: Alternatives Apartment Building Office Building Warehouse Probability 2.2 If the analysis report is negative: Alternatives Apartment Building Office Building Warehouse Probability 3. Final Solution 3.1 EMV (hire a business analyst) Probability of report results positive Probability of report results negative EMV (Report results positive) EMV (Report results negative) EMV (Hire a business analyst) 3.2 Final EMV EMV (Hire a business analyst) EMV (Do not hire a business analyst) Final EMV (Hire/Do not hire a business analyst) 0.53 0.47 $151,000 $50,000 $121,020 Node 7 8 6 $121,020 $174,200 Do not hire a business analyst Node 6 2 1 Analysis report result Positive result Negative result EMV Probability 151000 P 50000 1-P hire a business analyst: 151000P+50000(1-P)=101000P+50000 EMV hire a business analyst 101000P+50000 Do not hire a business analyst 174200 101000P+50000=174200 P=1.2297 P 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 EMV hire 50000 60100 70200 80300 90400 100500 110600 120700 130800 140900 151000 Sensitivity Chart EMV Do not hire 174200 174200 174200 174200 174200 174200 174200 174200 174200 174200 174200 200000 180000 160000 140000 120000 100000 80000 60000 40000 20000 0 0 0.1 0.2 0.3 0.4 hire B.A. 0.5 0.6 Do not hire 0.7 0.8 0.9 0.9 1 Do not hire a Business analyst 2 0 174200 1 174200 1 hire a business analyst 0 121020 0.32 Optimistic Conditions 0 220000 Apartment Building 0.57 Realistic Conditions 0 0 126000 140000 0.11 Pessimistic Conditions 0 -220000 0.32 Optimistic Conditions 0 0.57 Realistic Conditions office building 0 280000 174200 0 160000 0.11 Pessimistic Conditions 0 -60000 0.32 Optimistic Conditions 0 warehouse 220000 0.57 Realistic Conditions 0 153000 0 170000 0.11 Pessimistic Conditions 0 -130000 Apartment Building 0 0.53 positive analysis report office building 1 0 184000 130000 2 0 184000 warehouse 0 151000 Apartment Building 0 0.47 negative analysis report -53200 office building 2 0 50000 0 50000 warehouse 0 3500 220000 140000 -220000 280000 160000 -60000 220000 170000 -130000 0.5 Optimistic Conditions 206000 0 206000 0.4 Realistic Conditions 126000 0 126000 0.1 Pessimistic Conditions -234000 0 -234000 0.5 Optimistic Conditions 266000 0 266000 0.4 Realistic Conditions 146000 0 146000 0.1 Pessimistic Conditions -74000 0 -74000 0.5 Optimistic Conditions 206000 0 206000 0.4 Realistic Conditions 156000 0 156000 0.1 Pessimistic Conditions -144000 0 -144000 0.19 Optimistic Conditions 206000 0 206000 0.27 Realistic Conditions 126000 0 126000 0.54 Pessimistic Conditions -234000 0 -234000 0.19 Optimistic Conditions 266000 0 266000 0.27 Realistic Conditions 146000 0 146000 0.54 Pessimistic Conditions -74000 0 -74000 0.19 Optimistic Conditions 206000 0 206000 0.27 Realistic Conditions 156000 0 156000 0.54 Pessimistic Conditions -144000 0 -144000
Purchase answer to see full attachment