Scottish Qualifications Authority 8
SQA Advanced Assessment Support Pack/HP78 47/CASP001/AQ
IT in Business: Spreadsheets (SCQF level 7) January 2021
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:
Bootique Hotel, Jing’an District Bootique Hotel, Huangpu District
BJD9246755 Twin Room Rate ¥890 BHD87656 Twin Room Rate ¥990
BJD7786526 Double Room
Rate
¥1,090 BHD83373 Double Room
Rate
¥1,290
BJD7127689 King Size Room
Rate
¥1,490 BHD88223 King Size Room
Rate
¥1,690
BJD8875432 Family Room
Rate
¥1,790 BHD73388 Family Room
Rate
¥2,090
Scottish Qualifications Authority 9
SQA Advanced Assessment Support Pack/HP78 47/CASP001/AQ
IT in Business: Spreadsheets (SCQF level 7) January 2021
Occupancy Data: Number of rooms booked over the period are as follows
Bootique Hotel, Jing’an District Bootique Hotel, Huangpu District
2016 May June July Aug 2016 May June July Aug
Twin Room 16 16 17 18 Twin Room 22 19 19 20
Double Room 18 15 16 17 Double Room 20 23 24 23
King Size
Room
12 18 18 15 King Size
Room
Family Room 9 8 9 8 Family Room 17 17 18 17
Expenses
Monthly Expenses Bootique Hotel,
Jing’an District
Bootique Hotel,
Huangpu District
Laundry Costs ¥2,000 ¥2,000
Staff Costs — Permanent ¥28,000 ¥31,000
Cleaning Costs —
subcontracted
¥16,000
¥20,000
Overheads ¥30,000 ¥40,000
Staff Bonus Incentive
Company Wide
5%
Income: Bootique Hotel, Jing’an District
Bar Income May June July August
Bar ¥21,000 ¥21,580 ¥32,000 ¥32,500
Snacks ¥3,000 ¥2,560 ¥2,800 ¥3,100
Bar Lunch ¥7,900 ¥9,800 ¥7,670 ¥9,880
Bar Dinner ¥9,900 ¥10,100 ¥9,870 ¥12,000
Income: Bootique Hotel, Huangpu District
Bar Income May June July August
Bar ¥17,980 ¥18,650 ¥30,980 ¥32,650
Snacks ¥4,550 ¥6,380 ¥8,670 ¥7,690
Bar Lunch ¥6,580 ¥6,230 ¥5,690 ¥11,000
Bar Dinner ¥8,650 ¥9,630 ¥11,000 ¥19,000
Scottish Qualifications Authority 10
SQA Advanced Assessment Support Pack/HP78 47/CASP001/AQ
IT in Business: Spreadsheets (SCQF level 7) January 2021
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.
May June July Aug
Bootique Hotel,
Jing’an District
Sales
¥4,330 ¥2,670 ¥5,330 ¥2,560
Bootique Hotel,
Huangpu District
Sales
¥2,450 ¥1,670 ¥2,310 ¥1,990
Commission on
sales 15%
Scottish Qualifications Authority 11
SQA Advanced Assessment Support Pack/HP78 47/CASP001/AQ
IT in Business: Spreadsheets (SCQF level 7) January 2021
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.
Scottish Qualifications Authority 12
SQA Advanced Assessment Support Pack/HP78 47/CASP001/AQ
IT in Business: Spreadsheets (SCQF level 7) January 2021
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.
