Year 10 ICT Functional Skills Exam – May 2012

Exam Instructions

You have 2 hours to complete this exam

You are advised to spend 15 minutes on Section A and 1 hour and 45 minutes on Section B

You can use the internet for the first 15 minutes only

All the files you need for the exam can be found in: Shared:\Pupils\Senior Pupils\ICT\Functional Skills Level 2\June 2012 Exam Files

Save your work in your documents

Please make sure that you include your name and your science set (S1, S2, S3 or D1) on every print out.

You do not need to add your centre number or candidate number

Hand in your printouts at the end in question order.  You do not need a cover sheet, to hole punch your printouts or attach them together with a treasury tag.

10D1 Period 4 Monday 11th June 2012

Working with numbers and charts – Functional Skills Module 5 – Lesson 11

In this lesson students are learning how to:

• display numerical data in graphical form

STARTER 

Look at the image L11-1-Bar-Chart-no-info.

What is it?  What does it tell you?  What is missing?

Look at the bar chart below.  What makes this chart more meaningful?  What is still missing?  For example: Sales – of what?

TASK 1 – Which type of chart should you use? 

Bar charts 

Column and bar charts are useful if you want to compare different values, such as sales each month or different answers to a survey question.  The graph above is a bar chart showing income from sales for the four quarters of the year.  The sales value for a quarter can be found by ‘reading’ the height of the bar off the y-axis (Sales).  The line shows that sales in the autumn quarter were about £57,000.

Pie charts

Pie charts are good for showing proportions or percentages, but they do not work well if there are too many items.  Here is the same information shown now in a pie chart.  A pie chart is used when you want to show each data value as a percentage of the total.  This chart shows that the autumn quarter sales were £56,800 and that the quarter’s sales are 28% of the total.

Line graphs 

Line graphs are used to show values that are always changing.  For example, you could use a line graph to show the temperature over a year.  There is a graph of petrol costs for car journeys of different distances below. This is known as a ‘straight line graph’.  The two red lines that have been added show that a journey of 200 miles costs about £31.  Approximately how much would a journey of 120 miles cost?

What other charts and graphs do you know or have you seen?

TASK 2 – Creating a Bar Chart 

When creating a chart, you need to ask yourself the following questions:

• Is the type of chart suitable?
• Is the scale sensible?
• Is it easy to read the values from the chart?
• Are the headings and labels clear?  Do they give enough information to the reader?
• Are the colours clear?

Meaningful title 

The title is the most important item.  It should clearly say what the chart shows or what questions the chart answers.

Sensible axis labels 

Make sure you label what each axis shows.  Labels help the audience to understand the chart.

Gridlines and values 

You should be able to read off the exact values of each part of the chart.  You can insert gridlines and values to make this easier.

Legend and data labels 

Your chart will include the row and column headings from your spreadsheet.  These may be too long to use as horizontal data labels.  Sometimes you can change the angle.  Alternatively, you can use a legend.

Complete the Skills Builder (5.7) exercise.

Here are the results of a survey of 335 people who were asked about their favourite type of film.

Favourite type of film	Number of responses
Comedy	92
Action	63
Romance	48
Drama	50
Horror	37
Foreign	27
Sci-fi	29

• Plot this information as a vertical bar chart and set the titles as follows:
• • Chart title – ‘Favourite film categories’
  • X-axis title – ‘Film category’
  • Y-axis title – ‘Percentage’
• Make sure the x-axis labels are all shown.  You may need to set them at an angle.
• Do NOT show a chart legend.
• Plot the same data as a pie chart.
• Make sure a chart legend is shown that clearly identifies each ‘slice’ of the pie.
• Show the percentage value with two decimal places against each slice.
• Explain why this data is NOT suitable for plotting as a line graph. TASK 3 – Creating a Line Graph  Open SB5.8.1 – Weather Records.  What features add meaning to this spreadsheet?   e.g. two titles, tab with meaningful name rather than Sheet1 or 2.  Can you describe the layout of the spreadsheet?  Is the block of numerical data where you expected it?  Key points  Structured data – e.g. records, fieldnames, appropriate formatting (text wrap in row 6), and Finding and selecting informationYou need to acknowledge your sources and so it is useful to paste addresses of sources into a document.
  1. If you knew that you had to use AutoFilter, would you use a layout such as this for digital asset SB5.8.1?  If not – why not?
  2. Do you think you can use AutoFilter with digital asset SB5.8.1?

  Create a line graph – use HIGHEST and LOWEST temperatures, ready for printing out.  REMEMBER layout matters – a good layout for a spreadsheet is as important as meaningful labelling to a chart.  If you know that you need to print your spreadsheet, make sure your layout fits well onto standard sheets of paper (normally A4, portrait or landscape).  Identify your work by entering your name in the footer (especially useful if others on a network are working on the same task as you).

  EXTENSION – Create a line graph – use AVERAGE DAILY MAXIMUM and LOWEST temperatures. 

  

  NOTE: Temperatures below 0 will appear below the x-axis.

  TEST YOURSELF 

  You should be able to:

  • select and create appropriate charts and graphs
  • label graphs / charts to aid understanding
  • produce print-outs to meet requirements

  Skills Checker  Make sure you know how to:

  • enter formulas
  • use the SUM and AVERAGE functions
  • use an IF function
  • use ‘search’ and ‘replace’
  • use absolute cell references
  • use conditional formatting
  • display and print a spreadsheet showing the data
  • display and print a spreadsheet showing the formulas
  • set the print area
  • insert your name and other information in a footer
  • adjust column width and row heights
  • use colours, borders and shading
  • insert comments
  • select data in adjacent columns
  • select data in non-adjacent columns
  • choose a suitable chart or graph to display the data
  • add a suitable title and axis labels
  • add and remove a legend
  • save a chart

