sensitive analysis for finance project

The document is the instruction and the introduction of metrics in excel sheet, try to answer the question from document and excel.

ROOMS
AVG NIGHTLY RENT (1ST YR)
OCCUPANCY RATE
1ST YEAR
2ND YEAR
LONG TERM
OPERATING COSTS
LABOR-UTILITY-MAINT
PROP TAXES,INS
50
130.00
0.375
0.500
0.750
FIXED COST
VARIABLE COST
PROPERTY
ROOM
% ROOM RENT
2,000.00
0.15
80,000.00
100.00
INVESTMENT INFO
COST
400,000
8,000,000
1,000,000
LAND
BUILDING
FURNISHINGS AND EQUIPMENT
OTHER INFO
COST OF CAPITAL
0.08
TAX RATE
0.20
EXPECTED CHANGE IN PRICES (ANNUAL):
LIFE
VALUE IN
10 YEARS
1.20
40
0.60
10
0.20
Tax, Ins
Others
0.03
0.02
YEAR
0
INVESTMENT COSTS
LAND & BLDG
EQUIPMENT
OPERATIONS
REVENUES
OCCUPANCY
NIGHTLY RENT
REVENUES
COSTS AND EXPENSES
LABOR,UTILITIES,MAINT
PROP TAXES,INS
DEPRECIATION
BUILDINGS
FURN & EQUIP
EBIT
OPERATING INCOME TAX
EAT
ADD BACK DEP’N
SALVAGE VALUE IN YR 11
LAND AND BLDG
EQUIPMENT
TAX ON SALVAGE VALUE
LAND & BLDG
EQUIPMENT
CASH FLOWS
1
2
3
4
5
6
7
8
9
10
11
0.375
130.00
0.500
0.750
0.750
0.750
0.750
0.750
0.750
0.750
0.750
(8,400,000)
(1,000,000)
0
(9,400,000)
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.
VC
OR
0.1
0.65
0.12
0.14
0.16
0.18
0.2
0.22
0.24
0.26
0.28
0.67
0.69
0.71
0.73
0.75
0.77
0.79
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 accommodations, particularly along
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
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 10year 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.