SST 2020 S4 Computing P2

SECONDARY 4 PRELIM EXAMINATION COMPUTING Paper 2 Practical (Lab-based) 7155/02 14 August 2020 (Friday) 2 hours 30 minutes CANDIDATE NAME CLASS INDEX NUMBER Additional Materials: Electronic version of CAR.XLSX data file Electronic version of PASSWORD.PY file Electronic version of SHN.PY file Insert Quick Reference Glossary 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. This document consists of 6 printed pages. For Examiner’s Use 1 1 2 1 3 2 4 2 5 2 6 2 7 1 8 2 9 3 10 4 11 10 12 12 13 5 14 3 Total /50

Page 2 of 7 Task 1 A car dealer uses a spreadsheet software to record the list of customers who recently bought a car from them, and the type of loans they made. You are required to finish setting up the spreadsheet. For consistency, you are also required to display all results as positive numbers. Open the file CAR.xlsx. You will see the following data. Save your file as CAR_<your name>_<index number>.xlsx 1. In cell B19, enter a formula to calculate the total price of the vehicles sold. [1] 2. In cell B20, enter a formula to calculate the range of prices of vehicles sold. [1] 3. The loan amount is the amount of money for the car not paid in the down payment. In cells E3 to E17, enter a formula that uses an appropriate function to calculate the loan amount each customer has to take to complete the Loan Amount column. [2] 4. In cells G3 to G17, enter a formula that uses an appropriate function to search for the Interest Rate Per Annum (P.A.) in the Rate table and use it to complete the Interest Rate (P.A.) column. [2] 5. In cells H3 to H17, enter a formula to calculate the monthly instalment each customer has to pay and use it to complete the Monthly Instalment column. [2] 6. A reminder will be sent to customers who have taken more than or equal to $28 000 in loans and whose loan terms are 5 years or less. In cells I3 to I17, enter a conditional statement to identify such customers and put Yes in the Reminder column. Otherwise put No in the cell. [2] Save and close your file.

Page 3 of 7 [Turn ov