Course Title: Develop and use complex spreadsheets

Part B: Course Detail

Teaching Period: Term1 2010

Course Code: COSC5946C

Course Title: Develop and use complex spreadsheets

School: 650T TAFE Business

Campus: City Campus

Program: C6072 - Advanced Diploma of Accounting

Course Contact : Angelo Sellan

Course Contact Phone: +61 3 9925 5460

Course Contact

Name and Contact Details of All Other Relevant Staff

Ian Nash

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


This course is delivered and assessed holistically along with FNSACCT608B (ACCT5264C) Evaluate an Organisations Financial Performance and FNSACCT501B (ACCT5240C) Provide Business and Financial Performance Information.

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 spreadsheets

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


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 and develop spreadsheets

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


Represent numerical data in graphic form

Performance Criteria:

5.1.Determine style of graph to meet specified requirements and manipulate spreadsheet data if necessary to suit graph requirements
5.2.Create graphs with labels and titles from numerical data contained in a spreadsheet file
5.3.Save, view and print graph within designated time lines


Use spreadsheets

Performance Criteria:

4.1.Enter, check and amend data in accordance with organisational and task requirements
4.2.Import and export data between compatible spreadsheets and adjust host documents, in accordance with software and system procedures
4.3.Use manuals, user documentation and online help to overcome problems with spreadsheet design and production
4.4.Preview, adjust and print spreadsheet in accordance with organisational and task requirements
4.5.Name and store spreadsheet in accordance with organisational requirements and exit the application without data loss or damage

Learning Outcomes

Details of Learning Activities

Activities may include a variety of methods such as tests, workbooks, presentations, class exercises, case studies, assignments, group work, etc.

Teaching Schedule

WeekWeek CommencingTopicAssessment
1Feb 8
 Introduction –Financial management functions / Goal: Profit maximization v Maximise share holders wealth.
Leverage effects on profits, Operating v Financial leverage, capital structure and earnings per share. Spreadsheet revision
2 Feb 15
 Long term planning. Pro forma statements
Financial analysis: Introduction to ratios, areas of performance, Trend analysis and benchmarks, Liquidity ratios.
Develop and prepare spreadsheets
3 Feb 22
 Long term planning. Pro forma statements use with Internal growth rate, Sustainable growth rate.
Financial analysis; Profitability, Gearing and Valuation ratios
Standardise spreadsheets
4 March 1
 Short term planning - working capital requirements / management
Efficiency / utelisation ratios, operating and cash cycles.
Business Plan: Choosing a business idea / setting key business objectives.
Spreadsheet charts
5 March 8
 Short term planning – Accounts receivable management.
In class consultation for assignment one.
Business Plan: Evaluating the business idea.
Spreadsheet logical functions.
 1st meeting with clients (assignment two)
6 March 15
 Short term planning – Inventory management EOQ, reorder points.
In class consultation for assignment one.
Business Plan: Assessing Business Opportunities.
Use spreadsheets
 Assignment One
7 March 22
 All of the above further exercises to consolidate and integrate topics.
Business Plan: Marketing.
Use spreadsheets
8 March 29
  April 5
 Mid Semester Break 
9 April 12

 Weighted average cost of capital
Business Plan: Financial Plan. Standardise and use spreadsheets

10 April 19
 Weighted average cost of capital (WACC); components of WACC, calculation and application of WACC.
Business Plan: Balanced score card. Use Spreadsheets
 2nd meeting with clients (assignment two)
11 April 26
 Long term planning. Financial math and net present value revisited. Cash flow construction for capital budgeting net present value applications. Use spreadsheets
12 May 3
 Long term planning. Financial math and net present value revisited. Cash flow construction for capital budgeting net present value application. Use spreadsheets  
13 May 10
 Risk in capital budgeting
Risk adjusted discount rate, Systematic risk, Various approaches to evaluating risk. Use spreadsheets
 Presentations to clients (assignment two)
14 May 17
 Accounting for leases. Operating Vs Financial Leases and differences in accounting treatment.
In class consultation assignment three. Use spreadsheets
15 May 24
 Evaluation of leases, Borrow and buy Vs Lease.
In class consultation assignment three. Use spreadsheets
16 May 31
17 June 7
 Deferred assessments. Resubmissions. Feedback
 Assignment Three

Learning Resources

Prescribed Texts

Clive Wilson, Bruce Keers, Andrew Medlen, Brain Walters.
Financial Management Principles and Applications, 5th Edition.
Pearson / Prentice Hall.



Other Resources

Lecture materials, Class exercises notes on DLS

Overview of Assessment

Assessment may incorporate a variety of methods including technical requirements documentation, homework, assignments, group and/or individual projects, in class exercises, written and practical tests, problem solving exercises, presentations, direct observation of actual and simulated work practice, presentation of a portfolio of evidence which may comprise documents, and/or photographs and/or video and audio files, review of products produced through work-based or course activities.

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

Assessment Tasks

2 tests at 15% each = 30%
Assignment 1, 25%. Assignment 2, 25%. Assignment 3, 20%.
To gain overall competency, students must show competency in each assignment. A graded result will only be supplied where in addition to competent completion of assignments both tests are completed and the over all cumulative score of all assessments is 50% or greater.

Assessment Matrix

Course Overview: Access Course Overview