I certify that the work submitted for this assessment pack is my own

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 1
Unit Assessment Pack (UAP) – Cover Sheet
Student and Trainer/Assessor Details

Student ID

Student name

Contact number

Email address

Trainer/Assessor name

Course and Unit Details

Course code	BSB51915
Course name	Diploma of Leadership and Management
Unit code	BSBITU402
Unit name	Develop and use complex spreadsheets

Assessment Submission Method

☐ By hand to trainer/assessor	☐ By email to trainer/assessor	☐ Online submission via Learning Management System (LMS)
☐ By Australia Post to RTO	☐ Any other method _________________________________________________ (Please mention here)

Student Declaration

• I certify that the work submitted for this assessment pack is my own. I have clearly
referenced any sources used in my submission. I understand that a false declaration is a
form of malpractice;
• I have kept a copy of this assessment pack and all relevant notes, attachments, and
reference material that I used in the production of the assessment pack;
• For the purposes of assessment, I give the trainer/assessor of this assessment the
permission to:
o Reproduce this assessment and provide a copy to another member of staff; and
o Take steps to authenticate the assessment, including communicating a copy of
this assessment to a checking service (which may retain a copy of the assessment
on its database for future plagiarism checking).
Student signature: ________________________________
Date: ____/_____/______________

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 2
Assessment Plan
To demonstrate competence in this unit, you must be assessed as satisfactory in each of the
following assessment tasks.

Evidence recorded	Evidence Type/ Method of assessment	Sufficient evidence recorded/Outcome
Unit Assessment Task 1	Unit Knowledge Test (UKT)	S / NS (First Attempt) S / NS (Second Attempt)
Unit Assessment Task 2	Unit Skills Test (UST) – Prepare financial report using spreadsheets	S / NS (First Attempt) S / NS (Second Attempt)
Unit Assessment Task 3	Unit Project (UP) – Create spreadsheet	S / NS (First Attempt) S / NS (Second Attempt)
Final result	C/NYC	Date assessed
Trainer/Assessor Signature

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 3
Unit Assessment Result Sheet (UARS)
Assessment Task 1 – Unit Knowledge Test (UKT)
Student and Trainer/Assessor Details

Unit code

BSBITU402

Unit name

Develop and use complex spreadsheet

Outcome of Unit
Assessment Task (UAT)

First attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Second attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Feedback to Student

First attempt:

Second attempt:

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 4

Student Declaration	• I declare that the answers I have provided are my own work. Where I have accessed information from other sources, I have provided references and or links to my sources. • I have kept a copy of all relevant notes and reference material that I used as part of my submission. • I have provided references for all sources where the information is not my own. I understand the consequences of falsifying documentation and plagiarism. I understand how the assessment is structured. I accept that all work I submit must be verifiable as my own. • I understand that if I disagree with the assessment outcome, I can appeal the assessment process, and either re-submit additional evidence undertake gap training and or have my submission re-assessed. • All appeal options have been explained to me.
Student Signature
Date
Trainer/Assessor Name
Trainer/Assessor Declaration	I hold:  Vocational competencies at least to the level being delivered  Current relevant industry skills  Current knowledge and skills in VET, and undertake  Ongoing professional development in VET I declare that I have conducted an assessment of this candidate’s submission. The assessment tasks were deemed current, sufficient, valid and reliable. I declare that I have conducted a fair, valid, reliable, and flexible assessment. I have provided feedback to the above named candidate.
Trainer/Assessor Signature
Date
Office Use Only	Outcome of Assessment has been entered onto the Student Management System on _________________ (insert date) by (insert Name) __________________________________

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 5
Unit Assessment Result Sheet (UARS)
Assessment Task 2 – Unit skills test (UST)
Student and Trainer/Assessor Details

Unit code

BSBITU402

Unit name

Develop and use complex spreadsheets

Outcome of Unit
Assessment Task (UAT)

First attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Second attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Feedback to Student

First attempt:

Second attempt:

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 6

Student Declaration	• I declare that the answers I have provided are my own work. Where I have accessed information from other sources, I have provided references and or links to my sources. • I have kept a copy of all relevant notes and reference material that I used as part of my submission. • I have provided references for all sources where the information is not my own. I understand the consequences of falsifying documentation and plagiarism. I understand how the assessment is structured. I accept that all work I submit must be verifiable as my own. • I understand that if I disagree with the assessment outcome, I can appeal the assessment process, and either re-submit additional evidence undertake gap training and or have my submission re-assessed. • All appeal options have been explained to me.
Student Signature
Date
Trainer/Assessor Name
Trainer/Assessor Declaration	I hold:  Vocational competencies at least to the level being delivered  Current relevant industry skills  Current knowledge and skills in VET, and undertake  Ongoing professional development in VET I declare that I have conducted an assessment of this candidate’s submission. The assessment tasks were deemed current, sufficient, valid and reliable. I declare that I have conducted a fair, valid, reliable, and flexible assessment. I have provided feedback to the above named candidate.
Trainer/Assessor Signature
Date
Office Use Only	Outcome of Assessment has been entered onto the Student Management System on _________________ (insert date) by (insert Name) __________________________________

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 7
Unit Assessment Result Sheet (UARS)
Assessment Task 3 – Unit Project (UP) – Create spreadsheet
Student and Trainer/Assessor Details

Unit code

BSBITU402

Unit name

Develop and use complex spreadsheets

Outcome of Unit
Assessment Task (UAT)

First attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Second attempt:

Outcome (please make sure to tick the correct checkbox):
Satisfactory (S) ☐ or Not Satisfactory (NS) ☐
Date: _______(day)/ _______(month)/ ____________(year)

Feedback to Student

First attempt:

Second attempt:

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 8

Student Declaration	• I declare that the answers I have provided are my own work. Where I have accessed information from other sources, I have provided references and or links to my sources. • I have kept a copy of all relevant notes and reference material that I used as part of my submission. • I have provided references for all sources where the information is not my own. I understand the consequences of falsifying documentation and plagiarism. I understand how the assessment is structured. I accept that all work I submit must be verifiable as my own. • I understand that if I disagree with the assessment outcome, I can appeal the assessment process, and either re-submit additional evidence undertake gap training and or have my submission re-assessed. • All appeal options have been explained to me.
Student Signature
Date
Trainer/Assessor Name
Trainer/Assessor Declaration	I hold:  Vocational competencies at least to the level being delivered  Current relevant industry skills  Current knowledge and skills in VET, and undertake  Ongoing professional development in VET I declare that I have conducted an assessment of this candidate’s submission. The assessment tasks were deemed current, sufficient, valid and reliable. I declare that I have conducted a fair, valid, reliable, and flexible assessment. I have provided feedback to the above named candidate.
Trainer/Assessor Signature
Date

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 9

Office Use Only