Click HERE to watch teaching videos about CHARTS, the SUM, AVERAGE and IF Functions, Absolute Cell Referencing and Conditional Formatting.

EXTENSION WORK 

• Produce three more fully annotated charts based on SB5.8.1, two for printing out on A4 portrait, one on A4 landscape.  Place each chart as a new sheet (not as an object with the data)  Use Print Preview to check that the charts are fully labelled and that additional information is formatted and placed appropriately on the page, e.g. header/footer with own name, name given to the spreadsheet and Tab with chart, date, etc.

• Write a statement saying which type of chart is most appropriate and why.

10D1 Period 4 Monday 21st May 2012

Working with numbers and charts – Functional Skills Module 5 – Lesson 10

In this lesson students are learning how to:

• work with conditions

STARTER 

The Loyalty Card Scheme wants to introduce a weekly Lucky-Number draw.  A computer will randomly generate numbers between the minimum and maximum number of points.  The first five members whose number of points match the Lucky-Number win 100 bonus points!

Lucky-Number = Points, 100 bonus points, else no points 

If            condition          then      if true                 if false 

This statement as a formula:

=IF(Lucky-Number=Points, “100 bonus points”, “no points”) 

Open SB4.1.1 (Customer Loyalty Scheme LESSON 10).  The numbers of Points are in column I for all members.

Let’s test the formula with an obvious example.

Type Lucky-Number into cell N1 and type 249 as the first lucky number underneath into cell N2 – if the formula works, Mr Barker is a lucky winner!

Highlight cell L2 – instead of typing in the formula, click on Formulas, Insert Function and select IF.

Other symbols you could use instead of = are (<, >, >=, =<).

Work in your groups.

• How would these symbols change the outcome of the weekly Lucky-Number draw?
• Check the formula works by changing the Lucky-Number. 
• How would you apply the formula to all the other members?
• Before you copy the formula down – think about what you have just learnt about cell references.  The reference to the cell with the Lucky-Number needs to be absolute.  The reference to the cells with Points can stay relative.

Delete the formula in cell N2 and rebuild the IF Function, adding the $ sign before clicking OK.

Complete the Skills Builder (5.4) exercise.

• Re-open SB5.2.1 (Promotional Material LESSON 10).  The method of charging for delivery has changed.  If the value of the order is more than £2000, then delivery is free.  Otherwise delivery costs £18.
• Enter a formula in cell D12 using the IF function:  =IF(D11>2000,0,18)
• See what happens if you reduce the number of flyers needed to 3000.  What affect does it have on the cost of delivery?

TASK 1 Conditional Formatting 

So far, the formatting you have used in a spreadsheet stayed in place unless you changed it e.g. currency – but what if you wanted formatting to apply only under particular circumstances?

Similar to the IF function:  define a condition, then set one format if true, a different one for false.

There are two parts to conditional formatting:

• defining the condition or conditions
• selecting an appropriate format

Here is a simple example;

Enter the number 50 into a cell.  Highlight it, and then select Home, Conditional Formatting.  Explore the options given in the drop-down list and ensure you understand the symbols for the entries (=, >, <, >=, =<)

The entries in the three fields for Condition 1 need to read like a sentence: Cell Value is equal to 50; click on Format and select a different font colour.  Include Border and Shading as formatting features.  Confirm with OK.

The number 50 should now be displayed in the selected colour.  Change the number and the format changes back to the default format.

With the cell highlighted, return to conditional formatting – click Add, then Add again.  The maximum number of conditions is 3.

• When might conditional formatting might be useful?
• For what purposes and audiences might you use it?

CLICK HERE AND WATCH THE VIDEO CONDITIONAL FORMATTING

Complete the  Skill Builder (5.5) exercise – the challenge is apply conditional colour-coding for three grades of membership: bronze, silver and gold.

