Information and Communication Technology 0417/31 May/June 2019: question paper and mark scheme

Question

• Open and examine the file j1931emp.csv in a spreadsheet package. • Replace the text in cell A1 with: SDS Weekly Pay • Delete rows 3 to 7 inclusive. • Format the top of the spreadsheet to look like this: A B C D E F G H I J K 1 2 SDS Weekly Pay 3 Office Job Office Job Job 4 Office code name code description Rate of pay 5 A Amsterdam S1 Office Sales €10.00 6 B Brussels SA Sales Assistant €8.00 7 C Cologne S2 Sales Accounts €11.90 8 D Dresden M2 Manufacturing €10.20 9 E Edinburgh D Distribution €8.00 10 F Ankara A Administration/Office €8.00 11 L London M1 Manufacturing €12.50 12 T Turin SM Sales Manager €12.25 13 Z Zagreb DM Distribution Manager €12.25 14 15 Employees Payroll First Last Office Office Job Contract Hours 16 number name name code Job code name description hours worked Rate Pay 17 SDS0001 Matthias O’Neil F M1 25 18 • Format the merged cell A1 to have white text on a dark green background. • Save this spreadsheet with the file name SDS_ followed by your centre number and candidate number, for example, SDS_ZZ999_9999 [11]

Question

• Place your name, centre number and candidate number, right aligned in the header. • Place the text: Last revised on: followed by an automated date, then a space and then the automated time, left aligned in the header. [2]

Question

• Create a named range called offices using only cells A5 to B13 inclusive. [2] EVIDENCE 1 Place in your Evidence Document a screenshot showing how you created this named range.

Question

• In cell F17 use a function to look up the office name. Use the Office code column for the lookup value and the range offices for the array. [4]

Question

• In cell G17 use a function to look up the job description. Use the Job code column for the lookup. Do not use a named range. [5]

Question

• In cell J17 use a function to look up the rate of pay. Do not use a named range. [3]

Question

• In cell K17 use a formula to calculate the pay based on the hours worked by this employee. [2]

Question

• Replicate the functions used in steps 4 to 7 for each employee. [1]

Question

• In cell I67 use a function to count the number of employees who worked some hours during this week. [3] 10 • In cell K67 use a function to calculate the total pay for all employees. [1]

Question

• The Hours worked by all employees were the same as their Contract hours except for the following: Name Hours worked Alfonso Irizarry 16 Sergie Anaya 24 Heine Weissmuller 0 Tracy Royle 32 Sandra Cooper 0 Amy Nichols 34 Pablo Bravo 22 Peggy Hedges 25 Tracy Cushing 16 Otmar Weber 12 Adelina Sanger 42 Karl Seiler 40 • Place the number of hours worked for each employee in the Hours worked column. [1] 12 • Apply appropriate formatting to rows 17 to 67. [1] 13 • Save your spreadsheet. • Print the spreadsheet showing the values. Make sure the: • printout fits on a single page wide • contents of all cells are fully visible with no split words. PRINTOUT 1 Make sure that you have entered your name, centre number and candidate number on your spreadsheet showing the values. [1] 14 • Print your spreadsheet showing the formulae. Make sure: • it is in landscape orientation • the row and column headings are displayed • the contents of all cells are fully visible. PRINTOUT 2 Make sure that you have entered your name, centre number and candidate number on your spreadsheet showing the formulae. [2] 15 • Create a spreadsheet extract showing only the employees who work in London or Zagreb and have the word Sales in their job description. [2] 16 • Sort the extract into ascending order of Last name. [1] 17 • Hide rows 3 to 15 inclusive. Hide row 67. [1] 18 • Hide columns D, E, H, I and J. [1] 19 • Print the extract showing the values. Make sure the: • printout fits on a single page • contents of all required cells are fully visible. PRINTOUT 3 Make sure that you have entered your name, centre number and candidate number on your extract showing the values. [1]

Question

