Bsbitu 402 v1.0 SAG - .................................. PDF

Preview

Summary

.....................................

Description

Student Assessment Guide BSBITU402 Develop and Use Complex Spreadsheets

Version 1.0

March 2016

Level 2, 25 George Street, Parramatta, NSW 2150 Phone: +61 2 9633 1222 Fax: +61 2 9633 1888 Email: [email protected] Web: www.australisinstitute.nsw.edu.au ABN 17 120 701 911 | RTO ID 91630 | CRICOS ID 03173K

Inte Intellllllec ec ectu tu tual al Pr Pro ope perty rty SSta ta tate te teme me men nt VET Fair owns all copyright to its products as contained in the Copyright Act 1968. This means you must not in whole or in part; reproduce this or any of our products, contextualise or customise or adapt, resell to any party or individual and or make available to other stakeholders. Purchasers of our products however, are entitled to use these products for their own operations but the intellectual property remains with VET Fair; therefore, they will be required to identify VET Fair as the source on any modified, adapted or customised tools. Purchasers must include the following statement in each copy of a modified, adapted or customised resource that is distributed electronically and/or in a physical format to any target audience: “The assessment activities and information in this guide are derived from the BSBITU402 Develop and Use Complex Spreadsheets assessment resources provided by VET Fair. VET Fair owns all copyright to this information and the intellectual property of this resource remains with VET Fair.” Breaches of this copyright will result in VET Fair claiming for loss of sales. The names of people and business entities used in these simulations are fictitious and any resemblance to actual people or companies is not intended.

Ta Table ble o off C Conte onte ontents nts Assessment Information

1

Pre-assessment Checklist

3

Assessment Event 1 – Short Answer Questions

4

Instructions

4

Questions Question 1 Question 2 Question 3

4 4 5 6

Assessment Event 2: Case Study

7

Background to the Simulation

7

Assessment Conditions

7

Task 1: Prepare to Develop Spreadsheet Assessment Conditions 1.1 Clarify spreadsheet specifications 1.2 Organise personal work environment in accordance with ergonomic requirements and apply work organisation practices 1.3 Apply energy and resource conservation techniques to plan work activities

8 8 9 9 9

Task 2: Prepare the Sales Report Template to Populate with Data Assessment Conditions 2.1 Develop the initial template 2.2 Name, save and store the template in accordance with organisational requirements

10 10 10 10

Task 3: Import Data to The Sales Template Using a Look Up Function Assessment Conditions 3.1 Look up data using the ‘Look Up Current Data Macro Button’ 3.2 Name, save and store the template in accordance with organisational requirements

11 11 11 11

Task 4: Import Data to The Sales Template Using Spreadsheet Functions Assessment Conditions 4.1 Import data using the ‘Import Past Data Macro Button’ 4.2 Ensure error handling in macros using external assistance 4.3 Name, save and store the template in accordance with organisational requirements

12 12 12 12 13

Task 5: Calculate the Changes in Sales Performance Assessment Conditions 5.1 Analyse performance change using the ‘Analyse Change Macro Button’ 5.2 Highlight over performing and underperforming products 5.3 Name, save and store the template in accordance with organisational requirements

14 14 14 14 15

Task 6: Represent Numerical Data in Graphic Form Assessment Conditions 6.1 Create charts to visualise the sales performance data 6.2 Name, save and store the template in accordance with organisational requirements

16 16 16 16

Task 7: Export Data from The Sales Template Assessment Conditions 7.1 Export the report using the ‘Export the Report Macro Button’ 7.2 Name, save and store the report and the template in accordance with organisational requirements

17 17 17 17

Task 8: Prepare the Sales Report Template for Future Use Assessment Conditions 8.1 Clear the template using the ‘Clear Template Macro Button’

18 18 18

8.2 Name, save and store the template in accordance with organisational requirements Assessor Feedback Sheets (Assessor Use Only)

18 19

Observation Check Sheet

19

Task Outcome Sheet

24

