Posted: August 5th, 2022

BSBITU402 Develop and Use Complex Spreadsheets

FNS50615 Diploma of FINANCIAL PLANNING
BSBITU402 Develop and Use Complex Spreadsheets
Learner Assessment
TABLE OF CONTENTS
This is an interactive table of contents. If you are viewing this document in Acrobat, clicking on a heading will transfer you to that page. If you have this document open in Word, you will need to hold down the Control key while clicking for this to work.
Instructions 4
What is Competency-Based Assessment 4
The Basic Principles of Assessing Nationally Recognised Training 5
The Dimensions of Competency 6
Reasonable Adjustment 7
The Unit of Competency 8
Context for Assessment 9
Assessment Requirements 9
Assessment Methods 10
Resources Required for Assessment 10
Assessment Workbook Cover Sheet 11
Knowledge Assessment 12
Practical Assessment 17
Instructions to Student 17
Case Study Overview 18
Part 1 – Follow safe work practices 19
Part 2 – Develop and use complex spreadsheets 21
Task 1 – Roleplay Activity 23
Task 2 – Sales Summary 27
Task 3 – Quality Audit Report 30
Task 4 – Spreadsheet Template 38
Task 5 – Balance Sheet 42
Task 6 – Trading Statement 44
Task 7 – Financial Ratios and Graphs 45
Part 3 – Conclusion 47
Workbook Checklist 49
Appendix 55

INSTRUCTIONS
The questions in this workbook are divided into two categories: Knowledge Assessment and Practical Assessment.
The questions under Knowledge Assessments are all in a short answer format. The longer questions requiring creative and analytical thought processes are covered in the Practical Assessment: Case Study. You must answer all questions using your own words. However, you may reference your Learner Guide and other relevant resources and learning materials to complete this assessment.
Some questions cover processes you would likely encounter in a workplace. Ideally, you should be able to answer these questions based on the processes that are currently in place in your workplace. However, if you do not currently have access to a workplace, then answer the questions based on processes that should be implemented in a typical workplace setting.
WHAT IS COMPETENCY-BASED ASSESSMENT
The features of a competency-based assessment system are:
It is focused on what learners can do and whether it meets the criteria specified by the industry as competency standards.
Assessment should mirror the environment the learner will encounter in the workplace.
Assessment criteria should be clearly stated to the learner at the beginning of the learning process.
Assessment should be holistic. That is, it aims to assess as many elements and/or units of competency as is feasible at one time.
In competency assessment, a learner receives one of only two (2) outcomes; competent or not yet competent.
The basis of assessment is in applying knowledge for some purpose. In a competency system, knowledge for the sake of knowledge is seen to be ineffectual unless it assists a person to perform a task to the level required in the workplace.
The emphasis in assessment is on assessable outcomes that are clearly stated for the trainer and learner. Assessable outcomes are tied to the relevant industry competency standards where these exist. Where such competencies do not exist, the outcomes are based upon those identified in a training needs analysis.

Definition of Competency
Assessment in this context can be defined as:
The fair, valid, reliable and flexible gathering and recording of evidence to support the judgement on whether competence has been achieved. Skills and knowledge (developed either in a structured learning situation, at work, or in some other context) are assessed against national standards of competence required by industry, rather than compared with the skills and knowledge of other learners.
THE BASIC PRINCIPLES OF ASSESSING NATIONALLY RECOGNISED TRAINING
Developing and conducting assessment, in an Australian vocational education and training context, is founded on a number of basic conventions.
The principles of assessment
Assessment must be valid
Assessment must include the full range of skills and knowledge needed to demonstrate competency.
Assessment must include the combination of knowledge and skills with their practical application.
Assessment, where possible, must include judgements based on evidence drawn from a number of occasions and across a number of contexts.
Assessment must be reliable
Assessment must be reliable and must be regularly reviewed to ensure that assessors are making decisions in a consistent manner.
Assessors must be trained in national competency standards for assessors to ensure reliability.
Assessment must be flexible
Assessment, where possible, must cover both the on and off-the-job components of training within a course.
Assessment must provide for the recognition of knowledge, skills and attitudes regardless of how they have been acquired.
Assessment must be made accessible to learners through a variety of delivery modes so they can proceed through modularised training packages to gain competencies

