RECENT ASSIGNMENT

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.

Task 4 – Spreadsheet Template
Awesome Landscapes has provided you with their financial data in a .csv file and you need to download the file by accessing it from the link below:
BSBITU402 Combined Balance Sheet and Profit and Loss Statement.csv
You are required to complete a series of tasks to produce a Financial Summary using this .csv file.
Before you proceed to produce the Financial Summary, you need to use a Planning Document to analyse and evaluate the task and guide you through the process.
Go over the instructions for Tasks 4, 5, 6 and 7. Note that you will only have one (1) output for Tasks 4, 5, 6 and 7, the Financial Summary.
A Planning Document template is provided in the Appendix section of this workbook. You can access it here.
You might want to research how to create macros 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 4 Planning Document
Note: The financial summary must be completed not more than ten (10) working days after completing Task 3 – Quality Audit Report.

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 physical 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

Open the BSBITU402 Combined Balance Sheet and Profit and Loss Statement.csv file you have downloaded, and create a spreadsheet template by following the instructions below:
Open a new excel workbook.
Create a macro to import raw data from a .csv file to a new workbook.
Using the macro created in (b), import Awesome Landscapes’ combined Balance Sheet and Profit and Loss statement. This should be on the first sheet. Rename Sheet 1 to Combined BS and PL Statement.
Create another macro for your new tables to automatically follow a design template that is clear and easy to read.
Your output must be an MS Excel macro-enabled file with the .xlsm extension. When you are finished, save the Excel file as:

BSBITU402 Tasks 4-7 Financial Summary

Task 5 – Balance Sheet
Using the same file you created in Task 4 (BSBITU402 Tasks 4-7 Financial Summary), create a second tab and name it “Balance Sheet”.
On this tab, using custom cell formats where necessary, produce a 4-Year Balance Sheet.
Automate this sheet using appropriate formulae to automatically populate the cells from the corresponding raw data imported into Sheet 1 (Task 4) from the .csv file provided. You are to include all data from the rows of “Cash” and “Cash Equivalents” up to the “Retained Earnings/Accumulated Losses”.
Use the following definitions to produce the formulas for calculating the following:
Total Current Assets = Cash and Cash Equivalents + Account Receivable + Inventory + Work in Progress
Total Non-Current Assets = Plant and Equipment + Plant and Equipment-accumulated Depreciation
Total Assets = Total Current Assets + Total Non-Current Assets
Total Current Liabilities = Accounts Payable + Bank Overdraft + GST Payable
Total Non-Current Liabilities = The Sum of all Loans Payable
Total Liabilities = Total Current Liabilities + Total Non-Current Liabilities
Net Assets = Total Assets - Total Liabilities
Total Equity = Owner's/Shareholder's Capital + Retained Earnings/Accumulated Losses
Net Profit (to be computed for in Task 5)
Make sure that the table in this sheet is properly labelled as:
Awesome Landscapes Pty. Ltd.
Balance Sheet
Format this table according to the Awesome Landscapes Style Guide. You can access it here.
When you are finished, save the file. Maintain the file name:
BSBITU402 Tasks 4-7 Financial Summary

Task 6 – Trading Statement
On the same file used in Tasks 4 and 5, create a third tab and name it “Trading Statement”. On this tab, using custom cell formats where necessary, produce a 4-year Trading Statement.
Automate this sheet using appropriate formulae to automatically populate the cells from the corresponding raw data imported into Sheet 1 (Task 4) from the .csv file provided. You are to include all data from the rows of “Cash Sales” up to “Expenses”.
Use the following definitions to produce the formulas for calculating the following:
Total Sales = Cash Sales + Credit Sales
Gross Profit = Total Sales - Cost of Goods Sold
Net Profit = Gross Profit – Expenses
Include the net profit (values only) in the Balance Sheet created in Task 5.
Make sure that the table in this sheet is properly labelled as:
Awesome Landscapes Pty. Ltd.
Trading Statement
Format this table according to the Awesome Landscapes Style Guide. You can access it here.
When you are finished, save the file. Maintain the file name:
BSBITU402 Tasks 4-7 Financial Summary