Assessment Outcome Sheet

26

As Asse se sessm ssm ssment ent In Info fo forrma mati ti tion on Welcome to your Student Assessment Guide for BSBITU402 Develop and Use Complex Spreadsheets. This Student Assessment Guide will give you the opportunity to show your skills in preparing, designing and developing spreadsheets to carry out business tasks. This will be achieved through producing an automated spreadsheet template based on a set of requirements which will generate the required output performing a series of complex work tasks. This assessment has the following two events: Assessment Event 1 – Short Answer Questions There are three questions that will provide us with evidence on your general knowledge of ergonomics, work organisation strategies, energy and resource conservation techniques, and spreadsheet functions. This assessment is completed in your own time and by a submission date provided by your assessor. You may use support material in the development of your answers, but you must indicate the source. In addition, you must not cut and paste answers from your source but instead put it in your own words, unless it is a direct quote. Assessment Event 2 – Case Study: Design and Develop an Automated Spreadsheet Template You will complete a number of tasks in designing and developing an automated spreadsheet template. These tasks will be based on your role of Document Management Specialist in a simulation for Milk Daily Fresh, an organisation that retails fresh milk products. To do this case study, you will require to complete a number of tasks in which you will need to refer to the following resources: Project Brief (PDF file)

Outlines the business needs and provides a paper design prototype to give you an idea of how the Sales Report Template must look. You will finalise the layout for the Sales Report Template based on this paper sketch.

Current Sales Data (Excel file)

Spreadsheet that holds raw data for the current year’s sales performance per quarter for the entire range of products. You will use this data to retrieve the current sales performance for the selected range of products.

Historical Sales Data (Text file)

Text file that holds raw data for the previous year’s sales performance per quarter for the selected range of products. You will perform a number of spreadsheet operations based on the Current Sales Data and Historical Sales Data.

Organisational Logo (PNG file)

Milk Daily Fresh company logo. You will use this media file when inserting header into the Sales Template.

Milk Daily Fresh Style Guide (PDF file)

Organisational Style Guide that is designed to set standards and ensure consistency in document production. You must conform to the standards as outlined in this document when designing and producing the Sales Report Template.

Energy Management Policy and Procedures (PDF file)

Policy and procedure document that outlines Milk Daily Fresh’s organisational energy and resource saving practices for document production in order to maintain the proper management of their energy sources.

Workplace Ergonomics Policy and Procedures (PDF file)

Milk Daily Fresh strives to maintain a safe and healthy workplace for all of its employees. This workplace ergonomics policy and procedures document aims to promote and protect employee health through establishing ergonomically sound practices.

Remember, you do not write your answers in this Student Assessment Guide, but use the Student Assessment Workbook which is a separate document. This document is simply a guide to ensure that you can perform at your best.

BSBITU402 1.0 | March 2016

Page 1

Note that to be deemed competent for this unit, you must achieve a satisfactory outcome for all of the assessment tasks. This includes meeting the following conditions:  

you must complete all of the tasks outlined in this Student Assessment Guide document to the standard indicated in the performance requirements. These are listed under each of the tasks. you must perform the above two points within the given timeframes by your Assessor.

Please be aware that your Trainer/Assessor is here to provide you with the necessary support throughout the assessment process so that you can achieve the desired outcomes. If you have questions, then contact them for guidance.

BSBITU402 1.0 | March 2016

Page 2

Pre Pre-as -as -assses essm sm sme ent Check Checklist list Your assessor will go through the assessment for this unit BSBITU402 Develop and Use Complex Spreadsheets. It is important that you understand this assessment before taking on the tasks. To confirm that you have been given this overview, we ask you to complete the following Pre-Assessment Checklist. You are required to carefully read each checklist item provided below and tick either ‘Y’ to confirm your understanding or ‘N’ if you disagree. In case you disagree with an item, please provide your reason under the ‘Comments’ section. When you have done this, we ask you to sign this Pre-Assessment Checklist. This acknowledges that your Trainer/Assessor has discussed all of the information with you prior to undertaking this assessment. Checklist

