Course Title: Develop and use complex spreadsheets

Part B: Course Detail

Teaching Period: Term1 2012

Course Code: COSC5946C

Course Title: Develop and use complex spreadsheets

School: 650T TAFE Business

Campus: City Campus

Program: C5260 - Diploma of Accounting

Course Contact : Angelo Sellan

Course Contact Phone: +61 3 99255460

Course Contact Email:angelo.sellan@rmit.edu.au


Name and Contact Details of All Other Relevant Staff

Ian Nash

Phone: 99255733

ian.nash@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 performance outcomes, skills and knowledge required to use spreadsheet software to complete business tasks and to produce complex documents. No licensing, legislative, regulatory or certification requirements apply to this unit at the time of endorsement.




 


National Codes, Titles, Elements and Performance Criteria

National Element Code & Title:

BSBITU402A Develop and use complex spreadsheets

Element:

1.Prepare to develop spreadsheet
2. Develop a linked spreadsheet solution
3. Automate and standardise spreadsheet operation
4. Use spreadsheets
5. Represent numerical data in graphic form

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

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

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


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

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


Learning Outcomes



Details of Learning Activities

This course is co-delivered as part of a cluster with ACCT5315C Prepare Financial Forecasts & Projections and ACCT5310C Provide Financial & Business Performance Information. As a result, three course guides will be prepared but only the Elements, Performance Criteria, Critical Aspects, Knowledge and Skills requirements for each course will be specific to each course (guide). The remainder of the information such as "Teaching Activities" "Teaching Schedule" "Resources" and "Assessments Tasks & Matrix" will be identical on all three course guides
Importantly, the attached information is consistent with the unit mapping document for this cluster
Using various materials along with facilitator led discussion, students will be progressively introduced to the key aspects to creating a business plan for a specific client. This will involve outlining a time line which will serve as a plan of action providing key dates for monitoring of progress and meetings with clients for progressive briefings / feedback. Various forecasting methodologies along with identification of relevant assumptions and compliance with legislative requirements at local, state, and federal level will also be covered and incorporated into the business plan. The completed business plan will serve as a template document providing financial and business performance information for the specific users needs. Spread sheets will be incorporated specifically in the financials section of the business plan, clients needs together with business plan requirements will be analysed to allow development of spread sheet solutions designed to enhance decision making. This course will involve both theory and practical learning activities.
As part of this cluster , in-class activities will account for 96 of the 180 nominal hours allocated to these co-delivered cluster/courses and will include class work on theory, practical and computer work in the computer labs, together with work on portfolio exercises and assignment. Please note that in-class activities includes assessment. This will involve observation and one on one questioning of prescribed task execution. The remaining 84 of the 180 nominal hours are for self directed learning and should be taken up with work on any course study activity and the assignment outside of scheduled class time. Please note that the format of the classes will be a total of six hours per week (full time students)

Critical Aspects of Evidence

Evidence of the following is essential:
• developing complex spreadsheets
• developing graphical representations of data contained in spreadsheets.

Skills

literacy skills to interpret and evaluate the purposes and uses of various features of spreadsheets and to use a variety of strategies for planning and reviewing own work
• proofreading and editing skills to check for accuracy and consistency of information by consulting additional resources
• numeracy skills to collate and present data, graphs and related references.

Knowledge

• advanced functions of spreadsheet software applications
• impact of formatting and design on the presentation and readability of data
• key provisions of relevant legislation from all forms of government, standards and codes that may affect aspects of business operations, such as:
• anti-discrimination legislation
• ethical principles
• codes of practice
• privacy laws
• occupational health and safety
• organisational policies and procedures.


Teaching Schedule

The teaching schedule below incorporates the three clustered or co delivered units.

WeekDate Class Activity / Content Assessment
16/2/2012• Course requirements
• Course support documents
• Blackboard
• Overview of assessment requirements including Recognition of Prior Learning and Credit Transfers
• Grading
• Plagiarism
• Appeals
• Extensions
• Feedback
• Privacy
• Submission requirements
• Resubmission policy
• Where to get support
• Student responsibilities
Revision Excel: Cell referencing.
Ergonomic principles
 Evidence Portfolio: TASK EP1. Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date.
213/2/2012 Time Plan: incorporating review dates (staggered assessment) 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.
Model Building using budget exercise as illustration.
 TASK 1TL: TIme plan to be submitted to facilitator in class by second class of the week for facilitator approval. If resubmission / adjustment is required; plan will need to be resubmitted by last class of the week.
320/2/2012Simple model building using PMT CUMPIT and PV functions
Model building to produce forecast (Pro Forma) statements using % of sales method.
 Evidence Portfolio: TASK EP1ss and TASK EP2ss. Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date.
