BSBTEC402 Design And Produce Complex Spreadsheets Assessment

Important Note to Students:

Please read the Assessment Mapping carefully when preparing your assessments to ensure the relevant Performance Criteria, Performance Evidence and Knowledge Evidence for each question are met.

Part A: Written Responses

1. List the main characteristics of an ergonomically sound workstation. What postural aspects are important for using a computer?
2. What do you think are the most important things to get right around your computer before you start doing any work?
3. Describe the term carbon reduction and make reference to the following items:
   1. recycling used paper and shredded paper
   2. double sided paper printing
   3. using a power save option for equipment
4. Look at the following 2 scenarios (pictures) and you will recognise that there are a few problems with how the workstations are set up. For each picture, identify three problems with the ergonomics. For each of the problems,
   1. identify and describe the nature of the problem,
   2. explain why it is a problem,
   3. describe how it could be solved.

Part B: Case Study (Excel Spreadsheet)

Important Note to Students:

Use Excel Spreadsheet to answer the following 2 cases, and submit it together with your Part A written responses to myAPC.hub.

Case 1 Create an Excel graph

Create, save in excel and print a graph according to organisational requirements. You will need to determine the correct type of graphs to use and then create the graph. Your answer should be presented in an Excel spreadsheet with the name of Your name_Your student number_A20457 Complex Spreadsheet_A1_Case1_myapchub.

The volleyball committee wants to know about the distributions of boys and girls through the 4 houses (Orange, Green, Blue, Red), as well as which house boys and girls represent, to present to the finance committee for more funding.

Required:

Use the data given below to determine the correct type of graph(s). Create the graph with labels and titles, and save them as a full worksheet view.

Case 2 Case Study

Yolanda is currently running a restaurant. As her accountant, she approached you and sought your help to set up an Excel Spreadsheet for her business so that she can keep track of her sales and expenses on a daily basis, showing the following information/specifications:

1. Sales for Lunch and Dinner period.
2. Total sales on each day.
3. Daily expenses including the following categories:
   • Food & Beverage Purchases (All the purchases from the Food/Beverage Suppliers)
   • Advertising
   • Rent
   • Electricity & Gas
   • Shop Supplies
   • Salaries & Wages
4. The Expenses will be recorded in the Excel spreadsheet when paid.

Required:

Create an Excel Spreadsheet with the name of Your name_Your student number_A20457 Complex Spreadsheets_A1_Case2_myapchub, and create various sheets according to the following requirements:

• After entering all the data above to the sheet called Aug22-WK1, set up the formulae to work out the total and answer the following questions in the table. Your Totals to answer the following questions must be shown in your spreadsheet. (Important Notes: You are required to set up the formulae in the spreadsheet, and also submit this spreadsheet to myAPC.hub. PDF format and screenshots are not acceptable in this case. If the formulae are not found in your spreadsheet, your assessment will be returned.)

• Sheet 3: Create/Open a new sheet in the same spreadsheet and rename the sheet to Accounts. Type the following information to this sheet.

