Muirfield High School
VET Information Technology
Assessment Task

Topic : ICAITU006B : Operate computing packages

ICAITU006B
(40 hours) / Operate computing packages
Element of Competency / Performance Criteria
1.Use appropriate software /
  1. Requirements of task are determined
  2. Appropriate software is selected to perform task
  3. Software is used to produce required outcome
  4. Documents are saved and stored in appropriate directory

2.Access, retrieve and manipulate data /
  1. Software application is opened
  2. File is determined, opened and is amended according to requirements
  3. Documents are accessed or produced to meet organisational requirements
  4. File is saved in appropriate directory
  5. Applications are exited without loss of data

3. Access and use help /
  1. On line help is determined and used to overcome basic difficulties with applications
  2. Manuals and training booklets are used to solve minor problems
  3. Requests are logged with help desk if requiring further help

4.Use keyboard and equipment /
  1. Occupational health and Safety regulations are followed
  2. Keyboarding is carried out according to organisation guidelines on speed and accuracy

Assessment Component

/

Date

/

Signature

Preliminary Review

/

24/5/2005

Progress Report

/

31/5/2005

Submission Date

/

7/6/2005

Overview

This project has been modeled around a small retail computer outlet, called Bitsn’PCs, operating in a regional centre in an area with a widely spread population of 50,000. Clients are mainly small businesses and individuals whose computer literacy ranges from non-existent to moderately competent.
The purpose of this organization is to provide computer sales and support for a range of hardware, software, networking, Internet and telecommunications.

Personnel/roles responsibilities

The organization employs 6 personnel whose roles and responsibilities are as follows-
Customer Service Manager - Responsible for general running of the organisation including accounts, sales, support personnel welfare etc.
Sales Representatives (3)-
Hardware
Software
Telecommunications/Networking
Each employee is responsible for their particular field, however it is assumed that they have skills in the other areas which can be called on when required.
Each sales rep is responsible for client support in their particular area and training of customers where requested.
IT Support Officer - Responsible for first line customer help desk support including face to face, telephone and Internet enquiries, also responsible for sales and administrative support.

Assignment 1

As IT Support Officer for the above computer outlet, you have been asked by a customer to recommend a personal computer system for a small business.
This PC is situated in the front office of an Accounting Business. The customer would also like some advice on the application software they might need for general office duties, financial documents and presentation work, such as graphics or drawing requirements.
You are required to talk to the software representative in your organization and prepare a fax to be sent to the customer with some suggestions for the above enquiry.
Your organization wishes to keep a record of all interested customers on file to be used for mail-outs on new products, specials or upgrade information. You need to set up the data file, which should include:
Customer No, Customer or Company Name, Address, Contact Name, Phone No, Type of Computer
Add the above new customer to your data file, along with seven other customers (fictitious or from the phone book, friends etc).
Prepare a letter to be merged with the data file for a mail-out. This is a letter of invitation informing your customers about the upcoming ‘computer fair’ (organized by your organization) to be held at the local Town Hall in two (2) weeks time. You are required to make it eye catching and interesting, include the ‘name’, ‘address’, and ‘type of computer’ fields in your mail merge.
To accompany this letter you should include a table, listing some of the Software specials that will be available and a comparison of the regular and special prices.
All these tasks should be saved to your floppy disk in an appropriate directory, with descriptive file names, so they can be retrieved easily for later use.

Assignment 2

As IT Support Officer for the above computer outlet, you have been asked by a customer to troubleshoot their Payroll Spreadhseet.

  1. Create the following spreadsheet.

  1. Re-name the sheet tab to Pay.

The gross pay for John Smith is calculated by multiplying his hours worked by the rate given in cell B3. After entering the formula for John Smith you can fill down for the others in the table. To ensure the rate of pay remains constant, use an Absolute reference for B3 by entering $B$3 in the formula in cell D7.

  1. Use AutoFill to copy the formula down for the rest of the employees.
  2. Enter a formula for calculating the Taxation payable by once again using an Absolute reference for the tax rate.
  3. Complete the column under the heading Taxation.
  4. Complete a column to calculate the net pay, (Net Pay=Gross Pay-Taxation).
  5. Enter formulas in the Totals cells in row 15.
  6. Use the correct functions to calculate the lowest, highest and average values in rows 17, 18 and 19.
  7. Copy the sheet Pay and re-name the copied sheet Pay - Formulas.
  8. Print both worksheets.
  9. Save your work.

