Hotel Artisan Assessment 1

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Assessment 1

Outcomes covered 1, 2 and 3

Assessment instructions Background: Hotel Artisan

In 2012 the retro Bootique Hotel opened for business in the Jing’an District of Shanghai. The hotel went from strength to strength and by the end of 2015 the owners decided that it was time to open a second hotel in the Huangpu area of Shanghai. The Huangpu Bootique Hotel has also been a huge success and has found that customers are returning frequently and recommending the hotel to friends and family.

A review of the admin systems this year has shown that it is time to take a more joined up approach to the accounts and data management areas across both hotels.

With this in mind, and to start the change, the owners would like you to design a spreadsheet which will incorporate the data she needs to keep track of, but also give them an overview of how the joint net operating profit is looking for the entire company.

There is an amount of base information which is currently stored in a mixture of ways, some being in paper format and stored within various existing spreadsheets. This data has all been collated now and the base provided with is detailed below: data information you have been 

Occupancy Data: Number of rooms booked over the period are as follows

Part 1

It is your role to take the information provided and design a spreadsheet which will meet the brief.

To ensure efficiency of the resulting design you want to make use of your planning and layout skills. We are keen to future proof any spreadsheet created. With this in mind you should consider your design carefully. Should there be any updates made to the spreadsheet the changes should be as efficient as possible. It is suggested that you consider creating a separate data sheet within the workbook to contain all of the data provided.

Task 1

1  A Staff bonus incentive of 5% will only be paid if Total Gross Income exceeds ¥80,000 per month. This bonus will only be paid on the amount of gross income in excess of ¥80,000 and applies separately to each hotel. Please use an appropriate function to calculate whether or not this bonus will be paid and if so, how much it will be in each hotel. This will be paid into an account and used at our annual away day in March of each year.

2  In each bar we have independent sales income, this comes from local suppliers who pay a commission on any sales on our premises. This is currently 15% and the total figures received should be detailed in the Bar Income section of the spreadsheet.

Information required for our management meetings are as follows:

¨  Total Income from Rooms per month

¨  Total Bar Income

¨  Gross Revenue

¨  Total Expenses

¨  Net Operating Profit

3  During the consultation regarding this spreadsheet it was noted that the Room Reference codes were different lengths. Please use a function to only use the first 5 characters and numbers in the reference given.

4  Appropriate linking of all data should be used. Please name the cell containing the Bonus incentive percentage and the Independent Sales Commission rate appropriately and use this within your calculations.

5  As this data will be used and presented at our quarterly management meeting it is important that the final spreadsheet is visually pleasing. Please use your formatting skills to do this.

Task 2

1  Please create a consolidated sheet which uses 3D referencing conventions to create an overall sheet. This will give the management team a clear view of the financial position at a strategic level for both hotels.

2  It is possible (though not confirmed) that there will be a percentage increase in the room rates at both hotels. In order to give the management team an idea of what the outcome will be if this change is implemented please add a calculation to the consolidated sheet to show what the total income from rooms would be should a 12% increase per month be put in place — assuming the same number of guests stay in each hotel.

Task 3

1 Please create an appropriate chart which will show the combined room income for all room types for the period May–August.

Task 4

1 Please create a macro which will allow the management team to print the data stored within the individual hotels, plus a copy showing the formula. This should all be completed in one macro.

Task 5

1  In order to minimise the risk of accidental data corruption, can you please add protection to the spreadsheets containing information for our Jing’an District and Huangpu District Hotels. All cells containing formula should be protected against editing. Please use the password HOTEL123 to achieve this.

2  Please also protect the entire consolidation sheet. Use the same password.

Part 2

The management team of the Hotels have engaged an external consultant to collect the views of our customers. During the last year, each customer has been asked to complete an online survey following their stay with us. The consultant has provided us with the raw data from the survey (Bootique Hotel Survey.xls). Please access this file for the following tasks.

Task 6

1  We can see the gender of each of our respondents but it would be interesting to have a summary showing how many males, females and others who responded from each hotel. Please produce this summary.

2  Please present the gender demographic data in a graphical format, include an appropriate heading and percentage break down of the data.

Task 7

1  Please calculate the average score received in the Overall Score category.

2  Please provide the management team with results of the standard deviation calculation of the overall score.

3  With each of the other categories (CHECKIN, ROOM, RESTAURANT, etc) we would like to know how many of the respondents awarded a 5 for these categories.

Task 8

1 The management team would also like to see a frequency distribution table detailing the overall scores awarded by our customers.

Task 9

1  In order to look at the data collected during the time of the accounts information we have, can you use a spreadsheet function to highlight the months May– August in Green.

2  Use a spreadsheet feature which will retain the column headings in place while scrolling through the data.

Task 10

There is one member of the management team who does not have any experience of working with the type of statistical analysis data that a spreadsheet can provide. With this in mind, please evaluate the results of your calculations providing a written appraisal of the data that you have created. In this you should include:

¨  an explanation of the frequency distribution results

¨  an evaluation of the graphical representation of the gender demographics, suggesting any possible business improvement/expansion opportunities

¨  an explanation of the standard deviation result

A screen capture tool could be used to show extracts from the spreadsheet to illustrate your findings in this short report.