Applied Education
Applied Education
13SBITU402: Develop and Use Complex Spreadsheet
This unit applies to individuals employed in a range of work environments who require skills in the 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.
Performance Evidence
Evidence of the ability to:
^ follow organisational and safe work practices including:
• ergonomic requirements
^ energy and resource conservation techniques
• adhere to organisational requirements for:
^ ensuring consistency of style, design and layout
• saving and printing documents within designated timelines
^ naming and storing documents
• adhere to identified or task requirements when producing documents including:
^ editing macros and automating some tasks
• using appropriate templates
^ creating graphs to represent data
• resolve issues by referring to user documentation and online help
^ use appropriate data storage options
• evaluate tasks to improve efficiency
• apply knowledge of functions and features of contemporary computer applications
^ communicate with relevant personnel
Knowledge Evidence
To complete the unit requirements safely and effectively, the individual must:
^ explain advanced functions of spreadsheet software applications
^ describe impact of formatting and design on presentation and readability of data
• explain organisational requirements for ergonomics, work periods and breaks, and conservation techniques
Assessment Conditions
Assessment must be conducted in a safe environment where evidence gathered demonstrates consistent performance of typical activities experienced in the information and communications technology — IT use field of work and include access to:
^ organisational policies and procedures
• relevant workplace documentation and resources
^ industry software packages and user instructions
BSBITU402 Final Assessment V1.0 Page 1 of 10