Outcome of Assessment has been entered onto the Student
Management System on _________________ (insert date)
by (insert Name) __________________________________

Assessment task 2 -performance criteria/ performance checklist.

To be assessed as satisfactory (S) in this assessment task the participant needs to demonstrate competency in the following critical aspects of evidence	S	N/S	Trainer/Assessor to complete (Comment and feedback to students)
a. Organised the personal work environment in accordance with ergonomic requirements	❑
b. Identified organisational and task requirements in relation to data entry, storage, output, reporting and presentation requirements	❑	❑
c. Applied work organisation strategies and energy and resource conservation techniques to plan work activities.	❑	❑
d. Knowledge of the key provisions of relevant legislation from all forms of government, standards and codes that may affect aspects of business operations, such as: • Ethical principles • Privacy laws • Occupational health and safety.	❑	❑

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 10

e. Knowledge of organisational
policies and procedures.

❑

Assessment task 3-Performance checklist criteria:

To be assessed as satisfactory (S) in this assessment task the participant needs to demonstrate competency in the following critical aspects of evidence	S	N/S	Trainer/Assessor to complete (Comment and feedback to students)
a. Identified organisational, task, reporting and presentation requirements of data entry, storage and output and planned work activities by applying operational strategies, energy and resource conservation techniques.	❑
b. Successfully met identified requirements by utilising design software functions, formulas and linking spreadsheets in accordance with software procedures.	❑
c. Adequately formatted cells and used data attributes assigned with relative cell references in line with task specifications and confirming that the output meets task requirements using test formulae.	❑	❑
d. Identified the areas where automation would increase efficiency by evaluating task.	❑	❑
e. Efficiently created, used and edited macros and templates to fulfil the requirements of task and automated spreadsheet operations and to ensure consistency of designs and layouts for forms and reports in line with organisational requirements.	❑	❑

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 11

f. Effectively used spreadsheets, entered, checked and amended data as per organisational task requirement and imported, exported and adjusted data and host documents in line with software and procedures.	❑	❑
g. Aptly overcame problems with the design and production of spreadsheet using manual and documentation and preview, adjusted and printed spreadsheet as per organisational and task requirements.	❑	❑
h. Adequately stored and named spreadsheets as per organisational requirements and exit application without any loss of data or damage.	❑	❑
i. Successfully met the specified requirements and manipulated spreadsheet by determining the style of graph, creating graph with labels and titles for numerical data contained in spreadsheet	❑	❑

Assessment Conditions

Unit purpose/application

This unit describes the skills and knowledge required to use spreadsheet software to complete
business tasks and produce complex documents.
It applies to individuals employed in a range of work environments who require skills in creation of
complex spreadsheets to store and retrieve data. They may work as individuals providing
administrative support within an enterprise, or may be independently responsible for designing and
working with spreadsheets relevant to their own work roles.
No licensing, legislative or certification requirements apply to this unit at the time of publication.

What the student can expect to learn by studying this unit of competency

• Prepare to develop spreadsheet
• Develop a linked spreadsheet solution
• Automate and standardise spreadsheet operation
• Use spreadsheets
• Represent numerical data in graphic form

Training and assessment resources required for this unit of competency

The student will have access to the following:
• Learner guide
• PowerPoint presentation
• Unit Assessment Pack (UAP)
• Access to other learning materials such as textbooks

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 12
The resources required for these assessment tasks also include:
• Organisational policies and procedures
• Relevant workplace documentation and resources
• Industry software packages and user instructions.
• codes of practice and standards issued by government regulators or industry groups

Submission instructions

Your trainer/assessor will confirm assessment submission details for each assessment task.

Academic integrity, plagiarism and collusion

Academic Integrity
Academic Integrity is about the honest presentation of your academic work. It means acknowledging
the work of others while developing your own insights, knowledge and ideas.
As a student, you are required to:
• Undertake studies and research responsibly and with honesty and integrity.
• Ensure that academic work is in no way falsified.
• Seek permission to use the work of others, where required.
• Acknowledge the work of others appropriately.
• Take reasonable steps to ensure other students cannot copy or misuse your work.
Plagiarism
Plagiarism means to take and use another person’s ideas and or manner of expressing them and to
pass them off as your own by failing to give appropriate acknowledgement. This includes material
sourced from the internet, RTO staff, other students, and from published and unpublished work.
Plagiarism occurs when you fail to acknowledge that the ideas or work of others are being used,
which includes:
• Paraphrasing and presenting work or ideas without a reference
• Copying work either in whole or in part
• Presenting designs, codes or images as your own work
• Using phrases and passages verbatim without quotation marks or referencing the author or
web page
• Reproducing lecture notes without proper acknowledgement.
Collusion
Collusion means unauthorised collaboration on assessable work (written, oral or practical) with other
people. This occurs when a student presents group work as their own or as the work of someone
else.
Collusion may be with another RTO student or with individuals or students external to the RTO. This
applies to work assessed by any educational and training body in Australia or overseas.
Collusion occurs when you work without the authorisation of the teaching staff to:
• Work with one or more people to prepare and produce work
• Allow others to copy your work or share your answer to an assessment task
• Allow someone else to write or edit your work (without rto approval)

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 13
• Write or edit work for another student
• Offer to complete work or seek payment for completing academic work for other students.
Both collusion and plagiarism can occur in group work. For examples of plagiarism, collusion and
academic misconduct in group work please refer to the RTO’s policy on Academic integrity,
plagiarism and collusion.
Plagiarism and collusion constitute cheating. Disciplinary action will be taken against students who
engage in plagiarism and collusion as outlined in RTO’s policy.
Proven involvement in plagiarism or collusion may be recorded on students’ academic file and could
lead to disciplinary action.

Other Important unit specific Information

N/A

Unit outcome

• This unit is not graded and the student must complete and submit all requirements for the
assessment task for this cluster or unit of competency to be deemed competent.
• Students will receive a ‘satisfactorily completed’ (S) or ‘not yet satisfactorily completed (NS)
result for each individual unit assessment task (UAT).
• Final unit result will be recorded as competency achieved/competent (C) or competency not
yet achieved/not yet competent (NYC).

Prerequisite/s

Nil

Co-requisite/s

Nil

Foundation Skills

The Foundation Skills describe those required skills (learning, oral communication, reading, writing,
numeracy, digital technology and employment skills) that are essential to performance. Foundation
skills essential to performance are explicit in the performance criteria of this unit of competency.

Relevant Legislation

• Australian Human Rights Commission Act 1986
• Age Discrimination Act 2004
• Disability Discrimination Act 1992
• Racial Discrimination Act 1975
• Sex Discrimination Act 1984
• Code of ethics and codes of conduct
• Ethical Principles in the Workplace
• Codes of practice

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 14
• The Privacy Act 1988 (Privacy Act) and Australian Privacy Principles (APPs)
• Occupational Health and Safety Act 2004
• Work Health and Safety Act 2011
• Workplace environmental and resource efficiency strategies including organisational
policies, plans and procedures
• Workgroup member responsibilities and duties, and relationship to individual
responsibilities and duties.

