paper 3 june 2008.pdf

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS International General Certificate of Secondary Education INFORMATION TECHNOLOGY 0418/03...

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS International General Certificate of Secondary Education INFORMATION TECHNOLOGY 0418/03 Paper 3 Practical Test May/June 2008 2 hours 30 minutes Additional Materials: Candidate Source Files *0000000000* READ THESE INSTRUCTIONS FIRST Make sure that your Centre number, candidate number and name are clearly visible on every printout, before it is sent to the printer. Printouts with handwritten information will not be marked. Carry out every instruction in each task. Tasks are numbered on the left hand side of the page, so that you can see what to do, step by step. On the right hand side of the page for each task, you will find a box which you can tick (
) when you have completed the task; this checklist will help you track your progress through the assignment. Before each printout you should proof-read the document to make sure that you have followed all instructions carefully. At the end of the assignment put all your printouts into the Assessment Record Folder. This document consists of 8 printed pages. IB08 06_0418_03/5RP © UCLES 2008 [Turn over  2 You work for a company called Hothouse Design. You are going to perform some clerical tasks for this company.
1 Using a suitable software package, load the file J8PHONE.CSV 2 In the Department column use a lookup function to show the department name. Use the Dcode column for the lookup value and the file J8CODE.CSV for the array. Make sure that you use both absolute and relative referencing within your function. Replicate this function so that the department name for each of the 7 items is shown. 3 Enter the following data into the model Code Rate C 1.1 I 3 P 1.6 4 Name the cell containing 1.1 Cheap Name the cell containing 3 Intl Name the cell containing 1.6 Peak 5 Format these three cells as numbers to 1 decimal place. 6 Format cells A2, A3 and A4 so that they are right aligned. 7 Use a Countif function (that includes both absolute and relative referencing) in cell C7, to count the number of MD entries in the Dcode column. Do not count any entries in rows 1 to 13. Replicate this function into cells C8 to C13 to count the number of calls from other departments. 8 Use a function (that includes both absolute and relative referencing) in cell D7, to calculate the sum of the Duration column if the Dcode column contains MD Do not include rows 1 to 13. Replicate this function into cells D8 to D13 to sum the Total Duration for each Dcode 9 In the Units column (Cell E16) use an IF function to calculate the units used. If the Code is C then multiply the named cell Cheap by the Duration If the Code is I then multiply the named cell Intl by the Duration If the Code is P then multiply the named cell Peak by the Duration If the Code is not C, I or P then the units used should be zero. Replicate this function so that the Units for each item are shown. 10 Format all cells in rows 1, 6 and 15 to be centre aligned. 11 Format all cells in rows 1, 6 and 15 so that they are bold. © UCLES 2008 0418/03/M/J/08 [Turn over  3 12 Format only the Units column to no decimal places. 13 Set the page orientation to landscape. 14 Save the data model and print a copy of the sheet showing the formulae and functions used. Make sure that the contents of all cells are visible and that the printout is one page wide. It may be three or four pages tall. Make sure that your name, Centre number and candidate number are printed in the header of the page. 15 Set the page orientation to portrait. 16 Save the data model and print a copy of the sheet showing the values. Make sure that the contents of all cells are visible and that the printout is one page wide. Make sure that your name, Centre number and candidate number are printed in the header of the page. 17 Hide rows 1 to 14 inclusive. Hide column D. 18 Interrogate the data, to find all the items where the Code is I Print this data. Make sure that your name, Centre number and candidate number are printed in the header of the page. 19 Select all of the data. Leave the rows 1 to 14 and column D hidden. Hide column C. Interrogate this data, to find all the calls made on 04/01/2008 where the Dcode is PU or SA Print this data. Make sure that your name, Centre number and candidate number are printed in the header of the page. 20 Select from all of the data, ONLY the data for Department and Total Duration Use this data to create a vertical bar chart. Label the chart with the title Total Duration By Department Make sure that all category axis labels are fully visible and that there is no legend. Save this chart in a format that can be imported into a presentation authoring package. Print a copy of this chart. Make sure that your name, Centre number and candidate number are printed with this chart. © UCLES 2008 0418/03/M/J/08 [Turn over