Applied Education
Outline of tasks
For this task you will be required to complete each of the activities in full showing your skill set using Microsoft Excel.
You will be assessed on your ability to:
^ Develop complex spreadsheets
^ Develop graphical representations of data contained in spreadsheets
^ Spreadsheets terminology in order to understand the tasks
^ OHS knowledge to work in an ergonomically safe functioning environment.
^ Characteristics of software necessary for research or to record information electronically.
How do I know if I have passed? And what if I fail?
There are only two 'marks' or grades in a certificate course. They are 'competent' (C) and 'not yet competent' (NYC). If you are rated 'Not Yet Competent' (NYC) in a unit, you will receive feedback and some coaching or training if necessary. You will then be re-assessed only for those areas in which you were rated 'NYC' at a later time. If you still are not rated competent, then the process begins again. You will receive feedback and some coaching or training, as necessary. You will then be re-assessed.
This is one of the advantages of this type of training that is called 'competency based training'. You have three (3) attempts to repeat the parts you need. If you are deemed still as 'Not Yet Competent' (NYC) you will receive an alternative assessment to complete.
Student Name: Click here to enter text.
Date: Click here to enter a date.
Copyright Notice
This manual is copyrighted and all rights are reserved by Applied Education. No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any other language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual or otherwise without the written consent of Applied Education, PO Box 6013, East Perth WA 6892.
BSBITU402 Final Assessment V1.0 Page 2 of 10
Applied Education
Task 1 - Prepare to develop spreadsheets
1. Answer the following knowledge questions
a) Describe what the following functions can be used for
COUNT: Click here to enter text.
PMT: Click here to enter text.
IF: Click here to enter text.
NOW: Click here to enter text.
b) Which of these filenames is not a valid Excel file?
Sa-es.docx
Sales?.xlsx
First/Qtr.xlsx
Sales*.xls
Sales_Fst_Qtr_14.xlsx
Salesl4.xlsx
c) List a three (3) different methods you can use to save your work when closing a file
1. Click here to enter text.
2. Click here to enter text.
3. Click here to enter text.
d) Describe how formatting and design can affect the presentation and readability of data Click here to enter text.
BSBITU402 Final Assessment V1.0 Page 3 of 10
Li
Applied Education
2. Download the OfficeWise document, and/or speak to your workplace WHS Officer about policies in your workplace, then answer the following questions:
a) List five (5) ways that you can improve the overall office layout, to increase work effectiveness and decrease hazards.
1. Click here to enter text.
2. Click here to enter text.
3. Click here to enter text.
4. Click here to enter text.
5. Click here to enter text.
b) List two (2) substances or situations that can cause health issues in the office environment
1. Click here to enter text.
2. Click here to enter text.
3. Download the Office Awareness PDF document, review and answer the following questions:
a) List three (3) practices to take into consideration for conserving resources in the workplace
1. Click here to enter text.
2. Click here to enter text.
3. Click here to enter text.
b) Briefly explain the requirement for rest breaks within a workplace
Click here to enter text.
BSBITU402 Final Assessment V1.0 Page 4 of 10
Applied Education
4. Download the Policy and Procedures Extract document, review and answer the following questions. List four (4) organisational requirements that address data entry, storage, output and presentation requirements
1. Click here to enter text.
2. Click here to enter text.
3. Click here to enter text.
4. Click here to enter text.
5. Search Excel help for -validation-. Explain why you might 'Apply data validation to cells' Click here to enter text.
13513ITU402 Final Assessment V1.0 Page 5 of 10
Page Il
POLICIES & PROCEDURES EXTRACT
SPREADSHEETS
Software
The company's recommended spreadsheet software is Microsoft Excel. Other programs may be used at the discretion of the section manager.
Spreadsheet Requirements
1. The following procedures must be completed before spreadsheets are finalised:
• Data entry spellchecked and proofread
• Formatting used to ensure professional presentation of data
• Formulas checked and tested for accuracy
2. All files should be saved to the appropriate data storage device and/or uploaded to the Portal
3. Documents must be named according to the organisations naming convention
4. All documents must be backed up
5. Automation and/or templates should be used where possible
6. The following procedures must be completed before spreadsheets are printed:
• Documents should be previewed before printing
• Documents should be printed in black and white, with the exception of charts and graphs which can be printed in colour
• Documents should be printed to a PDF file where possible
I POLICIES & PROCEDURES
L.1
Applied Education
To complete the following tasks you will require access to Microsoft Excel. Refer to your Student manual and Excel help when required. Ensure you consider ergonomics, WHS policies and procedures and conservation techniques when completing this assessment.
Task 2 - Create a Spreadsheet and Insert Charts
a) Create a new Excel workbook and save the file as Sales.xlsx
b) Rename worksheets as follows:
• Sheet 1— Sales
• Sheet 2 — Expenses
• Sheet 3 —Totals
c) Enter the following information:
Sales sheet
Jan Feb Mar Total %
Betty 1400 850 3000
Craig 700 77 -45
John 390 900 22
Total
Expenses sheet
Jan Feb Mar Total %
Wages 900 890 950
Rent 200 200 200
Telephone 55 55 55
Insurance 77 500 77
Office supplies 22 60 90
Total
Totals sheet
Jan
Income
Expenses
Net Profit/Loss
BSBITU402 Final Assessment V1.0 Page 6 of 10
Feb
Mar
Total
,-.
INIIIIIII
...: - .
Applied Education
Automation
d) Look back over the above tables, and decide where you would apply automation to increase efficiency. Explain the automation you would apply, and why.
-11/41-1-1- 0 M q s j c Cc) (
• p k 1 LA cam,'00 tc,t....“)
Formulas and Functions r-Kc
e) Add total row and column to the Sales and Expenses sheet/
K, t..4-1 c_cCL3
f) Enter totals for each column and row using the Sum functichir7
g)
h) Calculate the % of the total for each income and expens tem in the last column for each sheet. Ensure you use absolute referencing in your for ula
According to the Policies and Procedures document, you are required to ensure the formulas are correct. Explain below what techniques you used to check your formulas in this spreadsheet
Click here to enter text.
Formatting
Click here to enter text.
On the Totals sheet, insert formulas linked to the sales and expiernses sheets
j)
k)
I)
Use formatting to show negative values in red with a --- negative sign Format all number cells to currency format to 0 decimal plac‘ Format the % column to show as a percentage to 1 decimal pliate
Insert a title at the top of each sheet in row 1 that spans 8 columns and is centre aligned Format font size to 18 and Bold
If Statement (Totals Sheet)
n) Add a new row below your net profit on the Totals sheet and insert an IF statement for each month that shows the words -Net Profit- if incom is greater than or equal to expenses, otherwise show the words -Net Loss-
Charting
o) Create a chart on a separate sheet showing total sales for each employee. Ensure you select a chart type appropriate for the data and include labels and titles
p) Name the sheet -Sales Chart-
q) Create another chart on a separate sheet showing net profit for each month. Ensure you select a chart type appropriate for the data and include labels and titles
r) Name the sheet -Profit Chart-
s) Print your charts to PDF files named Sales Chart and Profit Chart
BSBITU402 Final Assessment V1.0 Page 7 of 10
Applied Education
Task 3 - Create and Edit a Macro
a) Open a new workbook and name it Stadium.xlsx
b) Ensure Use Relative References is selected (this is on the Developer tab — if the Developer tab is not displaying you will need to go to Excel Options, Customize Ribbon and ensure Developer is selected/ ticked)
c) Start recording a macro in the workbook named Setup, entering your name in cell Al, format font as bold and red colour, and change page orientation to landscape. (Assign the macro to this workbook only). Stop recording and save the workbook as a macro-enabled file
d) Take a print screen (PrtScn) of the macro code ensuring this shows the orientation setting and paste into a new worksheet in the stadium.xlsm workbook. Name the worksheet: MacroCode
e) Edit the macro so the Orientation is equal to Portrait
f) Save as a Macro enabled file and close
Task 4 - Using Importing and Templates
a) Import the file Text File.txt into an Excel worksheet
b) Format the data as follows:
o Ensure text is Calibri font size 11
o Total Income and Total Expenses rows should have a top and bottom border
• Total column should have a left and right border
c) Rename the worksheet Sheet 1— Home
d) Name the workbook Home and save as a template file
Formulas and Functions
e) Calculate the Total Income and Total Expenses row using the sum function
f) Calculate the Total for Pay in cell N2 and replicate this formula down to Others N14
g) Calculate the Total for N16
h) Calculate the Savings for B16
i) Calculate the Savings as a cumulative calculation by subtracting Total Expenses from Total
Income and adding the Savings from the previous month
Hint — this formula should only be used from February on
j) Save the changes to the Home template
BSBITU402 Final Assessment V1.0 Page 8 of 10
Li
Applied Education
Edit the Template
k) Format the row labels to be bold
I) Format the rest of the labels to be Italic
m) Format all value cells to currency and 0 decimal places
n) Ensure you save the changes to the template
Using the Template
o) Enter the following information into your template
Home Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Pay 385 385 385 400 400 400 400 400 400 400 400 430
Other Income 0 0 0 0 0 0 0 0 0 0 0 0
Total Income
Rent 80 80 80 80 80 80 80 90 90 90 90 90
Holidays 0 0 ^ 50 0 0 0 210 0 0 0 0
Leisure 75 55 48 75 40 48 56 180 30 35 60 150
Electricity 49 0 0 43 0 0 29 0 0 31 0 0
Gas 46 0 0 51 0 0 32 0 0 35 0 0
Telephone 0 37 0 0 35 0 0 36 0 0 43 0
Car 0 0 91 0 0 13 0 0 74 27 0 0
Petrol 20 25 25 20 25 20 20 50 25 25 20 30
Food 60 60 60 75 60 55 50 80 55 60 70 90
Others 55 45 100 65 75 150 100 75 45 34 60 100
Total Expenses
Savings
p) Save the file as an Excel workbook called Home.xlsx
q) Test the formula for cell N16, to ensure it is calculating correctly. Explain below the steps you take to evaluate the formula
Click here to enter text.
Print
r) Print preview the -Home- workbook
s) Adjust the worksheet to fit to 1 page
t) Print the workbook as a PDF files named -Home.pdf-
BSBITU402 Final Assessment V1.0 Page 9 of 10
Applied Education
Assessment Checklist:
Prior to submitting your assessment, check that you have completed all tasks.
• Completed Assessment Document
• Sales.xlsx
• Sales Chart.pdf
• Profit Chart.pdf
• Stadium.xlsm (macro file)
• Home.xltx (template file)
• Home.xlsx
• Home.pdf (printouts)
Once completed, please upload your assessment and all required documents/files to the online platform.
BSBITU402 Final Assessment V1.0 Page 10 of 10
Page 11
POLICIES & PROCEDURES EXTRACT
SPREADSHEETS
Software
The company's recommended spreadsheet software is Microsoft Excel. Other programs may be used at the discretion of the section manager.
Spreadsheet Requirements
1. The following procedures must be completed before spreadsheets are finalised:
• Data entry spellchecked and proofread
• Formatting used to ensure professional presentation of data
• Formulas checked and tested for accuracy
2. All files should be saved to the appropriate data storage device and/or uploaded to the Portal
3. Documents must be named according to the organisations naming convention
4. All documents must be backed up
5. Automation and/or templates should be used where possible
6. The following procedures must be completed before spreadsheets are printed:
• Documents should be previewed before printing
• Documents should be printed in black and white, with the exception of charts and graphs which can be printed in colour
• Documents should be printed to a PDF file where possible
I POLICIES & PROCEDURES
Page 11
POLICIES & PROCEDURES EXTRACT
SPREADSHEETS
Software
The company's recommended spreadsheet software is Microsoft Excel. Other programs may be used at the discretion of the section manager.
Spreadsheet Requirements
1. The following procedures must be completed before spreadsheets are finalised:
• Data entry spellchecked and proofread
• Formatting used to ensure professional presentation of data
_
• Formulas checked and tested for accuracy
2. All files should be saved to the appropriate data storage device and/or uploaded to the
Portal
3. Documents must be named according to the organisations naming convention
4. All documents must be backed up
5. Automation and/or templates should be used where possible
6. The following procedures must be completed before spreadsheets are printed:
• Documents should be previewed before printing
• Documents should be printed in black and white, with the exception of charts and graphs which can be printed in colour
• Documents should be printed to a PDF file where possible
''DURES
, • .
Applied Education
Analyse task and determine
specifications for a spreadsheet
Excel is an electronic spreadsheet program that can be used for storing, organising and manipulating data. Charts can be easily generated from excel data and transformed into a visual report. Other common operations that excel can be used for is filtering and sorting data to find specific information.
When you look at the Excel screen (refer to the example Figure 1 Spreadsheet on this page) you see a rectangular table or grid of rows and columns.
An excel file is known as a workbook. When a workbook is opened it contains three (3) worksheets, the worksheets are initially named Sheet 1, Sheet 2 and Sheet 3, these names can be changed and worksheet can be added. Each worksheet is displayed as a tab; the active worksheet will be highlighted.
• •.11.
=MN •'-
MS .7. El • .•••• • • it kla OA AA CA At.
Ji •
c. . 1
•••,••••,-
Figure 1 Spreadsheet
Spreadsheets are often used to store financial data. Formulas and functions that are used on this type of data include:
• Performing basic mathematical operations such as summing columns and rows of figures.
• Finding values such as profit or loss.
• Calculating repayment plans for loans or mortgages.
• Finding the average, maximum, or minimum values in a specified range of data.
1IPage