Principles of assessment and rules of evidence

All assessment tasks will ensure that the principles of assessment and rules of evidence are adhered
to.
The principles of assessment are that assessment must be valid, fair, flexible, reliable and consistent.
The rules of evidence state that evidence must be sufficient, valid, current and authentic.

AQF Level

AQF levels and the AQF levels criteria are an indication of the relative complexity and/or depth of
achievement and the autonomy required to demonstrate that achievement.
All assessment tasks must ensure compliance with the requirements of AQF level and the AQF level
criteria. For more information, please visit http://www.aqf.edu.au/

Further Information

For further information about this unit go to https://ift.tt/2Ou6mpG
Additional Information
• This information will be managed by the provisions of the Privacy Act and the Freedom of
Information Act.)
• Students are required to satisfactorily complete and submit all assessment tasks that
contribute to the assessment for a unit.
• Students will be provided with one more attempt to complete this Unit assessment pack
(UAP) if trainer/assessor deems them not satisfactorily completed (NS) in any Unit
assessment task (UAT).
• Unit Pre-Assessment Checklist (UPAC) will be reviewed by the trainer/assessor to ensure
the student is ready for the assessment.
• Feedback regarding this Unit Assessment Pack (UAP) can be emailed to the compliance
and quality assurance department/administration department in your RTO for
continuously improving our assessment and student resources.
Feedback to student
Feedback on students’ assessment performance is a vital element in their learning. Its purpose is to
justify to students how their competency was assessed, as well as to identify and reward specific
qualities in their work, to recommend aspects needing improvement, and to guide students on what
steps to take.
Feedback defines for students what their trainer/assessor thinks is important for a topic or a subject.
At its best, feedback should:

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 15
• Be provided for each Unit Assessment Task (UAT).
• Guide students to adapt and adjust their learning strategies.
• Guide trainers/assessors to adapt and adjust teaching to accommodate students’ learning
needs.
• Be a pivotal feature of learning and assessment design, not an add-on ritual.
• Focus on course and unit learning outcomes.
• Guide students to become independent and self-reflective learners and their own critics.
• Acknowledge the developmental nature of learning.
If students have not received proper feedback, they must speak to compliance and quality assurance
department/administration department in the RTO/person responsible for looking after the quality
and compliance services of the RTO.
For more information, please refer to RTO Student Handbook.
Unit Pre-Assessment Checklist (UPAC)
It is important to read unit pre-assessment checklist before the start of
assessment 1, 2 and 3. This checklist is applicable to all three assessment tasks
of this unit.
Purpose of the checklist
The pre-assessment checklist helps students determine if they are ready for assessment. The
trainer/assessor must review the checklist with the student before the student attempts the
assessment task. If any items of the checklist are incomplete or not clear to the student, the
trainer/assessor must provide relevant information to the student to ensure they understand the
requirements of the assessment task. The student must ensure they are ready for the assessment
task before undertaking it.
Section 1: Information for Students
• Please make sure you have completed the necessary prior learning before attempting this
assessment.
• Please make sure your trainer/assessor clearly explained the assessment process and tasks
to be completed.
• Please make sure you understand what evidence is required to be collected and how.
• Please make sure you know your rights and the Complaints and Appeal process.
• Please make sure you discuss any special needs or reasonable adjustments to be considered
during the assessment (refer to the Reasonable Adjustments Strategy Matrix and negotiate
these with your trainer/assessor).
• Please make sure that you have access to a computer and the internet (if you prefer to type
the answers).
• Please ensure that you have all the required resources needed to complete this Unit
Assessment Task (UAT).
• Due date of this assessment task is according to your timetable.
• In exceptional (compelling and compassionate) circumstances, an extension to submit an
assessment can be granted by the trainer/assessor.
• Evidence of the compelling and compassionate circumstances must be provided together
with your request for an extension to submit your assessment work.
• Request for an extension to submit your assessment work must be made before the due date
of this assessment task.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 16
Section 2: Reasonable adjustments
• Students with carer responsibilities, cultural or religious obligations, English as an additional
language, disability etc. can request for reasonable adjustments.
• Please note, academic standards of the unit/course will not be lowered to accommodate the
needs of any student, but there is a requirement to be flexible about the way in which it is
delivered or assessed.
• The Disability Standards for Education requires institutions to take reasonable steps to
enable the student with a disability to participate in education on the same basis as a student
without a disability.
• Trainer/Assessor must complete the section below “Reasonable Adjustment Strategies
Matrix” to ensure the explanation and correct strategy have been recorded and
implemented.
• Trainer/Assessor must notify the administration/compliance and quality assurance
department for any reasonable adjustments made.
• All evidence and supplementary documentation must be submitted with the assessment
pack to the administration/compliance and quality assurance department.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 17

Reasonable Adjustment Strategies Matrix (Trainer/Assessor to complete)
Category	Possible Issue	Reasonable Adjustment Strategy (select as applicable)
 LLN	 Speaking  Reading  Writing  Confidence	 Verbal assessment  Presentations  Demonstration of a skill  Use of diagrams  Use of supporting documents such as wordlists
 Non English Speaking Background	 Speaking  Reading  Writing  Cultural background  Confidence	 Discuss with the student and supervisor (if applicable) whether language, literacy and numeracy are likely to impact on the assessment process  Use methods that do not require a higher level of language or literacy than is required to perform the job role  Use short sentences that do not contain large amounts of information  Clarify information by rephrasing, confirm understanding  Read any printed information to the student  Use graphics, pictures and colour coding instead of, or to support, text  Offer to write down, or have someone else write, oral responses given by the student  Ensure that the time available to complete the assessment, while meeting enterprise requirements, takes account of the student’s needs
 Indigenous	 Knowledge and understanding  Flexibility  Services  Inappropriate training and assessment	 Culturally appropriate training  Explore understanding of concepts and practical application through oral assessment  Flexible delivery  Using group rather than individual assessments  Assessment through completion of practical tasks in the field after demonstration of skills and knowledge.
 Age	 Educational background  Limited study skills	 Make sure font size is not too small  Trainer/Assessor should refer to the student’s experience  Ensure that the time available to complete the assessment takes account of the student’s needs  Provision of information or course materials in accessible format.  Changes in teaching practices, e.g. wearing an FM microphone to enable a student to hear lectures  Supply of specialised equipment or services, e.g. a note taker for a student who cannot write  Changes in lecture schedules and arrangements, e.g. relocating classes to an accessible venue

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 18

 Changes to course design, e.g. substituting an assessment task  Modifications to physical environment, e.g. installing lever taps, building ramps, installing a lift
 Educational background	 Reading  Writing  Numeracy  Limited study skills and/or learning strategies	 Discuss with the Student previous learning experience  Ensure learning and assessment methods meet the student’s individual need
 Disability	 Speaking  Reading  Writing  Numeracy  Limited study skills and/or learning strategies	 Identify the issues  Create a climate of support  Ensure access to support that the student has agreed to  Appropriately structure the assessment  Provide information or course materials in accessible format, e.g. a textbook in braille  Changes in teaching practices, e.g. wearing an FM microphone to enable a student to hear lectures  Supply of specialised equipment or services, e.g. a note taker for a student who cannot write  Changes in lecture schedules and arrangements, e.g. relocating classes to an accessible venue  Changes to course design, e.g. substituting an assessment task  Modifications to physical environment, e.g. installing lever taps, building ramps, installing a lift

