MSHS 2020 Computing P2

Class/ Index Number / Centre Number/ ‘O’ Level Index Number / Name MARIS STELLA HIGH SCHOOL PRELIMINARY EXAMINATIONS SECONDARY FOUR COMPUTING 7155/02 Paper 2 Practical (Lab-based) 28 August 2020 2 hours 30 mins Additional Materials: Electronic version of COMPANY.XLSX file Electronic version of DEPOSITS.PY file Electronic version of CASHBACK.PY file Insert Quick Reference for Python READ THESE INSTRUCTIONS FIRST 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 in 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 This document consists of 6 printed pages. 50 Thumb drive No:

2 Task 1 A maintenance company uses spreadsheet software to record details of its completed jobs. There are five different job categories provided by the company. For each type of job, the total cost comprises a deposit amount and hourly cost. You are required to finish setting up the spreadsheet to calculate the total cost of the jobs. Open the file COMPANY.xlsx. You will see the following data. Save the file as JOBS_<class>_<index number>_<your name>.xlsx 1 In cells E4 to E12, enter a formula that uses an appropriate function to extract the last 2 letters of the corresponding Job ID. The Job ID is a 11 character text. [2] 2 In cells F4 to F12, enter a formula that uses an appropriate function to search for the Deposit in the Job Code Fees table. [2] 3 In cells I4 to I12, enter a formula to calculate the Hourly Cost, which is the product of Rate Per Hour by Hours Required. In cells J4 to J12, enter a formula to calculate Total Cost, which is the Deposit added to the Hourly Cost. [2] 4 Format the cells J4 to J12 to automatically highlight those cells whose total cost is more than $400 with a red background. [1] 5 In cells J13, enter an appropriate function to calculate the total earned. [1] 6 In cell G13, enter an appropriate function to find the second highest hourly rate. [2] Save and close your file.

3 Task 2 A bank stores the names and deposits of its customers into two lists. The following program consists of five customer names and their corresponding deposits. It prints the names and deposits of the customers whose name begins with the letter ‘J’. names = ['Joy', 'Mary', 'John', 'Dean', 'Siva'] deposits = [540.35, 525.60, 539.50, 500.05, 570.95] for i in range(len(names)): if names[i].startswith('J'): print("Customer name is {}, Deposit amount is {}".format(names[i], deposits[i])) Open the file DEPOSITS.py Save the file as MYDEPOSITS_<class>_<index number>_<your name>.py 7 Edit the pr