Applied Education
Designing a workbook
Before creating a workbook you need to ask yourself a few questions.
• What data should be in rows and which in columns? Consider the readability of your data before you make this decision.
• Do you want your worksheet date to be long rather than wide so you can use the page up and page down?
• What formulas will be necessary to achieve the required output?
• Does the spreadsheet require printing?
• Who is the audience?
• Does the worksheet rely on imported date?
A simple plan is the best way to ensure all the required information is used for each spreadsheet.
Plan your spreadsheet
Before you begin entering data into a spreadsheet it is a good idea to do a bit of planning before you begin to type.
Points to consider
• What is the purpose of the spreadsheet?
• What information needs to be included?
• What headings are needed to explain the information in the spreadsheet?
• What is the best layout for the information, in rows or columns?
Some General Rules
Whenever possible, don't leave blank rows or columns when entering your data.
• Leaving blank rows and columns in data tables can make it difficult to use a number of Excel's built in features such as graphing and functions.
Enter your data in columns when possible
• When laying out your spreadsheet, place a title describing the data at the top of the first column of the table with the data below.
If there is more than one data series, list them one after the other in columns (left to right) with the title for each data series at the top.
Each organisation may have their specifications that you have to follow when designing a spreadsheet.
• Remove worksheets that are not being used
• Certain cells shaded
• Renaming the default worksheets
• Adding headers and footers
21Page

