Spreadsheets and ICT (Foundation) - Worksheets, Questions and Revision

18 original exam-style questions - 10 pages of questions with a full mark scheme - free printable PDF.

Download PDF Jump to mark scheme (page 11)Read the revision guide

Next in this strand

Revision Library

revisionlibrary.co.uk

FOUNDATION

F10 Spreadsheets and ICT (Foundation)

EDEXCEL 1ST0 · Calculator allowed · about 75 minutes

Total Marks

Name: _______________________________ Date: ____ / ____ / ______

Answer ALL questions. Show all your working.

A market stall holder keeps a record of the items she sells in a spreadsheet, as shown below.

(a)Write down the value stored in cell B3.(1)

(b)Write down the cell reference of the cell that contains the value 60.(1)

(c)Write down the value stored in cell A5.(1)

(Total for Question 1 is 3 marks)

For each statement about spreadsheets, write down whether it is True or False.

(i)A formula typed into a spreadsheet cell must begin with an equals sign (=).(1)

(ii)Once a number has been typed into a spreadsheet cell, it can never be changed afterwards.(1)

(Total for Question 2 is 2 marks)

The stall holder from Question 1 could instead add up her total sales by hand, using a calculator and a pen-and-paper list.

Give one advantage of using a spreadsheet, rather than doing the calculation by hand, to keep a record of her sales.

(Total for Question 3 is 1 mark)

The stall holder now adds a fourth column, Total takings (£), to work out how much money each item brought in.

(a)Write down the formula that should be typed into cell D2 to work out the total takings for crisps.(1)

(b)Work out the value that would appear in cell D2.(2)

(c)The formula from part (a) is copied down from cell D2 into cell D3. Write down the value that will appear in cell D3.(1)

(Total for Question 4 is 4 marks)

A student records the rainfall, in mm, for one week in a spreadsheet, as shown below. She wants cell B9 to show the total rainfall for the week.

(a)Write down a spreadsheet formula, using a named function, that could be typed into cell B9 to add up all the rainfall values in cells B2 to B8.(2)

(b)Work out the value that would appear in cell B9.(1)

(Total for Question 5 is 3 marks)

The student now wants cell B10 to show the mean rainfall per day for the week, using the spreadsheet from Question 5.

(a)Write down a spreadsheet formula, using a named function, that could be typed into cell B10 to find the mean rainfall for the week.(2)

(b)Given that the total rainfall for the week (cell B9) is 28 mm, work out the value that would appear in cell B10.(2)

(Total for Question 6 is 4 marks)

A PE teacher enters the scores, out of 20, that 6 pupils achieved in a fitness test into a spreadsheet, as shown below. She then sorts the data using column B, smallest to largest.

(a)Write down the name that would appear in row 2 after the data has been sorted.(1)

(b)Write down the name that would appear in row 7 after the data has been sorted.(1)

(c)Write down the value that would appear in cell B4 after the data has been sorted.(1)

(Total for Question 7 is 3 marks)

The teacher then applies a filter to the original (unsorted) spreadsheet from Question 7, so that only pupils who scored 15 or more are shown.

(a)Write down the names of the pupils that would remain visible after this filter is applied.(2)

(b)Give one advantage of using a spreadsheet filter, rather than checking each score by hand, to find the pupils who scored 15 or more.(1)

(Total for Question 8 is 3 marks)

A student records, in a spreadsheet, the number of pupils who chose each of 4 sports as their favourite.

Sport	Number of pupils
Football	12
Swimming	6
Athletics	5
Rugby	7

She highlights this data and uses the spreadsheet's chart tool to produce a chart.

(a)State the type of chart that would be most appropriate for this data: a bar chart, a line graph, or a scatter graph.(1)

(b)Give a reason for your answer to part (a).(1)

(c)A second student says a pie chart would also be a suitable way to display this data, because it can show each sport as a proportion of the 30 pupils asked. Is the second student correct? Give a reason.(1)

(Total for Question 9 is 3 marks)

