Section 2 — Assessment

The assessment example(s) below can be used by centres, or alternatively centres can develop their own assessment.

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 data information you have been provided with is detailed below:

Independent Sales Income

To encourage and support local small businesses we decided to sell hand crafted tourist items from a selection of these businesses. The total income from these sales is listed below. We receive 15% commission on these sales. This will be reviewed every 6 months for viability.

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.