JWSS 2018 P2

Class Index Number Name:__________________________________________________________________ Setter: Mr V Surya Jurong West Secondary School Preliminary Examinations 2018 COMPUTING 7155/02 Secondary Four Express 28 August 2018 Paper 2 Practical (Lab-based) 0800 – 1030 Additional Materials: 2 hours 30 minutes READ THESE INSTRUCTIONS FIRST Write your name, class and index number in the spaces provided at the top of this page. Write in dark blue or black pen. Do not use staples, paper clips, glue or correction fluid. Answer all questions. All tasks must be done in the computer laboratory. You are not allowed to bring in or take out any pieces of work or materials on paper or electronic media or any other form. Programs are to be written in Python. Save your work using the file name given in the question as and when necessary. The number of marks is given in brackets [ ] at the end of each question or part question. The total number of marks for this paper is 50. For Examiner’s Use Task 1 Task 2 Task 3 Task 4 Total After checking of answer script Checked by Signature Date Student 50 Electronic version of INS.XLSX file Electronic version of WORDS.PY file Electronic version of AVERAGE.PY file Insert Quick Reference Glossary This document consists of 7 printed pages. _________________________________________________________________________________

2 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express Answer all questions Task 1 (10 Marks) Great Western Assurance uses a spreadsheet for its employee sales records. You are required to finish setting up the spreadsheet. Open the file INS.xlsx You will see the following data. Save the file as MYINS_<class>_<index number>_<your name>. 1 In the Transaction No. column, enter a function to generate a random number between 1 and 888 inclusive. [1] 2 For the cell range D8:D17, extract the fifth letter of the Employee ID to represent the Gender. [1] 3 In cell H20, enter a formula to calculate the range of the No. of Policies Sold. [1] 4 In cell H21, enter a formula to calculate the number of female agents. [1] 5 Use an appropriate function to search for the Bonus in the Bonus Details Table and use it to complete the Bonus Awarded column. [1] 6 To show Total Bonus in the cell range H8:H17, • use an appropriate function to search for the Percentage Commission Rate in the Commission Table, • and multiply it by both the Bonus Awarded and No. of Policies Sold. [2]

3 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express 7 In cell H22, enter a formula to calculate the total bonus of all the agents. [1] 8 In order to check whether the target was met for the month of August, two conditions need to be met: • the total bonus of all the agents must exceed $190,000; • the range of the no. of policies sold must be less than 8. Using a conditional statement, identify whether the target was met, and put YES in the cell H23. Otherwise, put NO in cell H23. [2] Save and close your file.

4 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express Task 2 (10 marks) The following program accepts 7 alphanumeric words from the user and separates the digits and alphabets. The separated digits are then totalled before printing. num_words = 7 for i in range(num_words): word = input("Enter an alphanumeric word: ") num_total = 0 for ch in word: if ch.isnumeric(): num_total = num_total + int(ch) print("The total value of the digits in the word is", num_total) Open the file WORDS.py Save the file as MYWORDS_<class>_<index number>_<your name>. 9 Edit the program so that it: (a) Allows only 3 words to be entered. [1] (b) Prints out the concatenated string of alphabets as well. [4] (c) Checks if the word input provided by the user is made up of more than 5 characters and if not, asks the user to input again, as necessary, informing them about the requirements. [3] Save your program. 10 Save your program as VARWORDS_<class>_<index number>_<your name>. Edit your program so that it does not limit the number of words that are entered. [2] Save and close your file. Sample Output: Enter an alphanumeric word: al9hg6! The total value of the digits in the word is 15 The concatenated alphabets in the word is alhg

5 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express Task 3 (10 Marks) Knowing your expertise in the field of mathematics, you have been asked to write a program that will input several integers into a list and find the average of the integer elements in the list. If there are no elements in the list, the average will be 0. There are several syntax errors and logical errors in the program. values = [] while True input_str = input("Enter integer, blank to end: ) if input_str = "": break values.prepend(int(input_str)) if len(Values) > 0: total = 0 index = 0 while index < len[values]: total = total + values[index] index += 1 average = total * len(values) elif: average = 0 print("The average of the given inputs is { ".format(average)) Open the file AVERAGE.py Save the file as MYAVERAGE _<Class>_<Index>_<Name>. 11 Identify and correct the errors in the program so that it works correctly. Save and close your file. [10]

6 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express Task 4 (20 Marks) In the year 2029, global warming has begun to affect consumer items, namely eggs. A company wishes to employ data-mining to extract information such as the average number of eggs spoilt and which of their packing layers need maintenance. Presently, each clutch of eggs is packed in five layers. You have been employed to write a suitable program to assist the company in their objective. The program should allow you to: • Enter data in the format a:b:c:d:e where a, b, c, d and e are the number of spoilt eggs in each of the five layers of packaging. o An example would be 2:1:4:1:3, where 2 is the number of eggs spoilt in Layer 1, 1 in Layer 2 and so on. • Only allow data entry values greater than or equal to 0. • Calculate the total number of spoilt eggs in all the layers. o For the example 2:1:4:1:3, the total would be 11. • Repeat this for a total of six shipments, assuming the same five layers are utilised during each shipment. • Calculate the total number of spoilt eggs for the six shipments. • Find the average number of spoilt eggs per layer, across the six shipments, rounded to the nearest whole number. • Display this on the screen. Your output must look like this: Shipment Report: Shipment 1 11 eggs spoilt Shipment 2 13 eggs spoilt Shipment 3 20 eggs spoilt Shipment 4 14 eggs spoilt Shipment 5 17 eggs spoilt Shipment 6 18 eggs spoilt Total number of spoilt eggs 93 Average number of spoilt eggs per layer 19 12 Write your program and test that it works. [10] Save your program as EGG1_<class>_<index number>_<your name>.

7 JWSS Preliminary Examinations 2018 Computing (7155/02) Secondary 4 Express 13 When your program is working, use the following test data to show your test results. 2:1:4:1:3 1:2:3:4:3 4:2:4:5:5 3:3:3:2:3 2:4:4:3:4 3:3:2:4:6 Take a screen shot of your results and save it as a bitmap EGGRESULTS_<class>_<index number>_<your name>. [5] 14 Save your program as EGG2_<class>_<index number>_<your name>. Extend your program to identify layers that require maintenance as they contain more than 19 spoi