A spreadsheet formula, =B2*C2, is typed into cell D2, and is then copied down into cells D3, D4 and D5.

(a)Explain what happens to the cell references in the formula when it is copied down from cell D2 into cell D3.(2)

(b)A different formula, =B2*$G$1, is typed into cell E2, where cell G1 holds a fixed discount rate. This formula is also copied down into cells E3, E4 and E5. State which cell reference stays exactly the same in every copy of the formula, and name this type of cell reference.(2)

(Total for Question 10 is 4 marks)

A market research company plans to find out how often people in a town use public transport. They will use ICT, such as a spreadsheet, at different stages of the statistical enquiry cycle: plan, collect, process and interpret.

(a)Give one way ICT could be used at the collect stage of this enquiry.(1)

(b)Give one way ICT could be used at the process stage of this enquiry.(1)

(c)Give one way ICT could be used at the interpret stage of this enquiry.(1)

(d)Give one limitation of relying on ICT during this enquiry.(1)

(Total for Question 11 is 4 marks)

A researcher is deciding how to collect data from 500 customers about their shopping habits. She is considering two methods: a paper questionnaire filled in by hand, or an online survey using ICT.

(a)Give one advantage of using an online survey, rather than a paper questionnaire, to collect data from this many customers.(1)

(b)Give one disadvantage of using an online survey, rather than a paper questionnaire, to collect data from this many customers.(1)

(c)The researcher decides to store all 500 responses in a database, rather than in a single spreadsheet. Give one reason why a database might be more suitable than a spreadsheet for this large amount of data.(1)

(Total for Question 12 is 3 marks)

A sports club uses a spreadsheet to keep track of the monthly fees paid by its 5 members, as shown below.

(a)Write down the formula that should be typed into cell D2 to calculate the total amount paid by Aaliyah this year.(1)

