MSHS 2018 P2

Class Index Number Name MARIS STELLA HIGH SCHOOL PRELIMINARY EXAMINATION SECONDARY FOUR COMPUTING 7155/02 Paper 2 Practical (Lab-based) 29 Aug 2018 2 hours 30 minutes Additional Materials: Electronic version of TUITION.XLSX data file Electronic version of SORTNUMBER.PY data file Electronic version of HEIGHT.PY data file Insert Quick Reference Glossary READ THESE INSTRUCTIONS FIRST Answer all the 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

2 Task 1 A&J Tuition Agency uses spreadsheet software to record the financial details of the company. You are required to finish setting up the spreadsheet to record the details. Open the file TUITION.XLSX. You will see the following data. Save the file as TUITION_<your name>_<class>_<index number> Click on the Salary Pay-out Table worksheet to do the following questions. 1 Use an appropriate function to search for the Salary Scheme in the Job Scheme Table and use it to complete the Salary Scheme (per Hour) column. [2] 2 Enter a formula to calculate the monthly salary for each worker and use it to complete the Monthly Salary column. [1] 3 In cell E14 enter a formula to calculate the total monthly salary paid out by the agency. [1] 4 In cell B20 enter a formula to calculate the total number of hours of tuition taught by the tutors of the agency. [1] Click on the Bank Loan worksheet to do the following questions. 5 In cell B6 enter a formula to calculate the amount of loan repayment that is required based on the values in cell B3 to B5. [1]

3 Click on the Overall worksheet to do the following questions. 6 In cell E7 enter a formula to calculate the amount of earnings of the agency, from tuition, based on the total number of classes, students per class and amount paid per student. [1] 7 In cell B10 enter a formula to calculate the total earnings of the tuition agency after deduction of the monthly liabilities. [1] 8 In cell B11 enter a conditional statement to identify the total earnings calculated in cell B10. If the total earnings calculated is below $0, put Loss. If it is above $0, put Profit. And if it is equal to $0, put Break Even. [1] Save the file as TUITION_IF_<your name>_<class>_<index number> 9 Use an appropriate feature of spreadsheet to find out how many students per class is required in order for the T