Excel Online Structured Activity: Investment Timing Option: Decision-Tree Analysis Kim Hotels is interested in developing a new hotel in Seoul. The company estimates that the hotel would require an initial investment of $24 million. Kim expects the hotel will produce positive cash flows of $3.84 million a year at the end of each of the next 20 years. The project's cost of capital is 15%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. X Open spreadsheet a. What is the project's net present value? A negative value should be entered with a negative sign. Enter your answer in millions. For example, an answer of $1.2 million should be entered as 1.2, not 1,200,000. Do not round intermediate calculations. Round your answer to two decimal places. b. Kim expects the cash flows to be $3.84 million a year, but it recognizes that the cash flows could actually be much higher or lower, depending on whether the Korean government imposes a large hotel tax. One year from now, Kim will know whether the tax will be imposed. There is a 50% chance that the tax will be imposed, in which case the yearly cash flows will be only $2.16 million. At the same time, there is a 50% chance that the tax will not be imposed, in which case the yearly cash flows will be $5.52million. Kim is deciding whether to proceed with the hotel today or to wait a year to find out whether the tax will be imposed. If Kim waits a year, the initial investment will remain at $24 million. Assume that all cash flows are discounted at 15%. Use decision-tree analysis to determine whether Kim should proceed with the project today or wait a year before deciding. 3 No Timing Option: 4 Initial investment at t=0 (in millions) 5 Annual expected cash flow (in millions) 6 Number of years cash flow expected 7 Project cost of capital 8 Timing Option: 9 Initial investment at t=1 (in millions) 10 Number of years cash flow expected 11 Probability that tax will be imposed 12 Annual CF (in millions) if tax imposed, Years 2 to 21 13 Probability that tax will not be imposed 14 Annual CF (in millions) if tax not imposed, Years 2 to 21 15 Project cost of capital 16 17 No Timing Option: 18 NPV of project (in millions) at t=0, assuming no timing option 19 20 Timing Option: 21 NPV (in millions) at t=0, if tax imposed 22 NPV if tax imposed, reduced to zero if NPV negative 23NPV (in millions) at t=0, if tax not imposed 24 Expected NPV of project (in millions) at t=0, with timing option 26 | Should firm proceed now or wait to do the project? B $24.00 $3.84 20 15% $24.00 20 50% $2.16 50% $5.52 15% Formulas 0.04=PV(B7,B6,B5,0)B4 $9.11=(PV(B15,B10,B12,0)B9)/(1+B15)$0.00=F(B21B18, "Wait", "Proceed Now") Excel Online Structured Activity: Investment Timing Option: Decision-Tree Analysis Kim Hotels is interested in developing a new hotel in Seoul. The company estimates that the hotel would require an initial investment of $24 million. Kim expects the hotel will produce positive cash flows of $3.84 million a year at the end of each of the next 20 years. The project's cost of capital is 15%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. X Open spreadsheet a. What is the project's net present value? A negative value should be entered with a negative sign. Enter your answer in millions. For example, an answer of $1.2 million should be entered as 1.2, not 1,200,000. Do not round intermediate calculations. Round your answer to two decimal places. b. Kim expects the cash flows to be $3.84 million a year, but it recognizes that the cash flows could actually be much higher or lower, depending on whether the Korean government imposes a large hotel tax. One year from now, Kim will know whether the tax will be imposed. There is a 50% chance that the tax will be imposed, in which case the yearly cash flows will be only $2.16 million. At the same time, there is a 50% chance that the tax will not be imposed, in which case the yearly cash flows will be $5.52million. Kim is deciding whether to proceed with the hotel today or to wait a year to find out whether the tax will be imposed. If Kim waits a year, the initial investment will remain at $24 million. Assume that all cash flows are discounted at 15%. Use decision-tree analysis to determine whether Kim should proceed with the project today or wait a year before deciding. 3 No Timing Option: 4 Initial investment at t=0 (in millions) 5 Annual expected cash flow (in millions) 6 Number of years cash flow expected 7 Project cost of capital 8 Timing Option: 9 Initial investment at t=1 (in millions) 10 Number of years cash flow expected 11 Probability that tax will be imposed 12 Annual CF (in millions) if tax imposed, Years 2 to 21 13 Probability that tax will not be imposed 14 Annual CF (in millions) if tax not imposed, Years 2 to 21 15 Project cost of capital 16 17 No Timing Option: 18 NPV of project (in millions) at t=0, assuming no timing option 19 20 Timing Option: 21 NPV (in millions) at t=0, if tax imposed 22 NPV if tax imposed, reduced to zero if NPV negative 23NPV (in millions) at t=0, if tax not imposed 24 Expected NPV of project (in millions) at t=0, with timing option 26 | Should firm proceed now or wait to do the project? B $24.00 $3.84 20 15% $24.00 20 50% $2.16 50% $5.52 15% Formulas 0.04=PV(B7,B6,B5,0)B4 $9.11=(PV(B15,B10,B12,0)B9)/(1+B15)$0.00=F(B21B18, "Wait", "Proceed Now")