SFSS 4EX_Computing_Prelim_QP2_2022

[Turn Over SPRINGFIELD SECONDARY SCHOOL Preliminary Examination 2022 Secondary 4 Express/ 5 Normal Academic STUDENT NAME CLASS REGISTER NUMBER COMPUTING 7155/02 Paper 2 (Lab-based) 13 September 2022 2 hours 30 minutes Additional Materials: Electronic version of CARSALES.xlsx file Electronic version of EXCESSBAGGAGE.py file Electronic version of DICEGAME.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. For Examiner’s Use Task 1 /10 Task 2 /10 Task 3 /10 Task 4 /20 Total /50 Do not turn over this question paper until you are told to do so. ______________________________________________________________________________________________________ This question paper consists of 9 printed pages.

2 Task 1 A car sales company used spreadsheet software to calculate the revenue for its sales in year 2021. The company sold 8 models of car consisting of petrol, hybrid and electric type. You are required to finish setting up the spreadsheet to calculate the revenue generated by the car sales for year 2021. Open the file CARSALES.xlsx Save the file as MYCARSALES _<your name>_<class>_<index number>.xlsx 1 The three digits in the car model represents the power output of the car. In cells B4 to B11 enter a formula that uses a function to show the power output of each car model. [2] 2 In cell C13 enter a formula to show the total car sales. [1] 3 In cell C14 enter a formula to show the total number of electric cars sold. [1] 4 Use an appropriate function to search for the Rebate Amount in the Rebate Table, and use it to complete the Revenue (After rebate) column. [2]

3 [Turn Over 5 In the Top Sales column enter a formula that uses functions to find the car model that generates the most revenue and display the text 1st for the car model. All other cells in the column must be empty [2] 6 In cells A4 to F11 use a conditional formatting tool to make the text red in the row that contains the lowest revenue. [2]

4 Task 2 The following program is used by an airline to calculate the excess baggage co