The start date of each employee will be added to the spreadsheet in three separate cells, one for day, one for month and one for year. The spreadsheet will only accept numbers as valid data. Rules are to be used to test that the month has been entered as a valid number. Select data to test these rules. You must use each item of test data only once. • In your Evidence Document identify an item of test data that you would enter as: (a) extreme test data (b) another item of extreme test data (c) abnormal test data (d) normal test data EVIDENCE 2 Type your answers in your own words into your Evidence Document. [4] [Total: 49] Task 3 – Web Page

Question

A new style is to be added to a stylesheet to meet this specification: Style h6 must be a 14 point right aligned font. It should be set to Arial, or select the browser’s default sans-serif font if Arial is not available. This was created: .h6 {font-family:Arial;sans-serif;font-size:14pt;text-allign:right} • Evaluate in your own words how effectively this css meets the specification for the style. [4] EVIDENCE 3 Type your evaluation in your own words into your Evidence Document. You are going to create a web page and edit a stylesheet for Special-Disk-Sales. Many of the people who will view the web page have a very slow internet connection. The web page and stylesheet must work in any browser. All colour codes must be in hexadecimal. Make sure that your stylesheet contains no HTML. 22 • Create a new folder called SDS • Locate the following files and store them in your SDS folder. SDSdisk.jpg SDSbutton1.jpg SDSbutton2.jpg SDSbutton3.jpg SDSbutton4.jpg SDSstyle.css SDStext.txt • Display the contents of your SDS folder showing the folder name, all file names, extensions, file sizes and image dimensions. [1] EVIDENCE 4 Take a screenshot showing the contents of your SDS folder and place this in your Evidence Document. Make sure that the folder name, all file names, extensions, file sizes and image dimensions are clearly visible. 23 • Create a web page called sds.htm • This web page must work in all browsers and will have a table structure as shown below: A B 700 × 170 C D 356 × 150 400 × 770 E 356 × 150 344 × 600 F 356 × 150 G 356 × 150 • Each table cell is identified with a letter and all dimensions are in pixels. The letters and dimensions shown in the table must not appear on your final web page. • Table borders must not appear on the final web page. [10] 24 • Place in cell A the image SDSdisk.jpg • Set the width of this image to 400 pixels and maintain the aspect ratio. [2] 25 • Enter in cell B the text: Special-Disk-Sales • Set this text as style h1. [2] 26 • Using the most appropriate image from SDSbutton1.jpg to SDSbutton4.jpg, place in cell: • C the button to select cloud storage • E the button to select hard disk drives • F the button to select solid state drives • G the button to contact the company. [1] 27 • Make sure appropriate text is displayed if an image is not available. [1] 28 • In cell D place text from the file SDStext.txt • Below this text add the following new paragraph Updated by: followed by your name, centre number and candidate number • Set this text as style h2. [3] 29 • Attach the stylesheet SDSstyle.css to this web page. [1] • Save your web page. 30 • Edit the stylesheet SDSstyle.css to meet the following specifications: h1 font set to Arial or if that is not available to Helvetica or if that is not available to the browser’s default sans-serif font 52 points high italic centre aligned colour set with no blue or red components, but green set to 7f h2 font set to Arial or Helvetica or the browser’s default sans-serif font fully justified text • Add your name, centre number and candidate number as a comment at the start of the stylesheet. • Save this stylesheet in your SDS folder. EVIDENCE 5 Take a screenshot showing the content of your stylesheet and place this in your Evidence Document. Make sure that all contents and the file name are clearly visible. [6] • Display the web page in your browser. If necessary, resize it so that: • all the page can be seen • all text can be easily read • the address bar is visible. EVIDENCE 6 Take a screenshot of the browser window and place this in your Evidence Document. • Display the HTML source in your editor. EVIDENCE 7 Take a copy of the HTML source and place this in your Evidence Document. • Save and print your Evidence Document. PRINTOUT 4 Make sure that you have entered your name, centre number and candidate number on your Evidence Document. [Total: 31]