Explanation of reasonable adjustments strategy used (If required)

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 19
Unit Assessment Task (UAT)
Assessment Task 1 – Unit Knowledge Test (UKT)
Assessment type:
• Written Questions
Assessment task description:
• This is the first (1) unit assessment task you have to successfully complete to be deemed
competent in this unit of competency.
• The Unit Knowledge Test is comprised of fourteen (14) written questions.
• You must respond to all questions and submit them to your Trainer/Assessor.
• You must answer all questions to the required level, e.g. provide the number of points, to be
deemed satisfactory in this task.
• You will receive your feedback within two weeks – you will be notified by your
Trainer/Assessor when results are available.
Applicable conditions:
• All knowledge tests are untimed and are conducted as open book tests (this means you are
able to refer to your textbook during the test).
• You must read and respond to all questions.
• You may handwrite/use computers to answer the questions.
• You must complete the task independently.
• No marks or grades are allocated for this assessment task. The outcome of the task will be
Satisfactory or Not Satisfactory.
• As you complete this assessment task you are predominately demonstrating your written
skills and knowledge to your trainer/assessor.
• The trainer/assessor may ask you relevant questions on this assessment task to ensure that
this is your own work.
Resubmissions and reattempts:
• Where a student’s answers are deemed not satisfactory after the first attempt, a
resubmission attempt will be allowed.
• You must speak to your Trainer/Assessor if you have any difficulty in completing this task
and require reasonable adjustments (e.g. can be given as an oral assessment)
• For more information, please refer to your RTO Student Handbook.
Location:
• This assessment task may be completed in a learning management system (i.e. Moodle) or
independent learning environment.
• Your trainer/assessor will provide you further information regarding the location for
completing this assessment task.
Instructions for answering written questions:
• Complete a written assessment consisting of a series of questions.
• You will be required to correctly answer all the questions.
• Do not start answering questions without understanding what is required from you. Read
the questions carefully and critically analyse them for a few seconds, this will help you to
identify what is really needed.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 20
• Your answers must demonstrate an understanding and application of relevant concepts,
critical thinking, and good writing skills.
• Be concise to the point and write answers according to the given word-limit to each question
and do not provide irrelevant information. Be careful, quantity is not quality.
• Be careful to use non-discriminatory language. The language used should not devalue,
demean, or exclude individuals or groups on the basis of attributes such as gender, disability,
culture, race, religion, sexual preference or age. Gender inclusive language should be used.
• When you quote, paraphrase, summarise or copy information from the sources you are using
to write your answers/research your work, you must always acknowledge the source.
How your trainer/assessor will assess your work?
• This assessment task requires the student to answer all the questions.
• Answers must demonstrate the student’s understanding and knowledge of the unit.
• If all assessment tasks are deemed Satisfactory (S), then the unit outcome is Competent (C).
• If at least one of the assessment task is deemed Not Satisfactory (NS), then the unit outcome
is Not Yet Competent (NYC).
• Once all assessment tasks allocated to this Unit of Competency have been undertaken,
trainer/assessor will complete an Assessment plan to record the unit outcome. The outcome
will be either Competent (C) or Not Yet Competent (NYC).
• The “Assessment Plan” is available with the Unit Assessment Pack (UAP) – Cover Sheet.
Purpose of the assessment task:
This assessment task is designed to evaluate your following skills and abilities:
• Knowledge to organise personal work environment in line with ergonomics requirements
and determine specifications for spreadsheets based on analyzed task.
• Knowledge to identify organisational, task, reporting and presentation requirements of data
entry, storage and output.
• Knowledge to plan work activities by applying organisational strategies, energy and resource
conservation techniques.
• Knowledge to meet identified requirements by utilising design software functions, formulas
and linking spreadsheets in accordance with software procedures.
• Knowledge to format cells and use data attributes assigned with relative cell references in
line with task specifications and confirming that the output meets task requirements using
test formulae.
• Knowledge to identify the areas where automation would increase efficiency by evaluating
task.
• Knowledge to create, use and edit macros and templates to fulfil the requirements of task
and automate spreadsheet operations and to ensure consistency of designs and layouts for
forms and reports in line with organisational requirements.
• Knowledge to use spreadsheets, enter, check and amend data as per organisational task
requirement and import, export and adjust data and host documents in line with software
and procedures.
• Knowledge to overcome problems with the design and production of spreadsheet using
manual and documentation and preview, adjust and print spreadsheet as per organisational
and task requirements.
• Knowledge to store and name spreadsheets as per organisational requirements and exit
application without any loss of data or damage.
• Knowledge to meet the specified requirements and manipulate spreadsheet by determining
the style of graph, creating graph with labels and titles for numerical data contained in
spreadsheet.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 21
• Knowledge to present numerical data in graphical form, save, view and print graph with
designated timelines.
• Reading skills to collect, review, interpret/understand and analyse/review text-based
business information from a range/number of sources.
• Written and oral/speech communication skills to organise and deliver information to
effectively communicate complex spreadsheets to a range of stakeholders/interested
people.
• Numeracy/numbers- mathematical skills to interpret/understand mathematical data when
reviewing and analysing scenario/setting-situation business information.
• Knowledge to work independently/freely as well as collaboratively/together to make
decisions about spreadsheets.
• Knowledge to interact/cooperate with others using appropriate conventions/systems when
communicating to, and consulting/discussing with stakeholders/interested parties.
• Knowledge to sequence/in order and schedule/plan activities and manage spreadsheets.
• Knowledge to analyse relevant/appropriate information to identify scope/range of work,
goals and objectives and to evaluate/review options/other choices.
• Knowledge to use familiar/known digital technology to access/get to information, document
findings/results and communicate them to stakeholders.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 22
Assessment Task 1 – Unit Knowledge Test (UKT)
Instructions:
• This is an individual assessment.
• The purpose of this assessment task is to assess the students’ knowledge essential to develop
and use complex spreadsheets in a range of contexts and industry settings.
• To make full and satisfactory responses you should consult a range of learning resources,
other information such as handouts and textbooks, learners’ resources and slides.
• All questions must be answered in order to gain competency for this assessment.
• You may attach a separate sheet if required.
• You must include the following particulars in the footer section of each page of the attached
sheets:
o Student ID or Student Name
o Unit ID or Unit Code
o Course ID or Course Code
o Trainer and assessor name
o Page numbers
• You must staple the loose sheets together along with the cover page.
• You must attach the loose sheets chronologically as per the page numbers.
• Correction fluid and tape are not permitted. Please do any corrections by striking through
the incorrect words with one or two lines and rewriting the correct words.
Resources required to complete the assessment task:
• Computer
• Internet
• MS Word
• Printer or e-printer
• Adobe acrobat/reader
• Learning management system
• Access to office equipment and software
• Access to samples of data for inclusion in spreadsheets.
Questions:

