STRATHFIELD COLLEGE Strathfield College Pty Ltd CRICOS Code:02736K National Code: 91223 ABN: 85168435667 Suite 510, Level 5, 451 Pitt Street, Sydney NSW 2000, AUSTRALIA www.sc.nsw.edu.au email: [email protected] ASSESSMENT TASK 2 Assessment Details Qualification code BSB40515 Qualification Name Certificate IV in Business Administration Unit Code BSBITU402 Unit Name Develop and use complex spreadsheets Assessment Type Demonstration Term 2/2016 Due Date Filled out by student Trainer / Assessor's Details Full Name: This area is to be filled out by the student Date of Submission This is be filled out by student Student Details Student Name Filled out by student Student ID Filled out by student Instructions to the Candidates Pre Requirement for this task Student must review • Lesson materials provided in class • Examples used in lessons. Instructions to Students: • Please complete assessment task within the time frame given • You must attempt all the requirements that are addressed within this assessment task. • Copying or any kind of cheating is absolutely not permitted, if someone is found cheating or copying, will be debarred from the assessment and a disciplinary proceeding would begin. • This assessment covers the following Performance Criteria PC's Covered: 1.2, 2.1–2.4, 3.3, 4.1–4.5 Student Declaration: I declare that the work submitted is my own, and has not been copied or plagiarised from any person or source. Signature: ____________________________ Date: ____/_____/_____ FEEDBACK to Student: Student Declaration: I declare that I have been assessed in this unit, and I have been advised of my result. I am also aware of my appeal rights. Signature: ______________________ Date: ____/_____/_____ Assessor Declaration: I declare that I have conducted a fair, valid, reliable and flexible assessment with this student, and I have provided appropriate feedback Signature: ____________________________ Date: ____/_____/_____ Assessor's Assessment Result Outcome (Circle Outcome) Competent C Not Yet Competent NYC   The assessment task is due on the date specified by your assessor. Any variations to this arrangement must be approved in writing by your assessor. Submit this document with any required evidence attached. See specifications below for details. Performance objective The candidate must demonstrate the knowledge, skills and abilities required to develop linked spreadsheets, automate and standardise spreadsheet operations and use spreadsheets. Assessment description You are required to use Microsoft Excel to create spreadsheets for Covers Books that will manage payroll information and calculate fortnightly payroll amounts. Covers Books Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. Operating for just over two years, Covers Books specialises in fiction books and has built up a steady business with many repeat customers from the local area. DJ (the owner) has set up a table containing the details of the four staff members currently employed by Covers Books. You are required to automate the classification of the employee depending on their age, and then calculate the correct pay rate, based on the classification. You will then need to create a template for the fortnightly pay and enter data for the current fortnight. Procedure 1. Open the spreadsheet provided by your assessor titled Covers Staff. Make the following changes to the spreadsheet to enable the correct salary classification to be determined. a. Add a formula in E1 to calculate the current date. b. Add a formula to cells E3:E7 that calculates the age of the staff member as at the current date. c. Add a formula to cells F3:F7 that displays the salary classification. d. Add a formula to cells G3:G7 that displays the correct pay rate that applies to each staff member. e. A new staff member started last week and needs to be added to the staff list as follows: i. Name: Grace Jacobs ii. Date of Birth: 11 October 1992 f. Save the file using the following format: [Your initials] Covers Staff ddmmyy. For example: DP Covers Staff 221210. 2. Open the spreadsheet title 'Covers Fortnightly Payroll'. Make the following changes to the spreadsheet that will be used calculate each fortnight's payroll. a. Format cell F3 to be short date b. Merge, bold and right justify cells A18:C18 c. Place individual lines around each cell and a single bold border from A10:G18 d. Place a single bold border from A10:G10, A11:G17 and A18:G18 e. Add formulas that look up the staff code in the spreadsheet you created in the previous step of this procedure and return the first name of the staff member and the appropriate hourly rate, ensuring that an hourly rate of $0 is returned when no Staff ID is input. f. Add a formula to cells E11:E17 that will calculate the gross pay g. Add a formula to cells F11:F17 that will look up and calculate the tax amount payable h. Add a formula to cells G11:G17 that will calculate the net pay amount i. Add a formula that calculates the total hours, gross pay, tax and net pay j. Save the file as a template using the following format: [Your initials] Covers Fortnightly Payroll Template. For example: DP Fortnightly Payroll Template. 3. Use the template you created in step 2 of this procedure to open a new payroll sheet and enter the information in Appendix 1. a. It is policy of Covers Books that both a hard and soft copy of the fortnightly payroll is kept. b. Print a copy of this fortnight's payroll ensuring that its fits to a single sided A4 page. c. Save the file using the following format: [Your initials] Covers Payroll PE ddmmyy. For example: DP Covers Payroll PE 221210. Specifications You must submit: 1. A soft copy of the following files: a. the staff details file named [Your initials] Covers Staff ddmmyy b. the payroll template named [Your initials] Covers Fortnightly Payroll Template c. the current fortnights payroll file named [Your initials] Covers Payroll PE ddmmyy. Your assessor will be looking for whether you have: ● analysed the task and determined specifications for spreadsheets ● used spreadsheet design software functions and formulae to meet identified requirements ● linked spreadsheets in accordance with software procedures ● formatted cells and used data attributes assigned with relative and/or absolute cell references, in accordance with the task specifications ● tested formulae to confirm output meets task requirements ● developed, edited and used templates to ensure consistency of design and layout for forms, in accordance with organisational requirements ● entered, checked and amended data in accordance with organisational and task requirements ● imported and exported data between compatible spreadsheets and adjusting host documents, in accordance with software and system procedures ● used manuals, user documentation and online help to overcome problems with spreadsheet design and production ● previewed, adjusted and printed spreadsheet in accordance with organisational and task requirements ● named and stored spreadsheets in accordance with organisational requirements and exited the application without data loss or damage ● developed complex spreadsheets. Your assessor will also be looking for: ● the necessary literacy skills to interpret and evaluate the purposes and uses of various features of spreadsheets and used a variety of strategies for planning and reviewing your own work ● the necessary proofreading and editing skills to check for accuracy and consistency of information by consulting additional resources ● the necessary numeracy skills to collate and present data, graphs and related references ● a demonstrated knowledge of the advanced functions of spreadsheet software applications ● a demonstrated knowledge of the impact of formatting and design on the presentation and readability of data ● a demonstrated knowledge of organisational policies and procedures. Appendix 1 – Pay details Fortnight ended Enter the current date, as used in step 1 of the procedure. Hours worked for the fortnight Staff ID Name Hour Worked N00011 Sally Jenkins 64 N00012 David Nugyen 22 N00013 Natalie Di Natale 26 N00014 Cameron Johnson 20 N00015 Justine Lee 48 N00016 Grace Jacobs 8