Assessment Task 3
This is the third and final assessment for this unit. It consists of one part.
This assessment should take three hours to complete, and should follow the standards set out in the style guide.
When you are confident that you have met all requirements for this assessment, upload it to the Study Smart site for this unit for marking purposes. Make sure your name is on the file so that it is identifiable as your work.
You will be notified as soon as possible about your results for this assessment.
Workbook SGA1036 Applied Computing (Spreadsheets)
Assessment Task 3
Case Study Linked Spreadsheet Solution
You work as the assistant to the Operations Manager for a Heritage listed inner City Hotel. Last quarter’s revenue and profit was considerably lower than predicted so management are looking for ways to generate more income. You have suggested that the older seldom used part of the building be converted into function and/or conference rooms with provision for a smaller room to be used as Silver Service Dining. The Hotel has a 4-star rating for their kitchen, numerous bars and well trained staff.
Your boss, the operations manager has collected some preliminary information on fit out and equipment costs for the two function rooms and dining room. Other costs such as wages, overheads and consumables are readily available. Your boss now wants you to analyse the available data and design a spreadsheet solution that makes tracking the income and expenses for this section more efficient.
Your boss also wants to be able to use the spreadsheet to calculate how long it will take to see a ROI based on projected usage and dining figures and wonders if you could also use the design to record bookings and staff rosters. The Head Chef is also interested in this information but prefers graphics to just reading.
The Operations Manager wants a ‘nice’ report printout of the summary data as well as the usual headers and footers.
© Study Group Australia Pty Limited, SGA1036-L6/3/14-V1 75
Conference room 1
Has room for up to 300 people. The flat rate of $3000 a day includes all equipment and support staff.
Conference room 2
Has room for up to 100 people. The flat rate of $1000 a day includes all equipment and support staff.
Light refreshments $15 per person 4 wait staff per 100 people @ $80 each
Full catering $25 per person 6 wait staff per 100 people @ $120 each
Full catering with alcohol $45 per person 8 wait staff per 100 people @ $120 each
Open Thursday, Friday and Saturday for lunch and dinner. Seats 30 people and could turn over more than 60 people during each meal session. The a la carte menu average per person for two courses is $50. Two wait staff are required at $200 per day. The one Maître d'hôtel costs $350.
Renovation and fit out expenses
Conference room 1 Conference room 2 Dining Room
$50,000 $30,000 $30,000
Workbook SGA1036 Applied Computing (Spreadsheets) Requirements
1) Develop a linked spreadsheet solution that uses all of the following to solve the ROI problem for the Operations Manager and the Head Chef.
1. functions and formulae
3. testing and error checking formula
4. cell formatting and referencing
7. data import and export
8. getting help
2) During the development of your spreadsheet solution did you need to access an online help facility, excel help file, Lynda.com or other search engine to assist you?
If yes, specify the site or source and specify what assistance you required.
If no, compare two of the above sites/sources of help assistance (e.g. excel help and Google) and comment on which you prefer and why.
When you have completed this assessment upload your file/s using Study Smart for this subject (refer to the link ‘How to submit an assessment’ in the Study Smart home page).
© Study Group Australia Pty Limited, SGA1036-L6/3/14-V1 77
1) Items 1-10 are used in the creation of a linked spreadsheet and the links work.
2) A short response that demonstrates what the learner sourced to assist them in the development of spreadsheet file.