• SB5.1.1 contains information about members of a customer loyalty scheme.  There are three grades of membership: bronze for those with 250–499 points; silver for those with 500–749 points and gold for those who have earned 750 points or more.
• Re-open SB5.1.1 (CUSTOMER LOYALTY SCHEME CONDITIONAL FORMATTING).
• Sort the records by ‘Card number’ in ascending order.
• Apply conditional formatting to cell I2 to show membership grades.  Click on cell I2, then on ‘Home, ‘Conditional Formatting’, Highlight Cell Rules.  The dialogue box prompts you to define the first condition.
• Start with bronze.  Chose Between…  Complete the fields so that they read: ‘Cell Value is between 250 and 499’.  Drop down ‘Custom Format’ and, under ‘Fill, choose a suitable colour to shade the cell. Click ‘OK’.
• For silver, chose Between… and the fields should read:  ‘Cell Value is between 500 and 749’.  ‘Drop down ‘Custom Format’ and, under ‘Fill, choose another suitable colour. Click ‘OK’.
• For gold, chose More Rules… and then greater than or equal to.  The fields should read:  ‘Cell Value is greater than or equal to 750’.
• The current value in cell I2 is 249 – just below the threshold for bronze membership.  Enter 250 into the cell. The cell should now be appropriately shaded.
• Check that the second and third conditions are also working correctly by entering numbers in the silver and gold range.  When you have finished, re-enter 249 into cell I2.
• If cell I2 passed all your tests, copy the conditional formatting to the other Points cells.  An easy way is to use the Format Painter: highlight cell I2 and click on the Format Painter icon.  Click on cell I3, hold down the left mouse key and drag down to cell I101.
• CHECK – the number of points for the first six records were chosen deliberately – they are the border values of your conditional formatting and should show one neutral, two bronze, two silver and one gold.

TEST YOURSELF 

At the end of the lesson you should be able to:

• use the IF Function
• apply conditional formatting

EXTENSION WORK – Complete the SKILLS BUILDER (5.6) exercise 

• Re-open SB5.2.1.
• Add a suitable title.
• Switch on gridlines and show row and column headings.
• Find out how to switch on ‘formula view’.  Print out the spreadsheet in landscape, showing the formulas.
• Print out the spreadsheet in portrait, showing the data.
• Save the file.
• What else can you do to make the information clear and easy to read?

(1) Use the IF Function to display an appropriate text message to the right of the delivery charge, one text for £0, another text for £18

(2) Then apply appropriate conditional formatting to the text (combination of different Font and Border or Patterns features)

(3) Insert a comment into the cell as reminder of the conditional formatting.

10D1 Period 4 Monday 23rd April 2012

Working with numbers and charts – Functional Skills Module 5 – Lesson 9

In this lesson students are learning how to: 

• select and apply appropriate formats 
• carry out calculations with relative and absolute references 

Starter

Here are two puzzles: 

Puzzle1

Look at the image L9.1-100-Display. 

 

What was entered into the four cells and what format do they have?  Now look at the image L9.2-100-Display-Format.  The same number was entered into all four cells.  

The Roman number “C” should give you a clue.  What is the number behind the display?  Look at the image L9.3-100-DataEntry-Display-Format.  

 

Puzzle 2:

‘Spot the difference’.  Look at the image L9.4-10×10-text-number. 

What is the result for each line (10 x 10 = 100).  Now look at the image L9.5-10×10-results.

  

Can you explain why cell C1 displays the expected and correct result but C2 still shows the formula.  Look at the image the L9.6-10×10-warning.  

 

Spreadsheets can treat numbers like text if

• the cell is formatted to text 
• the user put a ‘ in front of the number, e.g. ‘100. 

Look at the image L9.7-10×10-comment.  The different colour (red) and location (top-right) of the marker indicates a comment.  The comments do NOT affect calculations or formatting.  

IMPORTANT – formatting matters. 

  

TASK 1 Formatting Cells 

The number 45.27.  What formats could you apply?  Look at the table below:

Now explore the Alignment, Font, Border and Patterns tabs. 

TASK 2 Formulas and Functions 

Look again at the formula used in puzzle 1 to multiply ten by ten (=A1*A2).  REMEMBER:

• + for additions 
• for subtractions 
• / for divisions 

Spreadsheets have a range of ready-made formulas for users to choose from.  Using these formulas is

• more efficient (saves time) 
• reduces the risk of making mistakes when typing in a formula from scratch 

How often would you have to enter the + symbol to add together the points of 100 members in the Loyalty Card Scheme?  The Function SUM is much better as it adds up all points between the first and last member.  The first and last cell set the range over which the function works.  

Here are the other most commonly used Functions:  AVERAGE, COUNT, MAX and MIN.

