Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

Unlock your academic potential

© 2024 | Zucol Services PVT LTD | All rights reserved.

Added on 2019/09/16

|3

|1088

|158

Project

AI Summary

The assignment is a financial analysis project for a hotel, Beautiful & Crazy (B&C) Hotel, which plans to open on January 1st, 2017 and operate for 10 years. The project requires students to prepare an income statement for the next 10 years using Microsoft Excel, as well as construct a loan amortization table for 20 years. Students are also provided with operating forecasts, including room rates, occupancy rates, food and beverage revenue, and other expenses. The assignment aims to test students' understanding of financial concepts and their ability to apply them in a real-world scenario.

Your contribution can guide someone’s learning journey. Share your
documents today.

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

[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

Need help grading? Try our AI Grader for instant feedback on your assignments.

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 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

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

1 out of 3