Financial Analysis Project: B&C Hotel Income Statement & Amortization

This project requires the creation of a 10-year income statement (2017-2026) and a 20-year loan amortization table for the hypothetical Beautiful & Crazy (B&C) Hotel. The assignment utilizes Microsoft Excel and involves forecasting revenue from rooms, food and beverage, and other operated departments, considering occupancy rates, ADR, and various expense categories. The project incorporates student ID-dependent variables for hotel room numbers, total project cost, and initial ADR. Students must calculate interest expense, apply an income tax rate, and address depreciation. The income statement format is a modified summary operations statement. The loan amortization table is for a 20-year loan with monthly compounding. The goal is to analyze the hotel's financial performance over the specified period.

FINANCIAL ANALYSIS PROJECT
[PART ONE]
INTRODUCTION
You are currently developing a hotel project, Beautiful & Crazy (B&C) Hotel. You plan to open the hotel on January
1st, 2017 and your plan is to operate the hotel for 10 years and sell it. First, you would like to know how the hotel
will perform in the next 10 years (from 2017 – 2026). Therefore, you are preparing income statement of the hotel
for 10 years.
REQUIREMENTS FOR PROJECT PART ONE
1. You must use Microsoft Excel. Handwritten submission will not be accepted.
2. All dollar figures should be rounded to the nearest dollar.
3. Your submission in ANGEL will be ONE file; an Excel spreadsheet.
REQUIRED ANALYSIS
1. Prepare the income statement for 10 years (from 2017 to 2026) (Tab 1 in Excel). The format of the income
statement will NOT follow the USALI, but will be a modified version of the summary operation statement.
This format will be discussed in the classroom.
2. Construct the loan amortization table for 20 years (Tab 2 in Excel).
1

HOTEL B&C PROJECT
GENERAL DESCRIPTION
1. Hotel B&C will have “100 + Last Two Digits of Your Student ID Number” rooms. For example, if your student ID
is 9 8742 5126, the hotel room number will be 126 (= 100 + 26).
2. The project is expected to cost “$10,000,000 + (The Second Digit from the Last of Your Student ID Number ×
$1,000,000) in total. For example, if your student ID is 9 8741 5126, the expected cost will be $12,000,000 (=
$10,000,000 + ($1,000,000 × 2)).
OPERATING FORECAST
1. Hotel B&C will be open 365 days per year.
2. Hotel B&C will have three revenue generating departments: rooms, food and beverage, and other operated
department. Hotel B&C will have the overall occupancy rate of 70% in year 1, 75% in year2, and 80%
thereafter. The overall ADR is expected to be “$100 + (Last Digit of Your Student ID Number × 10) in year 1
and increase at a compound annual rate of 5%. For example, if your student ID is 9 8741 5126, the overall
ADR in year 1 will be $160 (= $100 + $60). The ADRs in years 2 will be $168 (=$160 × 1.05); the ADR in year 3
will be $176 (= $168 × 1.05 = $176.4, therefore, round this to the nearest dollar); the ADR in year 4 will be
$185 (= $176 × 1.05 = $184.8, therefore, round this to the nearest dollar), and so on.
3. Food and beverage revenue is expected to be 25% of rooms revenue in each year of the forecast.
4. Other operated department revenue is expected to be 5% of rooms revenue in each year of the forecast.
5. Income tax rate is 35%.
6. Other expense information for year 1 and proportion or increase in forecast is projected to be as below:
Account Amount in Year 1 Proportion or Increase in Forecast
Rooms 25% of Rooms Revenue 25% of Rooms Revenue Each Year (P)*
Food and Beverage 60% of F&B Revenue 60% of F&B Revenue Each Year (P)
Other Operated Department 20% of Other Op. Dept. Rev. 20% of Other Op. Dept. Rev. Each Year (P)
Administrative & General 10% of Total Revenue 4% compounded annually (I)**
Franchise Fees† 6% of Rooms Revenue 6% of Rooms Revenue Each Year (P)
Property Operation & Maintenance 4% of Total Revenue 4% compounded*** annually (I)
Utilities 3% of Total Revenue 4% compounded annually (I)
Sales & Marketing 9% of Total Revenue 4% compounded annually (I)
Rent 1% of Total Revenue 3% compounded annually (I)
Property and Other Taxes 3% of Total Revenue 3% compounded annually (I)
Insurance 1% of Total Revenue 1% of Total Revenue Each Year (P)
Depreciation & Amortization 1% of Total Revenue Remains the same
Management Fees 2% of Total Revenue 2% of Total Revenue
& 5% of Total GOP & 5% of Total GOP Each Year (P)
*Note: (P) represents ‘proportion’.
**Note: (I) represents ‘increase’.
***Note: ‘compounded annually’ means that if the increase is 4%, for example, then each year’s expense is 4% larger than the
previous year’s expense.
†Note: Report ‘Franchise Fees’ separately from ‘Sales & Marketing’
FINANCING
1. Loan-to-Value Ratio is 60%.
2. The loan is for 20 years with the interest rate of 12%, compounded monthly. (Tip: PVIFA (1%, 240) = 90.8194)
2

[General Tips]
 Figure out room number of your hotel, total project cost, and overall ADR for year 1, correctly using your own
student ID number as instructed.
 For your financing, you have to use the total project cost (i.e., value) and loan-to-value ratio to figure out how
much you have to borrow at the beginning of the project (i.e., the beginning balance of your amortization
table).
 Interest expense: In your amortization table, you will have monthly interest payment for 20 years. Then, you
have to calculate each year's total interest payment (by summing up interest payments for 12 months of each
year) and report it on the annual income statement.
 Depreciation expense will remain the same in dollar amount from the first year to the 10th year.
3