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 mo