• Sheet 4: Create/Open a new sheet in the same spreadsheet and rename the sheet to Case 2 Q6. Yolanda asks you to instruct how to create a button on each sheet, which allows her to visit the Accounts sheet from any sheet anytime. Answer the following questions in this sheet.
  6. Which function can you use to automate this repetitive task so that you can do the task again with a single click on a button?
  7. Watch the video (https://www.youtube.com/watch?v=wBDp9G2zWe8) available on the Student Workbook. You are only required to understand how to fill out the Record Macro table. The creation and setup of a button is not needed. Required: After watching the video, go back to your Aug22-WK1 sheet (Sheet 2 you just created in Question 3) that records all the sales and expenses for the period from 1/08 to 7/08/2022, record a Macro and setup the shortcut key as Ctrl + Shift + A that allows the users to access Accounts sheet from any other sheet anytime.

(Hint: You are strongly recommended to follow the steps mentioned in the video. Please submit your Excel spreadsheet to myAPC.hub. The shortcut key will be checked when marking the assessment. If you need more support, please attend your tutorial classes before the assessment due date to seek consultation with your trainers.)

Important Note to Students:

Before you start this question (Question 6), please enable Developer mode in your Excel Spreadsheet. Please visit your tutorial classes before the due date if you need more support.

If you are using Windows:

• On the File tab, go to Options > Customize Ribbon.
• Under Customize the Ribbon and under Main Tabs, select the Developer check box.
• For more information, please click on the following link: https://support.microsoft. com/en-us/topic/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45

If you are using MacBook:

• Select Excel > Preferences > Ribbon & Toolbar.
• Under Customize the Ribbon, select Main Tabs and then check Developer.
• Click Save and then close Excel Preferences.
• For more information, please click on the following link: https://support.microsoft. com/en-us/office/use-the-developer-tab-to-create-or-delete-a-macro-in-excel-formac-5bd3dfb9-39d7-496a-a812-1b5e8e81d96a

Important Note to Students:

Please read the Assessment Mapping carefully when preparing your assessments to ensure the relevant Performance Criteria, Performance Evidence and Knowledge Evidence for each question are met.

Case Study

Background Information

You are working as the accounting apprentice to the family business in Riverside Reception. Revenue and Profit in the last quarter was lower than anticipated; therefore, management decided to find a way to generate more revenue. Your manager has approached you to analyse the available data that he collected.

Your manager wants you to analyse the available data and design a spreadsheet for him that makes tracking revenue and expenses to be more efficient. Your manager also wants to be able to use the Excel Spreadsheet to keep track of employee rosters and to record bookings. The head chef was also interested in this information but he preferred graphics rather than reading.

You also want a report printout of the summary data as well as the logo of the business as well as the usual headers and footers.

The data that your boss collected for you includes:

• There are 1 Function Room and 1 Dining Room open to the customers.
• The sources of income from the Function Room include the rental income and catering.
• The source of income from the Dining Room is catering.
• The key cost of the business is the wages paid to the staff.

Additional Information

Function Room

The function room is available for parties, gatherings and any celebration events. The function room is open 7 days a week. This room has capacity for up to 100 people. The fees charged to the customers include:

• Half days: flat rate of $600 a day (including all equipment and support staff)
• Full days: flat rate of $1,800 a day (including all equipment and support staff)

The fees above do not include the catering charges which are shown below.

Service Fees for Function Room Catering:

Staffing for Function Room Service:

The following table shows the number of staff/employees required for each event booking. Each staff member will be paid $120 per booking/event.

Dining Room

The dining room is open on Fridays, Saturdays and Sundays for lunch and dinner. Seats 40 people and could turn over more than 80 people during each meal session (i.e. There will be 2 rounds for booking for each lunch and dinner period.) 2 wait staff ($200 per staff per day) and 1 manager ($350 per day) are required each day. The menu average per person for 2 courses is $60.

Your manager asks you to record the following booking for the upcoming week to the spreadsheet.

Required:

Question 1:

Create an Excel Spreadsheet with the name of Your name_Your student number_A20457 Complex Spreadsheet_A2_myapchub. You are required to create 3 sheets to record the income and expenses for Function Room, Dining Room and Total Profit & Loss (P & L) for the upcoming week with the information provided in the scenario.

Sheet 1: Rename this sheet to Function Room. You can either type the following template in your Excel Spreadsheet or create your own. If you choose to create your own template, please ensure the following information must be covered, as the minimum requirements.

Sheet 2: Rename this sheet to Dining Room. You can either type the following template in your Excel Spreadsheet or create your own. If you choose to create your own template, please ensure the following information must be covered, as the minimum requirements.

Sheet 3: Rename this sheet to P & L. Transfer the relevant data from the Function Room and Dining Room sheets to this sheet and work out the totals as required in the following template. You can either type the following template in your Excel Spreadsheet or create your own. If you choose to create your own template, please ensure the following information must be covered, as the minimum requirements.

Question 2:

Create/Open a new sheet in the same spreadsheet and rename the sheet to A2 Case, and answer the following questions:

1. When you encounter any difficulties in preparing the Excel Spreadsheet (for example, technical issues, functional issues, etc.), what support can you access? Three suggestions are required.
2. Undertake online research in the Microsoft Support; advise if you can create an external reference between cells in different spreadsheets/workbooks. If yes, briefly describe how to do it.