Assessment must be mutually developed and agreed upon between assessor and the assessed.
Assessment must be able to be challenged. Appropriate mechanisms must be made for reassessment as a result of challenge.
(Source: Standards for RTOs 2015, Clauses 1.8 – 1.12)
The rules of evidence
When collecting evidence, there are certain rules that apply to that evidence. All evidence must be valid, sufficient, authentic and current:
Valid
Evidence gathered should meet the requirements of the unit of competency. This evidence should match, or at least reflect, the type of performance that is to be assessed, whether it covers knowledge, skills or attitudes.
Sufficient
This rule relates to the amount of evidence gathered. It is imperative that enough evidence is gathered to satisfy the requirements that the learner is competent in all aspects of the unit of competency.
Authentic
When evidence is gathered the assessor must be satisfied that evidence is the learner’s own work.
Current
This relates to the recency of the evidence and whether the evidence relates to current abilities.
(Source: Training in Australia by M Tovey, D Lawlor)
THE DIMENSIONS OF COMPETENCY
The national concept of competency includes all aspects of work performance and not only narrow task skills. The four (4) dimensions of competency are:
Task skills
Task management skills
Contingency management skills
Job or role environment skills

REASONABLE ADJUSTMENT
Adapted Reasonable Adjustment in teaching, learning and assessment for learners with a disability – November 2010 – Prepared by – Queensland VET Development Centre
Reasonable adjustment in VET is the term applied to modifying the learning environment or making changes to the training delivered to assist a learner with a disability. A reasonable adjustment can be as simple as changing classrooms to be closer to amenities or installing a particular type of software on a computer for a person with vision impairment.
Why make a reasonable adjustment?
We make reasonable adjustments in VET to make sure that learners with a disability have:
the same learning opportunities as learners without a disability
the same opportunity to perform and complete assessments as those without a disability.
Reasonable adjustment applied to participation in teaching, learning and assessment activities can include:
customising resources and assessment activities within the training package or accredited course
modifying the presentation medium
learner support
use of assistive/adaptive technologies
making information accessible both before enrollment and during the course
monitoring the adjustments to ensure learner needs continue to be met.

Assistive/Adaptive Technologies
Assistive/Adaptive technology means ‘software or hardware that has been specifically designed to assist people with disabilities in carrying out daily activities’ (World Wide Web Consortium – W3C). It includes screen readers, magnifiers, voice recognition software, alternative keyboards, devices for grasping, visual alert systems, digital note takers.
IMPORTANT NOTE
Reasonable adjustment made for collecting candidate assessment evidence must not impact on the standard expected by the workplace, as expressed by the relevant Unit(s) of Competency. E.g., If the assessment were gathering evidence of the candidate’s competency in writing, allowing the candidate to complete the assessment verbally would not be a valid assessment method. The method of assessment used by any reasonable adjustment must still meet the competency requirements.
THE UNIT OF COMPETENCY
The units of competency specify the standards of performance required in the workplace.
This assessment addresses the following unit of competency:
BSBITU402 – Develop and Use Complex Spreadsheets
Prepare to develop spreadsheet
Develop a linked spreadsheet solution
Automate and standardise spreadsheet operation
Use spreadsheets
Represent numerical data in graphic form
For a complete copy of the above unit of competency:
Download them from the TGA website:
http://training.gov.au/BSBITU402

CONTEXT FOR ASSESSMENT
To complete the assessments in this workbook, candidates need to have access to their learning materials and the Internet. The Knowledge Assessment and Case Study may be completed wholly at the candidate’s home or chosen place of study.
ASSESSMENT REQUIREMENTS
The assessment requirements specify the evidence and required conditions for assessment.
Each unit of competency can be unbundled to reveal three key assessment components:
Performance Evidence
describes the subtasks that make up the element of the unit
Knowledge Evidence
describes the knowledge that must be applied to understanding the tasks described in the elements
Assessment Condition
describes the environment and conditions that assessments must be conducted under
The associated assessment method in this kit covers all of these components as detailed in the matrix to follow:
Units of Competency
Assessment Activities BSBITU402
?
Practical Assessment ?

ASSESSMENT METHODS
Practical Assessment
A set of tasks or activities to be completed according to set instructions and guidelines in a simulated work environment to meet the requirements of the relevant units.
RESOURCES REQUIRED FOR ASSESSMENT
Candidate will need access to:
Computer with Internet and email access and a working web browser
Installed software: MS Word, MS Excel, Adobe Acrobat Reader
Cloud storage either Dropbox or Google Drive
A video and audio recording device
A volunteer (may be a friend, relative, or a colleague)
Access to office equipment and resources

