Cambridge A Level Applied Information and Communication Technology 9713 — 2013 May/June Paper 4 · Variant 1

This document consists of 4 printed pages. IB13 06_9713_04/RP © UCLES 2013 [Turn over *1281521389* UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS General Certificate of Education Advanced Subsidiary Level and Advanced Level APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/04 Paper 4 Practical Test May/June 2013 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 2013 9713/04/M/J/13 RockICT runs a small club to promote new bands. Only members on the mailing list for the RockICT newsletter can attend each performance. The manager wants you to carry out some tasks using the membership data. All documents published must be of a professional standard and suit the business context. You must provide evidence of your work, including screenshots at various stages. Each screenshot must clearly show the relevant evidence. Use a document named: CentreNumber_CandidateNumber_Evidence.rtf e.g. ZZ999_99_Evidence.rtf Place your name, Centre number and candidate number in the header of your evidence document. You have been given the following files: Membership.csv - members on the mailing list for the newsletter Attendances2012.csv - the record of the members’ attendances at each performance and their ratings of the new bands Employees.csv - the names and some details of the employees of RockICT Memo.rtf and Reminder.rtf - template documents 1 (a) The Membership.csv file contains details of people who are no longer members. The Attendances2012.csv file is the up-to-date list. The manager wants you to find out which members should no longer be included in the membership list. Provide a printout of the members to be deleted showing their ID number, email address and status. Include details of your method and/or any formulae you used in your evidence document. Delete the records for the identified members and save the file as Latest Membership (b) The manager is interested in the types of music members like. Members of the mailing list record whether they like pop, metal, rock and R&B music types. Use the Latest Membership file to find out how many members like each type of music. Display this information as a pie chart. Ensure each sector is clearly labelled with the music type and display the percentage value. Add the title Popularity of the music genres to the chart. Provide: • evidence of the formulae used • a values printout of a table showing the number of members who like each type of music • a separate printed copy of the chart.

3 © UCLES 2013 9713/04/M/J/13 [Turn over (c) The manager also wants attendance details and ratings for each performance. Use the data in Attendances2012.csv to: • create a table showing the number of members at each performance • calculate the average rating of each performance displayed to 2 decimal places. Provide: • evidence of the formulae used • a values printout of the table showing the number of members at each performance and the average rating for each performance. (d) Open Memo.rtf and insert the table displaying the number of members at each performance with the average ratings and a copy of the pie chart. Include a summary to present this data as information. Use the data in Employees.csv to mail merge copies of the memo to the heads of all departments except IT. The selection of the correct recipients should be part of the mail merge and not by manual inspection of the data. Provide evidence of your method of selection. Print a copy of the merge document showing all the field codes. Print the memos. [35] 2 (a) The manager requires some information about members. Calculate the number of times each member attended a performance. Automatically format the fill colour of the cells displaying the number of attendances as follows: • 0 attendances – Red • 1 to 3 attendances – Grey • 4 to 6 attendances – Yellow In your evidence document include evidence of the formulae used to calculate the number of attendances for each member and full details of each formatting rule used. (b) There are two levels of membership; Standard and Premium. Prepare a list of all Premium members who have not made any music choices and include the number of times they attended a performance in 2012. Provide evidence of your selection method in your evidence document and a printout showing the formulae used to display the number of attendances for each member. Save the list as NoChoices&Attendance

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 2013 9713/04/M/J/13 (c) Use Reminder.rtf to mail merge letters to all the Premium members who have not made any music choices. Include conditional fields to insert text as follows: • For the Premium members who attended no performances in 2012 include the text We hope our programme for next season is of more interest to you. • For those Premium members who attended up to 3 performances include the text Thank you for your support. • For Premium members who attended more than 3 performances include the text Many thanks for your regular support and participation. Print a copy of the merge document showing all the field codes. Perform the mail merge to create and print the individual letters. [40] 3 You are required to automate a mail-merge list for the monthly newsletter. Since the membership list changes as members join and leave you need to create a macro or procedure to: • copy Premium members and Standard members to 2 separate files • save the files as PnewsList.csv and SnewsList.csv respectively • close the files Ensure the macro or procedure has a meaningful name. Annotate the code with short descriptions of each stage. Print a copy of the code. Assign the macro or procedure to a menu or toolbar. Include screenshot evidence of how you did this in your evidence document. [15] Print your evidence document. Write today’s date in the box below. Date

CAMBRIDGE INTERNATIONAL EXAMINATIONS GCE Advanced Subsidiary Level and GCE Advanced Level MARK SCHEME for the May/June 2013 series 9713 APPLIED ICT 9713/04 Paper 4 (Practical Test B), maximum raw mark 90 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 should be read in conjunction with the question paper and the Principal Examiner Report for Teachers. Cambridge will not enter into discussions about these mark schemes. Cambridge is publishing the mark schemes for the May/June 2013 series for most IGCSE, GCE Advanced Level and Advanced Subsidiary Level components and some Ordinary Level components.

Page 2 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 04 © Cambridge International Examinations 2013 Mark Task 1(a) Create Latest Membership file Evidence of valid method to identify and select non–members 1 1 Correct 8 results (Printout required) 1 Correct fields shown (Member id, EmailAddress, Status) 1 Evidence of records deleted (from 545 records to 537 records) 1 [5] Task 1(b) Determine popularity of music genres. Display as pie chart Evidence of valid count method 1 Correct values seen (128,198,156,231) 1 Pie chart printed – (as printout not screenshot) 1 Correct title for pie chart (Popularity of the music genres) 1 Sector labels included (Genre & %age) 1 [5] Task 1(c) Count Attendances. Calculate average ratings of performances Evidence of valid Count method for attendance at each performance 1 Evidence of valid Average rating calculation for each performance 1 Correct Count Values seen(119,123,118,118,104,128) 1 Correct Average Values seen (2.78,3.43,2.99,1.87,2.95,4.09) 2dp 1 Table printed (Printout – fit for purpose –includes context for manager) 1 [5]

Page 3 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 04 © Cambridge International Examinations 2013 Task 1d Create internal memos (merge document) Evidence of Name mergefield inserted 1 Evidence of Department mergefield inserted 1 From – All candidate details (name, centre number, candidate number) inserted 1 Date inserted (Shown as a field) 1 Relevant Subject text inserted 1 Only these fields inserted 1 Table and chart inserted 1 Evidence of links for both table and chart 1 Relevant explanatory text inserted 1 Use Non-manual selection methods Evidence of valid method for selection of recipients(1) (Department<> IT) 1 Evidence of valid method for selection of recipients(2) (Role= Head) 1 Print Memos Data inserted – follow through from task 1c 1 Chart inserted – follow through from task 1b 1 Only the correct 5 memos published 1 Memo printed – John Peeves 1 Memo printed – Katy Skipper 1 Memo printed – Julie Haddock 1 Memo printed – Stuart Florist 1 Memo printed – Graham Crown 1 Memos printed on single page, layout and formatting fit for purpose 1 [20]

Page 4 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 04 © Cambridge International Examinations 2013 Task 2a Count individual member attendances Evidence of valid method for count of individual member attendance 1 Correct range used 1 Set conditional formatting Evidence of condition "0" set 1 Evidence of condition "1 to 3" set 1 Evidence of condition "4 to 6" set 1 [5] Task 2b Create NoChoices& Attendance list Evidence of selection of Premium members 1 Evidence of selection of members with no music choices recorded 1 Evidence of valid method to include individual member attendance data 1 using referenced data 1 from task 2a 1 [5]

Page 5 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 04 © Cambridge International Examinations 2013 Task 2c Publish reminder letters(create merge document) Date inserted (Format dd MMMM yyyy) 1 Date shown as field 1 Member id mergefield inserted 1 GivenName mergefield inserted 1 Surname mergefield inserted 1 Correct spacing maintained 1 StreetAddress mergefield inserted 1 City mergefield inserted 1 Post Code mergefield inserted 1 Only required fields inserted & layout fit for purpose 1 GivenName (Salutation) mergefield inserted 1 Space & comma included 1 Number of Attendances mergefield inserted 1 Correct spacing maintained 1 Multiple conditional mergefields used 1 Valid 1st condition set 1 Correct text(1) inserted 1 Valid 2nd condition set 1 Correct text(2) inserted 1 Correct text(3) inserted 1 Correct spacing maintained - (conditional text in same paragraph) 1 Efficient configuration of conditional fields - (only 2 conditional fields used) 1

Page 6 Mark Scheme Syllabus Paper GCE AS/A LEVEL – May/June 2013 9713 04 © Cambridge International Examinations 2013 Print letters Only correct 4 letters printed 1 Letter to Connor Chamberlain printed 1 Letter to Isabella Gibbs printed 1 Letter to Samantha Jennings printed 1 Letter to Hayden Nash printed 1 Correct attendance data for recipients (2,3,5,0) in letters 1 Correct conditional text in letters (matches attendance data) 1 Consistent formatting, paragraph spacing and document fit for purpose 1 [30] Task 3 Automate creation of mail merge lists Appropriate name for macro or procedure used 1 Comments inserted at filter/selection of members by membership status 1 Comments inserted where files named/saved 1 Comments inserted where files closed 1 Evidence of filter/selection for Premium members 1 Evidence of file saved as PnewsList 1 Correct file type - .csv used 1 Evidence of file closed 1 Evidence of filter/selection for Standard members 1 Evidence of file saved as SnewsList 1 Correct file type - .csv used 1 Evidence of file closed 1 Assign macro/procedure to Toolbar or Menu Evidence of assignment - (Screenshot of icon/menu item) 1 Evidence of context - (Screenshot of appropriate mouseover/menu text) 1 Evidence of Method - (Screenshots of dialog boxes etc.) 1 [15] [Total: 90]