Pls give me the excel formula to this exercise. If u know how to solve this, let me know, I'll see if i can send u the excel file

MOHOTEL CASE ANALYSIS Company Background Raul Rodriguez, the founder of MOHOTEL, was ready to retire to the Mexican Riviera in 2010. His ambitious niece, Esther Rodriguez, took the company public in early 2005, allowing Raul to retire, but retaining for herself an effective controlling interest in the company. Esther decided that as the postwar baby boomers retire, auto travel would increase dramatically for the foreseeable future. This meant that the demand for tourist accommodatione martirlarly along Text Highlight Color highways, would also increase. In order to take advantage of the relatively high prices being paid for the stock of small companies, the low interest rates on long term debt, and the relatively low cost of real estate and construction costs, the company is contemplating an expansion program. You have been hired to help MOHOTEL evaluate these possibilities. Before turning to the proposals, some current and recent data on the company and prospects for the industry will be considered. Company Information The company's current marginal tax rate is 20% which is expected to remain constant for the foreseeable future. A typical newer inn has 50 rooms that rent for an average of $130 per night. The occupancy rate, again on newer properties, averages 75%. But historically, new inns have had 1st and 2nd year occupancy rates that were about 1/2 and 2/3 of the ultimate occupancy rates, respectively. Some of the annual costs directly attributable to individual inns in this class at 2010 prices include: -Labor, utilities, repairs & maintenance: $2,000 per room plus 15% of gross revenues -Property taxes, insurance: $80,000 per inn + $100 per room. Room rentals are expected to increase by 2% per year due to inflation. Property taxes & insurance are expected to increase by 3% per year. $2,000 fixed costs per room for labor, utilities, repairs & maintenance are expected to increase by 2% per year. The company depreciates all equipment using the straight line method both for tax and book purposes, assuming zero salvage value and using the following lives: Buildings 40 years Equipment and furnishings 10 years . The company uses a 10 year planning horizon for all major hotel improvements. The company assumes zero salvage value to compute depreciation. However, historically, land, buildings and equipment salvage values after 10 year operation, as a percent of original cost, have averaged: Land 120% Buildings 60% Equipment and furnishings 20% . The company may or may not sell the properties at the end of the 10 years. However, all properties are subject to a review every ten years to determine whether they should be kept or sold. Expected capital expenditures are as follows: Land $400,000 Building 8,000,000 Furnishings & equipment 1,000,000 Text Highlight Color Construction and startup typically take a year (total capital expenditures will occur at the end of the first year), and operation begins in the second year for 10 years after a decision to build is made. The cost of capital is 8%. . . Notes: Land is not depreciable. Cash inflows will start from Year 2 to Year 11. Assume inflation effect on prices and costs will begin after Year 2. Evaluate just one inn (if one inn is profitable, total investment would be good). Most projects require some investments in net working capital, but hotel business is different. Most transactions are cash based and no inventory is needed. Therefore, we can assume no investments in net working capital. Cash flows of hotel projects are likely indefinite. To make this case simple, however, I set a 10- year horizon. If cash flows are indefinite, we need to assume that cash flows will be either perpetuity or growing perpetuity, and add the terminal value. . 0 R S 11 A B D E F H 1 J K M N ROOMS 50 2 AVG NIGHTLY RENT (1ST YR) 130.00 OCCUPANCY RATE 1ST YEAR 0.375 5 2ND YEAR 0.500 5 LONG TERM 0.750 7 OPERATING COSTS 3 FIXED COST VARIABLE COST 2 PROPERTY ROOM % ROOM RENT 0 LABOR-UTILITY-MAINT 2.000.00 0.15 1 PROP TAXES,INS 80,000.00 100.00 2 3 INVESTMENT INFO VALUE IN 4 COST LIFE 10 YEARS 5 LAND 400,000 1.20 6 BUILDING 8,000,000 40 0.60 7 FURNISHINGS AND EQUIPMENT 1.000.000 10 0.20 B OTHER INFO 9 COST OF CAPITAL 0.08 0 TAX RATE 0.20 1 EXPECTED CHANGE IN PRICES (ANNUAL): Tax, Ins 0.03 2 Others 0.02 3 4 YEAR 5 0 1 2 3 4 5 6 7 8 9 10 6 INVESTMENT COSTS 7 LAND & BLDG (8.400,000) B EQUIPMENT (1.000.000) 9 OPERATIONS 0 REVENUES 1 OCCUPANCY 0.375 0.500 0.750 0.750 0.750 0.750 0.750 0.750 0.750 2 NIGHTLY RENT 130.00 3 REVENUES 4 COSTS AND EXPENSES 5 LABOR UTILITIES, MAINT 6 PROP TAXES,INS 7 DEPRECIATION B BUILDINGS FURN & EQUIP 0 EBIT 1 OPERATING INCOME TAX 2 EAT 3 ADD BACK DEP'N 4 4 SALVAGE VALUE IN YR 11 5 LAND AND BLDG 6 EQUIPMENT 7 TAX ON SALVAGE VALUE B LAND & BLDG EQUIPMENT 0 CASH FLOWS 0 (9,400,000) 1 2 NPV 3 4 In this project, the variable cost percentage and the occupancy rate may be the most important variables. Complete the sensitivity analysis for these 4 two variables, and draw the sensitivity lines. 5 0.750 NPV In this project, the variable cost percentage and the occupancy rate may be the most important variables. Complete the sensitivity analysis for these two variables, and draw the sensitivity lines. OR VC 0.1 0.12 0.14 0.16 0.18 0.2 0.22 0.24 0.65 0.67 0.69 0.71 0.73 0.75 0.77 0.79 0.26 0.28 0.81 0.83 MOHOTEL CASE ANALYSIS Company Background Raul Rodriguez, the founder of MOHOTEL, was ready to retire to the Mexican Riviera in 2010. His ambitious niece, Esther Rodriguez, took the company public in early 2005, allowing Raul to retire, but retaining for herself an effective controlling interest in the company. Esther decided that as the postwar baby boomers retire, auto travel would increase dramatically for the foreseeable future. This meant that the demand for tourist accommodatione martirlarly along Text Highlight Color highways, would also increase. In order to take advantage of the relatively high prices being paid for the stock of small companies, the low interest rates on long term debt, and the relatively low cost of real estate and construction costs, the company is contemplating an expansion program. You have been hired to help MOHOTEL evaluate these possibilities. Before turning to the proposals, some current and recent data on the company and prospects for the industry will be considered. Company Information The company's current marginal tax rate is 20% which is expected to remain constant for the foreseeable future. A typical newer inn has 50 rooms that rent for an average of $130 per night. The occupancy rate, again on newer properties, averages 75%. But historically, new inns have had 1st and 2nd year occupancy rates that were about 1/2 and 2/3 of the ultimate occupancy rates, respectively. Some of the annual costs directly attributable to individual inns in this class at 2010 prices include: -Labor, utilities, repairs & maintenance: $2,000 per room plus 15% of gross revenues -Property taxes, insurance: $80,000 per inn + $100 per room. Room rentals are expected to increase by 2% per year due to inflation. Property taxes & insurance are expected to increase by 3% per year. $2,000 fixed costs per room for labor, utilities, repairs & maintenance are expected to increase by 2% per year. The company depreciates all equipment using the straight line method both for tax and book purposes, assuming zero salvage value and using the following lives: Buildings 40 years Equipment and furnishings 10 years . The company uses a 10 year planning horizon for all major hotel improvements. The company assumes zero salvage value to compute depreciation. However, historically, land, buildings and equipment salvage values after 10 year operation, as a percent of original cost, have averaged: Land 120% Buildings 60% Equipment and furnishings 20% . The company may or may not sell the properties at the end of the 10 years. However, all properties are subject to a review every ten years to determine whether they should be kept or sold. Expected capital expenditures are as follows: Land $400,000 Building 8,000,000 Furnishings & equipment 1,000,000 Text Highlight Color Construction and startup typically take a year (total capital expenditures will occur at the end of the first year), and operation begins in the second year for 10 years after a decision to build is made. The cost of capital is 8%. . . Notes: Land is not depreciable. Cash inflows will start from Year 2 to Year 11. Assume inflation effect on prices and costs will begin after Year 2. Evaluate just one inn (if one inn is profitable, total investment would be good). Most projects require some investments in net working capital, but hotel business is different. Most transactions are cash based and no inventory is needed. Therefore, we can assume no investments in net working capital. Cash flows of hotel projects are likely indefinite. To make this case simple, however, I set a 10- year horizon. If cash flows are indefinite, we need to assume that cash flows will be either perpetuity or growing perpetuity, and add the terminal value. . 0 R S 11 A B D E F H 1 J K M N ROOMS 50 2 AVG NIGHTLY RENT (1ST YR) 130.00 OCCUPANCY RATE 1ST YEAR 0.375 5 2ND YEAR 0.500 5 LONG TERM 0.750 7 OPERATING COSTS 3 FIXED COST VARIABLE COST 2 PROPERTY ROOM % ROOM RENT 0 LABOR-UTILITY-MAINT 2.000.00 0.15 1 PROP TAXES,INS 80,000.00 100.00 2 3 INVESTMENT INFO VALUE IN 4 COST LIFE 10 YEARS 5 LAND 400,000 1.20 6 BUILDING 8,000,000 40 0.60 7 FURNISHINGS AND EQUIPMENT 1.000.000 10 0.20 B OTHER INFO 9 COST OF CAPITAL 0.08 0 TAX RATE 0.20 1 EXPECTED CHANGE IN PRICES (ANNUAL): Tax, Ins 0.03 2 Others 0.02 3 4 YEAR 5 0 1 2 3 4 5 6 7 8 9 10 6 INVESTMENT COSTS 7 LAND & BLDG (8.400,000) B EQUIPMENT (1.000.000) 9 OPERATIONS 0 REVENUES 1 OCCUPANCY 0.375 0.500 0.750 0.750 0.750 0.750 0.750 0.750 0.750 2 NIGHTLY RENT 130.00 3 REVENUES 4 COSTS AND EXPENSES 5 LABOR UTILITIES, MAINT 6 PROP TAXES,INS 7 DEPRECIATION B BUILDINGS FURN & EQUIP 0 EBIT 1 OPERATING INCOME TAX 2 EAT 3 ADD BACK DEP'N 4 4 SALVAGE VALUE IN YR 11 5 LAND AND BLDG 6 EQUIPMENT 7 TAX ON SALVAGE VALUE B LAND & BLDG EQUIPMENT 0 CASH FLOWS 0 (9,400,000) 1 2 NPV 3 4 In this project, the variable cost percentage and the occupancy rate may be the most important variables. Complete the sensitivity analysis for these 4 two variables, and draw the sensitivity lines. 5 0.750 NPV In this project, the variable cost percentage and the occupancy rate may be the most important variables. Complete the sensitivity analysis for these two variables, and draw the sensitivity lines. OR VC 0.1 0.12 0.14 0.16 0.18 0.2 0.22 0.24 0.65 0.67 0.69 0.71 0.73 0.75 0.77 0.79 0.26 0.28 0.81 0.83