ASSESSMENT WORKBOOK COVER SHEET
To the candidate: Print this cover sheet and complete it by filling in all the required information and affixing your signature to the space provided. Your signature must be hand-signed. Scan the completed cover sheet and submit it along with your evidence submissions. Use the filename: BSBITU402 – Cover Sheet.
WORKBOOK: WORKBOOK BSBITU402
TITLE: Develop and Use Complex Spreadsheets
FIRST AND SURNAME:
PHONE:
EMAIL:
Please read the Candidate Declaration below and if you agree to the terms of the declaration sign and date in the space provided.
By submitting this work, I declare that:
I have been advised of the assessment requirements, have been made aware of my rights and responsibilities as an assessment candidate, and choose to be assessed at this time.
I am aware that there is a limit to the number of submissions that I can make for each assessment, and I am submitting all documents required to complete this Assessment Workbook.
I have organised and named the files I am submitting according to the instructions provided, and I am aware that my assessor will not assess work that cannot be clearly identified and may request the work be resubmitted according to the correct process.
This work is my own and contains no material written by another person except where due reference is made. I am aware that a false declaration may lead to the withdrawal of a qualification or statement of attainment.
I am aware that there is a policy of checking the validity of qualifications that I submit as evidence as well as the qualifications/evidence of parties who verify my performance or observable skills. I give my consent to contact these parties for verification purposes.
Name:
Signature:
Date:
PRACTICAL ASSESSMENT
Instructions to Student
The Case Study tasks are hypothetical situations which will not require you to have access to a workplace, although, your past and present workplace experiences may help with the responses you provide. You will be expected to encounter similar situations to these in the future as you work in the business sector, particularly in developing and using complex spreadsheets.
This assessment will help you demonstrate skill requirements in such situations.

Case Study Overview
Awesome Landscapes Pty Ltd provides landscaping solutions to large-scale commercial and residential developments. You can find out more about Awesome Landscapes by accessing their website:
Awesome Landscapes
(username: newusername password: newpassword)
You are the bookkeeper for Awesome Landscapes, and you directly report to the business manager, Shaar Azad.
You have been tasked to follow safe work practices and create spreadsheets that will be used by the business managers of the organisation.
In creating these spreadsheets, you will need to follow Awesome Landscapes’ policies and procedures, and guidelines for developing documents and use Microsoft Excel.
You will be provided with templates and supplementary guide questions throughout this assessment to document evidence of your completion of the relevant unit requirements addressed in this task.
Use the following naming convention to save your documents for submission
BSBITU402-Case Study Part #-Task #
as shown below.
or or
BSBITU402-Case Study Part 1-Task 1
Note: Within this part of the Case Study, you will be required to complete tasks within designated timelines. Ensure that you go over each task instruction before you proceed.

Develop and use complex spreadsheets
For this part of the Case Study, you are required to develop and use complex spreadsheets within the Awesome Landscapes simulation. You will do this by using Microsoft Excel.
To proceed with the tasks, you need to access the installed Microsoft Excel software on your desktop or laptop computer. An Excel Tutorial is available here.
Refer to the following guidelines when completing tasks under this part.
Guidelines:
Use the Awesome Landscapes Style Guide (username: newlearner password: newpassword) and prescribed templates (e.g. Planning Document).
Unless specified otherwise, present important information in alphabetical or chronological order.
In planning the files to be produced, the planning document will require you to specify your storage options for the outcomes. You must choose two (2) storage locations for all the outputs of this practical assessment. Your file location must include both:
A location on your computer for later submission along with this workbook.
An online storage system (such as Dropbox or Google Drive). You must only choose one (1) online storage system.
When filling out planning documents, the title of output field in each planning document must list all titles of the printouts for the particular task. For the number of pages to be printed, an approximate number is acceptable.
Print documents on white A4 size paper. You may use more than one (1) sheet of paper for one output so that all data in the document are printed.
You are required to source and use manuals, user documentation, and online help with spreadsheet design and production.
In Part 3 Task 2, you will be required to list at least one (1) issue or difficulty you encountered while creating the documents. For each issue encountered, you will be asked what manual, user documentation, and or online help you used to resolve it.
You are required to apply work organisation strategies throughout the conduct of all the tasks.
In Part 3 Task 3, you will be required to list at least two (2) strategies you applied while carrying out the tasks.
Submit documents along with this workbook using the prescribed file name for each task.
Ensure that you complete each document within the following designated timelines. Your assessor will take note of the date and time when each document was last modified. Take note of the following deadlines for each output provided below.
Output Deadline
Task 2
Quality Audit Report Must be completed not more than seven (7) working days after you completed Task 1 – Sales Summary
Task 3
Spreadsheet Template Must be completed not more than ten (10) working days after you completed Task 2 – Quality Audit Report
Task 4
Balance Sheet
Task 5
Trading Statement
Task 6
Financial Ratios and Graphs