427/2/2012Model building to produce forecast (Pro Forma) statements using % of sales method.  Evidence Portfolio: TASK EP2 and TASK EP3ss. Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date.
55/3/2012 Quantitative forecasting using excel linear time series
Generating a compound growth rate (Geometric Mean)
 Evidence Portfolio: TASK EP3 and TASK EP4ss Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date.
612/3/2012Qualitative forecasting: Environmental Analysis
Assignment consultation and feedback.
ASSESSMENT Task PS1 (Pitstop assessment of Task1AHA & Task1TL)
719/3/2012Ratios: Profitability / Efficiency / Stability Assignment consultation and feedback.ASSESSMENT Task PS1 (Pitstop assessment of Task1AHA & Task1TL)
Evidence Portfolio: TASK EP4. Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date. 
 

826/3/2012Assignment consultation and feedback.
Ratios: Use in forecasting
ASSESSMENT Task PS1 (Pitstop assessment of Task1AHA & Task1TL)
92/4/2012Concept of Gearing / Leverage
Sources of Finance
 Submission TASK 1AHA Feedback will be provided by week 10, resubmission will need to occure by end of week 11
   Break 
1016/4/2012Assignment consultation and feedback.ASSESSMENT Task PS2 (Pitstop assessment of Task1BHA & Task 1TL)
11 23/4/2012 Assignment conslutation and feedback.ASSESSMENT Task PS2 (Pitstop assessment of Task1BHA & Task 1TL)
12 30/4/2012Companies: Types / Regulatory Framework / Incorporatine relevant compliance matters into business plan Submission task 1BHA Feedback will be provided by week 13, resubmission will need to occur by end of week 14.
Evidence Portfolio: TASK EP5. Although all evidence portfolio pieces do not need to be submitted till week 15. It is expected for assessment and feedback purposes for this task to be completed by the last class of this week. Your facilitator will ask to sight this TASK anytime after this date.
13 7/5/2012 Assignment consultation and feedback. ASSESSMENT TASK PS3 (Pitstop assessment of Task 2AHA & Task 1TL)
14 14/5/2012 Assignment consultation and feedback. ASSESSMENT TASK PS3 (Pitstop assessment of Task 2AHA & Task 1TL)
Submission task 2AHA by last class of the week. Feedback for task 2AHA will occur in week 15, and any resubmissions must be made by end of week 16. 
 
15 21/5/2012 Assignment consultation and feedback. Submission of Evidence of Portfolio

16

 28/5/2012 Deferred and resubmitted assessments  

 


Learning Resources

Prescribed Texts


References


Other Resources


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

You are required to complete 8 tasks. You must successfully complete all 8 tasks to be deemed competent in this unit.
Task 1AHA Task 1BHA and Task 2AHA (Business Plan)
These tasks all involve completing a business plan.
You are given a brief from the Langi-Taan Ski Club (LTSC) an Incorporated Association.

LTSC is in the process of renewing its lease grant provided by the government. In order to do this it must prepare a Business Plan. You, together with a colleague have been selected by your firm to undertake this task.

Using the information from the comprehensive brief along with meetings with the client (LTSC); you are required to complete the business plan from the template provided you on blackboard.
TASK 1AHA Business Plan (Incorporated Association) due by end of week 9
A group task, 2 members per group. This first task involves completing a business plan for the LTSC under its current Incorporated Association status. The brief is quite extensive and provides detailed information of the LTSC ownership / management structure, objectives and operations, together with revenue sources 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 the business plan template which is supplied on the DLS.

This information should be used to develop the forecast financials required in the business plan.
To be deemed competent in this task you must cover the following:
• Ensure that the client objectives are clear.
• Ensure that any legislation / regulations local, state or federal applicable to the case is incorporated into the business plan provided to the client
• Ensure that where required you access data / information from the relevant authority.
• Forecast Income Statement, Balance Sheet, and Cash Flow Statements comply with standard generally accepted accounting principles and practice.
• Forecast Income Statement, Balance Sheet, and Cash Flow Statements comply with organisations policy.
• 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 trend analysis incorporating quantitative / qualitative variables for forecasting club revenues.
• Build an appropriately designed excel spreadsheet model to produce the forecast financial statements for the club
• Apply the information provided from the brief into the business plan and complete the business plan within the required time frame

TASK 1BHA Long term refurbishment finance plan due by end of week 12
A group task, 2 members per group, same group members as above. This task involves providing the management of LTSC with a plan to finance the 5 year refurbishment plan as detailed in the brief. The management of LTSC 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?

To be deemed competent in this task you must cover 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 clients objectives
• Build and excel spreadsheet model which shows the impact of any financing arrangement on the financials of the club
TASK 2AHA Business Plan (Company) due by end of week 14
A group task, 2 members per group, same group members as above. This task requires for you and your team member to replicate TASK 1AHA above, but in this case you need to approach the task from the point of view of a company. Or assume that LTSC were to convert to a company. The organisation would now be operating as a business rather than a not for profit association, a number of things will change. These changes will need to be incorporated into the new business plan, again utilising the template provided.

