You are trying to decide whether to make an investment of $500 million in a new technology to produce Everlasting Gobstoppers. There is a 60% chance that the market for these candies will produce profits of $100 million annually, a 20% chance the market will produce profits of $50 million, and a 20% chance that there will be no profits. The size of the market will become clear one year from now. Currently, the cost of capital of the project is 11% per year. There is a 20% chance that the cost of capital will drop to 9% in a year and stay at that level forever, and a 80% chance that it will stay at 11% forever. Movements in the cost of capital are unrelated to the size of the candy market. Construct the decision tree that shows the choices you have to make to see if you make the investment either today or one year from now.

Need Excel formulas for attachment

Problem 22-2 You are trying to decide whether to make an investment of $500 million in a new technolog Gobstoppers. There is a 60% chance that the market for these candies will produce profits o 20% chance the market will produce profits of $50 million, and a 20% chance that there wil market will become clear one year from now. Currently, the cost of capital of the project is 1 chance that the cost of capital will drop to 9% in a year and stay at that level forever, and a 8 11% forever. Movements in the cost of capital are unrelated to the size of the candy market. that shows the choices you have to make to see if you make the investment either today or o Investment (million) $500.00 Expected Value Decision tree ($ million): Profits (million) $100.00 $50.00 $0.00 Requirements To calculate the expected return on the investment opportunity, we need to construct t probabilities. 1. For the first possible outcome, in cell H15, by using cell references, input the first po G15, by using cell references, input the probability that this outcome will occur (1 pt 2. For the second possible outcome, in cell H21, by using cell references, input the seco cell G21, by using cell references, input the probability that this outcome will occur ( 3. For the third possible outcome, in cell H27, by using cell references, input the third p G27, by using cell references, input the probability that this outcome will occur (1 pt 4. Independent of the profits outcome, there are two possible costs of capital with their r occurrence. For the first possible profit outcome, in cell J14, by using cell references first cost of capital will occur (1 pt.). 5. In cell J16, by using cell references, input the probability that the second cost of capi 6. For the second possible profit outcome, in cell J20, by using cell references, input the of capital will occur (1 pt.). 7. In cell J22, by using cell references, input the probability that the second cost of capi 8. For the third possible profit outcome, in cell J26, by using cell references, input the p of capital will occur (1 pt.). 9. In cell J28, by using cell references, input the probability that the second cost of capi 10. For the first possible profit outcome, calculate the NPV of the investment opportunity capital. In cell K13, by using cell references, calculate the NPV (1 pt.). 11. In cell K17, by using cell references, calculate the NPV (1 pt.). 12. For the second possible profit outcome, calculate the NPV of the investment opportun capital. In cell K19, by using cell references, calculate the NPV (1 pt.). 13. In cell K23, by using cell references, calculate the NPV (1 pt.). 14. For the third possible profit outcome, calculate the NPV of the investment opportunit capital. In cell K25, by using cell references, calculate the NPV (1 pt.). 15. In cell K29, by using cell references, calculate the NPV (1 pt.). 16. Finally, in cell E21, by using cell references, calculate the expected value of the inves 00 million in a new technology to produce Everlasting candies will produce profits of $100 million annually, a d a 20% chance that there will be no profits. The size of the ost of capital of the project is 11% per year. There is a 20% ay at that level forever, and a 80% chance that it will stay at the size of the candy market. Construct the decision tree e investment either today or one year from now. Probability 60% 20% 20% Cost of Capital Probability 11.00% 80% 9.00% 20% Firm Value rtunity, we need to construct the decision tree of outcome l references, input the first possible profit (1 pt.). In cell this outcome will occur (1 pt.). cell references, input the second possible profit (1 pt.). In that this outcome will occur (1 pt.). ll references, input the third possible profit (1 pt.). In cell this outcome will occur (1 pt.). ble costs of capital with their respective probabilities of J14, by using cell references, input the probability that the ty that the second cost of capital will occur (1 pt.). using cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). ng cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). PV of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). V of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). he expected value of the investment opportunity (1 pt.)