Task 1 – Sales Summary
Awesome Landscapes is reviewing their national sales data over a three-month period. They are using Microsoft Excel software to organise and summarise their data. You are required to complete the Sales Summary by creating a pivot. You need to download the file by accessing it from the link below:
BSBITU402 Task 2 Awesome Landscapes Sales Summary
Before you proceed with creating the pivot table, however, you need to use a Planning Document to analyse and evaluate the task and guide you through creating your pivot table.
A Planning Document template is provided in the Appendix section of this workbook. You can access it here.
You might want to research on how to create a pivot table using an online or actual manual or user guide. Include this information in the Planning Document section ‘Manual or user guide accessed and used’.
If the manual or user guide is:
Accessed online, include the link to the website or the file.
An actual manual or user guide, include the title of the manual, the author and the year of publication.
Save and submit the completed planning document along with this workbook as shown below:
BSBITU402-Case Study Part 2-Task 2 Planning Document

Planning Document
Name
Position
Required Output(s)
Assessor
Due Date
Date Today
Software to be used
File Storage 0 Computer or other hardware
0Online storage
Location (Dropbox, Google Drive, etc.):
Presentation Option
(Select all that applies) 0 Via email to the assessor.
0 Handing in of the physically printed report to assessor after submitting completed workbook.
0 Submission along with the completed workbook.
Required Awesome Landscapes Resource(s)
(Include the style guide to be used)
Manual or user guide accessed or used
Output details
Title of Output
Number of Files Required
(Excluding planning document and photos)
Number of Pages to be Printed
Will this file use macros? 0 Yes
0 No
Will automation make work more efficient? 0 Yes
What functions can be automated?
0 No
Other comments
Link to uploaded document
After planning, proceed to create the pivot table in the tab ‘Pivot Table’ within the Excel file you previously downloaded by taking note of the following:
The sales totals must be clearly summarised by area, product, and month.
Format the pivot table according to the Awesome Landscapes Style Guide. You can access it here.
You are at liberty as to which categories will be used as rows and columns.
Arrange the row labels either alphabetically or chronologically.
Add this title to the pivot table:
Sales Figures as of June 2016
After that, do the following accordingly:
Save the file locally to your computer as
BSBITU402-Part 2-Task 2-Sales Summary.
Upload the file you saved in (a) to the online storage you chose. Provide a link to the uploaded document and ensure that it is accessible by your assessor. This is for backup purposes.
Preview the file for printing and ensure that it fits and is readable on a white A4 size paper. Adjust printing specifications, if necessary. Print the file. You may use more than one (1) sheet of paper, so all data in your document are printed.
Note: Your assessor will take note of the date and time when this document was last modified. Ensure that you complete Task 3 – Quality Audit Report, not more than seven (7) working days after this date.
Task 3 – Quality Audit Report
Awesome Landscapes is part way through their project to build the Cascade Peak Walkway, a 2330m boardwalk through the Cascade Peak National Park, for the Cascade Peak Shire Council.
As part of their quality control obligations, they are required to conduct a quality audit of the first section of the boardwalk and prepare a Quality Audit Report for the council.
The business manager has received the quality audit data and needs the following additional statistical data analyses to assist in producing the quality audit report:
Average height for each handrail measuring over 1060mm*
Standard deviation for the height of each handrail over the 1100m section
Minimum heights for each handrail at and beyond the 1100m section
Maximum heights for each handrail at and beyond the 1100m section
Number of instances the height of each handrail is outside of the height range specified in the minutes
Average gap between the slats at and over the 1100m section*
Standard deviation for the gap between the slats and over the 1100m section*
Minimum slat gaps throughout the boardwalk
Maximum slat gaps throughout the boardwalk
Number of instances where the gap between the slats falls outside of the gap range specified in the minutes
*Three (3) decimal places must be used.
You must use Excel appropriate functions in creating this report.
You will need to download the following documents to create the spreadsheet:
BSBITU402 Cascade Peak Walkway Audit Measurements
BSBITU402 Cascade Peak Walkway Meeting Minutes
Note: You are required to complete the Quality Audit Report in no more seven (7) working days after completing Task 2 – Sales Summary. Your assessor will take note of the date and time when your documents were last modified.
Before you proceed with completing the Quality Audit Report, you need to use a Planning Document to analyse and evaluate the task and guide you through the process.
A Planning Document template is provided in the Appendix section of this workbook which you can access here.
You might want to research how to use functions in Excel using an online or actual manual or user guide. Include this information in the Planning Document section ‘Manual or user guide accessed and used’.
If the manual or user guide is:
Accessed online, include the link to the website or the file.
An actual manual or user guide, include the title of the manual, the author and the year of publication.
Save and submit the completed planning document along with this workbook as shown below:
BSBITU402-Case Study Part 2-Task 3 Planning Document