To be deemed competent in this task you must do the following:
The requirements for this task as those for TASK 1AHA above with the following difference:
• Identify and communicate to the client the additional business and compliance issues related to a Company
• Adjust the business plan to incorporate these additional business and compliance burdens
o Example:
 Requirement for ABN
 Tax: PAYE, GST, BAS
 Company Registration
 Business names registration
 Corporations Act
 Superannuation
 Payroll tax
 Review of service
TASK TL1 Time Line Assessment. Submitted to facilitator in class by second class of the second week

A group task, 2 members per group same group members as business plan. This task requires the learners to prepare a plan indicating the course of action in order to complete the following Business Plan related assessment tasks 1AHA, 1BHA, and 2AHA . The learner will need to prepare a schedule outlining proposed key dates covering task stage completion and task final completion and report to client. Proposed team and client meeting dates (PS1, PS2, and PS3, see below; Pit Stop Assessments) must also be included in the time plan. Some allowance for contingencies should also be planned for. This time plan will be used to monitor the learners progress towards completion of the above tasks.
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:
o Meeting dates (client and team member) are clearly indicated in the time plan
o Actions / processes along with stage and final completion dates are shown in the time plan
o Time plan allows for review of tasks.
o Where required a time plan is adjusted to cater for client requirement or other contingencies
TASKS PS1, PS2, & PS3. Pit Stop Assessments. Pit stop assessments will run over a number of weeks refer to teaching schedule above for applicable weeks.

A group task, 2 members per group same group members as business plan. The Pit Stop Assessments together with the Time Line (TASK 1TL) 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.

The various pit stop assessments relate to the following business plan related tasks. Each pit stop assessment relates to a separate business plan related task as follows.

PS1 will serve as a pit stop assessment for TASK1AHA
PS2 will serve as a pit stop assessment for TASK1BHA
PS3 will serve as a pit stop assessment for TASK2AHA

For detail of assessment criteria please refer to Evidence Sheets for Task PS1 Task PS2 and Task PS3.
TASK 1 Evidence Portfolio. Completed portfolio to be submitted in week 15
Individually based assessment. The Evidence Portfolio provides the formative assessment of the course and comprises a number of exercises denoted by the following "TASK EP" 1 or 2...etc or "TASK EPss" (for spread sheet specific tasks)1 or 2...and so on as the case may be. The evidence portfolio includes nine (9) individual hurdle tasks (5 general tasks and 4 excel spreadsheet specific) Each EP Task will focus on a particular topic. The learner will need to progressively finish these tasks as the various topics are completed. The "assessment" column of the teaching schedule above outlines the dates by which these tasks need to be completed. Your facilitator will ask to sight these tasks during class any time after this date. You will be asked to orally explain or demonstrate how you solved the task. These short sessions should be used for feedback purposes. You will then have the opportunity to review and adjust your task if required. All tasks should be saved and backed up securely. The Evidence Portfolio with all included tasks should be submitted by the last class of week 15. ALL individual evidence portfolio pieces must be satisfactorily completed (competent) to achieve and overall competent for this assessment.
Submission Requirements

Assessment tasks need to be submitted via Blackboard.

You should:

• Ensure that you submit assessments on or before the due date. If your performance in the assessment is affected by unexpected circumstances, you should consider applying for Special Consideration. Information on the process and application forms is available at http://rmit.edu.au/browse;ID=ls0ydfokry9rz website.
• 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 work for assessment 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, Angelo Sellan, 55460, Task 2HA, ACCT5317C Evaluate an Organisations Financial Performance, Page 1 of 10.


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:

1. LEVEL OF INDEPENDENCE, INITIATIVE, ENTERPRISE AND PERFORMANCE OF WORK TASK

We are looking for 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 approach to your tasks
2. DEMONSTRATED BREADTH OF UNDERPINNING KNOWLEDGE AND A WILLINGNESS TO CONTINUE LEARNING

We are looking for depth of understanding of the key concepts and knowledge required in completing your tasks. You should be able to demonstrate a thorough understanding of all applicable requirements in all the assessment tasks.

3. TECHNIQUES & PROCESSES, TECHNOLOGY SKILLS AND PROBLEM SOLVING

We are looking for appropriate use of technology to assist in presenting all tasks clearly and suitable for the intended audience.
4. WORK ORGANISATION, PLANNING AND SELF MANAGEMENT

We expect to see timely submission of all required assessment tasks.


5. COMMUNICATION, PEOPLE NETWORKING, LANGUAGE AND INTERPERSONAL SKILLS AND TEAMWORK

We expect to see contributions to relevant discussions and scheduled collaborative sessions. In addition your team tasks will provide opportunities to demonstrate how well you work in a team.
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.


Assessment Matrix

Course Overview: Access Course Overview