Assessment 1

Outcomes covered 1, 2 and 3

Background: Hotel Artisan

In 2012 the retro Hotel Artisan opened for business in Broughty Ferry, near Dundee. 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 Leith area of Edinburgh. The Leith Artisan 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:

Hotel Artisan, Broughty Ferry

Leith Artisan Hotel

HAB9246755

Twin Room Rate

89

LAH87656

Twin Room Rate

99

HAB7786526

Double Room Rate

109

LAH83373

Double Room Rate

129

HAB7127689

King Size Room Rate

149

LAH88223

King Size Room Rate

169

HAB8875432

Family Room Rate

179

LAH73388

Family Room Rate

209

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

Hotel Artisan, Broughty Ferry

Leith Artisan Hotel

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

21

23

23

24

Family Room

9

8

9

8

Family Room

17

17

18

17

Expenses

Monthly Expenses

Hotel Artisan Broughty Ferry

Leith Artisan Hotel

Laundry Costs

200

200

Staff Costs Permanent

2,800

3,100

Cleaning Costs subcontracted

1,600

2,000

Overheads

3,000

4,000

Staff Bonus Incentive Company Wide

5%

Income: Hotel Artisan Broughty Ferry

Bar Income

May

June

July

August

Bar

2,100

2,158

3,200

3,250

Snacks

300

265

280

310

Bar Lunch

790

980

767

988

Bar Dinner

990

1,010

987

1,200

Income: Leith Artisan Hotel

Bar Income

May

June

July

August

Bar

1,798

1,865

3,098

3,265

Snacks

455

638

867

769

Bar Lunch

658

623

569

1,100

Bar Dinner

865

963

1,100

1,900

Independent Sales Income

To encourage and support local small businesses we decided to sell the hand crafted food 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

Artisan Broughty Ferry Sales

433

267

533

256

Leith Sales

245

167

231

199

Commission on sales

15%

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

1A Staff bonus incentive of 5% will only be paid if Total Gross Income exceeds

8,000 per month. This bonus will only be paid on the amount of gross income in excess of 8,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.

2In 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

3During 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.

4Appropriate 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.

5As 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.

To assist data analysis later, please add Sparklines or Databars (according to the software you are using), to create a visual snapshot of current income and net profit in both hotels.

Task 2

1Please 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.

2It is possible (though not confirmed) that there will be a % 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

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

Task 4

1Please 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

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

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