Information and Communication Technology 0417/3 October/November 2019: question paper and mark scheme

Question

• Using the file Invoice.csv • Merge cells A1 to G1 • Merge cells A9 to G9 • Format both merged cells to have white, centre aligned, 36 point sans-serif font on a black background. • Format the top of the spreadsheet to look like this: A B C D E F G H I J 1 2 Super-Disk-Sales Less than Discount rate 3 To: 10 0% 4 20 5% 5 50 10% 6 20% 7 8 9 10 Invoice Product Number Unit 11 code of items Make Model Capacity price Price 12 • At the bottom of the spreadsheet, format only the cells which contain text, to be bold and look like this: A B C D E F Price Total before 24 items discount Discount Discount 25 rate 26 Total 27 • Save this as a spreadsheet with the file name Invoice_ followed by your centre number_candidate number, for example, Invoice_ZZ999_9999 [9]

Question

• Place the text: Invoice date: followed by an automated date left aligned in the header. • Place your name, centre number and candidate number, right aligned in the footer. [2]

Question

• Place functions in cells C12 to F12 to lookup the make, model, capacity and unit price of this item; using the product code for the lookup value and the external file SSD.csv for the array. • Make sure that if no data is entered in the product code column then nothing is displayed in each of these cells. • Replicate these functions for rows 13 to 23 [15]

Question

• Place in cell G12 a formula to multiply the Number of items by the Unit price • Make sure that if no data is entered in the number of items column then nothing is displayed in this cell. • Replicate this formula for rows 13 to 23 [2]

Question

• Place in cell B24 a function to calculate the total number of items ordered. [2]

Question

• Place in cell G24 a function to calculate the total of the Price column. [1]

Question

• Place in cell E25 nested IF functions to display the discount rate percentage referring to the data in columns I and J, depending upon the total number of items ordered. Note that the discount rate in cell J6 is used if the total number of items ordered does not meet the criteria of cells I3 to I5. [4]

Question

• Place in cell G25 a formula to calculate the discount amount which is the Discount rate multiplied by the Price before discount [1]

Question

• Place in cell G26 a formula to calculate the total which is the Price before discount minus the Discount [1] 10 • Apply appropriate formatting to all cells containing numeric values in rows 12 to 26 [1]

Question

• Save your spreadsheet. Print the entire spreadsheet showing the values. Please note you will not yet have entered data into this invoice. Make sure the: • printout fits on a single page • contents of all cells are fully visible with no split words • gridlines are displayed • row and column headings are displayed. [2] PRINTOUT 1 Make sure that you have entered your name, centre number and candidate number on your spreadsheet showing the values. 12 • 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. [1] PRINTOUT 2 Make sure that you have entered your name, centre number and candidate number on your spreadsheet showing the formulae.

Question

Before this invoice will be used it needs to be tested. Test 1 will use normal data including item SSD1 • In your Evidence Document edit the Evidence 1 table to look like this: Test Test Item of Expected Cell Why selected number type test data outcome in G12 First product in SSD1 the data source Test 1 Normal • Examine the formula in cell G12 and use this to complete the table identifying: • the cell reference where SSD1 will be entered • another appropriate item of test data that will be used with SSD1 to complete Test 1 • the cell reference where this item of test data will be entered • why you selected this item of test data • the expected outcome from Test 1 that will be seen in cell G12. [5] EVIDENCE 1 Edit and complete the table in your Evidence Document. 14 • Enter the address as shown and format it like this: A B C D 1 2 Super-Disk 3 To: Tawara Technology Solutions 4 32 Acacia Avenue 5 Tawara 6 45673 7 • Enter the items into the invoice like this: A B 10 Product Number code of items 11 12 SSD8 10 13 SSD34 1 [2] 15 • Print only cells A1 to G26 showing the values. Make sure the: • printout fits on a single page • row and column headings are not displayed • contents of all cells are fully visible with no split words. [1] PRINTOUT 3 Make sure that you have entered your name, centre number and candidate number on your spreadsheet showing the values. [Total: 49] Task 3 – Evidence Document

Question

A table within a web page is designed to look like this: Super–Disk–Sales HDD SSD Optical media • Examine the files Q16 and HDD1 which were created by a trainee in their attempt to produce this web page. This is a copy of the HTML for the table: <table border=1> <tr colspan=3> <td>Super-Disk-Sales</h1></td> </tr> <tr> <td>HDD</td> <td>SSD</td> <td>Optical media</td> </tr> <tr colspan=3> <td><img src="HDD1.mp4" alt="Hard drive image></td> </tr> </table> • Analyse this HTML and suggest amendments. Identify where each of these amendments should be placed. Do not include CSS. [4] EVIDENCE 2 Place your answer in your Evidence Document. [Total: 4] Task 4 – Web Page A trainee has started to create a stylesheet for Super-Disk-Sales. The stylesheet is not finished and contains a number of errors. You are going to edit the stylesheet. All colour codes must be in hexadecimal. Make sure that you use the most efficient methods. Heading styles h4, h5 and h6 will not be used so do not need defining. Make sure that your stylesheet contains no HTML. 17 • Open the stylesheet 193style.css The specifications for this stylesheet are: table position Centre aligned within the browser window size 1200 pixels wide borders Not collapsed Visible Width 2 pixels Solid Border colour 808000 table data cell padding Top 8 pixels Left and right 20 pixels Bottom 6 pixels borders Visible Width 2 pixels Solid Border colour 808000 All heading styles Colour: Blue 00, Red ff, Green ff Font: Times New Roman, but if not available then Times, or if these fonts are not available, the browser’s default serif font h1 48 points high Alignment right h2 24 points high h3 18 points high Bold • Correct and complete this stylesheet using the information above. • Place your name, centre number and candidate number as a comment at the start of the stylesheet. • Save this stylesheet. Use the file name 193style followed by your candidate number, for example, 193style9999.css [25] EVIDENCE 3 Take a screenshot showing the content of your stylesheet and place this in your Evidence Document. Make sure that the contents are easy to read and the file name is clearly visible. 18 • Open the web page n19webpage.htm in your web authoring package. • Replace the text A Candidate, ZZ999, 9999 with your name, centre number and candidate number. • Attach the stylesheet SSDstyle1.css to this web page. • Attach the stylesheet you created in step 17 to your web page so that it has a higher priority than the stylesheet SSDstyle1.css • Save the web page. • Display the web page in your browser. Make sure that: • all the page can be seen • all the text can be easily read • the address bar is visible. [2] EVIDENCE 4 Take a screenshot of the web page in your browser and place this in your Evidence Document. • Display the HTML source in your editor. EVIDENCE 5 Take a copy of the HTML source and place this in your Evidence Document. [Total: 27] • 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.