An investor purchases a 300-room hotel in a downtown midsize city for $100,000.000. In an exchange the investor receives equivalent to $100,000,000 common stocks. The operating year is from January 1^st to December 31^st of 2021. Based on the following information.

Prepare an annual income state and a balance sheet at the end of the operating year. Both financial statements need to be completed on Excel.

The income statement requires to be prepared with three columns of:

(A: value, B: Dollar amount with 2 decimals and C: Percentages with two decimals).

Room division:

Average room occupancy: 75 percent

Average room rate: $300

Room revenue contributes 60 percent of the hotel’s Total revenue.

Dining Room:

Dining Room contributes 25 percent of the hotel’s total revenue.

Cocktail Bar:

Contributes the remaining 15 percent of the hotel’s total revenue

Expenses:

Room cost: 15 percent

Food cost: 25 percent

Beverage cost: 20 percent

Pay roll: 28 percent

Benefits: 3 percent

Marketing: 4 percent

Maintenance: 6 percent

Utilities: 8 percent

Administrative 2 percent

Insurance: $40,000.00

Property taxes: $50,000.00

Depreciation: $300,000.00

Income tax 25 percent f the net income before tax

Dividends: 33% of the net income after tax

Retained earnings: 67% of the net income after tax