(b)Work out the value that would appear in cell D4 (Chidi's total amount paid).(2)

(c)Write down a spreadsheet formula, using a named function, that could be typed into cell D7 to find the grand total paid by all 5 members.(2)

(d)Given that the total amounts paid are Aaliyah £120, Ben £120, Chidi £180, Divya £108 and Elin £108, work out the value that would appear in cell D7.(1)

(Total for Question 13 is 6 marks)

The sports club treasurer relies entirely on the spreadsheet formulas from Question 13 to work out how much each member owes.

(a)Give one disadvantage of relying on a spreadsheet formula, rather than checking the calculation by hand, to work out these totals.(1)

(b)Give one way the club could check that the spreadsheet formulas are working correctly.(1)

(Total for Question 14 is 2 marks)

A shopkeeper uses a spreadsheet to add VAT to the price of items. Cell F1, elsewhere on the sheet, holds the VAT rate as a decimal, 0.2. Column A holds the item name, column B holds the price before VAT (£), and column C is used to calculate the price including VAT.

Item	Price before VAT (£)
Kettle	25.00
Toaster	18.00
Blender	32.00

(Kettle is in row 2, Toaster in row 3, Blender in row 4.)

(a)Write down a formula that could be typed into cell C2 to work out the price of the kettle including VAT, using an absolute reference to cell F1.(2)

(b)This formula is copied down from cell C2 into cells C3 and C4. Explain why the reference to cell F1 must be absolute (contain $ signs) for this to work correctly.(2)

(c)Work out the value that would appear in cell C4 (the price of the blender including VAT).(2)

(Total for Question 15 is 6 marks)

A student says: "A chart produced by spreadsheet software is always more reliable than a chart drawn by hand."

Comment on this statement, giving reasons for and against it.

(Total for Question 16 is 3 marks)

A spreadsheet lists the times, in seconds, taken by 6 runners to finish a 100 m race.

Runner	Time (seconds)
Priya	13.9
Omar	14.5
Sam	12.8
Tia	15.2
Leo	13.1
Noor	14.0

(a)The times are sorted smallest to largest. Write down the runner's name that would appear in row 4 of the sorted list (row 2 is the first data row).(1)

(b)A filter is then applied to the sorted list to show only runners with a time under 14 seconds. Write down the names that would remain visible.(2)

(Total for Question 17 is 3 marks)

A national charity wants to survey 10 000 supporters about how often they volunteer, then compare the results year on year.

Recommend whether the charity should collect responses using a paper questionnaire or an online survey tool. Use the statistical enquiry cycle to justify your answer, referring to at least two stages (for example plan, collect, process or interpret).

(Total for Question 18 is 3 marks)

Mark scheme · F10 Spreadsheets and ICT

Question 1

(a) B1 0.85 oe (£0.85) cao
(a) Answer: 0.85
(b) B1 C4 cao
(b) Answer: C4
(c) B1 Apple cao
(c) Answer: Apple

Question 2

(i) B1 True cao
(i) Answer: True
(ii) B1 False cao
(ii) Answer: False

Question 3

B1 any valid advantage, e.g. it recalculates totals automatically when a value changes, oe it is faster, oe it reduces the chance of an arithmetic error
Answer: A spreadsheet updates every total automatically when a number is changed, so there is less chance of making an arithmetic error.

Question 4

(a) B1 =B2*C2 oe (accept =C2*B2)
(a) Answer: =B2*C2
(b) M1 0.60 x 40, oe
(b) A1 24 or 24.00 cao
(b) Answer: 24.00
(c) B1 21.25 ft (0.85 x 25)
(c) Answer: 21.25

Question 5

(a) B1 SUM used as a named function, e.g. =SUM(...)
(a) B1 correct range B2:B8
(a) Answer: =SUM(B2:B8)
(b) B1 28 (mm) cao
(b) Answer: 28

Question 6

(a) B1 AVERAGE used as a named function, e.g. =AVERAGE(...)
(a) B1 correct range B2:B8
(a) Answer: =AVERAGE(B2:B8)
(b) M1 28 / 7, oe
(b) A1 4 (mm) cao
(b) Answer: 4

Question 7

(a) B1 Chen cao
(a) Answer: Chen
(b) B1 Dana cao
(b) Answer: Dana
(c) B1 14 cao
(c) Answer: 14

Question 8

(a) B1 Beth and Dana identified (scores of 18 and 20, both at least 15)
(a) B1 Farah also identified, with Amir, Chen and Ewan correctly excluded
(a) Answer: Beth, Dana, Farah
(b) B1 any valid advantage, e.g. it is quicker, oe it is less likely that a qualifying pupil is missed or an error is made
(b) Answer: It is quicker and less likely that a pupil who qualifies is accidentally missed.

Question 9

(a) B1 bar chart cao
(a) Answer: Bar chart
(b) B1 valid reason, e.g. the sports are categories, not points in time or paired numerical values, so a line graph or scatter graph would not be suitable
(b) Answer: A line graph is for data collected over time and a scatter graph is for pairs of numerical values, but sport is a category, so neither would be suitable here.
(c) B1 Yes, with a valid reason, e.g. the data is categorical and the frequencies add up to a total (30 pupils), so a pie chart can show each sport's share of that total
(c) Answer: Yes. Because the sports are categories that add up to a total of 30 pupils, a pie chart can show each sport's share of the whole group.

Question 10

(a) B1 the cell references adjust/change automatically as the formula is copied down
(a) B1 correctly states the copied formula becomes =B3*C3
(a) Answer: The references shift down a row with the formula, so the copy in D3 becomes =B3*C3.
(b) B1 G1 stays the same in every copy
(b) B1 named as an absolute cell reference oe (fixed by the $ signs)
(b) Answer: G1 stays the same in every copy; this is called an absolute cell reference.

Question 11

(a) B1 any valid use, e.g. an online survey/questionnaire gathers responses directly into a spreadsheet or database
(a) Answer: An online questionnaire could collect people's answers directly into a spreadsheet.
(b) B1 any valid use, e.g. a named function such as SUM or AVERAGE calculates a total or the mean number of journeys
(b) Answer: A spreadsheet function such as AVERAGE could calculate the mean number of journeys per person.
(c) B1 any valid use, e.g. a chart/graph produced from the spreadsheet data helps to identify patterns or trends
(c) Answer: A chart produced from the spreadsheet data could help show which age groups use public transport most.
(d) B1 any valid limitation, e.g. data entered wrongly or a formula containing an error could lead to a wrong conclusion if nobody checks it
(d) Answer: If the data is typed in wrongly, or a formula has an error, the conclusions could be wrong unless someone checks the results.

Question 12

(a) B1 any valid advantage, e.g. responses are collected and stored automatically, so results do not need to be entered by hand and can be processed much faster
(a) Answer: Responses go straight into a spreadsheet or database, so nobody has to type up 500 paper forms by hand, which saves a lot of time.
(b) B1 any valid disadvantage, e.g. customers without internet access or a suitable device cannot take part, so the sample might not be representative
(b) Answer: Customers who do not use the internet cannot respond, so the results might not represent all 500 customers fairly.
(c) B1 any valid reason, e.g. a database can store, search and organise a much larger amount of data more efficiently, or allows several people to access and update records at the same time
(c) Answer: A database is built to store and search through large numbers of records efficiently, and can let several people update it at once.

Question 13

(a) B1 =B2*C2 oe
(a) Answer: =B2*C2
(b) M1 15 x 12, oe
(b) A1 180 cao
(b) Answer: 180
(c) B1 SUM used as a named function, e.g. =SUM(...)
(c) B1 correct range D2:D6
(c) Answer: =SUM(D2:D6)
(d) B1 636 cao
(d) Answer: 636

Question 14

(a) B1 any valid disadvantage, e.g. if the formula contains an error (such as the wrong cell reference), every value calculated from it will also be wrong, and this might not be noticed
(a) Answer: If a formula has a mistake in it, such as the wrong cell reference, every total it produces will be wrong, and this could easily go unnoticed.
(b) B1 any valid check, e.g. calculate one member's total by hand and compare it with the spreadsheet's answer
(b) Answer: Work out one member's total by hand and check that it matches the value the spreadsheet gives.

Question 15

(a) B1 correct structure, e.g. =B2*(1+F1) oe (accept =B2+B2*F1)
(a) B1 the reference to F1 is written as an absolute reference, $F$1
(a) Answer: =B2*(1+$F$1)
(b) B1 without $ signs, the reference would change/shift when copied down, e.g. to F2 then F3
(b) B1 F2 and F3 are empty, so the copied formulas would give the wrong answer (e.g. zero VAT) instead of using the rate in F1
(b) Answer: Without the $ signs, the reference would shift down to F2 and then F3 as the formula is copied, and those cells are empty, so the toaster and blender would be given the wrong price instead of using the VAT rate in F1.
(c) M1 32 x 1.2, oe
(c) A1 38.40 cao (accept 38.4)
(c) Answer: 38.40

Question 16

B1 a clear judgement is given (e.g. partly agree/it depends), rather than a simple yes or no with no comment
B1 a reason supporting the statement, e.g. the spreadsheet plots values exactly as entered, avoiding the plotting/measuring errors that can happen when a chart is drawn by hand
B1 a reason against the statement, e.g. the chart is only as accurate as the data or formulas entered, so wrong data or a formula error still produces a wrong (but neatly drawn) chart
Answer: It depends. A spreadsheet chart avoids hand-drawing errors because it plots the exact values entered, but it is only as reliable as the data and formulas behind it, so a mistake when entering the data would still produce an inaccurate chart.

Question 17

(a) B1 Priya cao
(a) Answer: Priya
(b) B1 Sam and Leo identified (12.8 and 13.1, both under 14 seconds)
(b) B1 Priya also identified, with Noor, Omar and Tia correctly excluded (Noor's 14.0 is not under 14)
(b) Answer: Sam, Leo, Priya