Course Title: Develop and use complex spreadsheets

Part B: Course Detail

Teaching Period: Term2 2016

Course Code: COSC6185C

Course Title: Develop and use complex spreadsheets

School: 650T Vocational Business Education

Campus: City Campus

Program: C5347 - Diploma of Accounting

Course Contact: Ben Sethia

Course Contact Phone: +61 3 9925 5982

Course Contact Email: ben.sethia@rmit.edu.au


Name and Contact Details of All Other Relevant Staff

Name: Helen Mustafa

Email: helen.mustafa@rmit.edu.au

Hemant Goel

hemant.goel@rmit.edu.au

 

 

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

None

Course Description

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.


National Codes, Titles, Elements and Performance Criteria

National Element Code & Title:

BSBITU402 Develop and use complex spreadsheets

Element:

1. Prepare to develop spreadsheet

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 of data entry, storage, output, reporting and presentation requirements

1.4 Apply work organisation strategies and energy and resource conservation techniques to plan work activities

Element:

2. 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 task specifications

2.4 Test formulae to confirm output meets task requirements

Element:

3. Automate and standardise spreadsheet operation

Performance Criteria:

3.1 Evaluate tasks to identify those where automation would increase efficiency

3.2 Create, use and edit macros to fulfil requirements of 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

Element:

4. 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 application without data loss or damage

Element:

5. 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 timelines


Learning Outcomes



Details of Learning Activities

A range of learning activities are planned for this course including self-paced and collaborative classroom based activities.

The self-paced activities will be delivered thought various technology platforms and include your contribution to wikis and discussion threads, reflective journals, quizzes and interactive sessions.


The collaborative classroom based activities will include group discussion, group problem solving activities and opportunities to practice your skills in a simulated/real workplace environment.
We expect you to participate and contribute in all scheduled learning activities.
 


Teaching Schedule

Induction Session


Prior to training commencement a program level induction session will be conducted that comprises the following:

  • Program overview and requirements
  • MyRMIT/Blackboard
  • Overview of assessment requirements
  • Pre-Training Review including:
    • Recognition of Prior Learning and Credit Transfers
    • Assessment of current skills and knowledge
  • Competency/Grading Criteria
  • Plagiarism
  • Appeals
  • Extensions
  • Feedback
  • Privacy
  • Submission requirements
  • Resubmission policy
  • Where to get support
  • Student responsibilities 

 The Teaching Schedule for this course is as follows but please note that it is subject to change.

     Week Week Commencing Topics                                                             Assessment   
 1  04/07/16 Ergonomic principles
Time Plan: incorporating review dates (pitstop assessments) Setting milestones. Establish criteria for gauging progress e.g. number of meetings, minimum attendance, meeting agreed dates.
Organising meeting times for review and feedback to/from client.
TASK 1TL: Time plan to be submitted to facilitator in class by second class of the week for facilitator approval. You will receive feedback on this assessment in class at the time you meet the assessor for the approval of the plan. The plan will need to be uploaded into Blackboard by end of week. If the plan was not approved in class, you will receive feedback via Blackboard (gradebook) in week 3.
 2  11/07/16  Absolute Vs Relative cell referencing. Model Building using budget exercise as illustration.
Simple model building using PMT CUMPIT and PV functions
 
 3 18/07/16  Model building to produce forecast (Pro Forma) statements using % of sales method. Income Statement and Balance Sheet only.  
 4  25/07/16  Model building to produce forecast (Pro Forma) statements using % of sales method. Income Statement, Balance Sheet and Cashflow Statement  
 5  01/08/16  Quantitative forecasting using excel linear time series. Determining a forecast growth rate.
Qualitative forecasting: Environmental Analysis. Adjusting the quantitative outcome
Macros
Assessment consultation and feedback.
 Pit stop review of Major Assignment Part A
 6  8/08/16  Business Plan related compliance matters; incorporating GST in the financial model.
Ratios: Profitability / Efficiency / Stability
Assessment consultation and feedback.
 Pit stop review of Major Assignment Part A
 7  15/08/16  Assessment task consultation and feedback  Pit stop review of Major Assignment Part A
 8 22/08/16  Assessment task consultation and feedback.  Pit stop review of Major Assignment Part A
