PLEASE HELP WITH EXCEL! 
Kim Hotels is interested in developing a new hotel in Seoul. The company estimates that the hotel would require an initial investment of $20 million. Kim expects that the hotel will produce positive cash flows of $3 million a year at the end of each of the next 20 years. The project's cost of capital is 13%. While Kim expects the cash flows to be $3 million a year, it recognizes that the cash flows could, in fact, 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.2 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 $3.8 million. Kim is deciding whether to proceed with the hotel today or to wait 1 year to find out whether the tax will be imposed. If Kim waits a year, the initial investment will remain at $20 million. Assume that all cash flows are discounted at 13%. 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. Open spreadsheet Use the Black-Scholes model to estimate the value of the option. Assume the variance of the project's rate of return is 6.87% and the risk-free rate is 8%. Enter your answer in millions. For example, an answer of $1.22 million should be entered as 1.22, not 1,220,000. Do not round intermediate calculations. Round your answer to two decimal places. million 4 No Timing Option: Initial investment at t = 0 (in millions) Annual expected cash flow (in millions) Number of years cash flow expected Project cost of capital $20.00 $3.00 20 13% 7 9 Timing Option: 10 Initial investment at t = 1 (in millions) 11 Number of years cash flow expected 12 Probability that tax will be imposed 13 Annual CF if tax will be imposed, Years 2 to 21 14 Probability that tax will not be imposed 15 Annual CF if tax will not be imposed, Years 2 to 21 16 Projected cost of capital 17 18 PV of CFs (in millions) at t = 1, if tax will be imposed 19 PV of CFs (in millions) at t = 1, if tax will not be imposed 20 PV of Expected CFs (in millions) at t = 1, with timing option $20.00 20 50% $2.20 50% $3.80 13% $15.45 $26.69 $21.07 22 Value of Option Using Black-Scholes Model: 23 Number of years until expiration expires Variance of project's expected rate of return of Risk-free rate of return, IRF Strike price (in millions) of underlying investment at t = 0, X 6.87% 8.00% $20.00 Price (in millions) of underlying investment, P = d1 = N(01) = d2= N(D2) = Value of option (in millions) = Formulas #N/A #N/A #N/A #N/A #N/A #N/A