______

You were so successful with your help with the payroll sheet, the company has now asked you to prepare the following spreadsheet for Sales.

  1. Create the following spreadsheet.

  1. Enter an IF statement in column D that will calculate a commission of 5% if the total sales are $100,000 or above. If the total sales are below $100,000 the commission rate should be calculated at 2.5%.
  2. Enter appropriate formulas and functions to calculate totals, minimum, maximum and average values.
  3. Protect all cells containing formulas.
  4. Save the workbook as Sales Comm
  5. Re-name the worksheet to Commission
  6. Enter your name and the worksheet name in the footer.
  1. Copy the worksheet named Commission and re-name the copied sheet to Commission – Formulas
  1. Change the Commission – Formulas sheet to formula view and adjust column widths as necessary. Apply gridlines and row and column headings.
  1. Print both worksheets.

Assignment 3

As IT Support Officer for the above computer outlet, you have been asked by a customer to troubleshoot their Company Database

Carol started a commercial cleaning business while she was attending university. She now employs four cleaning staff, and she wants to computerise her business records. Carol wants to start by transferring her client and employee records to Access.

  1. Create a database named Carols Cleaning Co
  2. Design a client table using the field information defined below.

  1. The Phone field requires an input mask.
  2. The Cleaning Day and State fields require validation rules and validation text.
  3. At least one field should contain a default value.
  4. Make the Client ID field the primary key field.
  5. Save the table as Clients.
  6. Enter the following records into the table:

  1. Adjust the column widths appropriately.
  2. Sort the records in primary key order.
  3. Preview the table. Change the orientation to landscape. Print and save.
  4. Create a suitable form to be used for entering data into the table. Format the form appropriately. Save the form as Clients Form
  5. Using the form, add six new records to the database. Use the data shown below, and use your imagination to complete the rest of the details for each record.

  1. Design an employee table using the field information defined below.
  2. Use appropriate default values, validation rules, validation text and an input mask.

  1. Make the Employee ID field the primary key field.
  2. Save the table as Employees
  3. Create a form for the Employee table.
  4. Add the following data to the Employee table.
  1. Create a relationship between the Employee and Client tables and enforce referential integrity.
  2. Preview the table. Change the orientation to landscape. Print and save.

Carol has decided that she would like to use her database to help make management decisions concerning her growing business. She would like the database to do the following:

  • Identify her largest clients.
  • Have a query that she can refer back to as her business grows to track total square metres cleaned and her total income.
  1. Create a query to show all clients with 10,000 square metres or more. The fields required in the query are Company, Square Metre and Rate. Sort the query on the Square Metre field. Save the query as Large Clients** (the ** should be replaced with your initials). Print the query.
  2. Create a second query to give Carol a summary showing the total size of all jobs and the total expected income. The fields required are Square Metre and Rate. Save the query as Square Metre and Rates Totals**. Print the query.

Carol would now like the database to create reports to produce the following information:

  • A breakdown of the cleaning jobs per employee.
  • A breakdown of the cleaning jobs per suburb.
  • A breakdown of the average rate and square metres for each suburb.

Note: All of the reports will require changes to be made to improve their looks. The final printouts should be of professional quality, ie no spelling errors, data correctly aligned, meaningful names used to identify data in the report etc.

  1. Create a report that shows grouping for each employee and details of each client they service. The fields required in the report are as follows:

from Employee table

Employee ID

Surname

First Name

from Client table

Company

Cleaning Day

Square Metre

  1. Add your name to the footer, print and save the report as Employee Breakdown.
  2. Create a report that shows all details of all clients grouped by Suburb. Add your name to the footer, print and save the report as Locality Breakdown.
  3. Create a report that shows the average rate and square metres for each suburb. The fields required in the report are Suburb, Rate and Square Metre. Add your name to the footer, print and save the report as Average Rate and Square Metre.

Carol has realised that Tara Stevens’ name has been entered into the employee table incorrectly.

  1. Create an update query to update Stevens to Stevenson. Run the query, save the query as Stevenson Update