ONLINE TEST 1. IN CLASS SUPERVISED TEST
    mid semester break  
 9 5/09/16  Concept of Gearing / Leverage
Sources of Finance
 Submission of Major Assignment Part A: Both business plan document and spreadsheet model need to be uploaded into Blackboard gradebook by COB Monday of this week. Feedback will be provided over the weeks 10, 11 & 12, resubmission will need to occur by end of week 14
10 12/09/16  Assessment consultation and feedback.  Pit stop review of Major Assignment Part B
 11 19/09/16  Assessment consultation and feedback.  Pit stop review of Major Assignment Part B
 12 26/09/16  Assessment consultation and feedback.  Pit stop review of Major Assignment Part B
 13 03/10/16  Assessment consultation and feedback.  Submission of Major Assignment Part B: Both the Report document and the Spreadsheet financials need to be submitted onto the Blackboard gradebook by COB Friday of this week. Feedback will be provided by week 14, resubmission will need to occur by end of week 15.
 14 10/10/16  Assessment consultation and feedback.  RESIT ONLINE TEST 1. Re-submission (if applicable) of Major Assignment Part A: Both business plan document and spreadsheet model need to be uploaded into Blackboard gradebook by COB Friday of this week
CHECK YOUR "MY GRADES" ROUTINELY FOR FEEDBACK AND RESUBMISSION REQUIREMENTS
15 17/10/16  Assessment consultation and feedback.  Re-Submission of Major Assignment Part A: Both the Report document and the Spreadsheet financials need to be submitted onto the Blackboard gradebook by COB Friday of this week.
CHECK YOUR "MY GRADES" ROUTINELY FOR FEEDBACK AND RESUBMISSION REQUIREMENTS
 16 24/10/16  Relevant facets of financial services legislation. Ethics. Materials provided online (Blackboard)
Deferred and resubmitted assessments
 ONLINE TEST 2: unsupervised with unlimited attempts. Must be completed by COB Friday of this week.
CHECK YOUR "MY GRADES" ROUTINELY FOR FEEDBACK AND RESUBMISSION REQUIREMENTS

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 bit also the amount of effort required to undertake, evaluate and complete all assessment requirements, including any non-classroom activities.

Please note, it is anticipated that two collaboration sessions will be provided during the course for flexed students. The weeks in which these will occur will be announced on Blackboard during the course of the semester. It is the responsibility of the student to have the required technical hardware necessary to participate in these sessions.


Learning Resources

Prescribed Texts


References


Other Resources

No Text has been prescribed for this cluster. All materials will be supplied through Blackboard. PLEASE NOTE: ALL materials for this cluster should be accessed via the PROGRAM shell for Diploma of Accounting under the "Financial Management" handle.
 

FOR ONLINE COLLABORATE SESSION, PLEASE ENSURE YOU HAVE JAVA INSTALLED ON YOUR COMPUTER https://java.com/en/download/index.jsp AND A HEADSET AND MICROPHONE


Overview of Assessment

In order to achieve competency in this unit, you must provide:

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

You are advised that you are likely to be asked to personally demonstrate your assessment work to your teacher to ensure that the relevant competency standards are being met.

Feedback

Feedback will be provided throughout the semester in class and/or online discussions.  You are encouraged to ask and answer questions during class time and online sessions so that you can obtain feedback on your understanding of the concepts and issues being discussed. Finally, you can email or arrange an appointment with your teacher to gain more feedback on your progress.

You should take note of all feedback received and use this information to improve your learning outcomes and final performance in the course. 


Assessment Tasks

Overview of Assessment

This course is co-delivered and co-assessed as a cluster of units. This cluster of units is called the FINANCIAL MANAGEMENT cluster. The competencies within this cluster include; 

ACCT5378C Provide Financial & Business Performance Information

ACCT5383C Prepare Financial Forecasts and Projections

COSC6185C Develop and Use Complex Spread Sheets

The assessments for this course have been designed to allow participants  apply their learning to particular simulated work scenarios and demonstrate their competence in a variety of ways.

TASK 1 TIME LINE (Due Date:WEEK 2)

 

