Financial statements
An investor purchases a100-room hotel in a downtown midsize city for $50,000,000.00. In an exchange the investor receives common stocks. The operating year is from January 1st to December 31st of 2021. Based on the following information, prepare:
Both financial statements need to be on Excel, where the income statement with three columns of: (A: Description, B: Dollar amount with 2 decimals and C: Percentages with two decimals).
Room division:
Average room occupancy: 80 percent
Average room rate: $200
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
- An annual income statement: Follow this format:
A B C
2.Room revenue $ 60%
3.Food revenue $ 25%
4.Beverage revenue $ 15%
5. Total Revenue $ 100%
6. Less: Cost of sale:
7. Room cost 15% of room revenue
8. Food cost 25% of food revenue
9. Beverage cost 20% of beverage revenue
10. Total cost of sales = sum(b7:b9) =b10/b5
11. =Gross profit =b5-b10 =b11/b5 or =b5-b10
12. Less: Operating Expenses:
13. Payroll =b5*c 13 30% of total revenue
14. Benefits =b5*c14 3% “ “
15. Marketing =b5*c15 6% “ “
16. Maintenance =b5*c16 8% “ “
17. Utilities =b5*c17 10% “ “
18. Administrative & General =b5*c18 2% “ “
19. =Total Operating Expenses =b13:b18 =b19/b5
20. Net Income before fixed Expenses =b11-b19 =b20/b5
21. Less Fixed Expenses:
22. Insurance $40,000 =b22/b5
23. Property taxes $50,000 =b23/b5
24. Depreciation $300,000 =b24/b5
25. Total Fixed Expenses =b22:b24 =b25/b5
26. Net Income before tax =b20-b25 =b26/b5
27. Less Income tax (25%) =b26*25% =b27/b5
28. Net Income after tax =b26-b27 =b28/b5
Dividends: $500,000
Retained Earnings (you need to calculate it)
- A balance sheet at the end of the operating year.
Current Assets Current Liabilities
Cash $27,900 Accounts payable $25,550
Accounts Receivables $20,620 Notes Payable $22,300
Credit card Receivables $2,480 =Total Current Assets ?
Marketable Securities $10,000
Food inventory $8,200 Long-Term Liabilities $2,170,000
Beverage inventory $9.600 Stockholders’ Equity:
Supplies $2,100 Common Stock $50,000,000
Prepaid expenses $5,200 Retained Earnings ?
=Total Current Assets ?
Fixed Assets:
Land $10,000,000
Buildings $35,000,000
Equipment $2,000,000
Furniture & Fixtures $5,000,000
Tableware, chinaware $1,000,000
=Total Fixed Assets $ ?
=Total Assets $ ? =Total Liabilities & /stockholders’ equity ?
Both financial statements need to be on Excel, where the income statement with three columns of: (A: Description, B: Dollar amount with 2 decimals and C: Percentages with two decimals).
