solve using excel

Spiral Galaxy Hotels is considering two different ways to develop a property the can buy for $6.8 million. The local hotel market sales are $350 million annually, and they hope to get 20% of the market if they build a large hotel for $85 million. Alternatively, they could build a smaller, upscale hotel that would only capture 15% of the market for $45 million. The large hotel would have variable costs that are 30% of revenue while the upscale variable costs would be 40% of revenue. The fixed costs are higher for the large hotel at $9.5million/year than the upscale hotel at $6 million/year. The large hotel requires additional net working capital of $1.2 million while the upscale hotel requires $0.7 million. The tax rate is 23% for both projects. Both hotels will be depreciated as non-residential real property under GDS using the half-year convention (consult the IRS documentation for depreciation). The cost-basis for depreciation should be the 100% of the construction costs. Assume that the hotels will operate for 15 years and then will be sold in year 15 ; the large hotel can be sold for $43.5 million and the small hotel for $19.25 milion. The land could be sold for $7.5 million. To finance their investment, Spiral Galaxy Hotels will sell 1.3 million shares to investors at $31.43/ share, and they will borrow any additional funds they need. If they build the large hotel, creditors will charge 7.75% annually on their debt, but they will only charge 6.25% on debt for the small hotel. Since the large hotel is a bigger venture that will use more debt, the firm's equity is assumed to be 2.5 times as risky as investing in publicly traded stocks (which we expect to produce 11%/y ear; the risk-free rate is 1.8% ). The small hotel, on the other hand, will result in equity that is only 2 times as risky as the average stock. For the large hotel, there is a 15% chance that the hotel will not do well, and revenues will bel only 50% of the expected; items that scale with revenue will decrease similarly, but everythiQ Homework: 1. Complete your analysis of the scenario described above. 2. Calculate the value of each project including the real options described below. 3. Explain in a few sentences how having abandonment and expansion options affect the two projects' values. If either hotel does poorly, we have the option to shut it down early; assume we will make that decision at the end of year three. In this case, we will assume that we can sell the hotel for 85% of book value and the land for 100% of book value. On the other hand, if either hotel is an enormous success, we could invest more and expand; we will decide on expansion after year three, invest during year four, and see changes from year five on. Assume there will be an available lot next door for sale for $4 million, we will spend 50% of our original construction budget to expand, and that we will finance at the same weighted-average cost of capital. The expansion will also require 40% more net working capital to be invested. The expansion project is expected to increase revenues by 55% (compared to an enormous success, not compared to the base case) and increase fixed costs by 25%. The new construction is a new asset for the purposes of depreciation and is treated the same way the original investment was. The property can be sold for 50% more than without the expansion. Spiral Galaxy Hotels is considering two different ways to develop a property the can buy for $6.8 million. The local hotel market sales are $350 million annually, and they hope to get 20% of the market if they build a large hotel for $85 million. Alternatively, they could build a smaller, upscale hotel that would only capture 15% of the market for $45 million. The large hotel would have variable costs that are 30% of revenue while the upscale variable costs would be 40% of revenue. The fixed costs are higher for the large hotel at $9.5million/year than the upscale hotel at $6 million/year. The large hotel requires additional net working capital of $1.2 million while the upscale hotel requires $0.7 million. The tax rate is 23% for both projects. Both hotels will be depreciated as non-residential real property under GDS using the half-year convention (consult the IRS documentation for depreciation). The cost-basis for depreciation should be the 100% of the construction costs. Assume that the hotels will operate for 15 years and then will be sold in year 15 ; the large hotel can be sold for $43.5 million and the small hotel for $19.25 milion. The land could be sold for $7.5 million. To finance their investment, Spiral Galaxy Hotels will sell 1.3 million shares to investors at $31.43/ share, and they will borrow any additional funds they need. If they build the large hotel, creditors will charge 7.75% annually on their debt, but they will only charge 6.25% on debt for the small hotel. Since the large hotel is a bigger venture that will use more debt, the firm's equity is assumed to be 2.5 times as risky as investing in publicly traded stocks (which we expect to produce 11%/y ear; the risk-free rate is 1.8% ). The small hotel, on the other hand, will result in equity that is only 2 times as risky as the average stock. For the large hotel, there is a 15% chance that the hotel will not do well, and revenues will bel only 50% of the expected; items that scale with revenue will decrease similarly, but everythiQ Homework: 1. Complete your analysis of the scenario described above. 2. Calculate the value of each project including the real options described below. 3. Explain in a few sentences how having abandonment and expansion options affect the two projects' values. If either hotel does poorly, we have the option to shut it down early; assume we will make that decision at the end of year three. In this case, we will assume that we can sell the hotel for 85% of book value and the land for 100% of book value. On the other hand, if either hotel is an enormous success, we could invest more and expand; we will decide on expansion after year three, invest during year four, and see changes from year five on. Assume there will be an available lot next door for sale for $4 million, we will spend 50% of our original construction budget to expand, and that we will finance at the same weighted-average cost of capital. The expansion will also require 40% more net working capital to be invested. The expansion project is expected to increase revenues by 55% (compared to an enormous success, not compared to the base case) and increase fixed costs by 25%. The new construction is a new asset for the purposes of depreciation and is treated the same way the original investment was. The property can be sold for 50% more than without the expansion