Cambridge A Level Applied Information and Communication Technology 9713 — 2010 Oct/Nov Paper 2 · Variant 1

This document consists of 4 printed pages. IB10 11_9713_02/5RP © UCLES 2010 [Turn over *3393483587* UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS General Certificate of Education Advanced Subsidiary Level and Advanced Level APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/02 Paper 2 Practical Test October/November 2010 2 hours 30 minutes Additional Materials: Candidate Source Files READ THESE INSTRUCTIONS FIRST Make sure that your Centre number, candidate number and name are written at the top of this page and are clearly visible on every printout, before it is sent to the printer. DO NOT WRITE IN ANY BARCODES. Carry out every instruction in each task. At the end of the exam put this Question Paper and all your printouts into the Assessment Record Folder. The number of marks is given in brackets [ ] at the end of each question or part question. Any businesses described in this paper are entirely fictitious.

2 © UCLES 2010 9713/02/O/N/10 You are going to help trainees in a company understand some of the ICT features they might be required to use and show them how to use them. 1 You are required to provide evidence of your work, including screen shots at various stages. Create a document named: CentreNumber_CandidateNumber_Evidence.rtf e.g. ZZ987_82_Evidence.rtf Place your name, Centre number and candidate number in the header of your evidence document. 2 Create a presentation using the file NXNOTES.RTF to help you. [1] 3 On the slide master: The slide master must have a white background and your Centre number, candidate number and name in a 12 point black serif font placed in the top right corner. Ensure on all slides that no objects overlap. [3] 4 Some slides contain a title and questions. Under each red bullet point briefly answer the question in your own words (related to the slide title and the preceding slide/s), using a bullet point in a black sans-serif font. [37] 5 Print all the slides of your presentation. 6 Insert hyperlinks on the first slide to make all of the text for each bullet point a link to the relevant slide. Include in your evidence document screenshots that show how you created these links. [8] 7 On the first slide add a fifth bullet point with the text Hothouse website that links to the URL http://www.hothouse-design.co.uk Include in your evidence document a screenshot that shows how you created this link. Save your presentation. [3] Hothouse Design has been asked to develop some large advertisements for the 2012 Olympic games. Use a spreadsheet to analyse the production dates for these advertisements. 8 Using a suitable software package, load the file NXJOBS.CSV 9 Insert a new row 8 and in cell A8 add the text Project 6 [2] 10 Replace the word Advertisement with the word Project wherever it appears in the spreadsheet. [1]

3 © UCLES 2010 9713/02/O/N/10 [Turn over 11 In cell B3 enter the date 28th November 2010. In cell B4 enter the date 16th October 2010. In cell B5 enter the date 2nd November 2010. In cell B6 enter the date 30th November 2010. In cell B7 enter the date 1st December 2010. In cell B8 enter the date 2nd December 2009. [6] 12 Delete row 2 from the spreadsheet. [1] 13 Embolden all cells in row 1 and all cells in column A. [1] 14 In cell C2 use a function to extract the day as a numeric value from cell B2. [1] 15 In cell D2 use a function to extract the month as a numeric value from cell B2. [1] 16 In cell E2 use a function to extract the year as a numeric value from cell B2. [1] 17 In cell J2 calculate the end date for the project using the start date and the number of days it will take for planning, design, creation and installation. [1] 18 Replicate the formulae used in steps 14 to 17 for all projects. [1] 19 In cell J7 change the date to 23rd July 2010. [1] 20 Format all the date cells in columns B and J into the format dd/mm/yy. [1] 21 Insert a formula in cell K2 which uses the file NXMONTH.CSV to look up the season from the Start date and adds this to the date. For example: 01/01/2010 will be displayed as 01 January 2010 (Winter) [14] 22 In cell L4 use a formula to calculate the difference in months, between the start date and the end date. Replicate this formula for all projects. [5] 23 In cell A10 use a formula to count the number of projects that start in Winter. [4] 24 Create a header which says Planning time for projects and a footer which contains your name, Centre number and candidate number. [2] 25 Save and print your spreadsheet, adjusting the page layout if necessary so that the whole table fits on a single page. Make sure that the contents of all cells are fully visible. [2] 26 Change the wording of the header to Formulae and functions used [1] 27 Print the spreadsheet in landscape orientation, showing all formulae instead of values. Ensure that all formulae and labels are fully visible. Show row and column headings. [4]

4 Permission to reproduce items where third-party owned material protected by copyright is included has been sought and cleared where possible. Every reasonable effort has been made by the publisher (UCLES) to trace copyright holders, but if any items requiring clearance have unwittingly been included, the publisher will be pleased to make amends at the earliest possible opportunity. University of Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of Cambridge Local Examinations Syndicate (UCLES), which is itself a department of the University of Cambridge. © UCLES 2010 9713/02/O/N/10 28 Produce a chart showing the analysis of days planned for each stage for projects 1 to 5. This chart should show a comparison of the overall project lengths and each stage within each project. Select the project numbers (eg. Project 1) for the category axis and the number of days from the start of the project for the value axis. Select the most appropriate type of graph or chart to display the data. Ensure that your chart is fully labelled. Place your name, centre number and candidate number on the chart. Save and print this chart. [11] 29 Compress the files saved in steps 7 and 25 into a single file. Include in your evidence document a screenshot that shows how you compressed these files. [2] 30 Include in your evidence document a screenshot showing the file names, file types, date and time modified and file sizes. [3] 31 At the end of your evidence document, give two reasons why you would compress these files. [2] 32 Save and print your evidence document. Write today’s date in the box below. Date

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the October/November 2010 question paper for the guidance of teachers 9713 APPLIED ICT 9713/02 Paper 2 (Practical Test A), maximum raw mark 120 This mark scheme is published as an aid to teachers and candidates, to indicate the requirements of the examination. It shows the basis on which Examiners were instructed to award marks. It does not indicate the details of the discussions that took place at an Examiners’ meeting before marking began, which would have considered the acceptability of alternative answers. Mark schemes must be read in conjunction with the question papers and the report on the examination. • CIE will not enter into discussions or correspondence in connection with these mark schemes. CIE is publishing the mark schemes for the October/November 2010 question papers for most IGCSE, GCE Advanced Level and Advanced Subsidiary Level syllabuses and some Ordinary Level syllabuses.

Page 2 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Slide 3 [4] Slide 4 [4] No marks to be awarded for any printout not containing the candidate name, candidate number and centre number

Page 3 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Slide 5 [4] Slide 6 [4]

Page 4 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Slide 8 [4] Slide 9 [4]

Page 5 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Slide 10 [4] Slide 12 [3]

Page 6 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Slide 13 [3] Presentation created 1 mark Text on slides Black font 1 mark Sans-serif font 1 mark Consistent within slide 1 mark Slide master Candidate name & no’s top right 1 mark Black Serif font 1 mark Not overlapping 1 mark

Page 7 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Planning time for projects Estimated Production dates Start date Day Month Year Planning Design Creation Installation End date Full start date Months Project 1 28/11/10 28 11 2010 50 24 30 6 18/03/11 28 November 2010 (Autumn) 4 Autumn Project 2 16/10/10 16 10 2010 32 22 50 10 07/02/11 16 October 2010 (Autumn) 4 Autumn Project 3 02/11/10 2 11 2010 30 21 45 7 13/02/11 02 November 2010 (Autumn) 3 Autumn Project 4 30/11/10 30 11 2010 36 28 42 21 06/04/11 30 November 2010 (Autumn) 5 Autumn Project 5 01/12/10 1 12 2010 45 45 72 14 26/05/11 01 December 2010 (Winter) 5 Winter Project 6 02/12/09 2 12 2009 23/07/10 02 December 2009 (Winter) 7 Winter Start dates during winter 2 Candidate name & numbers Row 8 inserted 1 mark ‘Project 6’ 100% correct 1 mark Global replace –Advertisement to Project 1 mark Row 2 deleted 1 mark 6 correct dates 6 marks Header 100% correct 1 mark Fits to single page 1 mark Fully visible 1 mark In footer 1 mark Row 1 & Column A - Bold 1 mark Row 8 inserted 1 mark ‘Project 6’ 100% correct 1 mark Global replace –Advertisement to Project 1 mark Cell J7 23/07/10 1 mark Columns B & J - dd/mm/yy format 1 mark Display for column K 1 mark

Page 8 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Formulae and functions used A B C D E F G H I J 1 Estimated Production dates Start date Day Month Year Planning Design Creation Installation End date 2 Project 1 40510 =DAY(B2) =MONTH(B2) =YEAR(B2) 50 24 30 6 =B2+F2+G2+H2+I2 3 Project 2 40467 =DAY(B3) =MONTH(B3) =YEAR(B3) 32 22 50 10 =B3+F3+G3+H3+I3 4 Project 3 40484 =DAY(B4) =MONTH(B4) =YEAR(B4) 30 21 45 7 =B4+F4+G4+H4+I4 5 Project 4 40512 =DAY(B5) =MONTH(B5) =YEAR(B5) 36 28 42 21 =B5+F5+G5+H5+I5 6 Project 5 40513 =DAY(B6) =MONTH(B6) =YEAR(B6) 45 45 72 14 =B6+F6+G6+H6+I6 7 Project 6 40149 =DAY(B7) =MONTH(B7) =YEAR(B7) 40382 8 9 Start dates during winter 10 =COUNTIF(M2:M7,"Winter") Header 100% correct 1 mark Orientation Landscape 1 mark Row headings Fully visible 1 mark Column headings Fully visible 1 mark Formulae & labels Fully visible 1 mark Day Correct function 1 mark Month Correct function 1 mark Year Correct function 1 mark End date Correct calculation 1 mark Replication All 4 formulae 1 mark Max 4 for different correct method: eg. COUNTIF 1 mark Correct range 1 mark Condition Month (J) 1 mark Extraction Winter – lookup or extraction 1 mark

Page 9 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 K 1 Full start date 2 =TEXT(B2," dd mmmmmmmm yyyy")&" ("&LOOKUP(D2,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 3 =TEXT(B3," dd mmmmmmmm yyyy")&" ("&LOOKUP(D3,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 4 =TEXT(B4," dd mmmmmmmm yyyy")&" ("&LOOKUP(D4,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 5 =TEXT(B5," dd mmmmmmmm yyyy")&" ("&LOOKUP(D5,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 6 =TEXT(B6," dd mmmmmmmm yyyy")&" ("&LOOKUP(D6,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 7 =TEXT(B7," dd mmmmmmmm yyyy")&" ("&LOOKUP(D7,NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12)&")" 8 9 10 L M 1 Months 2 =(YEAR(J2)-YEAR(B2))*12+MONTH(J2)-MONTH(B2) =LOOKUP(MONTH(B2),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) 3 =(YEAR(J3)-YEAR(B3))*12+MONTH(J3)-MONTH(B3) =LOOKUP(MONTH(B3),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) 4 =(YEAR(J4)-YEAR(B4))*12+MONTH(J4)-MONTH(B4) =LOOKUP(MONTH(B4),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) 5 =(YEAR(J5)-YEAR(B5))*12+MONTH(J5)-MONTH(B5) =LOOKUP(MONTH(B5),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) 6 =(YEAR(J6)-YEAR(B6))*12+MONTH(J6)-MONTH(B6) =LOOKUP(MONTH(B6),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) 7 =(YEAR(J7)-YEAR(B7))*12+MONTH(J7)-MONTH(B7) =LOOKUP(MONTH(B7),NXMonth.csv!$A$1:$A$12,NXMonth.csv!$B$1:$B$12) Full start date Text Correct function 1 mark Reference Correct cell 1 mark Space Included after date 1 mark Day portion Correct format 1 mark Month portion Correct format 1 mark Year portion Correct format 1 mark Concatenate or & 1 mark “(“ 1 mark Lookup Function used 1 mark Reference Correct cell 1 mark Range External 1 mark Concatenate or & 1 mark “)“ 1 mark Months Subtract both year functions 1 mark Correct brackets Year only 1 mark Multiply by 12 1 mark Add End month 1 mark Subtract Start month 1 mark

Page 10 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Correct values 2 marks Chart type Appropriate (Stacked bar) 2 marks Category Axis Project 1 etc 1 mark Meaningful & appropriate axis label 1 mark Value Axis Meaningful & appropriate axis label 1 mark Chart Title Meaningful & appropriate chart title 1 mark Title of appropriate size 1 mark Name & numbers on chart 1 mark Legend or series labels Visible, correct and appropriate 1 mark Name, centre number & candidate number

Page 11 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Evidence document Correct text 1 mark Internal - to correct slide 1 mark Correct text 1 mark Internal - to correct slide 1 mark

Page 12 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Correct text 1 mark Internal - to correct slide 1 mark Correct text 1 mark Internal - to correct slide 1 mark

Page 13 Mark Scheme: Teachers’ version Syllabus Paper GCE A/AS LEVEL – October/November 2010 9713 02 © UCLES 2010 Step 30 Reasons for compression: • Reduce transmission time for files • For e-mail attachments / file transfer using network / internet • Server space / reduce storage space for files 2 from Max [2] Correct text 1 mark Web page 1 mark Correct URL 1 mark Files (both) zipped 2 marks File type 1 mark File size 1 mark Date & time 1 mark