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 |
Performance Criteria: |
1.1 Design specifications are identified and interpreted |
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