Question 1: In order to attain a comfortable, productive and safe work environment, what
organizational requirements would you recommend each for the following?
Explain the requirements for each of the following in 100-200 words

a. Posture- Activity- Exercise (Ergonomics)
b. Lightning, air and noise conservation techniques
c. Work style, periods and breaks

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 23

Question 2: “Sustainability in the office helps to reduce the expenses, making feel good and look
good”. Write down at least seven (7) tips which make this statement clearer and understandable.

Question 3: Explain ways to conserve energy for the following areas in 50-100 words each.
a) Transportation
b) Thermostat management
c) Computer usage
• Cash receipts journal
• Cash Payment journal

Question 4: a. Why consistency is so important in your design? Answer in 15-30 words.
b. List six (6) points that tells a designer about the consistency of the design or layout.

Question 5: Why is it essential in any office that documentation work flow should be smooth? Also
share some knowledge of essential points for documentation. Answer in 70-100 words.

Question 6: What is a macro? List any five (5) function that you can perform with the help of a
macro. Write answer in 100-150 words.

Question 7: Define the term template and explain why templates are important? Give some
examples of templates. Answer in 100-150 words.

Question 8: The following is the report of a company’s turn over per year.
Illustrate this through pictorial view (use line graph) in MS-Excel and attach screenshot.

Question 9: Why people prefer charts alongside the data table? Why is it important to choose the
correct type of chart? Write your answer in 150-200 words.

Question 10: How can you create a macro? Write your answer in 200-250 words.

Question 11: As per the organisational policy, company A wants their financial data to be
processed in MS Excel.
You are required to prepare the following reports in MS-Excel and attach the screenshot:

Year Turn over
2000 $10,000.00
2001 $15,000.00
2002 $12,000.00
2003 $9,000.00
2004 $18,000.00
2005 $20,000.00
2006 $50,000.00
2007 $45,000.00
2008 $35,000.00
2009 $57,000.00
2010 $60,000.00
2011 $58,000.00
2012 $92,000.00

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 24

Following are the given transactions:
• Customer A sends a check for a prior sale, paying $10,357.55 and taking a $102.55 sales
discount;
• You make a cash sale for $452;
• Customer B sends a check for a prior sale, paying $6,120;
• You are paid $3,000 in principal and $155 in interest on a note.
• Paid $455 for office equipment;
• Paid $1,200 for purchases;
• Paid $2,000 to Supplier A against accounts payable, taking a purchases discount of $225;
• Paid $1,125 for purchases;
• Paid $765.50 to Supplier B against accounts payable.

Question12: Below given is an Excel sheet with scattered data in it. Examine the data meticulously
and explain how to use the Ms Box function with this data set. Write answer in 150-200 words.

Question13: Below are the sales record of the music company. Create two different spreadsheets.
Explain the procedure in 200-250 words that you used and provide a screenshot of MS Excel to do
the following:
a) Change the names of both sheets to east, west
b) Consolidate the data of both sheets on 3rd sheet
c) Link both the worksheets
d) Save the workbook on a removable drive
e) Print the consolidated sheet

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 25

