diff_months: 15

BSBTEC302 - Design and produce spreadsheets :Project Assessment

Download Solution Now
Added on: 2023-04-08 06:41:42
Order Code: 488177
Question Task Id: 0

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

Item

Organisational requirements and features

Ergonomics (desk and chair)

 

Work periods and breaks

 

Sustainability - regarding printing

 

Sustainability – regarding power supply

 

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. 
  1. Pie Chart to show the employees’ surnames and sales total for the month, with percentages displayed below the data on each sheet.
  2. Ensure the worksheet tab name reflects the month and type of data.

Table 3 - Spreadsheet Plan 1

Spreadsheet Plan 1

 

Spreadsheet purpose

 

Audience:

 

Presentation requirements:

 

Personnel to clarify requirements with:

 

Software application required

 

Timeframe

Spreadsheets required by:

 

Number of Spreadsheets and tab name:

 

Numerical data format:

 

Cell alignment for text and data:

 

File Name:

 

Is a chart required?

If yes – complete the information

 

Chart details

Only complete the table if a chart is required

Type of chart

 

Title of chart

 

Vertical Axis data

 

Horizontal Axis data

 

Is a legend required?

 

Data to be included in the legend?

 

Task 2

Plan a spreadsheet to record stock on hand (inventory) of mobile phones. On the 30th 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

Spreadsheet Plan 2

 

Spreadsheet purpose

 

Audience:

 

Presentation requirements:

 

Personnel to clarify requirements with:

 

Software application required

 

Timeframe

Spreadsheets required by:

 

Number of Spreadsheets and tab name:

 

Numerical data format:

 

Cell alignment for text and data:

 

File name:

 

Is a chart required?

If yes – complete the information

 

Chart details

Only complete the table if a chart is required

Type of chart

 

Title of chart

 

Vertical Axis data

 

Horizontal Axis data

 

Is a legend required?

 

Data to be included in the legend?

 

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

Spreadsheet Plan 3

 

Spreadsheet purpose

 

Audience:

 

Presentation requirements:

 

Personnel to clarify requirements with:

 

Software application required

 

Timeframe

Spreadsheets required by:

 

Number of Spreadsheets and tab name:

 

Numerical data format:

 

Cell alignment for text and data:

 

File name:

 

Is a chart required?

If yes – complete the information

 

Chart details

Only complete the table if a chart is required

Type of chart

 

Title of chart

 

Vertical Axis data

 

Horizontal Axis data

 

Is a legend required?

 

Data to be included in the legend?

 

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

Spreadsheet Plan 4

 

Spreadsheet purpose

 

Audience:

 

Presentation requirements:

 

Personnel to clarify requirements with:

 

Software application required

 

Timeframe

Spreadsheets required by:

 

Number of Spreadsheets and tab name:

 

Numerical data format:

 

Cell alignment for text and data:

 

File name:

 

Is a chart required?

If yes – complete the information

 

Chart details

Only complete the table if a chart is required

Type of chart

 

Title of chart

 

Vertical Axis data

 

Horizontal Axis data

 

Is a legend required?

 

Data to be included in the legend?

 

 

  • Uploaded By : Katthy Wills
  • Posted on : April 08th, 2023
  • Downloads : 0
  • Views : 351

Download Solution Now

Can't find what you're looking for?

Whatsapp Tap to ChatGet instant assistance

Choose a Plan

Premium

80 USD
  • All in Gold, plus:
  • 30-minute live one-to-one session with an expert
    • Understanding Marking Rubric
    • Understanding task requirements
    • Structuring & Formatting
    • Referencing & Citing
Most
Popular

Gold

30 50 USD
  • Get the Full Used Solution
    (Solution is already submitted and 100% plagiarised.
    Can only be used for reference purposes)
Save 33%

Silver

20 USD
  • Journals
  • Peer-Reviewed Articles
  • Books
  • Various other Data Sources – ProQuest, Informit, Scopus, Academic Search Complete, EBSCO, Exerpta Medica Database, and more