Task 7 – Financial Ratios and Graphs
On the same file from Tasks 4, 5, and 6, create a fourth tab named “Financial Ratios”. This tab will contain the financial ratios from the data you have produced in Task 5 – Balance Sheet.
Use the following definitions to produce the formulas for calculating the following:
Gross Profit Ratio = (Gross Profit)/(Total Sales)
Net Profit Ratio = (Net Profit)/(Total Sales)
Debt to Equity Ratio = (Total Liabilities)/(Total Equity)
Current Ratio = (Total Current Assets)/(Total Current Liability)
Working Capital Turnover = (Cost of Goods Sold)/(Total Current Assets-Total Current Liabilities)
You must use three (3) decimal places only.
When you are finished, save the file. Maintain the file name:
BSBITU402 Tasks 4-7 Financial Summary
After producing the financial ratios, create a fifth tab in the same Excel file. Rename this tab as “Charts”.
On this tab, you must create charts to represent the following data:
Profit Ratios (both Gross and Net Profit ratios displayed on the same chart)
Debt to Equity Ratio (include a linear trend line)
Current Ratio (include a linear trend line)
Working Capital Turnover (include a linear trend line)
When you are finished, save the file. You should have five (5) tabs in your current file. Maintain the file name BSBITU402 Tasks 4-7 Financial Summary
Preview the “Charts” tab for printing and ensure that it fits and is readable on a white A4 size paper. You may use more than one (1) sheet of paper, so all the charts in this tab are printed.
Print the charts and save the file locally to your computer as
BSBITU402-Task 7 Charts
Upload the files you saved:
BSBITU402 Tasks 4-7 Financial Summary
BSBITU402-Task 7 Charts
to the online storage you chose. Provide links to the uploaded document and ensure that it is accessible by your assessor. This is for backup purposes.
The files must be uploaded not more than ten (10) working days after you have completed Task 3 – Quality Audit Report.
Note: Your assessor will take note of the date and time when this document was last modified. Ensure that you complete the files not more than ten (10) working days after completing Task 3 – Quality Audit Report.

Part 3 – Conclusion
You have just completed all the spreadsheets for the business manager. Send an email to Shaar Azad advising him of this. Your email must specifically include:
A statement advising Shaar that you have completed all the spreadsheets.
A statement outlining the spreadsheets you have completed:
Sales Summary
Quality Audit Report
Financial Summary
A statement including the link(s) to access these files.
Write your email in the spaces provided below.
From Candidate Candidate’s email address
To Shaar Azad shaar.a@awesomelandscapes.com.au
Subject
Message

You have just completed all the tasks required of you. List at least two (2) work organisation strategies you applied in the conduct of the tasks in the Case Study in any of these areas:
Productivity
Quality
Costs
Personnel
Communication
Workplace health and safety
Environment

