BSBTEC302 - Design and produce spreadsheets :Project Assessment

Assessment instructions

Table 1 - Assessment instructions

Specific task instructions

The instructions and the criteria in the tasks and activities will be used by your Teacher/Assessor to determine if you have satisfactorily completed this assessment event. Use these instructions as a guide to ensure you demonstrate the required knowledge and skills.

Part 1: Short answer questions

Read the question or scenario carefully. Your answer should be a minimum of 50 words but no longer than 500 words.

1. List down features of cloud-based application and non-cloud based application.

2. When planning spreadsheets, you need to consider organisational requirements and codes of practice. Refer to:

• GE_Sustainability-Policy-and-Procedure.pdf

(Long URL: hsttps://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=74b18d25-f423-4cb5-80d4-c3d594127cdb)

• GE_Work-Health-and-Safety-Policy.pdf

(Long URL: https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=38d70a96-f9d4-477f-8ead-dedc98ae2c5a)

Using this information list three organisational requirements for each item listed in the table no. 2.

• your answers can be in dot points
• recommended length should be a minimum of 20 words but no longer than 60 words for each item.

Table 2 - Short Answer Question

Part 2: Plan spreadsheet design

For this part of the assessment, you have been asked to complete the following tasks that involve planning and design of spreadsheets.

Scenario

You are employed as an administrative officer at Gelos Enterprises. Your position is based in Customer Service, but you are often required to support other departments in the organisation.

You are currently supporting the audit department and assisting in the development of spreadsheets.

You will prepare a plan for each spreadsheet before producing the spreadsheet. The spreadsheets will be emailed to the project officer for approval prior to completing the spreadsheets.

It is 10:45am Monday morning and you have received the following email from the Audit Department Project Officer:

Email

From: Kay McCormack

Sent: Monday, 01 September 202X 10:44 AM

To: Student Name [Administration Officer]

CC: 

Subject: Spreadsheets Requirements

Dear Student Name,

Please complete a plan to design a spreadsheet for each task as specified. The spreadsheets will be used by the managers to create presentations for the weekly managers meeting on Tuesday at 11:00am. I need the spreadsheets to be completed by 3:30pm today, so I can review the designs and advise you of any changes before the spreadsheets are finalised. Once completed, each spreadsheet will be provided to the manager of the department involved: Sales, Purchasing & Property.

I have attached the information you require. Please use a variety of chart types as per the instructions for each task.

Also, ensure you follow the Style Guide for our organisational layout and presentation instructions when creating the spreadsheets.

If you have any questions regarding these tasks, please contact me.

Kind regards

Kay McCormack

Internal Audit Officer

m: 0491 000 000

111 Business Avenue

TULITZA NSW 9460

p: 02 0000 0000

e: contactus@gelosmail.com.au

Task 1 Requirements

Plan a spreadsheet to record weekly sales and calculate weekly, employee and monthly totals. Use your spreadsheet knowledge to complete the design of the spreadsheet.

The sales data is located in the worksheet tab Task 1 & 2 in the file:

BSBTEC302_AE_Appx_Data.xlsx  

(Long URL: https://share.tafensw.edu.au/share/items/10777aed-0864-47c7-911b-e0423c905311/0/?attachment.uuid=91ce2254-dfaf-43a0-bd0d-4a51cb889876)

Please note:

• The data may be used for analysis, therefore, do not merge cells
• The employees’ names may need to be sorted by surname, separate first and surname into different cells.

Task Requirement

You are required to complete Spreadsheet Plan 1 in the table no. 3 to design a spreadsheet for displaying monthly sales data. The sales data will be used to create a spreadsheet for each month on a separate tab.

The spreadsheet should include the following features:

1. Formula required:

• totals for each week
• a total for each employee
• total monthly sales. 

2. Pie Chart to show the employees’ surnames and sales total for the month, with percentages displayed below the data on each sheet.
3. Ensure the worksheet tab name reflects the month and type of data.

Table 3 - Spreadsheet Plan 1

Task 2

Plan a spreadsheet to record stock on hand (inventory) of mobile phones. On the 30^th of June, a stocktake was held and provided the data for mobile phones.  In future, a stocktake will be held every month and the data will be entered into the spreadsheet you create. This file contains the mobile phone stock on hand to be discussed at the weekly managers’ meeting.

Use your spreadsheet knowledge to complete the design of the spreadsheet.

The mobile phone data is located in the worksheet tab Task 2 in the file https://share.tafensw.edu.au/share/items/10777aed-0864-47c7-911b-e0423c905311/0/?attachment.uuid=91ce2254-dfaf-43a0-bd0d-4a51cb889876>BSBTEC302_AE_Appx_Data.xlsx  

(Long URL: https://share.tafensw.edu.au/share/items/10777aed-0864-47c7-911b-e0423c905311/0/?attachment.uuid=91ce2254-dfaf-43a0-bd0d-4a51cb889876)

Please note:

• the data may be used for analysis, therefore, do not merge cells
• the Cost price of the mobile phones does not include GST.

Task Requirement

You are required to complete Spreadsheet Plan 2 in the table no. 4. The spreadsheet is required to display the value of stock on hand at cost price, the GST, and stock on hand at selling price.  The data will be used to create a spreadsheet for each month on a separate tab.

Task 2 spreadsheet should include the following features:

1. Add additional columns to your worksheet if required, to calculate each of the following:
   1. Value of Stock:This is the value of the stock on hand at cost price, for example, if there are 10 mobile phones at a cost of $50 each, the overall stock value would be $500 (do not include GST).
   2. Freight: The flat rate for freight is $200 per product. The $200 must be shown in a separate cell so that an absolute cell referencecan be used. Freight must be calculated per item per shipment. Divide the freight cost ($200) by the number of items in stock for each product, for example, if there are 10 Apple Phones the freight would be $20 ($200/10).
   3. CoGS: (Cost of Goods Sold) this is the cost of each item to get it to the store (cost price plus freight), for example, the COGS for one phone is $50 (cost) + $20 (freight) = $70.
   4. Mark-up:The mark up is 45%, which is added to the CoGS on each item. The 45% must be shown in a separate cell so that it can be edited at any time. You must use an absolute cell reference in the formula. For example, mark-up for one phone is $70 x 45% = $31.50
   5. GST:Calculate the GST for each item. The 10% must be shown in a separate cell so that it can be edited at any time. You must use an absolute cell reference in the formula. The GST amount is 10% and is added to the cost of the phone including the freight and mark-up, for example, GST for one phone is $101.50 x 10% = $10.15.
   6. RRP:(Recommended Retail Price) the price that each item will sell for, including all costs, mark up and GST, for example, mark-up for one phone is $70 + $31.50 + 10.15 = $111.65
2. Beneaththe data on the spreadsheet in column A add the labels:
3. Total Value of Stock.
4. Average Cost of Freight.
5. Total Number of Phones on Hand.
6. In Column B use the appropriate formula to calculate:
7. Total value of stock currently in the warehouse at cost price.
8. Average cost of freight.
9. Total number of phones on hand.
10. Createa column chart to show the total value of stock on hand at cost price at 30 June for each phone, include the description of each phone in the chart. Display the chart below the data.
11. Ensure the worksheet tab name reflects the month and type of data.

Table 4 - Spreadsheet Plan 2

Task 3

Plan a spreadsheet using the property sales data and create charts for the managers’ weekly meeting. The sales data has been collected by the Real Estate and Property department of Gelos Industries of all property sales for the past 12 months and includes the suburb, type of property and value of the sale.

Use your spreadsheet knowledge to complete the design of the spreadsheet.

At 30 June the Property department of Gelos Industries had sold the properties located in the worksheet tab Task 3 in the file: BSBTEC302_AE_Appx_Data.xlsx  

(Long URL: https://share.tafensw.edu.au/share/items/10777aed-0864-47c7-911b-e0423c905311/0/?attachment.uuid=91ce2254-dfaf-43a0-bd0d-4a51cb889876)

Please note:

• the data may be used for analysis, therefore, do not merge cells.

Task Requirement

You are required to complete Spreadsheet Plan 3 in the table no. 5. The spreadsheets in Plan 3 are required to display the properties sold in each suburb on a separate tab.

Task 3 spreadsheet should include the following features:

• sort the data into sales per suburb and place it in separate tabs for each suburb
• rename the worksheets to the name of each suburb
• use a function to Count the number of sales in each suburb.

Table 5 - Spreadsheet Plan 3

Task 4

In Task 4 open the Excel workbook you prepared in Task 3.

Save a copy of the file as BSBTEC302_AE_2of2_StudentName_Task4.xlxs.

The Property Manager would like a chart to highlight the number of sales in each suburb.

Use your spreadsheet knowledge to complete the design of the spreadsheet.

Task Requirement

You are required to complete Spreadsheet Plan 4 in the table no. 6. The spreadsheet in Plan 4 is a summary of the number of properties sold in each suburb on a separate tab: Summary.

Task 4 spreadsheet should include the following features:

• On a separate worksheet called Summary, create a summary of the suburbs and the number of property sales in each suburb. The summary should be linked to each suburb worksheet.
• On the Summary worksheet create a bar chart to display the number of property sales in each suburb. Add data labels to show the number of sales in each suburb.

Table 6 - Spreadsheet Plan 4