SPS 2019 P2

READ THESE INSTRUCTIONS FIRST Write your name, class, index number in the spaces at the top of this page. Write in dark blue or black pen. 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. Parent’s Signature : ____________________________ Date: ____________________________ Remarks (if any) : For Examiner’s Use Marks /50 Total % This document consists of 7 printed pages including this page and ANNEX A. ST. PATRICK'S SCHOOL PRELIMINARY EXAMINATIONS 2019 SECONDARY 4 EXPRESS NAME CLASS INDEX NUMBER COMPUTING Paper 2 Practical (Lab-based) 7155/02 2 September 2019 2 h 30 min Additional Materials: Electronic version of RESORT.xlsx data file Electronic version of DISARIUM.py file Electronic version of RATING.py file Insert Quick Reference Glossary (ANNEX A)

2 [Turn over

3 [Turn over

4 [Turn over Task 1 A holiday resort, Red Resort uses spreadsheet software to calculate the cost of a holiday for each customer. The resort has five types of rooms and offers 20% discount for members. You are required to finish setting up the spreadsheet to calculate the cost for each customer. Open the file RESORT.xlsx. You will see the following data. Save the file as NOVRESORT_<Class>_<Class_Index_Number>_<Your_Name> 1 In cell C20 enter a formula to count the number of customers. [1] 2 In cell C21 enter a formula to count the number of Members. [1] 3 Use an appropriate function to search for the Cost per Night in the Room Type table, and use it to complete the Cost column. The cost must take into consideration the Number of Nights each customer is staying. [2] 4 Use a conditional statement to identify whether a customer will receive the Discount. Only members will receive a 20% discount and the corresponding cells must display this discount. [2] 5 Use a conditional statement to calculate the total cost of each customer’s stay at the resort in the Total Cost column. The Total Cost must take into account any discount given to the customer. [2] 6 In cell H20 enter a formula to calculate the average revenue for the month. If the average revenue exceeds $400, use conditional formatting to fill cell H20 with green colour. [2]

5 [Turn over Task 2 At a particular company, employees are rated at the end of each year. The rating scale begins at 0.0, with higher values indicating better performance and resulting in larger raises. The value awarded to an employee is either 0.0, 0.5, or 0.6. Values between 0.0 and 0.5, and