Open digital asset SB4.1.1 – the Loyalty Card Scheme LESSON 9 spreadsheet and use the SUM function on the Points and Cash Value columns.  Now try switching between data-view and formula-view by using Ctrl + ` (to the left of the number key 1). 

On which fields it would not make any sense to use the SUM function?

• all pure text fields 
• text-number fields like postcodes 
• number fields such as telephone numbers 

Now find the number of entries (COUNT), the total, average (AVERAGE), highest (MAX) and lowest (MIN) entries in

• the Points field 
• the Cash Value field 

Can you suggest a way so that you do not have to do the work twice?

 

TASK 3 – Relative and Absolute Cell References 

If a formula contains a reference that must always point to the same cell, then this is called an absolute cell reference and is indicated by the $ sign. 

Complete the Skill Builder 5.4 exercise. 

• Open digital asset SB5.4.1 – FIVE TIMES TABLE. 
• You need to change this spreadsheet so that you can quickly show different times tables with just a few key presses. 
• Click on cell E5, press the equals sign.  Click on cell D3 and press Enter.  Cell E5 should display =D3. 
• Click on cell D3 and enter another number.  Press Enter. 
• A new number is shown in cell E5; all other numbers remain the same. 
• Click on cell E5 again and copy the formula down to E16.  What has gone wrong?  Use the keyboard shortcut to show the formulas.  As you dragged down the formula, the important reference to cell D3 was lost! 
• Click on cell E5 and add the dollar signs (=$D$3), then copy down to cell E16.  Cells E5 to E16 now all refer to cell D3.  This means the table will work as expected. 
• Use the keyboard shortcut to switch back to see the results. 
• Enter other numbers into cell D3.  What happens if you enter a number bigger than 1000?  What do you need to do to show all the numbers? 

  

TEST YOURSELF 

At the end of the lesson you should be able to: 

• display numbers and text in appropriate formats 
• enter formulas using (+, -, *, /) 
• use functions SUM, AVERAGE, COUNT, MAX and MIN 
• use absolute cell references 

EXTENSION WORK 

Complete the Skills Builder (5.2) exercise.  PROMOTIONAL MATERIAL (5 2 1)

• Open SB5.2.1.  The table contains information about promotional material for a trade fair. 
• Adjust the column widths so that all the headings are visible. 
• In cell D2, enter the formula =B2*C2 to calculate the total cost of the 12 page leaflet. 
• Copy the formula in D2 to cells D3 to D8 (move the mouse pointer onto the bottom-right handle of cell D2, hold down the left mouse button and drag down to cell D8). 
• Click in cell C2 and drag into cell D8 to select the range C2:D8.  Format the data in this range as currency with two decimal places. 

STEP 1

STEP 2

STEP 3

• Enter the formula =SUM(D2:D8) in cell D11 to calculate the cost of the order. 
• Enter the formula =AVERAGE(C2:C8) in cell C10 to calculate the average cost of the promotional items. 
• Enter a formula in cell D13 to give the total including delivery. 
• Add your name and the date to the footer. 
• Save the file. 

FURTHER EXTENSION WORK 

If you have one piece of information about a member but need a second one.  The LOOKUP function as an easy way to find linked information.  Study carefully the following diagrams (L9.8 LOOKUP, L9.9 LOOKUP-Function-Arguments, L9.10 LOOKUP-Results and L9.11 LOOKUP-Formula). 

10D1 Period 4 Monday 19th March 2012

Finishing Lesson 8…

STARTER

Please watch the following teaching videos.

• CHARTS
• FILTERING DATA

The VisitorSurveyL2 file contains data from a survey. A sample of visitors to the New Forest were asked: 

• How far have you travelled? 
• How many times a year do you visit the New Forest? 
• How did you get here? 
• What incentive can we offer that would persuade you to leave your car at home? 

(e) Most of the people surveyed used their car to travel to the New Forest.  They were asked what incentive would persuade them to leave their car at home on their next visit to the New Forest.  The choices were: 

• discounts on cycle hire  (DISCOUNTS)
• vouchers for food and drink  (VOUCHERS)
• reduced admission to attractions  (REDUCED ADMISSIONS)
• free transport from bus/railway stations to their holiday accommodation. (FREE TRANSPORT).   

WATCH the FILTERING DATA video again.  Click HERE. 

The choices are recorded in VisitorSurveyL2. 

Produce a chart showing the popularity of each incentive. 

The chart must be clear and easy to read.  (5)

WATCH as many of the CHART videos you feel you need to.  Click HERE. 

Evidence 

A printout of your chart. 

Remember 

Insert your name, candidate number and centre number on the printout.

Save the chart.  (Total for Task 2 = 15 marks)

Extension Work.  

Open a copy of the ACTIVITY SPREADSHEET from Lesson 8 in the shared area.  Now take a copy of the Key Skills Activity Question sheet.  Do not attempt Question 4a.  Begin at Question 4b.  NOTE Question 5b is another example of using the VLOOPUP function. 

Please watch the following teaching videos.

• VLOOKUP
• PRINT – GRIDLINES
• PRINT – ROW and COLUMN HEADINGS
• IF FUNCTION

If you have finished read your module 4 booklet.

10D1 Period 4 Monday 5th March 2012

STARTER

Please watch the following teaching videos.

• VLOOKUP
• SORTING DATA
• AVERAGE
• PRINT – GRIDLINES
• PRINT – ROW and COLUMN HEADINGS
• CHARTS
• FILTERING DATA

Task 2 – Visitor survey 

Carbon dioxide (CO2) emissions from cars harm the environment. The New Forest National Park Authority wants to reduce the damage caused to the environment by cars.  If the number of visitors who use cars to get to the forest could be reduced, less damage would be done. 

The VisitorSurveyL2 file contains data from a survey. A sample of visitors to the New Forest were asked: 

• How far have you travelled? 
• How many times a year do you visit the New Forest? 
• How did you get here? 
• What incentive can we offer that would persuade you to leave your car at home? 

A green, amber, red rating system was used to classify their vehicles according to how much carbon dioxide (CO2) they produce.

This is your EMISSIONS table, which you will need to use in your VLOOKUP function.   

• Green = 0.12 kg per mile 
• Amber = 0.20 kg per mile 
• Red = 0.30 kg per mile 

 

(a) Open the VisitorSurveyL2 spreadsheet. 

Enter your name, candidate number and centre number (29265)in the footer. 

Use the spreadsheet to calculate the total amount of carbon dioxide (CO2) produced by cars in a year by each of the people surveyed.  (6)

WATCH the VLOOKUP function video again!  Click HERE.   

(b) Order the spreadsheet so that it ranks the people surveyed according to how much carbon dioxide (CO2) they produce.  (2)

WATCH the SORT video again.  Click HERE. 

(c) Calculate the average amount of carbon dioxide (CO2) produced by a visitor in

a year. 

Add this to the VisitorSurveyL2 spreadsheet.  (1)

WATCH the AVERAGE function video again.  Click HERE.   

(d) Format the spreadsheet so that it is clear and easy to read.  (1)

 

Evidence

A printout of your spreadsheet showing the data. 

A printout of the spreadsheet showing the formula you used. 

WATCH the PRINT GRIDLINES video again.  Click HERE. 

WATCH the Print ROW and COLUMN headings video again.  Click HERE.

Remember 

Insert your name, candidate number and centre number (29265)on both printouts. 

Resave the file. 

 

(e) Most of the people surveyed used their car to travel to the New Forest.  They were asked what incentive would persuade them to leave their car at home on their next visit to the New Forest.  The choices were: 

• discounts on cycle hire  (CYCLE)
• vouchers for food and drink  (FOOD & DRINK VOUCHERS)
• reduced admission to attractions  (REDUCED ADMISSION)
• free transport from bus/railway stations to their holiday accommodation. (FREE TRANSPORT).   

WATCH the FILTERING DATA video again.  Click HERE. 

The choices are recorded in VisitorSurveyL2. 

Produce a chart showing the popularity of each incentive. 

The chart must be clear and easy to read.  (5)

WATCH as many of the CHART videos you feel you need to.  Click HERE. 

Evidence 

A printout of your chart. 

Remember 

Insert your name, candidate number and centre number on the printout.

Save the chart.  (Total for Task 2 = 15 marks)

Extension Work.  

Open a copy of the ACTIVITY SPREADSHEET from Lesson 8 in the shared area.  Now take a copy of the Key Skills Activity Question sheet.  Do not attempt Question 4a.  Begin at Question 4b.  NOTE Question 5b is another example of using the VLOOPUP function. 

10D1 Period 4 Monday 30th January 2012

Working with structured data – Functional Skills Module 4 – Lesson 8

 

In this lesson students are learning how to: 

• present information to meet requirements
• validate data

 

STARTER 

The Loyalty Card Scheme is planning to offer a Welsh language service.  Mr Baker (Card no 11500), Mr King (Card no 129887), Mr Brennan (Card no 182344) and Ms Hughes (Card no 209889) all want to take advantage of this service.  Produce a one page report showing their membership details (apart from street, postcode and telephone number) plus:  

• the length of their membership (in number of days)

The report must also show:  

• a title, the author of the report, the date it was created, number of pages, and its file name.

What needs to be done to meet these requirements? 

You need to: 

• search for the FOUR records 
• save them as a sub-set 
• create a new date field 
• calculate the difference in days between the “JoinedDate” and today’s date 
• decide where and how to provide the contextual information. 

 

READ – Making tables clear and easy to understand on. 

Use titles that make it clear what the table is about. 

Make sure column headings are meaningful and displayed in full. 

Check column alignment.  This can cause problems if you have a right-aligned column next to one that is left-aligned. 

Use the date function to insert the date into the page footer. 

Switch on grid lines. 

Hide columns that contain unnecessary information. 

Use landscape orientation and adjust the margins to fit everything onto one page. 

 

 

 

TASK 1 Calculating with Dates 

Grab a white board pen and cloth. 

Write down today’s date, then 14 February 2008 (the date when Mr Barker joined the Loyalty Scheme).  What do you need to find out?  Draw a line under 14 February 2008 and write “???  number of days” underneath it. 

 

Put a minus symbol in front of 14 February 2008.   Do you think a spreadsheet can calculate the difference between dates by subtracting the older from the newer? 

How many days in a year?  What is the number of days between 14 February 2008 and one year later, 14 February 2009?  Open a spreadsheet and then find out whether or not it works. 

Enter the 2009 date into cell A1, the 2008 date into cell A2, and the formula =A1-A2 into cell A3. 

What do you think of the result? 

If you are puzzled, what did you expected and need? – A number!  What is actually shown – a date!  Format the result in cell A3 as a number without any decimal places. 

366 not 365? – 2008 was a leap year with 29 days in February!

REMEMBER the importance of: 

• appropriate formatting 
• checking the accuracy of a formula with easy numbers (for mental arithmetic or calculator in accessories).

 

 

TASK 2 Using Data Validation to Keep Rubbish Out. 

The information you get out of a table is only as good as the data you put in. 

What can be done to minimise risks of entering wrong data or entering data into the wrong place.  

Spreadsheets have facilities which help users to minimise risks.  A spreadsheet can check data entered against criteria set by the user: it accepts data that meets the criteria, e.g. a date for JoinedDate fields, and rejects all that does not, e.g. text for Points fields.  The process of checking data on entry is called validation.  You should test any data validation rules you set – just as you tested calculating days between two dates earlier in the lesson. 

You have 5 minutes to explore the options under Data, Data Validation. 

One way of controlling what goes in is to create a drop-down list of options.

 

TEST YOURSELF  

At the end of you should be able to: 

• enter and format numeric data 
• perform calculations using data in two fields 
• use a drop-down list for data-entry 
• produce print-outs to meet requirements 

 

SKILLS CHECK – Make sure that you can 

enter suitable field names 

adjust column widths 

use the wrap text and text orientation features 

enter and edit records 

limit entries into a field 

add a field to a table 

sort on one field 

sort on two fields 

search on a single field 

search on more than one field 

use relational and logical operators in a search 

use wild cards in a search 

hide columns 

enter information into a footer 

use the date function 

 

Extension Work – Complete SAM L2 , Task 2 

Section B 

You must not use the internet from this point onwards. 

Task 2 – Visitor survey 

Carbon dioxide (CO2) emissions from cars harm the environment. The New Forest National Park Authority wants to reduce the damage caused to the environment by cars.  If the number of visitors who use cars to get to the forest could be reduced, less damage would be done. 

The VisitorSurveyL2 file contains data from a survey. A sample of visitors to the New Forest were asked: 

• How far have you travelled? 
• How many times a year do you visit the New Forest? 
• How did you get here? 
• What incentive can we offer that would persuade you to leave your car at home? 

A green, amber, red rating system was used to classify their vehicles according to how much carbon dioxide (CO2) they produce. 

Green = 0.12 kg per mile 

Amber = 0.20 kg per mile 

Red = 0.30 kg per mile 

 

(a) Open the VisitorSurveyL2 spreadsheet. 

Enter your name, candidate number and centre number in the footer. 

Use the spreadsheet to calculate the total amount of carbon dioxide (CO2) produced by cars in a year by each of the people surveyed.  (6)

 

(b) Order the spreadsheet so that it ranks the people surveyed according to how much carbon dioxide (CO2) they produce.  (2)

 

(c) Calculate the average amount of carbon dioxide (CO2) produced by a visitor in

a year. 

Add this to the VisitorSurveyL2 spreadsheet.  (1)

 

(d) Format the spreadsheet so that it is clear and easy to read.  (1)

 

Evidence

A printout of your spreadsheet showing the data. 

A printout of the spreadsheet showing the formula you used. 

Remember 

Insert your name, candidate number and centre number on both printouts. 

Resave the file. 

 

(e) Most of the people surveyed used their car to travel to the New Forest.  They were asked what incentive would persuade them to leave their car at home on their next visit to the New Forest.  The choices were: 

• discounts on cycle hire 
• vouchers for food and drink 
• reduced admission to attractions 
• free transport from bus/railway stations to their holiday accommodation. 

The choices are recorded in VisitorSurveyL2. 

Produce a chart showing the popularity of each incentive. 

The chart must be clear and easy to read.  (5)

 

Evidence 

A printout of your chart. 

Remember 

Insert your name, candidate number and centre number on the printout.

Save the chart.  (Total for Task 2 = 15 marks)

 

 

10D1 Period 4 Monday 16th January 2012

Working with structured data – Functional Skills Module 4 – Lesson 7

 

In this lesson students are learning how to: 

• search structured data

 

STARTER

Look at the following serial number – EE27 545098 

It is the serial number of a UK bank note.  How could you find the denomination of the bank note if you had a spreadsheet with data about all UK bank notes?  

Search for the serial number in the DENOMINATION field of the spreadsheet. 

 

Task 1 Using Search criteria 

REMEMBER the internet searches you conducted.  Is searching a table or spreadsheet the same on a smaller scale?  Are the outcomes the same? 

For example, how many hits would you get if you entered just the serial number into:  

1. a search engine 
2. the search field of a spreadsheet with UK bank notes.

The likely outcome is – more hits on the internet than in the spreadsheet. 

 

Searching on one field 

Any field in a table of data can be searched. 

<=18 is an example of a search criterion.  ‘<=’ is called an operator.  There are lots of operators you can use in a search – on their own or in combination. 

 

Look at the table below:  It can be found on PAGE 8 of the MODULE 4 booklet. 

TASK 2 Using Wild cards 

What if you did not have the complete serial number of the bank note but only the beginning, middle or end of it?  ? and * are examples of placeholders. 

• Use ? as a placeholder for a single character.  For example, a search for ‘Sm?th’ would find ‘Smith’ and ‘Smyth’. 
• Use * as a placeholder for a group of characters.  A search for ‘*on’ would find ‘Southampton’, ‘London’ and ‘Brighton’ and all other towns with names ending in ‘on’. 

TASK 3 AutoFilter 

Spreadsheets have a special feature called Filter.  What do filters do?  e.g. a coffee filter.

Filters divide liquids, solids, etc into two – those they hold back and those they let through.  

In a spreadsheet, users can design their own filters to find records and groups of records. 

Open the Customer Loyalty Scheme (SB4.1.1) spreadsheet – to set filters choose Data / Filter and then drop down the arrow and choose field from the drop down list.  To remove filters (Data / Filter / Clear).

Some filters are more useful than others – a filter on Card number shows all entries, a filter on Gender only two – male and female).

 

Complete Skill Builder 4.4 exercise. 

Open Customer Loyalty Scheme (SB4.1.1). 

Choose Filter from the Data menu. 

Click on the filter arrow in the ‘Card Number’ column.  The dropdown menu will list all the numbers in this column.  What happens when you click on one of them? 

Use filters to find details of: 

• the person whose card number is 180210 
• everyone who lives in St Leonards 
• everyone whose post code contains the letters ‘ES’
• everyone who has more than 500 points
• everyone who has less than 50 points
• everyone who has between 100 and 125 points
• all members of the loyalty scheme whose telephone number starts with 077 

 

TASK 4 Entering Data and Keeping the Rubbish Out

The information you get out of a table is only as good as the data you put in.  One way of controlling what goes in is to create a drop-down list of options. 

In the toolbar, click on ‘Data’, then ‘Data Validation’ and explore options to help you keep the rubbish out.   This image can also be found on PAGE 7 of the MODULE 4 booklet. 

Work in your groups.  Grab a white board pen and cloth. 

How do you think the data for the customer loyalty scheme was entered?   How is it maintained?  Keyboard entries, drag & drop, imports, automatic updates, e.g. for points earned may be some of the ways.  Can you think of advantages and disadvantages for each of these data entry method.

Complete Skill Builder 4.5 exercise. 

Make these changes to the records in SB4.1.1, and then re-save the file as version 2.

Peter Slater has changed his mobile phone.  His new number is 07747689332. 

Amy Johnson has recently signed up to the loyalty scheme.  Her card number is 20999, her contact details are: 13 London Crescent, Sleaford NG34 8YY; mobile 07874637788. Amy has earned 10 points. 

Add yourself to the data, with 0 in the Points field and the current date as the joined date. 

 

TEST YOURSELF 

At the end of the lesson you should be able to: 

• use Find to locate data 
• set and customise Filters (AutoFilter) 
• use Filters on single and multiple columns 

 

EXTENSION WORK – Skills Check

Make sure you can:

• enter suitable field names 
• adjust column widths 
• use the wrap text and text orientation features 
• enter and edit records 
• limit entries into a field 
• add a field to a table 
• sort on one field 
• sort on two fields 
• search on a single field 
• search on more than one field 
• use relational and logical operators in a search 
• use wild cards in a search 
• hide columns 
• enter information into a footer 
• use the date function 

 

 

10D1 Period 4 Monday 12th December 2011

Working with structured data – Functional Skills Module 4 – Lesson 6

 

In this lesson students are learning how to: 

• structure and present data in a table. 

 

STARTER 

Work in your groups.  Apart from the register can you suggest three other ways of ordering everyone in the group?  One must be based numbers e.g. date of birth, UPN – unique pupil number on text.  

Can you suggest a combination of criteria, such as Shrewsbury girls first, in age, or name, followed by NON Shrewsbury girls in age or name. 

 

TASK 1 Tables of Structured Data 

Open this web site – http://www.bankofengland.co.uk/banknotes/current/index.htm 

Scroll down so that the £5 note is at the top of the screen with other notes showing underneath. 

What are the obvious features that differentiate UK bank notes? 

Look at the TABLE above. 

DENOMINATION – data type is number and the format is currency 

1^st ISSUED – data type is number and the format is date 

SIZE data type is number and the format is number (could calculate the area in square mm) 

PORTRAITS data type is text and the format is standard 

COLOUR data type is text and the format is matching font colour  

Rows – one for the ‘headings’ in capital letters and BOLD and one each for the four denominations, £5, £10, £20, £50. 

Columns – one for each feature – ID, DENOMINATION, 1^st ISSUED, SIZE, PORTRAITS, COLOUR. 

Where rows and columns intersect, they create fields.  Each one has a name (fieldname) which it gets from the column heading it is under.  

Look at the TABLE above. 

• a record is a set of data that belongs together, arranged on the same row.   In this case there are four records, one per denomination  
• there are different types of data which you can format to aid understanding
• you can carry out calculations with numbers, e.g. totalling-up of DENOMINATION (£85) and SIZE (45280 mm). 

Open this web site -http://www.bankofengland.co.uk/banknotes/about/stats.htm  

Zoom in on the table showing the stock of notes in circulation. 

Is it possible to uniquely identify each of the thousands of notes in circulation or stock?  And if so, how? 

Each bank note has a unique serial number.  Therefore the table needs an ID field heading. 

Work in your groups. 

By how many rows would the table grow if you included the serial numbers as a bank note’s unique identifier?  

What software application program could handle these records? 

A table gives data a structure, however small or large the data set, and that once data is in a table it is much easier to work with it. 

 

TASK 2 Which application software? 

Both spreadsheets and database software can handle large amounts of structured data.  You will use spreadsheets as you will handle single tables, not several tables at the same time (related tables). 

REMEMBER the importance of meaningful names for files and folders. The same applies to fields. 

TASK 3 Formatting data. 

File and folder names cannot be formatted but field names can.  Field names – like headlines in a newspaper or magazine – should stand out and not be truncated.  Adjust the width of a column to show a long fieldname in full.   

TURN TO PAGE 3 IN YOUR MODULE 4 BOOKLET. 

Look at the first table.  It is data about people who belong to a loyalty card scheme.  It is NOT at all clear what the data means.  Then look at the second table.  Here is most of the data, with some labels added.  Now the data is meaningful. 

Task 4 Optimising presentation by hiding columns. 

The table above shows most of the data in the text lines above it.  Where is the missing data?  The data is there but hidden to get more important data into view.  In the table below members’ address details have pushed loyalty scheme information off the screen.

Complete the Skills Builders (4.1) exercise. 

• Open SB4.1.1.  It contains information about members of a customer loyalty scheme. 
• Save the file in your user area. 
• Open the file in a spreadsheet application. 
• Use a suitable method to make the field names visible. 
• Embolden the field names to make them stand out from the data stored in the table. 
• Add your name in the footer. 
• Save the file. 

 

Practise hiding und unhiding the columns in the spreadsheet.

TASK 5 Sorting data 

Can you think of an alternative way to arrange the four records for bank notes using their denomination (£50 – £5).  Think about descending and ascending for numbers.  The table below is sorted descending number order on the first column. 

How can the four records be rearranged by using one of the text field names?  REMEMBER ascending (A-Z) and descending (Z-A) with letters.  The table below is sorted ascending alphabetical order on the second column. 

TURN TO PAGE 6 IN YOUR MODULE 4 BOOKLET. 

Complete Skill Builders 4.2 exercise

• Open SB4.2.1 contains definitions of different types of land. 
• Save the file in your user area.
• Open the file in a spreadsheet application.

Complete Skill Builders 4.3 exercise. 

• Open SB4.1.1 in a spreadsheet application. 
• Sort the table on ‘Gender’ followed by ‘Points’. 
• What happens if you select the ‘No header row’ option in the ‘Sort’ dialogue box? 
• Find out what other sort options are provided by your spreadsheet software.  When might they be useful? 

 

TEST YOURSELF 

You should be able to: 

• build a data table
• enter meaningful field names
• work with different types of data
• format data
• sort data in different ways

 

What did you think of the website we used at the beginning of the lesson?  How would you rate it?  The web pages were from the Bank of England website. 

Think about the rating criteria for websites you used – validity, accuracy and authority.  How would you rate the Bank of England and why? 

 

EXTENSION WORK 

Design and create a five-column table for books, cars, or mobile phones. 

Devise a unique identifier and choose a meaningful name for it. 

Format the fieldnames in the header row so that they stand out and are easy to read. 

Create six records by entering data for six different items. 

Present the six records sorted in three different ways. 

 

FOR EXAMPLE – a unique identifier – ISBN for books, serial number for mobile phones.  

A table structure for holding details of cars might have these fields: Registration number, Make, Model, Engine size, Fuel (diesel/petrol) 

 

 

 

 

 

10D1 Period 4 Monday 14th November 2011

Finding and Selecting Information – Functional Skills Module 3 – Lesson 5

 

In this lesson students are learning how to: 

• manage and acknowledge information selected for use in own work

 

STARTER

 

Look at the image Copyright-not-right-to-copy.  Last lesson , what did you find out about Copyright?  What search criteria did you use?  What websites did you use?  Which did you judge to be the best? 

Look at the image Copyright-protected.  Acknowledge sources and avoid plagiarism. 

TASK 1 Test Tips. 

How much time do you spend on the internet every day?  In the test you have access to the internet for just 15 minutes or 12.5% of the 2-hour paper.  It is ONE block of time, right at the START OF TEST. 

It is not just the marks for Task 1 that depend on how well you do in the first 15 minutes.  You will need the information you find, select and save in Task 1 when you are doing Task 3 – without access to the internet.

The first 15 minutes includes time for reading through all the tasks because you must establish the information requirements, audience and purpose BEFORE you can search, select and save APPROPRIATE information.

TASK 2 Managing selected information for future use. 

You have just 15 minutes on the internet to practice searching for and selecting information, then saving and pasting it into a new document.  

REMEMBER to use what you have learnt in previous lessons: 

Is the website reliable (apply evaluation criteria from previous lesson)

• Where and how do I record the sources I selected?  (it is best to copy the URL directly from the address browser window and paste into a text document with a date) 
• How can I save the information I have found?  (appropriate folder structure, meaningful file names / file types, accessible storage area. 
• How long will it take to download it?  (if the speed is slow, what do I do while I am waiting?)
• Is the information in a format that I can work with?  Do I have the necessary software? 
• How much editing do I have to do?  How long will it take?

Your theme for a picture search is LONDON TOURIST ATTRACTIONS.  Use the website web site www.creativecommons.org

Complete Skill Builder 3.5 exercise. 

• Create a folder for information from the internet. 
• Experiment with different ways of saving the information. 
• Practice using information from the internet in other application software, e.g. word processing or presentation software. 
• Which format is easiest to use?

TASK 3 Evidence management of saved information. 

Take screenshots of the folder(s) and files you have created, paste the screenshots into a new WORD document and save it. 

You will have to take many more screen shots and save them with your NAME and DATE in the footer.  

TEST YOURSELF. 

You should be able to:

• save information appropriately
  • acknowledge sources and information they use
  • evidence how they manage information saved from the internet

READ – Tips for finding reliable information on the internet. 

• Think before you search. 
• Do not believe everything you see or hear on the internet. 
• Always check information using another source. 
• Check permissions before using material you find. 
• Record where exactly you found the information.

READ – Skills check – make sure you know how to:   

• use an internet browser 
• use a search engine 
• use keyword searches 
• use advanced search options 
• bookmark a page 
• copy and paste a link 
• save information in usable formats 
• record sources 
• stay safe online