Course Title: Develop and use complex spreadsheets

Part B: Course Detail

Teaching Period: Term2 2011

Course Code: COSC5977C

Course Title: Develop and use complex spreadsheets

School: 350T Fashion & Textiles

Campus: Brunswick Campus

Program: C6086 - Advanced Diploma of Fashion and Textiles Merchandising

Course Contact : Russell Edis

Course Contact Phone: +61 3 99259496

Course Contact Email:russell.edis@rmit.edu.au


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

Operate Computing Technology In A Textiles, Clothing And Footwear Workplace

Course Description

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.


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

Performance Criteria:

1.1 Design specifications are identified and interpreted
1.2 Suitable programs and equipment are identified
1.3 Garment considerations are identified
1.4 Production requirements are identified
2.1 Design programs and equipment are used to create required designs, patterns or production specifications
2.2 Design programs and equipment are used to edit required designs, patterns or production specifications
2.3 Design programs and equipment are used to test required designs, patterns or production specifications
2.3 Design programs and equipment are used according to OH&S practices
3.1 Outputs generated using electronic design programs are assessed against design specifications and production requirements
3.2 Outputs are presented to appropriate personnel to assess accuracy and completeness
3.3 Improvements are made as required
4.1 Designs, patterns or production specifications are created, stored, organised and protected
4.2 Designs, patterns or production specifications are used, maintained and stored


Learning Outcomes



Details of Learning Activities

Use technology to create complex spreadsheets.
Design appropriate spreadsheet models using functions and features to achieve required outcomes in a timely manner.
Problem solve.
Use on-line resources to research.
Teaching Schedule
1 Advanced Excel – Building Models, Store Allocation
Creating validation rules for data entry input create descriptions which display when moving to cells.
Conditional statements.

OTB Budget formulas, INT, Rounddown, Roundup function in the context of distribution of budget.
Importance of checking for accuracy.
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1, 4.3, 4.4, 4.5




Teaching Schedule

1 Advanced Excel – Building Models, Store Allocation
Creating validation rules for data entry input create descriptions which display when moving to cells.
Conditional statements.

OTB Budget formulas, INT, Rounddown, Roundup function in the context of distribution of budget.
Importance of checking for accuracy.
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1, 4.3, 4.4, 4.5



2 OTB Budget

Preview, adjust and print spreadsheet using PDF file format in line with sustainability requirements

BOM Costing spreadsheets.
1.1, 1.2, 1.3. 1.4, 2.1, 2.2, 2.3, 2.4, 4.1, 4.3, 4.4, 4.5



3 OFFSET and MATCH Functions
Exercise –Selecting Flight Schedule Exercise
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4



4 INDEX and MATCH Functions
Exercise –Selecting Flight Schedule Exercise
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.5



5 Conditional Formatting using complex criteria

Create Graphs



Import and Export data between compatible spreadsheets
4.5, 5.1, 5.2, 5.3



6 Combo Boxes, List Boxes ActiveX Controls.
Combo Box Exercise 1 and 2
List Box 1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1, , 4.5



7 YEP Presentations



8 Combo Boxes, List Boxes ActiveX Controls.
Combo Box Exercise 1 and 2 List Boxes
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1, , 4.5



9 Database Advance Filters
Revision using macros to automate operations
Searching on two variables from one field AND type operator and OR.

Advanced Filter by selecting data from a list on another worksheet.
Copy and Paste data. Using formula.
Using a combo box to create the criteria properties
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1, 4.5



10 Filter by selecting data from a list on another worksheet.
1.1, 1.2, 1.3. 1.4, 2.1, 2.3, 2.4, 4.1



11 Macros – Moving Data, Cell Pointer movement Using Ctrl + Arrow keys, Appending data to NAMED cells.
3.1, 3.2, 3.3, 4.1, 4.5

Term Break



12 Macros – Moving Data, Cell Pointer movement Using Ctrl + Arrow keys, Appending data to NAMED cells.

Appending data Exercise Combo Boxes and Macros
Attach macro to combo box. 2.2, 4.2 , 4.5



13 Macros to “hardwire” functions in model
Use functions to calculate data in cells, apply macro to create a value using Paste Special Paste Value.
2.2, 4.2 , 4.5



14 Macros
2.2, 4.2 , 4.5



15 Practical Assessment100%


Learning Resources

Prescribed Texts


References


Other Resources


Overview of Assessment

Practical Test using Excel software


Assessment Tasks

Practical assessment using Excel - 100%.


Assessment Matrix

Course Overview: Access Course Overview