Cambridge A Level Applied Information and Communication Technology 9713 — 2016 Feb/March Paper 4 · Variant 0

This document consists of 7 printed pages and 1 blank page. DC (ST/SG) 116110/3 © UCLES 2016 [Turn over Cambridge International Examinations Cambridge International Advanced Level * 1 8 9 2 5 2 9 5 4 4 * APPLIED INFORMATION AND COMMUNICATION TECHNOLOGY 9713/04 Paper 4 Practical Test February/March 2016 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. Printouts with handwritten candidate information on will not be marked. 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 9713/04/F/M/16 © UCLES 2016 RockICT are planning to sell branded products. You are required to model the costs, sales and profits of a project involving the sale of earbud headphones. You must use the most efficient methods to solve each task. All documents produced must be of a professional standard and suit the business context. Make sure that your name, Centre number and candidate number are shown on all printouts. You are required to provide evidence of your work, including screen shots at various stages. Each screen shot should clearly show the relevant evidence. You should use a document named: CentreNumber_CandidateNumber_Evidence e.g. ZZ999_99_Evidence You have been given the following files: Earbuds.ods – a template model in open document spreadsheet format Earbuds.xls – the template model in Microsoft Excel format RockStaff.csv – the list of the RockICT directors RockICT Memo1.rtf – a template memo document for mail merge RockICT Memo2.rtf – a template memo document for mail merge HeadphoneData.txt – data saved by a non-standard application HeadphoneTested.txt – data saved by a non-standard application. 1 (a) Open the Earbuds spreadsheet to inspect the model. The model contains the named ranges Annual_costs and Calculations and a hidden formula for the projected sales. Display all costs and the earbud selling price as currency in £ (GBP). Display the sales as an integer. (b) The income is calculated by multiplying the number of Sales by the Earbud selling price. Enter a formula in the appropriate cell to calculate the income and set the currency format to match the costs. The profit is calculated by subtracting the total Annual_costs and the total manufacturing costs (Manufacturing cost per Earbud x number of Sales) from the Income. Enter a formula in the appropriate cell to calculate the profit and set the currency format to match the costs. Provide evidence of the formulae used. Save a copy of the spreadsheet with the name Earbud project

3 9713/04/F/M/16 © UCLES 2016 [Turn over The Earbud project will be used as a promotion and each Earbud will be sold at a price that just covers the cost i.e. makes little or no profit but does not make a loss. (c) The Manufacturing cost per Earbud is quoted as £1.28. Enter this value into the appropriate cell. The Project Administration is expected to be £8000. Enter this value into the appropriate cell. Advertising is expected to be £5000. Enter this value into the appropriate cell. Use the model to determine the minimum earbud selling price so that the project breaks even i.e. there is little or no profit, but does not make a loss. Provide evidence of your method and print a copy of the spreadsheet showing the values. Make sure that all the data and labels are fully visible and fit on a single page. (d) Determine the minimum earbud selling price if the advertising increased to £8000. Provide evidence of your method and print a copy of the spreadsheet showing the values. (e) With the advertising still at £8000, set the earbud selling price to £2. Determine the manufacturing cost per earbud that will enable the project to break even. Provide evidence of your method and print a copy of the spreadsheet showing these values. [12] You are required to report your results for the Earbud project. 2 Use RockICT Memo1.rtf to create memos for the CEO and the Directors of RockICT only. RockICT staff information is contained in the file RockStaff.csv Enter The Earbud Project as the subject. Insert appropriate mergefields, links and text where indicated. Use conditional fields where indicated to achieve the following: • For all recipients, except the Director of Finance, display the text A copy of the spreadsheet model is available from the Director of Finance. • For the Director of Finance only, display the text Projected sales and the linked range named Calculations. Provide evidence of the merge document showing all the field codes. Print the merged memos. [23]

4 9713/04/F/M/16 © UCLES 2016 The Director of Sales has decided you should investigate selling the earbud headphones to make a profit after all. You will need to extend the spreadsheet model. 3 (a) The model needs to explore the sales, income and profit for different selling prices. Unprotect the model. The password is CIE Limit the advertising costs to between £2000 and £15000. Insert suitable input and error alert messages. Provide evidence of your method and text. The manufacturers have agreed to supply the earbuds at £1.25 each. Enter this value in the correct cell. The model needs to be extended to look like this: Enter formulae to increase the Earbud selling price by £1 every row. Amend the Sales, Income and Profit formulae so they may be replicated to calculate the values for each selling price. Print a copy of the table. Provide evidence of the formulae you used in your Evidence Document.

5 9713/04/F/M/16 © UCLES 2016 [Turn over (b) Create an appropriate graph or chart to display the Profit against the Earbud selling price. Use the Earbud selling price series as the horizontal axis. Add the Sales data as a new series with a secondary vertical axis. Make sure the data conveys the information clearly by adding suitable text for a chart title, axes titles and a legend. Print your graph or chart on a full page. [27] 4 Explore the effect of altering the amount spent on advertising. By changing the amount spent on advertising in multiples of £1000, determine the maximum profit and the corresponding earbud selling price. Present your findings in a presentation containing 3 slides as follows: • Slide 1 – A title slide for The Earbud Project • Slide 2 – The modelled spreadsheet table with callout boxes indicating the maximum profit value and the corresponding Earbud selling price • Slide 3 – The graph or chart overlaid with a vertical line through the point of maximum profit and the category axis. Use a master slide to include your name, Centre number and candidate number on the bottom of all slides. Print each slide on a full page. [11]

6 9713/04/F/M/16 © UCLES 2016 You have been asked to investigate other types of headphones and create a database. 5 (a) You have been provided with data that has been produced using a non-standard application. It may not be possible to import the data directly into your database application. HeadphoneData.txt contains data on all the headphones being considered. HeadphoneTested.txt contains the test results for some of the headphones. Open the files to inspect the data. Show evidence of any alterations or adjustments you make in your Evidence Document. When you import the data use the following structures: HeadphoneData table HeadphoneTested table Field Name Data Type Format Field Name Data Type Format Headphone_Name Text Headphone_Name Text Open-Closed Text Comfort Number Integer Over-On-ear Text Sound_Quality Number Integer Amp_Required Text Build_Quality Number Integer Bass Text Treble Text Price Currency £, English, (UK) 2 dp Show evidence of the database table structures in your Evidence Document. (b) Prepare a report listing the headphones that have not been tested, displaying all the fields grouped by the Open-Closed and Over-On-ear fields with the headphone names in ascending order. Make sure all data and labels are fully visible and the report fits on a single page. The title of the report should be Headphones not yet tested Insert your name, Centre number and candidate number in the page footer. Print the report.

7 9713/04/F/M/16 © UCLES 2016 (c) From the list of headphones not tested, select headphones that are: • closed • over-ear • no amp required • bass and treble are neutral • price is less than £70. These are to be tested by the technicians in the Studio department of the RockICT staff. Use the RockICT Memo2.rtf file to create memos to be sent to the Head of Studio and the technicians in the Studio department only. Insert mergefields, data and text where indicated. Use a conditional field where indicated to insert text as follows: Text for the Head of Studio Please arrange for the following headphones to be tested by your technicians: Text for the technicians Please test each of the following headphones: Provide evidence of the merge document showing all the field codes. Print the merged memos. [17] 6 Save and print your Evidence Document. Write today’s date in the box below. Date

8 9713/04/F/M/16 © UCLES 2016 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. To avoid the issue of disclosure of answer-related information to candidates, all copyright acknowledgements are reproduced online in the Cambridge International Examinations Copyright Acknowledgements Booklet. This is produced for each series of examinations and is freely available to download at www.cie.org.uk after the live examination series. 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. BLANK PAGE