Y

Comments

N

I, the student, understand the purpose of the assessment.

Y

N

I understand when and where the assessment will occur, who will assess me, how and in what format the assessment will be submitted.

Y

N

I understand the methods of assessment.

Y

N

I understand what resources are required to successfully complete this assessment.

Y

N

I understand the performance level required for each assessment event.

Y

N

I understand that it must be my own work. I have been explained and understood the serious consequences in case this work is found plagiarised.

Y

N

I understand the process if I am deemed not yet competent.

Y

N

I understand the feedback process and the appeals process.

Y

N

The assessor has discussed with me if I have any special needs and if so what arrangements have been put in place.

Student Full Name

BSBITU402 1.0 | March 2016

Student ID

Student Signature

Date

Page 3

As Asse se sessm ssm ssment ent Ev Event ent 1 – SSho ho horrt A Ans ns nsw wer Q Que ue uesti sti stio ons Ins Instru tru tructio ctio ctions ns The information contained in this section lists the questions that you will need to develop a written response to. These questions are theoretical and provide evidence of your understanding of ergonomics, work organisation strategies, energy and resource conservation techniques, and spreadsheet functions. Each question includes a word count to give you an indication of the depth and the performance requirements to achieve a satisfactory result for each answer. Note you must answer these questions in your own words. Remember, you must get a satisfactory result with each question to be deemed satisfactory for the whole of Assessment Event 1.

Qu Quest est estion ion ionss Qu Ques es estio tio tion n1 Using the table format below, explain each of the following advanced functions of Excel and provide an example of where each can be used in a real-life setting. Functions Conditional Formatting

Explanation

Example of use

Data Analysis Cell Formatting Functions and Formulas Pivot Table Charts Sorting Filter Formula Auditing Macros Freeze Panes Split View Protect and Share Workbook Get External Data Data validation What-If Analysis Performance requirements (PR): to achieve a satisfactory response for this question you will need to: PR 1. explain all the functions listed in the table above a. each explanation must be accurate and relevant b. word count for each explanation is approximately 25 words PR 2. provide an example for each function listed in the table above a. each example must be a real-life use of the function in a workplace context b. examples can relate to any industry, however must be in a workplace context c. these examples must be specific and relevant to the function PR 3. word count for each example is approximately 25 words.

BSBITU402 1.0 | March 2016

Page 4

Qu Ques es estio tio tion n2 Describe how formatting and designing an Excel worksheet impact the presentation and readability of data. Performance requirements (PR): to achieve a satisfactory response for this question you will need to: PR 1. describe how any five of the following items used in formatting impact the presentation and readability of data: a. cell alignment b. fonts c. borders d. number formatting e. conditional formatting f. styles g. formatting as table h. named range i. cell references (absolute and relative) j. merge & center PR 2. your description must be related to: a. presenting data to a client b. how each of these items increases the readability of data PR 3. these descriptions must be specific and relevant to each item PR 4. word count is approximately 25 words per description for the selected items (five from PR 1) PR 5. describe how any five of the following principles used in designing a worksheet impact the presentation and readability of data: a. page orientation b. page margins c. header and footer d. freeze panes e. split view f. gridlines g. hide/unhide columns/rows h. background image i. print area j. group/ungroup PR 6. your description must be related to: a. presenting data to a client b. how each of these items increases the readability of data PR 7. these descriptions must be specific and relevant to each item PR 8. word count is approximately 25 words per description for the selected items (five from PR 5).

BSBITU402 1.0 | March 2016

Page 5