Question 14: A spreadsheet is a mixture of text, graphics and mathematics. What impact does the
formatting and design of a spreadsheet have on demonstrability and readability of data? Write
your answer in 100-150 words.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 26
Unit Assessment Task (UAT)
Assessment Task 2 – Unit Skills Test (UST)
Assessment type
• Unit Skills Test(UST) – Organise personal work environment
Assessment task description
• This is the second (2) unit assessment task you have to successfully complete to be deemed
competent in this unit of competency.
• This assessment task is comprised of a Unit Skills Test (UST).
• You are required to organise personal work environment.
• You must attempt all criteria to the required level, e.g. Assessment criteria mentioned in the
performance checklist to be deemed satisfactory in this task.
• You must organise personal work environment by following all given instructions, for the
trainer/assessor to assess competency in this assessment task.
• You will receive your feedback within two weeks – you will be notified by your
Trainer/Assessor when results are available.
Applicable conditions
• This skill test is untimed and conducted as an open book test (this means you are able to
refer to your textbook or other learner materials during the test).
• You are required to organise personal work environment.
• You will be assessed independently on this assessment task.
• No marks or grades are allocated for this assessment task. The outcome of the task will be
Satisfactory or Not Satisfactory.
• As you complete this assessment task you are predominately demonstrating your practical
skills, techniques and knowledge to your trainer/assessor.
• Trainer/Assessor may ask you relevant questions during this assessment task.
Resubmissions and reattempts
• Where a student’s answers are deemed not satisfactory after the first attempt, a
resubmission attempt will be allowed.
• You must speak to your Trainer/Assessor if you have any difficulty in completing this task
and require reasonable adjustments (e.g. can be given as an oral assessment).
• For more information, please refer to your RTO Student Handbook.
Location
• Part A of this assessment task is to be completed in simulated environment prepared by
trainer/assessor.
• This assessment task may be completed in a classroom, workplace, or independent learning
environment.
• Trainer/Assessor will provide student further information regarding the location of
completing this assessment task.
General Instructions for attempting the skills test:
• You will be required to correctly attempt all activities of this assessment task.
• You will organise personal work environment.
• You will be expanding the knowledge and skills acquired during the previous assessment
task.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 27
• Instructions to organise personal work environment are provided within the assessment
task.
How your trainer/assessor will assess your work?
• This assessment task requires the student to demonstrate his/her skills to organise personal
work environment.
• Answers must demonstrate the student’s understanding and skills of the unit.
• Assessment objectives/ measurable learning outcome(s) are attached as performance
checklist/ performance criteria with this assessment task to ensure that you have
successfully completed and submitted the assessment task.
• If all assessment tasks are deemed Satisfactory (S), then the unit outcome is Competent (C).
• If at least one of the assessment task is deemed Not Satisfactory (NS), then the unit outcome
is Not Yet Competent (NYC).
• Once all assessment tasks allocated to this Unit of Competency have been undertaken,
trainer/assessor will complete an Assessment plan to record the unit outcome. The outcome
will be either Competent (C) or Not Yet Competent (NYC).
• The “Assessment Plan” is available with the Unit Assessment Pack (UAP) – Cover Sheet.
Purpose of the assessment task:
This assessment task is designed to evaluate student’s following skills and abilities:
• Skill to organise personal work environment in accordance with ergonomic requirements.
• Skill to apply work organisation strategies and energy and resource conservation techniques
to plan work activities.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 28
Assessment Task 2 – Unit skills test (UST)
Assessment Instructions
• This assessment task requires you to organise personal work environment.
• This assessment task is divided into two parts:
o Part A: practical demonstration
o Part B: Answer the question based on legislative knowledge, organisational and
task requirements
• You must demonstrate your knowledge and skills to organise work environment.
• You must refer to Appendix 1 and 2 to complete this assessment task.
• Your assessor will assess your work according to the given performance criteria/
performance checklist.
• You may attach a separate sheet if required.
• You must include the following particulars in the footer section of each page of the attached
sheets:
o Student ID or Student Name
o Unit ID or Unit Code
o Course ID or Course Code
o Trainer and assessor name
o Page numbers
• You must staple the loose sheets together along with the cover page.
• You must attach the loose sheets chronologically as per the page numbers.
• Correction fluid and tape are not permitted. Please do any corrections by striking through
the incorrect words with one or two lines and rewriting the correct words.
Resources required to complete the assessment task:
• Computer
• Internet
• MS Word
• Printer or e-printer
• Adobe acrobat/reader
Task 1:
In this assessment task, student is required to:
Part A: Setup or adjust their workstation in accordance with ergonomic considerations, practical
layout of necessary documents and resource conservation requirements.
Part B: Answer questions on legislative knowledge, organisational and task requirements.
You must refer to Appendix 1 and 2 to complete this assessment task.
Part A:
In this part of the assessment task, your trainer/assessor will prepare a simulated workstation
environment. You will organise and adjust the workstation based on the ergonomic requirements.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 29
Your trainer/assessor will observe you making the adjustments. One student will follow your
instructions to help you adjust the workplace.
Procedure:
1. Setup or adjust the workstation, desk or work area in accordance with ergonomic
considerations, the practical layout of necessary documents and occupational health and
safety requirements.
a. Use the checklist in Appendix 2 to setup or adjust your work area.
Additional information on the correct set up of your workstation can be found on the Ergonomics
in Australia website at
<https://ift.tt/1soJYj5>.
b. Obtain the assistance of a friend/ fellow student to take up to three photos of you
seated at your work area. The photos are required to show that the height of the chair
has been correctly adjusted for you and that the monitor and keyboard (and any
document holder) are correctly positioned for you when you are seated at your work
area.
c. Insert the photos into a document or email and add text beneath each photo
explaining the key points of ergonomic compliance and the date the photo was taken.
2. Setup or adjust the energy conservation settings on your computer using the following
settings. Take a screen shot of the settings screen showing that you have made the
requested adjustments.
a. Set the monitor to turn off after 15 minutes of inactivity.
b. Set the computer to go into standby mode after 1 hour of inactivity.
Hints:
For Windows users – Hold down the ‘Alt’ key and then press the ‘Print Screen’ key to take a screen
shot of the active screen. This can then be pasted into a document.
For Mac users – Hold down the ‘Command’ and ‘Shift’ keys then press ‘4’. Select the ‘open window’
option from the screen shot choices. This can then be pasted into a document.
Part B
Use the template provided below to record your answers. Word limit for each question is 50-70
words.
Provide answers to the questions specified below.
3. Describe two exercises and one other strategy (not exercise) that you can undertake to
reduce the risk of injury when using a computer. Explain how often you would recommend
the exercises are done
4. Using the information provided in Appendix 1, describe two additional ways that DJ could
conserve energy or resources at Covers Books.
5. Using the information provided in Appendix 1, identify the task requirements by
undertaking the following tasks.
a. Identify at least three spreadsheets Covers Books are likely to need.
b. Briefly describe the purpose or use of each spreadsheet.
6. Where and how would you suggest that DJ store the business records?

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 30
Ensure that you include references to both hard (paper) and soft (electronic) storage suggestions and
data back-up options in your answer
7. Explain how each of the following may affect business operations at Covers Books:
a. ethical principles
b. privacy laws
c. occupational health and Safety laws and regulations
Specifications
You must submit:
● a completed copy of the ergonomic and environmental checklist (Appendix 2)
● photos (a maximum of three) of the student at their computer work area clearly showing
that the chair height, monitor and keyboard are correctly positioned and adjusted
● a screen shot showing the energy settings on the computer have been adjusted or set as
per the instructions in step 2 of the procedure
● Answers (in writing unless otherwise directed by your assessor) to the questions asked in
steps 3, 4, 5, 6 and 7 of the procedure above.
Your assessor will be looking for whether you have:
● Organised your personal work environment in accordance with ergonomic requirements.
● Identified organisational and task requirements in relation to data entry, storage, output,
and reporting and presentation requirements.
● Applied work organisation strategies and energy and resource conservation techniques to
plan work activities.
Your assessor will also be looking for:
● knowledge of the key provisions of relevant legislation from all forms of government,
standards and codes that may affect aspects of business operations, such as:
o ethical principles
o privacy laws
o Occupational health and safety.
● Knowledge of organisational policies and procedures.
Note: To complete assessment task refer to appendix attached at the end of this assessment pack.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 31
Unit Assessment Task (UAT)
Assessment Task 3 – Unit Project (UP) – Develop and use
spreadsheets
Assessment type:
• Unit Project (UP) – Develop and use spreadsheets
Assessment task description:
• This is the third (3) assessment task you have to successfully complete to be deemed
competent in this unit of competency.
• This assessment task requires you to complete a Unit Project (UP).
• You must develop, and use spreadsheets based on given data in order to successfully
complete this project.
• You must refer to Appendix 1 to obtain data to complete this assessment task.
• You will receive your feedback within two weeks – you will be notified by your
trainer/assessor when results are available.
• You must attempt all activities of the project for your trainer/assessor to assess your
competency in this assessment task.
Applicable conditions:
• This project is untimed and are conducted as open book tests (this means you are able to
refer to your textbook).
• You must develop, and use spreadsheets based on given data to successfully complete the
project.
• You must use computers to answer the criteria of the project.
• You must complete the task independently.
• No marks or grades are allocated for this assessment task. The outcome of the task will be
Satisfactory or Not Satisfactory.
• As you complete this assessment task you are predominately demonstrating your practical
skills, techniques and knowledge to your trainer/assessor.
The trainer/assessor may ask you relevant questions on this assessment task.
Resubmissions and reattempts:
• Where a student’s answers are deemed not satisfactory after the first attempt, a
resubmission attempt will be allowed.
• You must speak to your Trainer/Assessor if you have any difficulty in completing this task
and require reasonable adjustments (e.g. can be given as an oral assessment).
• For more information, please refer to your RTO Student Handbook.
Location:
• This assessment task may be completed in a classroom, workplace, or independent learning
environment.
• Trainer/Assessor will provide student further information regarding the location of
completing this assessment task.
General Instructions for attempting the project:
• You will develop, and use spreadsheets based on given data, in this assessment task.
• Instructions to create the spreadsheet is provided within the assessment task.
• You will be required to correctly attempt all activities of this assessment task.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 32
How your trainer/assessor will assess your work?
• This assessment task requires the student to successfully develop, and use spreadsheets
based on given data.
• Answers must demonstrate the student’s understanding and skills of the unit.
• You will be assessed according to the provided performance checklist/ performance criteria.
• Assessment objectives/ measurable learning outcome(s) are attached as performance
checklist/ performance criteria with this assessment task to ensure that you have
successfully completed and submitted the assessment task.
• If all assessment tasks are deemed Satisfactory (S), then the unit outcome is Competent (C).
• If at least one of the assessment task is deemed Not Satisfactory (NS), then the unit outcome
is Not Yet Competent (NYC).
• Once all assessment tasks allocated to this Unit of Competency have been undertaken,
trainer/assessor will complete an Assessment plan to record the unit outcome. The outcome
will be either Competent (C) or Not Yet Competent (NYC).
• The “Assessment Plan” is available with the Unit Assessment Pack (UAP) – Cover Sheet.
Purpose of the assessment task:
This assessment task is designed to evaluate your following skills and abilities:
• Skill to determine specifications for spreadsheets based on analysed task.
• Skill to identify organisational, task, reporting and presentation requirements of data entry,
storage and output.
• Skill to plan work activities by applying organisational strategies, energy and resource
conservation techniques.
• Skill to meet identified requirements by utilising design software functions, formulas and
linking spreadsheets in accordance with software procedures.
• Skill to format cells and use data attributes assigned with relative cell references in line with
task specifications and confirming that the output meets task requirements using test
formulae.
• Skill to identify the areas where automation would increase efficiency by evaluating task.
• Skill to create, use and edit macros and templates to fulfil the requirements of task and
automate spreadsheet operations and to ensure consistency of designs and layouts for forms
and reports in line with organisational requirements.
• Skill to use spreadsheets, enter, check and amend data as per organisational task
requirement and import, export and adjust data and host documents in line with software
and procedures.
• Skill to overcome problems with the design and production of spreadsheet using manual and
documentation and preview, adjust and print spreadsheet as per organisational and task
requirements.
• Skill to store and name spreadsheets as per organisational requirements and exit application
without any loss of data or damage.
• Skill to meet the specified requirements and manipulate spreadsheet by determining the
style of graph, creating graph with labels and titles for numerical data contained in
spreadsheet.
• Skill to present numerical data in graphical form, save, view and print graph with designated
timelines.
• Numeracy/numbers- mathematical skills to interpret/understand mathematical data when
reviewing and analysing scenario/setting-situation business information.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 33
Assessment Task 3 – Unit Project (UP) – Develop and use
spreadsheets
Instructions to complete this assessment task:
• This assessment task requires you to create a spreadsheet using MS Excel.
• You must create income statements in the spreadsheets.
• You must refer to Appendix 1 to obtain information on the finances of the organisation.
• You must follow the organisational and task requirements and incorporate the same in the
spreadsheets.
• You must follow user instructions of MS Excel.
• You trainer/assessor will hand out the user instructions to the students.
• Please develop and use spreadsheets using MS Excel.
• You may attach a separate sheet if required.
• You must include the following particulars in the footer section of each page of the attached
sheets:
o Student ID or Student Name
o Unit ID or Unit Code
o Course ID or Course Code
o Trainer and assessor name
o Page numbers
• You must staple the loose sheets together along with the cover page.
• You must attach the loose sheets chronologically as per the page numbers.
• Correction fluid and tape are not permitted. Please do any corrections by striking through
the incorrect words with one or two lines and rewriting the correct words.
• The premise of the project must be closely related to the previous assessment task.
• This submission must be well presented and follow the guidelines and instructions provided.
• Please follow the format as indicated in the template section below.
• One of the most important steps that you can take: proofread your project.
• Spreadsheet created in the project must be based on provided data, using 11-point font,
double-spaced, and must include a cover page, table of contents, introduction, body,
summary or conclusion, and works cited.
• Appropriate citations are required.
• All RTO policies are in effect, including the plagiarism policy.
Resources required to complete the assessment task:
• Computer
• Internet
• MS Word
• MS Excel
• Printer or e-printer
• Adobe acrobat/reader
Project:
This assessment task is in continuation with assessment task 2. In this assessment task, you are
required to prepare income statements for three consecutive years.
The finances of the company “Cover Books” are attached as Appendix 1 to this unit.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 34
Organisational and task requirements:
• Develop a linked spreadsheet solution considering income statement of three years
• Automate and standardise spreadsheet operation on these income statements
• Use spreadsheets
o Increase expenses by 10% for each year.
o Recalculate the net income/losses.
• Represent numerical data in graphic form
Spreadsheet design
Include the follow characteristics:
• design of the workbook needs to follow the general income statement templates
• the areas of the worksheet (to be determined by trainer/assessor)
Spreadsheet needs to be:
• Accurate
• Flexible – Easily changed and adapted for many situations
• Clear – You need to be able to see quickly and easily what the numbers relate to and what
you need to change
• Efficient – Easy to use and entering data is a fairly easy process
• Auditable – Easy to prove to someone else that you have got your calculations correct. Errors
can be located quickly.
When preparing the spreadsheets:
• Define the task to make sure you understand
• Determine how the results will be displayed
• Work out formulas from a pen and paper sketch,
• Determine what data is constant and unchanging and that data that will be changing
• Worksheet areas should be defined and arranged logically.
• Check the accuracy of formulas, test using different types of data, Excel has an auditing tool
that can be used to help trace and identify errors, printed hardcopy should be
understandable and clear.
Data entry
When creating a spreadsheet document from scratch you must start with a blank document and
create your own spreadsheet from the keyboard.
Storage
Save them on a USB memory stick, the hard disk on your PC. You should implement a system for
naming your files, so they can be easily located.
Output
Your spreadsheet is to be:
• Included within the text of a report
• Sent as part of an email.
Considering the finances given in Appendix 1, you will be required to:
Part A:

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 35
• Analyse task and determine specifications for spreadsheet. (You must engage in discussion
with your trainer/assessor to gain more clarity on the specifications.)
• Identify organisational and task requirements of data entry, storage, output, reporting and
presentation requirements (specified above).
• Utilise spreadsheet (MS Excel) design software functions and formulae to meet identified
requirements.
• Create three separate spreadsheets for each year.
• Link the spreadsheets considering MS Excel procedures. (attach screenshot)
• Format cells and use data attributes assigned with relative and/or absolute cell references
based on income statements. Refer to hints provided below. (attach screenshot)
• Test formulae to confirm output meets task requirements.
• Evaluate tasks to identify those where automation would increase efficiency. (attach
screenshot)
• Create, use and edit macros to automate spreadsheet operation. (attach screenshot)
• Develop, edit and use templates to ensure consistency of design and layout for forms and
reports, in accordance with organisational requirements.
• Enter, check and amend data in accordance with organisational and task requirements.
Amendments:
o Increase expenses by 10% for each year.
o Recalculate the net income/losses.
• Import and export data between compatible spreadsheets of income statement and adjust
host documents. (Refer to hints)
• Preview, adjust and print spreadsheet in accordance with organisational and task
requirements (attach to this assessment task)
• Name and store spreadsheet in accordance with organisational requirements and exit
application without data loss or damage.
Send an email to your trainer/assessor attaching the screenshots and the soft copy of the
spreadsheets as well.
Note: you can use manuals, user documentation and online help to overcome problems with
spreadsheet design and production
Hints:
a) Enter the data onto Sheet1. Double click on the tab for the worksheet and rename it: East.
Enter the data onto Sheet1. Double click on the tab for the worksheet and rename it: west
b) Add one more sheet to the workbook and rename it as consolidated Click on the tab
Consolidate to make it the active worksheet.
c) Add one more sheet to exiting workbook and rename it as link, click on the tab for Link to
make this the active worksheet. Click on the white box to the left of “Create links to source
data.” if you make any change in any sheet the second will be updated automatically
d) Click on file tab and click on save option and choose the target source as removable disk then
click on save

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 36
Part B:
Using the outcomes of part, A of this assessment task, you are required to:
• Prepare a graph considering specified requirements in the case study and manipulate
spreadsheet data.
o Determine style of graph that will most accurately depict the data given in the
incomes statements.
o Your graph must reveal the differences in the income statements,
• Create graphs with labels and titles from numerical data contained in a spreadsheet file
(refer to income statements in the spreadsheets).
• Save file based on the requirements specified in the task.
Attach the print outs of the graph along with the assessment task.
Send an email to your trainer/assessor attaching the screenshots and the soft copy of the graphs as
well.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 37
Appendix 1 – Covers Books
Covers Books is a small bookstore located in a local shopping precinct owned by DJ Mathis. The store
does not hold a lot of stock but does offer a special-order service for stock not kept on hand.
Stock details
The store carries approximately 5000 titles covering the following categories:
● children’s books
● teenage fiction
● general fiction
● science fiction and fantasy
● romance
● true crime and thriller
● classics.
Staffing
In addition to the owner, DJ, who is at the store on a full-time basis, there is one part-time and two
casual sales staff and one part-time admin/bookkeeping staff member.
Suppliers
Covers have eight main suppliers for the books plus an office supplier to assist with general office
needs such as stationery. Most suppliers are happy to offer 30-day payment terms.
Customer purchasing and payment options
Covers accept cash and major credit cards for customer purchasers.
An eight-week lay-buy option is available for orders of $20 or more. This arrangement requires a 10%
deposit to be paid.
Special orders are available on request. Orders less than $50 require no pre-payment, although
special requests for items totaling $50 or more require a 10% deposit to be paid prior to the order
being placed.
IT Usage
All computers and software provided by Covers Books are to be used primarily for work related
purposes only. Limited personal use is allowed; however, this privilege may be withdrawn if usage is
found to be too high. All computer usage will be monitored to ensure proper use.
User IDs and passwords must not be shared.
Records and files must not be saved to the ‘C’ drive. All work-related files and records should be
saved to the shared drive which is backed up on a weekly basis. Any personal records or files should
be saved to an external device, such as a memory stick.
Finances
The finances for the first three years are shown below.

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 38

