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.

10S3 Period 4 Monday 18th June 2012

Working with text and images – Functional Skills Module 6 – Lesson 13

 

At the end of the lesson students will be able to: 

• ·         create a table in a text document 
• ·         format it to aid understanding 
• ·         produce print-outs to meet requirements 

 

STARTER 

Work in your groups.  Grab a white board, pen and cloth.  What key words spring to mind when you hear the word: “table” – a data table, not a piece of furniture! 

Look at the image L13.1-Tables-previous.

Think about structured data (Lesson 6) and working with numbers and charts (Lesson 9). 

 

TASK 1 – Table Menu 

Look at the image L13.2-Table-menu.   

Some options are greyed out (Convert Text to Table…), why?  Investigate all the menu options. 

Draw a simple table like the one below: 

Look at Sort and Formula on the Layout menu… – what might they offer you? 

Is A>Z in alphabetical sort order ascending or descending? 

 

TASK 2 Table Properties 

Investigate the Design menu

and the Layout menu. 

TASK 3 – Explore a Table 

Complete the Skills Builder (6.4) exercise. 

Open a new document in your word processor and create an empty table with three columns and four rows. 

Enter this text into the first cell in the table:  ‘GCSE ICT 2010 – events schedule’. 

Note what happens when the text exceeds the cell width. 

Click in the last cell in the table.  Now press the Tab key and note what happens. 

Click in any cell in the table, apart from the last one.  Now press the Tab key and note what happens. 

Click in the text in the first cell in the table.  Now press the keys Ctrl and Tab together, and note what happens. 

 

TASK 4 – Creating a Table 

Complete the Skills Builder (6.5) exercise. 

Open SB6.5.1 – Outdoor Activities available in August 

In the table, delete the fifth column (Average per hour). 

Merge the cells in the first row so that the heading stretches across the whole table. 

Shade the header row. 

Use the ‘Layout menu to set the height of all the rows in the table to 1.5cm. 

Right click on the mouse and change the alignment of text in every cell of the table to centre . 

Add an additional row before ‘snowboarding’.  Insert the information about tobogganing (Location: Ski slope; Age: 14 and over; Max per hour: 16). 

The lake has dried up.  Remove the row containing information about canoeing. 

Switch gridlines off. 

 

TEST YOURSELF

You should be able to: 

• ·         create a table in a text document 
• ·         format it to aid understanding 
• ·         produce print-outs to meet requirements 

 

Extension work 

1. open a new text document (portrait orientation) 
2. present the information below in a table 
3. format it appropriately 
4. use a formula within the table to add-up the expenditure 
5. put a suitable title in the header, and their name, date and filename in the footer.

 

Information

Title: San Francisco, diary summary 

Sunday: City, cable cars & trams, $64.00, great fun! 

Monday: China Town, guided walking tour with lunch, $45, strange food 

Tuesday: Golden Gate Bridge, bicycle, $32, fantastic 

Wednesday: Museums, buses, $50 , impressive  

Thursday: Golden Gate Bridge, walking, free, don’t remind me! 

Friday: Piers and Alcatrez, boat tour, $36, shocking 

Saturday: airport, BART, $10, sorry to be leaving 

 

 

10S3 Period 4 Monday 14th May 2012

Working with text and images – Functional Skills Module 6 – Lesson 12

 

In this lesson students are learning how to: 

·         select and format text to meet requirements 

 

STARTER 

·         Open the Gutenberg-Museum Shop website: www.gutenberg-shop.de . 

·         Click on English Version (last option of the menu on the left). 

·         Click on Online Shop, then Miniature Books. 

·         Click of the image of a miniature book for a closer look.  The page shows the smallest book in the world. 

·         Click on Miniature Accordion fold books to display a wide range of small books. 

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

• What font size is needed to fit text onto such small pages (2 cm x 2 cm)? 
• Could the text be formatted – if so, in what ways? 
• What character sets are needed to print the text in languages other than German? 
• How would you read such text? 

 

For information on the history of printing, visit the Gutenberg Museum’s website – http://www.gutenberg-museum.de/index.php?id=29&L=1 

 

TASK 1 – Formatting Text 

Two important things that decide how easy text is to read are the font and the font size. 

Fonts 

Fonts are usually identified by name.  Here are three very different fonts. 

 

Font size

A large amount of text will not be easy to read if the font size is too small.  These three examples use the same font, but in different sizes. 

Text alignment 