A group task, 2 members per group same group members as Major Assignment Part A (below).
When you work in the financial services industry you will be required to complete work within a deadline. In this task learners are required to prepare an action plan related to the Major Assignment Parts A and B assessment tasks (see below) The learner will need to prepare a schedule outlining proposed key dates covering task stage completion and task final completion / submission and report to client. Proposed team and client meeting dates, these will be your pit stops (see below) must also be included in the time plan. The time line will need to allow for contingencies and review of work. This time plan will be used to monitor the learners progress towards completion of the above tasks.
The Pit Stops together with the Time Line prepared by the learner (see above) will be used to formally monitor the learners progress throughout the semester. They will also serve as proxy meetings between service provider (learner) and client (facilitator) These meetings will be used to establish clarification and or modification of relevant client objectives. They will also be used to assess progress vis a vis time plan. Learners are expected to discuss various financial options / processes open to the client, and briefly outline relevant local state and federal regulations particularly salient to the client business.

Part A of the Major Assignment will have two (2) pit stop reviews
Part B of the Major Assignment will have one (1) pit stop review.
To be deemed competent in this task you must do the following:
With due regard to firm policies and client requirements organise a time plan showing the following:
a. Meeting dates (client and team member) are clearly indicated in the time plan
b. Actions / processes along with stage and final completion / submission dates are shown in the time plan
c. Time plan incorporates scheduled review of tasks.
d. The time plan is to be completed using an excel spread sheet
Where required a time plan is adjusted during the semester to cater for client requirement or other contingencies.

TASK 2 MAJOR ASSIGNMENT part A and B (Due Date: Part A WEEK 9 Part B  WEEK 13)

Part A
Part A requires students to work in groups of two (2) only.
Part A comprises the completion of a summarised Business Plan and Financials Spread Sheet Model
In the financial services industry you will frequently be approached by various clientele groups requesting information on a wide range of financial / business issues. You will need to provide the client with information which will meet the client’s needs; information which will assist the client in achieving a desired objective.
This task involves completing a Business Plan. Upside Down Ski Club (UDSC) an incorporated association (the client) will provide you, with detailed information of the UDSC ownership / management structure, objectives and operations, together with past financials and an outline of the market segments together with relevant strengths, weaknesses, opportunities and threats. You are to take this information and incorporate it into a business plan.
A significant section of any business plan is the financials. The financials include a forecast Income Statement, Cash Flow Statement and Balance Sheet. You will be creating a spreadsheet model which will generate the forecast financials, which will then be imported into the business plan.
The Business Plan will involve the following.
• Identifying client objectives and incorporate them into the business plan.
• Legislation / regulations local, state or federal applicable to the client / case is incorporated into the business plan.
• Accessing information from the relevant authorities (e.g. Client. Statutory bodies; ATO, ASIC. Financial Institutions; Banks) in order to verify reliability and accuracy of data and correct treatment of that data.
• Complete the financials section of the business plan. This will involve generating an Income Statement (monthly for the year), Balance Sheet, and Cash Flow Statement (monthly for the year) which needs to comply with standard generally accepted accounting principles, practice, and relevant legislative requirements.
• Forecast Income Statement, Balance Sheet, and Cash Flow Statements which comply with client reporting policies.
• Forecast Income Statement, Balance Sheet, and Cash Flow Statements comply with standard generally accepted accounting principles and practice.
• Ensure that where assumptions / parameters are provided by the client, you apply these assumptions to the forecast / financials
• Where no assumptions are provided, you must, on an item needs basis, provide an appropriate assumption to generate a forecast (output).
• Use the spreadsheet to conduct a quantitative trend analysis
• Build an appropriately designed excel spreadsheet model to generate the forecast financial statements for the club
• The spreadsheet must meet client requirements
• Apply the information provided from the client into the business plan and complete the business plan together with the spreadsheet model within the required time frame
 

Part B
To be completed individually.
Part B continues the dealings with the above client in Part A, and comprises the completion of a spread sheet model (finance plan) and a client report.
This task involves providing the management of UDSC with a plan to finance the proposed refurbishment / capital expansion. The management of UDSC has proposed a multi stage refurbishment of current facilities together with expansion of current building structure over the next five years. The details of the works together with the associated cost and planned stage of works and completion are provided. Given specific objectives you are required to provide the management with a tailored way of financing this venture. Where is the money coming from?
The finance plan and accompanying report will involve the following:
• Identify clearly the clients objectives
• Identify all relevant sources of finance available and limits on those finances
• Provide a recommended funding arrangement for the works which meets the client’s objectives
• Identify and inform on any legislative / contractual restrictions which may apply with relation to funding options
• Inform clients of any legislative / contractual obligations with regard to reporting any changes in funding arrangements to the relevant party / authority.
• Inform client of financing arrangements impact on entity financial stability and profitability.
• Development of a financial model to perform the relevant financial calculations.

 