INCOME	Year 1	Year 2	Year 3
Net Sales	322,205	362,481	412,503
Cost of Goods Sold
Starting Inventory	185,000	175,690	171,460
add Purchases	97,360	92,500	101,385
add Freight Charges	10,648	9,653	10,970
Sub Total	293,008	277,843	283,815
Less closing inventory	175,690	171,461	169,890
Cost of Goods Sold	117,318	106,383	113,925
Gross Income	204,887	256,098	298,578
EXPENSES
Salaries	82,749	86,472	113,569
Utilities	6,843	7,185	7,543
Rent	30,000	30,000	30,000
Office Supplies	8,243	7,336	7,409
Insurance	4,440	4,573	4,776
Advertising	12,000	7,250	8,327
Telephone	2,393	2,502	2,611
Interest paid	2,964	2,993	3,024
Repairs and maintenance	1,250	841	974
Taxes and licences	11,240	11,465	11,924
Total Expenses	162,122	160,617	190,157
Net Income	42,765	95,481	108,421

Document Type: Unit Assessment Pack (UAP)	Version 9 – Compliance and Quality Assurance Department
Release Date: Jan 2018	Review Date: Jun 2019	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 39
Appendix 2 – Ergonomic and environmental checklist

Checklist	Yes	No
Are you placed under pressure to meet demanding work targets or deadlines?
Have work pauses been taken as appropriate?
Is the chair easily adjusted from a seated position?
Can you get close to the workstation without impediment?
Is the seat height adjustable so that your thighs are parallel to the floor, with feet resting on the floor or a footrest?
Is the backrest height-adjusted to fit into the small of your back and adequately support your spine?
Is the backrest angle-adjusted so that you are sitting upright while typing?
Are your forearms parallel with the floor or slightly angled downward?
Is the desk height adjustable?
If yes, is the adjustment easily operated? If no, do you have a footrest?
If there is a footrest, is it large enough to support both feet and allow a change of position?
When sitting tall and looking straight ahead, are you looking at the top edge of the screen?
Is the screen at a comfortable reading distance?

Document Type: Unit Assessment Pack (UAP)	Version 8 – Compliance and Quality Assurance Department
Release Date: Feb 2016	Review Date: Dec 2017	Unit Name:	BSBITU402 – Develop and use complex spreadsheets

Page | 40

Checklist	Yes	No
Are all characters in the display easily legible and the image stable?
Can the position and contrast of the screen be adjusted by the user?
Is the keyboard detached from the screen to ensure a comfortable working position?
Is the keyboard thin enough for comfortable positioning of the arms (it should be less than 30mm thick at the home row of keys)?
Is the keyboard matte finished to prevent irritation from glare and reflection?
Is there sufficient space for large documents, completed work or writing?
Is there sufficient space for CAD furniture, equipment and hardcopy materials?
Do you find the lighting satisfactory?
Do you find the noise level conducive to concentration?
Do you find the temperature and airflow in the room comfortable?