The appearance of text is also affected by how it is aligned and by the spacing between the lines. 

For text that doesn’t have that many words to the line, left alignment works best.  Right alignment and centred are not as easy to read, because each line starts from a different position.  Justified text, where each line is made the same width by adjusting the spaces between letters and words, gives very varied spacing in narrow columns and is more difficult to read. 

Adjusting the spacing between lines is often done to make text fit a given space.  The standard line space is single.  Reducing the line spacing can make text more difficult to read.

Text style

Font styles such as bold, italic and underlined are used to give emphasis to small amounts of text.  Regular font style is best for large amounts of text. 

Text colour

Colour can also be used to bring text to life.  Use it with care, though, as it generally makes the text less easy to read. 

TIP – you can quickly copy all formatting by using the Format Painter or paintbrush. 

 

 

Complete Skill Builder (6.1) exercise. 

• Open SB6.1.1 – I want to be a professional gardener. 
• From the list of fonts available, choose a font and font size that you consider easy to read and apply it to all the text in the file. 
• Find the plant word ‘Agapanthus’ and give it the italic font style.  Find the phrase ‘garden design programme’ and give it the bold font style. 
• Select a font to use for the title and headings in the document that is different from the font for the main text.  Then choose one suitable font style (such as bold, italics or underlined) for the title and headings. 
• ·         Repeat the work, experimenting with different fonts and font styles.  You may also want to try using a colour for the title and headings. 

 

TASK 2 Bullets and Numbering 

Bullets can make a list of points easier to read.  Make sure that all the bullets in a list are consistent and in line. 

If a list is meant to be read in a particular order, it will make more sense to number the points rather than use bullets. 

 

Look at the image  L12.1-Bullets-Numbering. 

Look at the Bullets, Numbering, Multilevel List icons in the Paragraph section of the Home menu. 

1.      explore the options

2.      find out what happens when they highlight one line in the middle of a bulleted list and then click on the Increase Indent key. 

For example:

·         Abseiling 

• o   Orienteering 
• o   Canoeing 

·         Disco Dancing 

Complete the Skill Builder (6.2) exercise 

Open SB6.2.1 – Small animals that are common in gardens. 

Select the list of animal names and format it to display as a bulleted list. 

On a new line below the list, enter the text ‘I like these animals in this order’.  Make a copy of the animal list below the new text.  Remove the bullets from this list. 

Drag and drop the animal names into the order in which you like them.  For example, if you like butterflies most and slugs least, then butterflies should be at the top of the list and slugs at the bottom. 

Format the list as a numbered list from 1 to 13.  Then delete the sixth animal from the list.  If you have formatted the list correctly as a numbered list, the numbers below the deleted line will adjust automatically. 

 

TASK 3 – Selecting and Formatting text for publications 

Look at the image L4.7-Fit-for-Purpose. 

Information and the way it is presented must be fit for purpose and audience.  A tiny font size is appropriate if you want to produce the smallest book in the world – but no good at all traffic sign, bill board or poster.  

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

A book is one type of publication – posters and flyers as examples of other types.  What would you, the audience expect? 

1.       How much text? 

2.       What kind of text? (single words/short phrases or complete sentences) 

3.       What format or formats? (not just font size) 

 

 

Look at digital asset SB6.3.1 – Bentley Charity Ball. 

·         Are 228 words suitable for (a) a poster or (b) a flyer? 

·         Is the wording of the text suitable for (a) a poster or (b) a flyer? 

·         Is plain / unformatted text suitable for (a) a poster, and (b) a flyer? 

 

Complete Skills Builder (6.3) exercise. 

• Open SB6.3.1. – Bentley Charity Ball 
• Select and prepare some of the text for inclusion in: 
  • a printed poster  
  • a flyer to advertise the ball 
  • a presentation for the council 
• Edit and format the text appropriately. 
• Save texts for different publications in separate files. 

 

TEST YOURSELF 

You should be able to: 

·         select and prepare text for publications 

·         apply appropriate formatting 

·         apply bullets and numbering to lists 

·         produce print-outs to meet requirements 

 

10S3 Period 4 Monday 30th April 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 zero 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.

10S3 Period 4 Monday 26th March 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.

10S3 Period 4 Monday 12th March 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 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). 

10S3 Period 4 Monday 27th February 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.

10S3 Period 4 Monday 6th February 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. 

10S3 Period 4 Monday 23rd 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)

 

 

10S3 Period 4 Monday 9th 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