TEE103 ICT Skills Assignment

Section A1: Advance Formula Creation tools

You just started a new job after three months of training in the accounting department at Birmingham City University International College. Your boss has given you a task to do using some of the advanced formula-making tools in Excel that you learned to use during your training. The following are the tasks you need to perform:

1. Open the file called Charts.
2. Create a column chart on a new sheet showing all the locations and the numbers for each quarter. Use the range A5:E9.
3. Rename the sheet tab “Chart - all qtrs”.
4. Create another column chart on a new sheet showing all the locations and the numbers for only quarters 1 & 4. Use the ranges A5:B9 & E5:E9.
5. Rename the sheet tab “Chart – qtrs. 1&4”.
6. On the Chart Data sheet, create an embedded 3D pie chart to represent each location and the totals. Use the ranges A5:A9 & F5:F9.
7. Move the chart underneath the data and size it to match the table size.
8. Using the shapes button drawing tool, circle cell F7. Take the fill out and make the line thicker so that you can see the $10,800 in that cell.
9. Draw an arrow from the circle to the German piece of the pie. Make the line thicker.
10. Save and close the file.

5marks

Section A2

1. Open the file called Sort & Filter.
2. Sort the list by the column labeled LAST.
3. Sort the list by the column labeled HRS.
4. Sort the list by columns labeled DIVISION, then by LAST.
5. Filter the list to only show employees in the Children’s DEPT.
6. Add a second filter. Filter the list to show employees in the Children’s DEPT and also in the Australia DIVISION.
7. Save and close the file

5marks

Section A3

1. Open the file called Book Sales.
2. Turn on the Outline feature in this file.
3. Show the outline levels as per the picture below:
4. Save and close the file.

5marks

Section A4

1. Open the file called Subtotals.
2. Create subtotals showing the total GROSS PAY for each DEPT.
3. Create subtotals showing the Count of GROSS PAY for each DEPT.
4. Create subtotals showing the Sum and the Count of GROSS PAY for each DEPT.
5. Using the Outline, show only levels 1 & 2.
6. Save and close the file.

Section B: Word-Based Skills 10marks

Your boss has asked you to do the following tasks using the Microsoft Word skills you've just learned in your most recent training.

1. Open the file called Practise Module 2 and carry out all the tasks in the document.

Section B1 10marks

1. Create a new blank document
2. Make a list of 10 things you want to accomplish this week. Number the list
3. Underneath each “Level 1” item, add a second level item which talks about the first level
4. In the Autocorrect options, add an entry to the list so that you can type your initials in any document and your name appears. Note: screenshot the steps taking to achieve this particular task.
5. Save and close the file.

Section C: Database

Your boss has given you a task on an Excel worksheet that contains duplicate information and also lacks data integrity. You have been asked to use the skills you recently acquired on database normalisation (Access Database) to achieve this task so that the customer information and the order information will be on separate tables, ensuring data duplication is minimised and data integrity is preserved.

• Open the file called Normalization and carry out database normalization using Microsoft Access Database.

20marks