Applied Education
Name and Store Spreadsheets
Naming Convention
Most organisations have a naming convention system for naming and saving files. Files need to be named uniquely to reflect the content of the file and the place in the hierarchy of files. An example of this is if you saved a file as Costings.xlsx, this would be fine, however what happens if a company has lots of costing files. A more precise name could be Jan14_Costing.xlsx or Janl4Costing.xlsx. As you can see the file name doesn't have a space, this has been replaced by an underscore. Be very careful when you are naming files, to follow the Windows file name rules — you cannot us the characters/:*?- I
Spaces are ok to be used in files names, however some programs do not recognise spaces — as above
I have used an underscore to replace the space. If you are not sure of all the end users for the file, do not leave a space.
File Storage
Files can be saved in various locations, depending on your company or home computer. Broadly speaking you will save to removable storage such as USB, External Hard drive, CD or DVD, or local hard drive and network.
File Structure
By default file structures are saved in order and show in a hierarchical order. To view the file structure you need to access Windows Explorer or it could be called Explore by right clicking on your start command. An example of a file structure:
jv a Computer a Local Disk (C:) - E
Organize • Extract all files
.e„, Local Disk (C:)
. ATI
• I Excel Data
• I. Data Modell.xlszczip
Jels
customXml
J. docProps
•
J. ids
I. activeX
J. drawings
J. media
J. model
pivotCache
I. pivotTables
J, printerSettings
j. theme
J. worksheets
3 IPage
1 item