Reference no: EM132188953

Contract Bid A company is ultimately trying to decide whether they should prepare a proposal for a certain contract or not. They estimate that merely preparing the bid will cost $ 10,000. The company believes that if they were to prepare a proposal, there is a 50% chance that their proposal will make the "short -list ." They also believe that if their proposal does not make the “ short -list ” of proposals, their proposal will be rejected. If the com pany’s proposal makes the “short -list,” the company will be asked to suppl y a more detailed proposal, which will consume additional time and money . The company estimates that the cost for creating the more detailed proposal will be $5,000 . The company is u nsure whether their proposal should ask for $155,000, $ 170,000 , and $190,000. The company has studied the requirements of the contract that they are hoping to win. If they win the contract, the company has estimated that the cost of labor and material asso ciated with the requirements of the contract will be $ 127,000. They know if they ask for too much money, they will likely not win the contract. On the other hand, they know if they do not ask for enough, the profit margin will be low, but they are more lik ely to win the contract. After an extensive study, the company estimates that the probabilit y of these bids being accepted once they have been short -listed is 0.90, 0.75 , and 0.35 respectively. 1. Develop a decision tree from the information that was provided . a) HINT : Develop the decision tree by referencing the data found in the starting file. Create cell references to this data and develop formulas to analyze the decision tree so that a data table can be developed later. b) HINT : It is recommended that you form at cost as negative v alues and the revenue from the contract as a positive value. 2. Develop a formula that determines the final expected monetary value based on your decision tree. 3. Develop a formula that determines the final decision that the company should make. 4. The company is not very confident that the actual probability of making the short -list of proposals is 50%. They know the probability is likely to change based on the numbe r of submissions. In other words, if the number of submissions from other competing companies is low, the probability of success could be much higher than 50%. In addition, if the number of submissions from other competing companies is high, the probabilit y of success could be much lower than 50%. Therefore, they would like to investigate the relationship between the probabilities of success of making the short -list against the expected monetary value of their potential earnings. a) Create a data table so the decision and expected monetary value can be evaluated based on varying the probability of success from 0% to 100%. 5. Summarize the data table that was created in the previous part. Report how many times each decision was the best option, the minimum and maxi mum ranges each decision should be made based on the probability of success of making the short -list. 6. In terms of percentage, what is the largest value that the current probability of success could be reduced by and still be profitable? a) HINT : Assume that a profit is not made if the probability of success is less than or equal to 48%. Thus, a profit can be realized as long as the probability of success is equal to or greater than 49%. For this example, the maximum the current probability of success (i.e. 50 %) can be reduced by and still be profitable is 1%. 7. Create an XY Scatter plot with straight lines where the X -axis is Probability of Success and the Y -axis is the expected monetary value (EMV) . 8. If you were the ultimate decision -maker for the given scenar io, what decision would you make? If your decision were to prepare a proposal, how much would you ask for (i.e. $155,000, $ 170,000 , and $190,000)? Do you feel the decision is risky or safe? Justify your responses.