TASK 3 Online Test One (Due Date: In week commencing WEEK 8)

The online test will cover the process of completing a workable spread sheet model which abides by best practice principles. The model will then be used to generate a complete set of Pro forma statements (Income Statement, Balance Sheet, and Cash Flow Statement) The model must be capable of utilising client offered objectives and assumptions. The model output will then be used to provide accurate and timely information to the client, this is demonstrated by selection of correct multiple choice option. This is a supervised test.

TASK 4. Online Test Two (Due Date: WEEK 16)

UDSC is an incorporated association but it could easily also have selected registering as a company limited by guarantee. This online multiple choice short answer test is required to be completed within a designated time through the Black Board, but may be accessed remotely.
The test will cover differences / similarities related to the two entity types, with regards to management; structure; role and duties, member’s liability, applicable legislation relevant overriding statutory authority (to whom is UDSC answerable) under what circumstance would either option be selected, and related obligations with emphasis on Corporations and Consumer Law.
 

 

You must successfully complete all assessment tasks to achieve competency.


Assessment Matrix

Other Information

Submission Requirements

You should:

• Ensure that you submit assessments on or before the due date.
• Always retain a copy of your assessment tasks. (hard copy and soft copy)
• When you submit work for assessment at RMIT University you need to use a cover sheet that includes a declaration and statement of authorship. You must complete, sign and submit a cover sheet with all work you submit for assessment, whether individual or group work. On the cover sheet you declare that the work you are presenting for assessment is your own work. An assignment cover sheet for submission of each assessment task is available on blackboard.
• Each page of your assessment should include footer with your name, student number, the title of the assessment, unit code and title and page numbers. For example, Callie Harvey, 324567, Task 2, OHS2345C Ensure safe workplace, Page 1 of 10.


Late Submission Procedures


You are required to submit assessment items and/or ensure performance based assessment is completed by the due dates.


If you are prevented from submitting an assessment item on time, by circumstances outside your control, you may apply in advance to your teacher for an extension to the due date of up to seven calendar days. <check links are still current>


More Information: http://www.rmit.edu.au/students/assessment/extension


Form to use: http://mams.rmit.edu.au/seca86tti4g4z.pdf


Where an extension of greater than seven days is needed, you must apply for special consideration. Applications for special consideration must be submitted no later than two working days after the assessment task deadline or scheduled examination. <check links are still current>
 

More Information: http://www.rmit.edu.au/browse;ID=g43abm17hc9w


Form to use: http://mams.rmit.edu.au/8a5dgcaqvaes1.pdf


Resubmissions (VET Programs):


If you are found to be unsuccessful in a Course Assessment Task you will be allowed one resubmission only. Your teacher will provide feedback regarding what you need to do to improve and will set a new deadline for the resubmission. The highest grade you will receive if your resubmission is successful is “CAG”.


If you are still not meeting the assessment requirements you must apply to your Program Manager in writing outlining the steps you will take to demonstrate competence in your course. Your submission will be considered by the Program Team and you will be advised of the outcome as soon as possible.


Adjustments to Assessment


In certain circumstances students may be eligible for an assessment adjustment. For more information about the circumstances under which the assessment arrangements might be granted please access the following website: <check link is still current>
More Information: http://rmit.edu.au/browse;ID=7usdbki1fjf31

Marking Guide (Competency):

You must demonstrate that you have all the required skills/knowledge/elements in the unit of competency you are studying.

You will receive feedback on each assessment task that will inform you about your progress and how well you are performing.


Marking Guide (Grading)

After achieving competency we then grade your performance in the unit and you will achieve one of the following grades:

Final Grades 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


Further information regarding the application of the grading criteria will be provided by your teacher.
 

Course Overview: Access Course Overview