BSBTEC302 Design and produce spreadsheets

Assessment 2 | Practical Task 1

Student Name

Hui Sin Kho

Student ID

Instructions

You are required to plan, design, produce and finalise a spreadsheet for the simulated business, Callistemon Quays Universal Hotel (CQU Hotel).

To successfully complete these tasks, you are required to

carefully read the scenario and complete the tasks

ensure task are completed to industry standards. 100% accuracy is required.

Tools & Resources

Follow the guidelines from the simulated business, CQU Hotel, to assist with completing this assessment particularly, CQU Hotel Spreadsheet Formatting Guidelines.

Files Required

Maintenance Budget.xlsx

CQU Hotel Spreadsheet Formatting Guidelines

Scenario

You are employed as a business trainee at CQU Hotel. You have been rotated through different areas and are currently working with Sharna Dines, Personal Assistant to the General Manager. Your work consists of administration duties supporting all areas of the hotel. Sharna assigns tasks to you such as spreadsheet design and production.

You will plan, design, produce and finalise four different spreadsheets using the most appropriate application.

For each task you must

identify the purpose of the spreadsheet and the audience

design the spreadsheet to suit the purpose, audience, and information requirements

identify the requirements for data entry, storage, output, timeline, and format

follow the CQU Hotel Spreadsheet Formatting Guidelines

save the file with the required file name and upload to Moodle

ORGANISATIONAL CHART

The Callistemon Quays Universal Hotel (CQU Hotel) is a boutique five-star hotel located in the beautiful tropical location of Palm Cove, North Queensland. The organisational chart is provided below:

Setting:

The Assistant Manager, Keith Jones, requires a spreadsheet that compares the budget for maintenance with the amounts spent in the first quarter of 2023 (January, February, March). This is an internal document. Keith Jones has asked for a one-page PDF version of the file emailed to him by close of business today.

You are to:

Read the description of the task on the next page

Provide evidence of your planning by filling in the Planning table

Consult with the stakeholder (your assessor) if necessary

Design and produce the spreadsheet

Check and finalise the spreadsheet making sure you follow the Spreadsheet Formatting Guidelines.

Below is an extract from CQU Hotel Spreadsheet Formatting Guidelines

You need to show that you can add a path to the footer. Your own path is acceptable; CQU Hotel path is not needed.

Planning table for the spreadsheet
What is the purpose of the spreadsheet?	Comparison figure and chart of the budget for maintenance with the amounts spent in the first quarter of 2023
Who is the target audience?	The Assistant Manager, Keith Jones
Appropriate application to use	MS Excel & PDF
Requirements Where will the data come from?	XLS file named Maintenance Budget supplied by Keith Jones
Will it be typed in or imported?	Typed
Where is file to be saved?	Into a subfolder within the department folder with name starting from date
Presentation format. What output is required? For example, email attachment, link to another document, print out, create a PDF, shared from OneDrive	One page PDF to be created and emailed.
Who is to receive the output above?	The Assistant Manager, Keith Jones
When does the spreadsheet need to be ready by?	Today, by close of the business
Describe any organisational requirements, e.g. for formatting, data security, shared data	CQU Hotel Spreadsheet Formatting Guidelines to be followed
Other notes	Comparison figure & two different charts required.

Description of task

1. Keith Jones has supplied a file Maintenance Budget.xlsx for you to use.

2. The Finance Manager, Samantha Warne, has supplied the following figures for you to enter.

Maintenance Expenses
January to March 2023
Hotel Area	Amount Spent
Apartments	4,125.84
Dining	4,331.65
Kitchen	7,841.67
Outdoors	4,267.42
Pool area	3,176.49
Reception	4,158.31
Villas	2,117.42

3. For each hotel area, Keith wants to know the difference between the amount spent and the budgeted amount (Budget minus the Amount Spent).

4. Use conditional formatting to highlight the difference amounts that are positive. That is, where not all the budget amount was spent.

5. For each area of the hotel, Keith wants a percentage of the budget spent (Amount Spent divided by the Budget amount).

6. Create an input area for Forecast Factor 5%.

7. Add a column with a heading, Forecast. In the new column, calculate an increase on the Amount Spent for each area, using the percentage in the input area. Amount spent + (Amount Spent times the Forecast Factor). (Hint: absolute reference required in the formula)

8. Totals all columns except the percentage column. For the percentage column, find a percentage of Total Amounts Spent divided by the Total Budget.

9. Create a chart to show the Budget amounts and the Amount Spent for each hotel area. (Do not include totals in the chart)

10. Copy the chart and paste it beside the first chart. For the second copy of the chart, change the chart type but still show the Budget amounts and the Amount Spent for each hotel area. Indicate the areas that are overspent using an arrow on the chart.

11. Adhere to the CQU Hotel Spreadsheet Formatting Guidelines. See the following extract and apply to your spreadsheet. Save the spreadsheet as Maintenance Budget Jan-Mar 2023.

12. Check the following and edit as necessary before resaving. Layout and format follow the CQU Hotel Spreadsheet Formatting Guidelines Formulas have been tested and checked Output meets the task requirements Readability - font sizes, logical layout, use of input area, data and totals labelled appropriately

13. Prepare a one-page PDF file for the stakeholder.

KEY ELEMENTS OF TASK 1

Components

o Create a comparison spreadsheet with given calculations following formatting guidelines

o Create a chart, modify a chart

o Create a one-page PDF file

SUBMISSION REQUIREMENTS

Upload the following to the Moodle activity - Assessment 2| Practical Tasks on the BSBTEC302 Moodle page.

This completed assessment document (BSBTEC302 Assessment 2 Practical Task 1)

Maintenance Budget Jan-Mar 2023.xlsx

Maintenance Budget Jan-Mar 2023.pdf

Your assessor will provide feedback via the Moodle activity.