SST 2018 Prelim P2

SECONDARY 4 END-OF-YEAR EXAMINATION COMPUTING Paper 2 Practical (Lab-based) 7155/02 10 September 2018 (Wednesday) 2 hour 30 minutes CANDIDATE NAME CLASS INDEX NUMBER Additional Materials: Electronic version of HOUSING_LOAN.XLSX data file Electronic version of INCOME_TAX.PY file Electronic version of NUMBERS.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 8 printed pages.

Page 2 of 8 Task 1 The spreadsheet calculates the repayment table of a mortgage loan based on information on a loan package offered by the Bank of Singapore (BOS). 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 HOUSING_LOAN.xlsx. You will see the following. Save your file as HOUSING_LOAN _<your name>_<index number>. 1 In cell B4, enter a formula to calculate the number of monthly repayments based on a given loan tenure, given in years, entered in cell B3. [1] 2 Mr. Quek wants to borrow a million dollar ($1 000 000) for his property. He took up the following housing package: Assuming the 8-months Fixed Deposit Home Rate (FHR8) stays at 0.25% throughout the intended tenure of loan, key in appropriate values in cells F2 to F5. [1]

Page 3 of 8 [Turn over 3 Starting with row 10, you will need to build up formulae for the various columns before you copy them to other rows. Hence, you are expected to use relative references for data that varies. (a) Column B is to display the month number of the repayment table. Using a conditional function in excel, write a formula in cell B10 to display the month number if it is less than or equal to the value of cell B4, which is a fixed reference. Otherwise, leave the cell blank. Example: If the loan tenure is 1 year (12 months), cell B21 is to contain the month number 12 but B22 is to be blank. Hint: =ROW(B10) returns the row number 10. And the difference between the row number and the month number is 10 – 1 = 9. [1] (b) In cell A10, the existing formula is given as =IF(B10="", "", "Modify here"). Replace the text string "Modify here" with a mathematical formula to display the year number based on the month number in column B. [1] (c) In cell E10, the existing formula is given as =IF(B10="", "", "Modify here"). Replace the text string "Modify here" with a formula that will calculate the monthly instalment Mr. Quek has to pay the bank if he takes up the given loan packag