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 package. [1] (d) Similar to part (c), in the given formula in cell F10, replace the text string "Modify here" with a formula that will calculate the amount of the monthly instalment that goes to the interest repayment. [1] (e) For each given formula in cells G10 and H10, replace the text string "Modify here" with a formula to calculate the principal amount paid and the ending principal respectively. [2] (f) Copy the formula in A10:H10 and paste them to row 11 to 489 accordingly. [1] 4 Mr. Quek wants to reduce the amount of interest he needs to pay for the mortgage loan. He wishes to spend at most $4000 for the monthly instalment throughout the loan tenure. Using the goal seek function, find the length of tenure, in years and months, that he should set for the loan. Write your answers in cells B6 and B7 accordingly. [1] Note: The monthly instalment is a negative amount from the perspective of the borrower. Save and close your file.

Page 4 of 8 Task 2 The Singapore resident tax rates for employees earning up to $120 000 per year is given in the table below: Resident Tax Rates (From Year 2017) Chargeable Income Income Tax Rate(%) Gross Tax Payable($) First $20, 000 Next $10, 000 0 2 0 200 First $30, 000 Next $10, 000 - 3.5 200 350 First $40, 000 Next $40, 000 - 7 550 2,800 First $80, 000 Next $40, 000 - 11.5 3, 350 4, 600 The program below accepts the annual income of 3 employees in 2017 and prints the average tax that they pay. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 size = 3 totalTax = 0 for employee in range(size): income = int(input("({})Annual income in $: ".format(employee+1))) if income <= 20000: tax = 0 elif income <= 30000: tax = income * 0.02 elif income <= 40000: tax = 200 + income * 0.035 elif income <= 80000: tax = 550 + income * 0.07 else: tax = 3350 + income * 0.115 totalTax += tax averageTax = totalTax/size print("Average tax payable is ${}.".format(round(averageTax,2))) Open the file INCOME_TAX.py Save the file as INCOME_TAX _<your name>_<index number>

Page 5 of 8 [Turn over 5 Edit the program so that it: (a) Accepts the scores for 10 employees instead. [1] (b) Tests if the input is from 0 to 120 000, and if not, asks the user for input again. [2] (c) Prints out the highest tax payable among the 10 employees. [2] (d) Prints out the employee number of the highest tax payable among the 10 employees. [1] (e) Calculates and prints, to one decimal place, the percentage of employee(s) who need not pay tax at all. [2] 6 There are logical errors in the way the tax payable for an employee is calculated in lines 5 -14. Identify and correct the error(s). [2] Save your program. A sample output is shown below:

Page 6 of 8 Task 3 The program below reads in positive integer(s) entered by a user. It stops when “done” is entered. If the user enters anything else, the program will print an error message and ask the user to enter the input again. Before exiting, it will print the total, sum, average, maximum and minimum of the numbers. There are several logical and syntax errors in the program. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 s = 0, count = 0 while True x = input("Enter a positive integer. Type "done" to finish.") if x = "done": break elif not x.isdigit: print "Invalid input. Try again." else: x = integer(x) if count == 0 : M = m = x else: M = max(M, x) m = min(m, x) s += x count = 1 if count==0: average = s = M = m = "NA" avreage = round(s/count, 1) print("\nYou have entered {} number(s).".format(count) print("The sum of the number entered is {}.".format(s)) print("\nThe average of the number entered is {}.".format(average)) print("\nThe maximum of the number entered is {}.".format(M)) print("The minimum of the number entered is {}.".format(m)) Open the file NUMBERS.py Save the file as NUMBERS_<your name>_<index number>. 7 Identify and correct the errors in the program so that it works correctly according to the rules above. [10] Save your program.

Page 7 of 8 [Turn over Task 4 You have been asked to write a program to calculate some statistics of a string of numbers entered in your program. The program should: • Ask user to input the numbers. • Only allow data entry of numerals or space, i.e. from the set {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", " "}. Otherwise, the program will request the user to re-enter the input. • Calculate the frequency of occurrence for each of the 10 digits in the input. • Calculate the number of blocks of numerical input, each separated from the next by one or more space. • Display the output on the screen, like this: 8 Write your program and test that it works. Save your program as FREQUENCIES_<Your name>_<Index number>. [12] 9 When your program is working, use the following test data to show your test results: You should get the output as shown above. Take