Crystal Hotel Pty Ltd is a privately owned 3.5 stars hotel located in Sydney. The Hotel consists of 160 rooms with maximum capacity of 350 guests, a restaurant with capacity of 150 guests, a function room with maximum capacity of 250 guests and a conference room with maximum capacity of 200 guests. The average price per room per night is $150.
While the hotel is located in a very popular location, close proximity to a river and in the city centre, it is becoming quite out-dated. The owners rely heavily on their corporate clientele. Clients usually use the hotel for their expat employees. Due to long-term contracts, they pay on credit with invoices being issued at the end of each month. The hotel is often faced with outstanding invoices. The owners have so far tolerated it as it usually occurs with their long-term clients. Additionally, to the accommodation services, they often use hotel facilities for their functions and conferences.
The hotel is constantly having difficulties retaining a good quality staff as they always get junior personnel, which once gaining experience will usually leave for better opportunities in hotels with higher ratings. The biggest issue is to retain high quality personnel in the hotel restaurant, especially a chef.
The owners would like to increase the hotel star rating by renovating or refurbishing the hotel and improving their services. They are thinking about building a Wellness Centre on the rooftop of the hotel, which would include a massage treatment room, gym, spa, sauna and an outdoor pool.
As new plans will require quite extensive capital investment, the owners would like to know where they stand financially before making any major decisions. You have been appointed to analyse their financial statements and to give them an insight on which areas should be improved and analysed further.
You are expected to conduct the financial statement analysis in an Excel workbook and then use your findings in creating a business report to be submitted to the client.
Excel Workbook Calculations
This Excel Workbook is available on learning portal under assessment section. You will find 6 worksheets within the file. The first 3 contain financial information about the hotel. The final 3 worksheets are for you to complete calculations. All answers to be rounded to 2 decimal places.
You must submit the completed Excel Workbook, Business Report and presentation materials.
- Vertical Analysis:
- Conduct a Vertical Analysis of Crystal Hotel’s Statement of Profit and Loss for the year ended 30 June 2018 using Vertical Analysis P&L
- Conduct a Vertical Analysis of Crystal Hotel’s Statement of Financial Position as at 30 June 2018 using the Vertical Analysis SOFP
- Ratio Analysis:
- Conduct a Ratio Analysis using Ratio Analysis worksheet.
| Crystal Hotel Pty Ltd | |
| Statement of Profit or Loss | |
| For the period ended 30/06/2018 | |
| Revenue | |
| Rooms Revenue | $5,206,920 |
| Food and Beverage Revenue | $2,986,497 |
| Functions | $1,752,326 |
| Other Revenue | $381,024 |
| Total Revenue | $10,326,767 |
| Cost of Sales | |
| Rooms Cost of Sales | $961,945 |
| Food and Beverage Cost of Sales | $946,354 |
| Other Cost of Sales | $248,636 |
| Total Cost of Sales (excluding personnel cost) | $2,156,935 |
| Gross Profit | $8,169,832 |
| Personnel Costs | |
| Rooms | $1,952,451 |
| Food and Beverage | $2,199,354 |
| Administrative and General | $353,274 |
| Sales and Marketing | $240,543 |
| Property Management and Maintenance | $160,245 |
| Total Personnel Costs | $4,905,867 |
| Unallocated Operating Costs | |
| Administrative and General | $512,452 |
| Information Systems | $8,065 |
| Sales and Marketing | $160,345 |
| Security | $56,004 |
| Transportation | $105,334 |
| Property Operations and Maintenance | $315,247 |
| Utilities | $135,354 |
| Total Undistributed Operating Costs | $1,292,801 |
| Operating Profit | $1,971,164 |
| Insurance Expense | $24,404 |
| Depreciation and Amortisation | $40,276 |
| Income Before Interest and Tax | $1,906,484 |
| Interest Expense | $35,787 |
| Income Before Income Taxes | $1,870,698 |
| Income Taxes | $561,209 |
| Net Profit | $1,309,488 |
| Crystal Hotel Pty Ltd | |
| Statement of Financial Position | |
| at at 30/06/2018 | |
| Current Assets | |
| Cash and Cash Equivalents | $474,214 |
| Accounts Receivable | $268,925 |
| Inventory | $470,989 |
| Pre-paid Expenses | $160,935 |
| Total Current Assets | $1,375,063 |
| Non Current Assets | |
| Property, Plant & Equipment | $4,978,677 |
| Intangible Assets | $681,764 |
| Vehicles | $356,168 |
| Other Non-current Assets | $138,338 |
| Total Non-Current Assets | $6,154,948 |
| Total Assets | $7,530,011 |
| Current Liabilities | |
| Bank Overdraft | $50,751 |
| Accounts Payable | $359,530 |
| Provisions | $150,210 |
| Other Current Liabilities | $530,694 |
| Total Current Liabilities | $1,091,185 |
| Non Current Liabilities | |
| Loans | $590,350 |
| Total Non Current Liabilities | $590,350 |
| Total Liabilities | $1,681,535 |
| Owner’s Equity | |
| Investment Capital | $2,905,208 |
| Capital Reserve | $1,372,992 |
| Retained Earnings | $1,570,275 |
| Total Owner’s Equity | $5,848,475 |
| Total Liabilities and Onwer’s Equity | $7,530,011 |
| Crystal Hotel Pty Ltd | ||||
| Cumulative Statements of Financial Position | ||||
| 2015 | 2016 | 2017 | 2018 | |
| Current Assets | ||||
| Cash and Cash Equivalents | $842,946 | $961,823 | $605,192 | $474,214 |
| Accounts Receivable | $351,228 | $486,315 | $626,806 | $268,925 |
| Inventory | $149,137 | $248,561 | $297,193 | $470,989 |
| Pre-paid Expenses | $75,649 | $84,943 | $96,831 | $160,935 |
| Total Current Assets | $1,418,959 | $1,781,642 | $1,626,021 | $1,375,063 |
| Non Current Assets | ||||
| Property, Plant & Equipment | $2,306,214 | $2,528,838 | $2,669,329 | $4,978,677 |
| Intangible Assets | $540,350 | $732,715 | $733,687 | $681,764 |
| Vehicles | $54,977 | $84,295 | $118,877 | $356,168 |
| Other Non-current Assets | $49,280 | $96,723 | $111,312 | $138,338 |
| Total Non-Current Assets | $2,950,821 | $3,442,570 | $3,633,205 | $6,154,948 |
| Total Assets | $4,369,780 | $5,224,212 | $5,259,227 | $7,530,011 |
| Current Liabilities | ||||
| Bank Overdraft | $51,063 | $56,169 | $26,537 | $50,751 |
| Accounts Payable | $494,961 | $372,842 | $516,575 | $359,530 |
| Provisions | $253,965 | $122,724 | $144,814 | $150,210 |
| Other Current Liabilities | $384,729 | $372,842 | $430,119 | $530,694 |
| Total Current Liabilities | $1,184,717 | $924,577 | $1,118,044 | $1,091,185 |
| Non Current Liabilities | ||||
| Loans | $540,350 | $540,350 | $540,350 | $590,350 |
| Total Non Current Liabilities | $540,350 | $540,350 | $540,350 | $590,350 |
| Total Liabilities | $1,725,067 | $1,464,927 | $1,658,394 | $1,681,535 |
| Owner’s Equity | ||||
| Investment Capital | $1,864,208 | $1,864,208 | $1,864,208 | $2,905,208 |
| Capital Reserve | $287,558 | $530,624 | $540,350 | $1,372,992 |
| Retained Earnings | $492,947 | $1,364,454 | $1,196,275 | $1,570,275 |
| Total Owner’s Equity | $2,644,713 | $3,759,285 | $3,600,833 | $5,848,475 |
| Total Liabilities and Onwer’s Equity | $4,369,780 | $5,224,212 | $5,259,227 | $7,530,011 |
| Crystal Hotel Pty Ltd | Vertical Analysis |
|
| Statement of Profit or Loss | ||
| For the period ended 30/06/2018 | ||
| Revenue | ||
| Rooms Revenue | $5,206,920 | ? |
| Food and Beverage Revenue | $2,986,497 | ? |
| Functions | $1,752,326 | ? |
| Other Revenue | $381,024 | ? |
| Total Revenue | $10,326,767 | ? |
| Cost of Sales | ||
| Rooms Cost of Sales | $961,945 | ? |
| Food and Beverage Cost of Sales | $946,354 | ? |
| Other Cost of Sales | $248,636 | ? |
| Total Cost of Sales (excluding personnel cost) | $2,156,935 | ? |
| Gross Profit | $8,169,832 | ? |
| Personnel Costs | ||
| Rooms | $1,952,451 | ? |
| Food and Beverage | $2,199,354 | ? |
| Administrative and General | $353,274 | ? |
| Sales and Marketing | $240,543 | ? |
| Property Management and Maintenance | $160,245 | ? |
| Total Personnel Costs | $4,905,867 | ? |
| Unallocated Operating Costs | ||
| Administrative and General | $512,452 | ? |
| Information Systems | $8,065 | ? |
| Sales and Marketing | $160,345 | ? |
| Security | $56,004 | ? |
| Transportation | $105,334 | ? |
| Property Operations and Maintenance | $315,247 | ? |
| Utilities | $135,354 | ? |
| Total Undistributed Operating Costs | $1,292,801 | ? |
| Operating Profit | $1,971,164 | ? |
| Insurance Expense | $24,404 | ? |
| Depreciation and Amortisation | $40,276 | ? |
| Income Before Interest and Tax | $1,906,484 | ? |
| Interest Expense | $35,787 | ? |
| Income Before Income Taxes | $1,870,698 | ? |
| Income Taxes | $561,209 | ? |
| Net Profit | $1,309,488 | ? |
| Crystal Hotel Pty Ltd | Vertical Analysis |
|
| Statement of Financial Position | ||
| at at 30/06/2018 | ||
| Current Assets | ||
| Cash and Cash Equivalents | $474,214 | ? |
| Accounts Receivable | $268,925 | ? |
| Inventory | $470,989 | ? |
| Pre-paid Expenses | $160,935 | ? |
| Total Current Assets | $1,375,063 | ? |
| Non Current Assets | ||
| Property, Plant & Equipment | $4,978,677 | ? |
| Intangible Assets | $681,764 | ? |
| Vehicles | $356,168 | ? |
| Other Non-current Assets | $138,338 | ? |
| Total Non-Current Assets | $6,154,948 | ? |
| Total Assets | $7,530,011 | ? |
| Current Liabilities | ||
| Bank Overdraft | $50,751 | ? |
| Accounts Payable | $359,530 | ? |
| Provisions | $150,210 | ? |
| Other Current Liabilities | $530,694 | ? |
| Total Current Liabilities | $1,091,185 | ? |
| Non Current Liabilities | ||
| Loans | $590,350 | ? |
| Total Non Current Liabilities | $590,350 | ? |
| Total Liabilities | $1,681,535 | ? |
| Owner’s Equity | ||
| Investment Capital | $2,905,208 | ? |
| Capital Reserve | $1,372,992 | ? |
| Retained Earnings | $1,570,275 | ? |
| Total Owner’s Equity | $5,848,475 | ? |
| Total Liabilities and Onwer’s Equity | $7,530,011 | ? |
| Profitability Ratios | 2018 | Industry |
| Gross Profit Margin | ? | 81% |
| Net Profit Margin | ? | 11% |
| Return on Assets | ? | 8% |
| Return on Equity | ? | 9% |
| Efficiency Ratios | ||
| Inventory Turnover | ? | 8.60 |
| Number of days Inventory Held | ? | |
| Accounts Receivable Turnover | ? | |
| Accounts Receivable Collection Period | ? | 35.00 |
| Liquidity Ratios | ||
| Current ratio | ? | 3.20 |
| Quick Ratio | ? | 2.12 |
| Solvency Ratios | ||
| Debt to Equity Ratio | ? | |
| Debt Ratio | ? | |
| Equity Ratio | ? | |
| Interest Coverage | ? |
NOTE: Net Credit Sales for the period ended 30 June 2018 were $2530853
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.