20. Spreadsheets
A section of Information and Communication Technology, 0417
Listing 10 of 51 questions
An examiner has created a spreadsheet to calculate his travel and hotel expenses when he attends meetings. Part of the spreadsheet that he has created is shown below. Departure date Return date ID Destination Distance Hotel per night Total cost for hotel Travel cost 2018/01 2018/02 2018/03 Cambridge Coventry Manchester Distance threshold greater than 400 400 or less Distance rate 10/01/2019 15/02/2019 20/02/2019 149.8 $196.40 $250.20 $196.40 $175.50 $180.00 $67.41 $98.20 $83.40 $98.20 $0.25 $0.45 12/01/2019 18/02/2019 22/02/2019 A B C D E F G H I J K If the car mileage is greater than 400 km he gets paid $0.25 per km for the whole distance otherwise he gets paid $0.45 per km for the whole distance. Write a formula, that is easy to replicate, to be placed in cell H2 that calculates the travel cost for the meeting. Your formula must work even when the distance rates change. = Write a formula, that is easy to replicate, to be placed in cell G2 that calculates the total cost for the hotel for the meeting. = When the spreadsheet is completed the examiner will need to list the details of all the meetings where the distance is 400 km or greater. Explain how the examiner would use a filter to achieve this. A computer can be used to model scenarios. Explain why computer models are used instead of creating the real thing.
0417_w19_qp_13
THEORY
2019
Paper 1, Variant 3
View
Khalid keeps a spreadsheet to record his expenses and to plan for future spending. This spreadsheet is split into two separate sheets, which he has named Plan and Model. Plan contains details of his future spending. Model contains a model of his income and expenses. Plan A Income Statement Income Expenditure items Taxes Housing Food Transportation Entertainment / Other Healthcare Investment return Income Growth Expense Ratio % % of spending 63,784 12,757 19,135 6,378 9,568 6,378 3,189 4% 2.5% 90% 22.22% 33.33% 11.11% 16.67% 11.11% 5.56% 20% 30% 10% 15% 10% 5% % of income B C D Housing Food Transportation Model A Model Income Investment Income Total Income Taxes Entertainment / Other Can I afford a car? Savings Balance Sheet Net Income (Savings) Total Expenses Healthcare 72,166 70,406 68,688 67,013 1,450 1,123 65,379 73,616 71,529 69,504 67,540 65,634 64,950 63,366 61,820 60,312 58,842 57,405 63,784 63,784 14,433 14,081 13,738 13,403 13,076 12,757 21,650 21,122 20,607 20,104 19,614 19,135 10,825 10,561 10,303 10,052 9,807 9,568 7,217 7,041 6,869 6,701 6,538 3,608 3,520 3,434 3,351 3,269 3,189 7,217 7,041 6,869 6,701 6,538 44,912 36,246 28,083 20,399 13,171 8,666 8,163 7,684 7,228 6,792 Y Y N N N N 6,379 6,379 6,378 6,378 B C G F E D Khalid has entered a formula in cell C4 of the Model sheet. The formula is B4+(B4*Plan!$B13) Explain, in detail, what the formula does. Include in your answer an explanation of why the $ and the ! are used in the formula. He is saving up to buy a new car; this will cost at least $35,000. Khalid has entered a formula in cell B21 of the Model sheet. The formula is IF(B20>35000,"Y","N") Explain, in detail, what the formula does. Plan A Income Statement Income Expenditure items Taxes Housing Food Transportation Entertainment / Other Healthcare Investment return Income Growth Expense Ratio % % of spending 63,784 12,757 19,135 6,378 9,568 6,378 3,189 4% 2.5% 90% 22.22% 33.33% 11.11% 16.67% 11.11% 5.56% 20% 30% 10% 15% 10% 5% % of income B C D Khalid is planning to create an appropriate graph/chart to be placed in a new sheet. The graph/chart will display the % of income and the names of the expenditure items from the Plan sheet. Identify the most appropriate graph/chart he could use and describe the steps he needs to take to produce this graph/chart in a new sheet.
0417_w20_qp_12
THEORY
2020
Paper 1, Variant 2
View
A teacher is setting up a school shop. The teacher is setting up a spreadsheet to keep the accounts of the shop. shows a box containing 50 Choco Bars to sell in the shop. The information on the box will be entered into the spreadsheet. Contains: Sell by: 1/4/2025 Special Price Only $1.20 Choco Bars D A B C State the most appropriate display format in the spreadsheet for each piece of data shown on the box. Your answer must be different in each case. A B C D Each box delivered to the school shop includes a QR code. Explain why QR codes are used. [ 1 10 The teacher has created a formula in the spreadsheet that calculates the cost of purchasing a number of different items. She has created a formula that: • subtracts the discount per item from the price of the item to find the cost of the item • then multiplies the cost of the item by the quantity. Product A Choco Bar Quantity D Price of item B $1.20 Total E –$1.80 F Discount per item C $0.20 The formula she enters into cell E2 is: =B2-C2*D2 When this formula is entered into cell E2 the result is not what she expected. The result should be $15.00 Explain why the result was not $15.00 1 10
0417_w23_qp_13
THEORY
2023
Paper 1, Variant 3
View
Recaud Inc. stock many CDs. Below is a small selection of CDs stored on their database. ID number Artist_name CD_title Price (Rs) Year_released Number_of_tracks Nayan Pancholi Maa Na Nayan 249.00 Nayan Pancholi, Gargi Vora Maa Amba 145.00 Lakshmi Shankar Ecstasy 120.00 Anindo Chatterjee Anindo and His Tabla 230.00 Jagjit Singh Greatest Hits 255.00 Write down the most suitable data type for the following fields. CD_title Price (Rs) Year_released Number_of_tracks Describe the range check which could be carried out on the Price (Rs) field. The maximum price and the minimum price are shown in the table above. The owner of the shop, Alam, receives requests from customers about the CDs he has in stock. He makes a note of these requests using comparison and Boolean operators. For example, a customer might ask for a CD which was released after 2002 and has at least 7 tracks. Alam would write this down as: Year_released > 2002 AND Number_of_tracks >= 7 Write down a request for a customer who wants a list of all the CDs which were released before 2007. This list must also contain all the CDs which cost at least 145 rupees. Write down the ID numbers of those records which would match your criteria in .
0417_m15_qp_12
THEORY
2015
Paper 1, Variant 2
View
For Examiner's Use 14 The manager of a travel company asked her secretary to look into the reliability of trains in Birmingham. The secretary obtained the details of some of the trains. He created two spreadsheets to help him do this. Sheet 1 is a list of some of the UK stations that trains from Birmingham go to. Sheet 2 consists of the train details he looked at. A B Code Station E Edinburgh LB Longbridge B Bournemouth LI Lichfield S Shrewsbury W Wolverhampton LO London P Plymouth Sheet 1 (Commas are used as delimiters in the functions shown below.) A B C D Arrival Station Code Number =VLOOKUP(B2,Sheet1!$A$2:$B$9,2) E =COUNTIF($C$9:$C$18,B2) =VLOOKUP(B3,Sheet1!$A$2:$B$9,2) LO =COUNTIF($C$9:$C$18,B3) =VLOOKUP(B4,Sheet1!$A$2:$B$9,2) S =COUNTIF($C$9:$C$18,B4) =VLOOKUP(B5,Sheet1!$A$2:$B$9,2) LI =COUNTIF($C$9:$C$18,B5) =VLOOKUP(B6,Sheet1!$A$2:$B$9,2) W =COUNTIF($C$9:$C$18,B6) Arrival time Minutes late Arrival code Late Y/N 11:03 E =IF(B9>0,"Y","N") 11:05 LO =IF(B10>0,"Y","N") 11:07 -4 W =IF(B11>0,"Y","N") 11:10 LO =IF(B12>0,"Y","N") 11:12 S =IF(B13>0,"Y","N") 11:14 -2 LI =IF(B14>0,"Y","N")
0417_s13_qp_12
THEORY
2013
Paper 1, Variant 2
View
For Examiner's Use 15 A Sports Science student had to analyse the performance of athletes prior to the 2012 Olympic Games. He created a spreadsheet to do this. Below is his analysis of the javelin event. It is split into two sheets. Sheet 1 contains the Code for each nationality. Sheet 2 contains a list of athletes and the results they achieved in a pre-Olympic event. A B Code Country AUS Australia FRA France GHA Ghana RUS Russia THA Thailand TUN Tunisia TUR Turkey USA America Sheet 1 (Commas are used as delimiters in the functions shown below.) Because athletes sometimes miss a throw or throw outside the area, this is recorded in the spreadsheet as a no throw using the letters NT. A B C D E F G H Country Athlete Code Throw Throw Throw Total Number thrown of throws =LOOKUP(C3,Sheet1!$A$2:$B$9) Chevonne Baines USA 85.02 73.29 76.9 =SUM(D3:F3) =COUNTIF(D3:F3,”<>NT”) =LOOKUP(C4,Sheet1!$A$2:$B$9) Mary Bempe GHA NT 66.64 70.61 =SUM(D4:F4) =COUNTIF(D4:F4,”<>NT”) =LOOKUP(C5,Sheet1!$A$2:$B$9) Rhiannon Jones USA 81.22 68.4 71.18 =SUM(D5:F5) =COUNTIF(D5:F5,”<>NT”) =LOOKUP(C6,Sheet1!$A$2:$B$9) Abhasra Boonme THA 83.04 76.43 60.35 =SUM(D6:F6) =COUNTIF(D6:F6,”<>NT”) =LOOKUP(C7,Sheet1!$A$2:$B$9) Jean Claude Devandre FRA 68.04 45.98 62.62 =SUM(D7:F7) =COUNTIF(D7:F7,”<>NT”) =LOOKUP(C8,Sheet1!$A$2:$B$9) Brett Lewis AUS 79.98 68.78 NT =SUM(D8:F8) =COUNTIF(D8:F8,”<>NT”) =LOOKUP(C9,Sheet1!$A$2:$B$9) Vera Stepanov RUS 70.42 49.88 90.18 =SUM(D9:F9) =COUNTIF(D9:F9,”<>NT”) =LOOKUP(C10,Sheet1!$A$2:$B$9) Murat Mehmet TUR 49.81 67.51 53.66 =SUM(D10:F10) =COUNTIF(D10:F10,”<>NT”) =LOOKUP(C11,Sheet1!$A$2:$B$9) Joan Lewington USA 88.85 NT 69.44 =SUM(D11:F11) =COUNTIF(D11:F11,”<>NT”) =LOOKUP(C12,Sheet1!$A$2:$B$9) Youssef Ibrahim TUN 93.39 63.1 47.14 =SUM(D12:F12) =COUNTIF(D12:F12,”<>NT”) =LOOKUP(C13,Sheet1!$A$2:$B$9) Laura Bennett AUS 55.97 49.45 70.07 =SUM(D13:F13) =COUNTIF(D13:F13,”<>NT”) Sheet 2 Explain what the function in cell A3 does. For Examiner's Use What value would you expect to see in cell A6? Explain what the function in cell G3 does. Explain what the function in cell H4 does. What value would you expect to see in cell H6? Spreadsheets are often used to produce computer models. There are however, other forms of models such as simulations. Explain, using air pilot training as an example, what is meant by simulation.
0417_s13_qp_13
THEORY
2013
Paper 1, Variant 3
View
A farmer has purchased a computerised milking system for her cows. She has asked a systems analyst to create a database to store details of the cows being milked. Examples of the details of the cows which will be stored are: Animal_passport_number Average_milk_yield Weight_of_cow Date_of_birth Breed_of_cow 998/2017 24.5 725.9 25/02/2017 Holstein 972/2016 20.1 715.0 15/03/2016 Ayrshire 971/2016 25.0 732.7 25/02/2017 Jersey 765/2016 25.0 715.0 10/10/2016 Holstein Complete the following table by entering the most appropriate data type for each field. For any numeric field, specify the type of number. Data type Field name Breed_of_cow Date_of_birth Weight_of_cow Average_milk_yield Animal_passport_number State which field would be the most appropriate for the primary key. A validation check is used when entering the animal passport number into the database. Name and describe the most appropriate validation check that could be applied to this field. [ 1 18 The amount of milk each cow produces is currently recorded daily in a spreadsheet. This is part of the spreadsheet. A B C D Daily Milk Yield Animal Passport Number 971/2016 Date of milking Amount produced 26/04/2018 27/04/2018 28/04/2018 29/04/2018 30/04/2018 01/05/2018 02/05/2018 03/05/2018 04/05/2018 05/05/2018 Litres 24.5 23.2 22.1 25.1 24.9 24.7 24.6 23.9 23.9 23.8 You have been asked to produce a graph or chart to show the amounts of milk for the cow with Animal Passport Number 971/2016. Describe the steps you would use to produce a graph or chart of this data as a separate sheet. Include in your answer the name of the new sheet. 1 18
0417_s18_qp_11
THEORY
2018
Paper 1, Variant 1
View
An auction company sells toys. It uses a spreadsheet to show each person’s items and the amount of money the buyer and seller owe to the company. Part of the spreadsheet is shown below. A B C D E F G H I J Auction: 18th July 2017 Name of Seller Item code Item description Valuation Selling price Buyer’s premium Seller’s commission Product value Commission 608a Double Decker bus toy € 20.00 € 45.00 € 10.00 € 1.50 608b Blackpool Balloon tram € 25.00 € 45.00 € 50.00 € 2.00 608c Blackpool Steam tram € 25.00 € 40.00 € 100.00 € 2.50
0417_s18_qp_12
THEORY
2018
Paper 1, Variant 2
View
A teacher is creating a spreadsheet that will record the grades students achieved in recent tests and compare the average grades of these tests with the students’ forecast grades. The grading the teacher is using awards 1 for the highest grade down to 10 for the lowest. The teacher has created a validation rule on the grade entered. Give examples of data used to test the validation rule. Your answers for abnormal test data must be for a different reason in each case. Example of test data Type of test data Normal Abnormal Abnormal Extreme [ 1 15 Part of the spreadsheet is shown, column G subtracts the Forecast grade from the Average grade. Describe the steps the teacher can take to edit the spreadsheet to prevent new data being typed into the Forecast grade column. The teacher has typed a formula in cell E3. The formula is ROUNDUP(AVERAGE(B3:D3),0) Explain, in detail, what the formula does. 1 15 The teacher has produced a graph showing the differences between the forecast grade and the average grade for each student. The graph looks like this. Describe the steps taken to create the graph. Include in your answer three improvements that could be made to the graph. Method Improvement 1 Improvement 2 Improvement 3 [ 1 15
0417_s20_qp_12
THEORY
2020
Paper 1, Variant 2
View
Questions Discovered
51