Planning Document
Name
Position
Required Output(s)
Assessor
Due Date
Date Today
Software to be used
File Storage 0 Computer or other hardware
0 Online storage
Location (Dropbox, Google Drive, etc.):
Presentation Option
(Select all that applies) 0 Via email to the assessor.
0 Handing in of the physically printed report to assessor after submitting completed workbook.
0 Submission along with the completed workbook.
Required Awesome Landscapes Resource(s)
(Include the style guide to be used)
Manual or user guide accessed or used
Output details
Title of Output

Number of Files Required
(Excluding planning document and photos) 1
Number of Pages to be Printed
Will this file use macros? 0 Yes
0 No
Will automation make work more efficient? 0 Yes
What functions can be automated?
0 No
Other comments

After the preparation of the Planning Document, refer to the following documents to proceed with the task. You need to access and download the documents below:
BSBITU402 Cascade Peak Walkway Audit Measurements
BSBITU402 Cascade Peak Walkway Meeting Minutes
to complete the Quality Audit Report.
Going back to the details in the introduction of Task 3, the Quality Audit Report needs additional requirements as listed on page 37.
To compute requirements a-j, you would have to use the appropriate Excel functions. You need to analyse which function is appropriate to use to compute for the specific requirements. The functions you are to use include:
AVERAGE
AVERAGEIF
STDEV
MIN
MAX
COUNTIF, and
COUNTIFS
Do the following:
On a new tab on the same worksheet of the file BSBITU402 Cascade Peak Walkway Audit Measurements, construct the table for the additional requirements.
Provide the figures for the second column by using the different Excel functions as appropriate to the requirement. Remember to take note of the data in the Audit Measurements of the Walkway and the Meeting Minutes.
Should the computed figures have decimal places, use up to three (3) decimal places.
The table should be similar to the one below.
Quality Audit Report (mm)
a) Average height for each handrail measuring over 1060mm
b) Standard deviation for the height of each handrail over the 1100m section
c) Minimum height for the handrails at and beyond the 1100m section
d) Maximum height for the handrails at and beyond the 1100m section
e) Number of instances the height of each handrail is outside of the height range specified in the minutes
f) Average gap between the slats at and over the 1100m section
g) Standard deviation for the gap between the slats and over the 1100m section
h) Minimum slat gaps throughout the boardwalk
i) Maximum slat gaps throughout the boardwalk
j) Number of instances where the gap between the slats falls outside of the gap range specified in the minutes
Make sure that the table is properly labelled as:
Quality Audit Report
Format this table according to the Awesome Landscapes Style Guide. You can access it here.
After that, do the following accordingly:
Save the file locally to your computer as
BSBITU402-Part 2-Task 3-Quality Audit Report.
Upload the file you saved in (a) to the online storage you chose. Provide a link to the uploaded document and ensure that it is accessible by your assessor. This is for backup purposes.
Preview the file for printing and ensure that it fits and is readable on a white A4 size paper. Adjust printing specifications, if necessary. Print the file. You may use more than one (1) sheet of paper, so all data in your document are printed.
Note: Your assessor will take note of the date and time when this document was last modified. Ensure that you complete Task 3 – Quality Audit Report, not more than seven (7) working days after this date.
The file must be uploaded not more than seven (7) working days after you have completed Task 1 – Sales Summary.
The next tasks, Task 4, 5, 6, and 7 must be completed not more than ten (10) working days after completing the Quality Audit Report.

Check Price Discount

Study Notes & Homework Samples: IT Professional Issues and Project Methods MMI223999 »OPM61 TASK 1: International Logistics