Course Title: Develop and use complex spreadsheets

Part B: Course Detail

Teaching Period: Term2 2012

Course Code: COSC5946C

Course Title: Develop and use complex spreadsheets

School: 650T TAFE Business

Campus: City Campus

Program: C5261 - Diploma of Financial Planning

Course Contact : Michael Elliot

Course Contact Phone: +61 3 9925 5519

Course Contact

Name and Contact Details of All Other Relevant Staff

Nominal Hours: 50

Regardless of the mode of delivery, represent a guide to the relative teaching time and student effort required to successfully achieve a particular competency/module. This may include not only scheduled classes or workplace visits but also the amount of effort required to undertake, evaluate and complete all assessment requirements, including any non-classroom activities.

Pre-requisites and Co-requisites

There are no pre-requisites to this course but it is co-delivered with:

FNSFPL501A Comply w financial planning practice ethical & operational guidelines & regulations
FNSINC401A Apply principles of Professional Practice to work in Fin Services Industry
FNSFPL506A Determine client requirements and expectations
FNSCUS505A Determine client requirements and expectations
FNSCUS506A Record and implement client instructions

Course Description

The 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.

National Codes, Titles, Elements and Performance Criteria

National Element Code & Title:

BSBITU402A Develop and use complex spreadsheets


Automate and standardise spreadsheet operation

Performance Criteria:

1.1. Organise personal work environment in accordance with ergonomic requirements
1.2. Analyse task and determine specifications for spreadsheets
1.3. Identify organisational and task requirements in relation to data entry, storage, output, reporting and presentation requirements
1.4.Apply work organisation strategies and energy and resource conservation techniques to plan work activities


Develop a linked spreadsheet solution

Performance Criteria:

2.1. Utilise spreadsheet design software functions and formulae to meet identified requirements
2.2. Link spreadsheets in accordance with software procedures
2.3. Format cells and use data attributes assigned with relative and/or absolute cell references, in accordance with the task specifications
2.4.Test formulae to confirm output meets task requirements


Prepare to develop spreadsheet

Performance Criteria:

3.1. Evaluate tasks to identify those where automation would increase efficiency
3.2. Create, use and edit macros to fulfil the requirements of the task and automate spreadsheet operation
3.3.Develop, edit and use templates to ensure consistency of design and layout for forms and reports, in accordance with organisational requirements

Learning Outcomes

Details of Learning Activities

Learning Activities will consist of classroom collaborative delivery of course notes and relevant exercises around student centred learning and research.

Teaching Schedule

Teaching Schedule
The nominal hours associated with this are a guide only and represent the total teaching time and student effort required to successfully complete the course. This may include not only scheduled classes but also the amount of effort required to undertake, evaluate and complete all assessment requirements, including any non-classroom activities.

Week    Week Comm                              Topics            Assessment                                 
0           25th June                 Orientation                                       
12nd JulyPre-training review:
Familiarisation with:
• Program
• Services/facilities
• Other students
• Teacher(s)
• Where to get support
• Student responsibilities
• Course requirements
• Course support documents
• Blackboard
• Overview of assessment requirements                                                                                                                                     
• Grading
• Plagiarism
• Appeals
• Extensions
• Feedback
• Privacy
• Submission requirements
• Resubmission policy
Financial Services Industry – Module 1- Chapter 1
Spreadsheet exercises
29th JulySpreadsheet exercises
Financial Services Industry – Module 1- Chapter 1
316th JulyFinancial Services Industry – Module 1- Chapter 2 
423rd JulyFinancial Services Industry – Module 1- Chapter 3
Assessment on Topics from weeks 1 and 2
Excel Activity Questions Grading (CA/NYC)
530th JulyStudent Study WeekNo Formal classes
66th AugFinancial Services Industry – Module 1- Chapter 3
713th AugFinancial Services Industry – Module 1- Chapter 4 
820th AugFinancial Services Industry – Module 1- Chapter 4
Financial Services Industry – Manage Client Services- Chapter 1
  Mid semester break (27th August to 31st August) 
93rd SeptFinancial Services Industry – Manage Client Services- Chapter 2 
1010th SeptFinancial Services Industry – Manage Client Services- Chapter 3 
1117th SeptFinancial Services Industry – Manage Client Services- Chapter 4 
1224th SeptFinancial Services Industry – Manage Client Services- Chapter 5
Introduction to Role Plays (This is outlining the assessment)
Final Role Play Assessment discussion
With teacher
131st OctIntroduction to Role Plays (This is outlining the assessment)Final Role Play Assessment Practice
With teacher
148th OctCompletion of role Plays and Final assessment review and Discussion Continued as per above
1515th OctAssessment 3 – (CA/NYC)
(From Topics covered in Weeks 3 to Weeks 14) (CA/NYC)
1622nd OctRe-submissions if required 
1729th OctFeedback 

Learning Resources

Prescribed Texts


Other Resources

Overview of Assessment

Assessment may incorporate a variety of methods including written tests, a group assignment and final assessment.

Tests are closed book and consist of multiple choice, practical exercises and short answer questions.

The group assignment requires students to research the role and function of a financial institution or a major contemporary issue relating to the financial system and of concern to consumers.

Students are advised that they are likely to be asked to personally demonstrate their assessment work to their teacher to ensure the relevant competency standards are being met. Students will be provided with feedback throughout the course to check their progress.

Assessment Tasks

You are required to complete the following task. You must successfully complete this task to be deemed competent in this unit.

This will be developing an excel spreadsheet. CA/NYC

Marking Guide (competency):
Vocational Education and Training (VET) is based on current industry needs and the focus on preparing you for the workplace. Because VET courses are informed by practical application of knowledge and skills, they are based on a system known as ‘competency based training’ (CBT). So when you are assessed in VET it is about whether you are competent to do the job, as well as having a firm grasp on the knowledge and skills required to do that job, as opposed to traditional curriculum based education settings that are often based on knowledge retention.

You need to demonstrate you are competent in each element of the unit of competency you are studying. You will receive feedback on each assessment task that will inform you whether you are competent or not and how well you are performing. Once competent in all elements of the unit you receive a competency grading. Please refer to the Final Grades table below.

Marking Guide (Grading)
After achieving competency we then grade your performance in the unit; this gives you the opportunity to have the level of your performance formally recognized against industry standards and employability skills.
The grading is according to the following criteria:

You are required to demonstrate a high level of ability to complete all tasks independently as per the specifications as well as demonstrating a high level of initiative in your access of information required for the assessments tasks, accuracy and quality of information presented to the client and appropriate recommendation using the information you have access, analysed and presented.

You need to show a depth of understanding of the key concepts and knowledge of the financial products covered in the assessment tasks, including personal loans and overdrafts and general insurance products. Accuracy of all related interest rates, repayment calculations, fees and charges is essential.

Demonstration of appropriate use of technology to assist in presenting all tasks clearly and suitable for the intended audience. This requires use of bank and financial institution web sites to access financial data and demonstrate a capacity to use financial calculators to calculate repayments and credit limits.

You must complete all work requirements as per course requirements and timely submission of all required assessment tasks. You need to collate data and organize it in a presentable format that meets the requirements of the assessment tasks.

You are required to work in groups to complete assessment task 3. Communication skills and teamwork is required to collaboratively complete this task. Additionally you will need to network to collate financial data for personal loan products required in assessment task 2.

Final Grade Table
CHD Competent with High Distinction
CDI Competent with Distinction
CC Competent with Credit
CAG Competency Achieved – Graded
NYC Not Yet Competent
DNS Did Not Submit for assessment

Assessment Matrix

Course Overview: Access Course Overview