Please complete the problems using Excel, answer the questions asked in Word, then submit both files.
IMPORTANT: To ensure that your solution is visible in a SpeedGrader, please take a screenshot of your decision trees and submit these images together with your Excel file. Thank you!
Question 1
Mr. Brown is looking to invest some money in stock and has four possible options. After consulting with his financial planner, he has the following estimated values of his investment based on the various market outcomes:
SUA YSP HTC YHA
Investment Value Probability Investment Value Probability Investment Value Probability Investment Value Probability
$69,150 55% $65,820 50% $58,205 45% $57,250 65%
$48,375 30% $49,320 25% $52,940 35% $50,250 30%
$35,365 15% $46,765 25% $49,605 20% $45,250 5%
To assist Mr. Brown in his decision, build a decision tree and answer the following questions:
Which stock should Mr. Brown invest in and what is the expected monetary value of that decision?
Which stock has the worst expected monetary value?
How sensitive the optimal decision strategy is to changes in probabilities associated with YSP? Specifically, if the probability for $65,820 could vary anywhere between 40% and 60%, how would that affect an outcome of the tree? (Hint: replace 50% with a parameter function, change the lowest 25% to a formula that ensures all options total to 100%)
Question 2
Excelia Inc. is planning to develop a new drug and has to make a series of decisions during its development. Since Excelia has already signed contracts to provide its drug, stopping the development would cost then $230,000. However, if they were to continue the development, the production costs are expected to be $606,000. If they invest these costs they would have to conduct clinical trials and there is only a 30% chance the drug is successful in these trials. If it isn’t successful, all invested money is lost. If it is successful, Excelia will have to seek FDA approval or stop development at this point. If they seek approval it will cost Excelia $25,000. There is a 60% chance the FDA will approve the drug and only a 40% chance they will not. Finally, if they approve the drug, there is various market potentials for the drug’s demand. There is a 60% chance the market is large with an expected revenue of $6,202,000, a 30% chance the market is moderately sized with an expected revenue of $2,389,000, and a 10% chance the market is only small with an expected revenue of $1,749,000.
Build a decision tree to model the decision process for Excelia Inc. and answer the following questions:
Which decision should Excelia make along the development process?
What is the expected monetary value of that decision?