WORKBOOK CHECKLIST
When you have completed assessing the assessment workbook, review the candidate’s submission against the checklist below:
0 The candidate has completed all the assessments in the workbook and has submitted all of the required evidence:
0 Knowledge Assessment
0 Practical Assessment
0 Part 1 – Follow safe work procedures
0 Part 2 – Develop and use complex spreadsheets
0 Task 1 – Sales Summary
0 Task 2 – Quality Audit Report
0 Task 3 – Spreadsheet Template
0 Task 4 – Statement of Financial Position
0 Task 5 – Trading Statement
0 Task 6 – Financial Ratios and Graphs
0 Part 3 – Conclusion
If you have completed all the assessments above, then you are ready to submit this workbook. Please ensure you submit the following documents:
0
This completed workbook
0
BSBITU402-Case Study-Part 2 Roleplay Activity Video
0
BSBITU402-Case Study Part 1-Task 1 Workstation
0
BSBITU402-Case Study Part 1-Task 2 Energy Conservation Technique
0
BSBITU402-Case Study Part 1-Task 2 Resource Conservation Technique
0
BSBITU402-Case Study Part 2-Task 1 Planning Document
0
BSBITU402-Part 2-Task 1-Sales Summary
0
BSBITU402-Case Study Part 2-Task 2 Planning Document
0
BSBITU402-Part 2-Task 2-Quality Audit Report
0
BSBITU402-Case Study Part 2-Task 3 Planning Document
0
BSBITU402 Tasks 3-6 Financial Summary
0
BSBITU402-Task 6 Charts
IMPORTANT REMINDER
Candidates must achieve a satisfactory result in ALL assessment tasks to be awarded COMPETENT for the unit relevant to this subject.
To award the candidate competent in the units relevant to this subject, the candidate must successfully complete all the requirements listed above according to the prescribed benchmarks.
OVERALL MARKING RESULT FORM (For the Assessor’s Use Only)
Assessment Details
Candidate’s Name:
RTO Name
RTO Contact Number
RTO Email Address
Assessor’s Name:
Subject Develop and Use Complex Spreadsheet
Unit(s) of Competency BSBITU402 Develop and Use Complex Spreadsheet
BSBITU402 Develop and Use Complex Spreadsheet
Knowledge Assessment S NYS
Question 1 • •
Question 2 • •
Question 3 • •
Question 4 • •
Question 5 • •
Practical Assessment
Case Study S NYS
Case Study, Part 2, Task 1. • •
Case Study, Part 2, Task 2. • •
Case Study, Part 2, Task 3 • •
Case Study, Part 2, Task 4 • •
Case Study, Part 2, Task 5 • •
Case Study, Part 2, Task 6 • •
Case Study, Part 2, Task 7 • •
Case Study, Part 3, Conclusion • •
Rules of Evidence S NYS
All knowledge and skills evidence submissions are valid • •
All knowledge and skills evidence submissions are authentic • •
All knowledge and skills evidence submissions are sufficient • •
All knowledge and skills evidence submissions are current • •
Signature Authentication Checklist
This checklist will guide you in authenticating the signatures provided by the candidate in their assessment workbook and evidence submissions.
Read each checklist item and tick YES only if you confirm that the item is a true and accurate reflection of the signature authentication you have conducted.
Checklist Item Check if completed
I have checked the signature provided by the candidate in the Assessment Workbook Cover Sheet against the signature they provided to the Training Provider. •
I confirm the signature provided by the candidate in the Assessment Workbook Cover matches the signature they provided to the Training Provider. •
I confirm ALL signatures provided by the candidate in their evidence submissions match with the signature they provided to the Training Provider •

Third Party Verification Log
Instructions for the Assessor:
You are required to contact all third-party personnel involved in the candidate’s assessment to verify the candidate’s performance and evidence submissions and to confirm with them whether the candidate’s evidence submissions are true and accurate.
Complete this Third-Party Verification Log to document your completion of this process. In completing this log, provide all of the following required information for each third-party personnel:
Name of third-party personnel contacted
Role in the candidate’s assessment (e.g. workplace supervisor, observer, or learner)
Contact details (phone number or email address)
Date contacted
You must also confirm that third-party personnel has verified the candidate’s evidence submissions are true and accurate.
Name of Third-party Contacted Role in the Candidate’s Assessment Contact Details (Phone number or email address) Date contacted Third-Party verifies evidence submissions of the candidate are true and accurate?

Yes ? No ?
Assessor’s notes:

Yes ? No ?
Assessor’s notes:

Yes ? No ?
Assessor’s notes:

Overall Result for this Workbook S NYS
Overall Result ?
?
Assessor’s Comments/Feedback

Assessor’s Declaration
I declare that the results recorded in this Assessment Cluster Final Result Record are a true and accurate record of the candidate’s results for each task.
Assessor’s Name
Assessor’s Signature

Date
End of Marking Form (For the Assessor’s Use Only)

APPENDIX
Planning Document
Name
Position
Required Output
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 assessor
0 Handing in of physically printed report to assessor after submitting completed workbook
0 Submission along with 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
End of Document