Qu Ques es estio tio tion n3 Explain organisational requirements for ergonomics, work periods and breaks, and conservation techniques. Performance requirements (PR): to achieve a satisfactory response for this question you will need to: PR 1. outline three ergonomic requirements when working with spreadsheets for the items listed below: a. computer screen b. chair c. footrest d. keyboard and mouse position e. lighting f. ventilation g. posture h. desk (work surface) PR 2. word count for each requirement in PR 1 is approximately 15 words PR 3. outline two work organisation strategies when working with spreadsheets for the items listed below: a. exercise breaks and work periods b. stretching and exercising the body, which must include: - the neck - the shoulders - the arms - the back - the wrists and hands c. 20-20-20 rule PR 4. word count for each strategy in PR 3 is approximately 15 words PR 5. explain the 3R concept in resource conservation when working with spreadsheets for the processes below: a. reduce b. reuse c. recycle PR 6. word count for each process in PR 5 is approximately 15 words PR 7. your responses for all of the above must be accurate PR 8. your responses for all of the above must be specific and relevant to working with spreadsheets.

BSBITU402 1.0 | March 2016

Page 6

As Asse se sessm ssm ssment ent Ev Event ent 2: Cas Case e St Stud ud udyy In this assessment you will undertake a number of tasks associated with designing and developing a spreadsheet template which automates a series of work tasks. The focus is on gathering design and task requirements to accurately produce the spreadsheet template adhering to the organisational style guide in order to maintain consistency of style, design and layout. This involves undertaking a simulation to demonstrate your:   

interpersonal skills to effectively listen and question to clarity spreadsheet requirements technical and numeracy skills to design and develop an automated spreadsheet based on specific design and task specifications ability to follow organisational work practices including ergonomics, work organisation and energy management.

Bac Backgr kgr kgrou ou ound nd to the Sim Simula ula ulatio tio tion n The simulation centres on the activities of an organisation called Milk Daily Fresh. The company is a retailer of milk drinks including full cream, unhomogenised, skim, fat free, goat, long life, almond, and rice based products. It was reported in the previous sales meeting that the Sales Team has been having issues with analysing the sales performance based on current and historical raw data stored in spreadsheet and text file formats. The Sales Team needs to have regular statistical reports to keep track of sales performance for each individual product. However, the Sales Team does not have the necessary skills to create a master template that can automate a statistical sales performance analysis and visualise the outcome accordingly. For this reason, the Sales Manager needs a Document Management Specialist who has the essential skillset to design and develop a spreadsheet template to accommodate the needs of the Sales Team. Moreover, the Sales Manager has recently been elected as the Work Health and Safety representative (HSR) to represent workers of Milk Daily Fresh on health and safety matters. The main duty of the Sales Manager as a HSR is to monitor the health and safety practices of the organisation in order to maintain safe work practices and direct unsafe work to stop. As a result, the HSR wants to see if you are up-to-date with current ergonomic practices. Furthermore, Milk Daily Fresh prides itself as a good corporate citizen that accepts the importance of being collectively responsible for its local community and environment as an integral part of their core business. Therefore, the company has established energy management policy and procedures to promote sustainability in the workplace. The Sales Manager needs to ensure whether or not you have a sound understanding of the organisation’s energy management procedures.

Asse Assessme ssme ssment nt C Co ond nditio itio itions ns In this case study, you will take the role of the Document Management Specialist whose responsibility includes not only designing and developing organisational templates, but also maintaining safe, healthy and sustainable work practices. Note that the role play in Task 1 will be a 45-minutes session and divided into the following three parts: Part 1: this is focused on your interpersonal skills to effectively listen and question the Sales Manager to clarify spreadsheet requirements. Approximate duration is 15 minutes. Part 2: this is centred on your ability to organise your work environment in accordance with Workplace Ergonomics Policy and Procedures. Approximate duration is 20 minutes. Part 3: this is where you demonstrate how you apply organisational Energy Management Policy and Procedures. Approximate duration is 10 minutes.

BSBITU402 1.0 | March 2016

Page 7

Tas Deve Taskk 1 1:: Pre Prepare pare to Deve evelop lop Sp Spre re read ad adshe she sheet et In this task you will demonstrate your interpersonal skills in listening and questioning to clarify spreadsheet design and task requirements. Also, y...