Silver Unit 5 Spreadsheets (ITQ: SS)

Relevant LINKS

BACK TO ITQ UNITS

Handbook home page

Overview

This is the ability to use a software application designed to record data in rows and columns, perform calculations with numerical data and present information using charts and graphs. The  candidate will use a range of basic spreadsheet software tools and techniques to produce, present and check spreadsheets that are straightforward or routine. Any aspect that is unfamiliar will require support and advice from others. 
 
Spreadsheet software tools and techniques will be defined as ‘basic’ because:
  • The range of data entry, manipulation, formatting and outputting techniques are straightforward
  • The tools, formulas and functions involved will be pre-determined or commonly used (for example, sum, divide, multiply, take away and fractions).
  • The the structure and functionality of the spreadsheet will be pre-determined or familiar. 
Example of context: A work sheet for keeping track of simple personal expenses.

Activities supporting the assessment of this award

Assessor's guide to interpreting the criteria

General Information

QCF general description for Level 1 qualifications

  • Achievement at QCF level 1 (EQF Level 2) reflects the ability to use relevant knowledge, skills and procedures to complete routine tasks. It includes responsibility for completing tasks and procedures subject to direction or guidance.
  • Use knowledge of facts, procedures and ideas to complete well-defined, routine tasks. Be aware of information relevant to
    the area of study or work

  • Complete well-defined routine tasks. Use relevant skills and procedures. Select and use relevant information. Identify whether actions have been effective.

  • Take responsibility for completing tasks and procedures subject to direction or guidance as needed

Requirements

  • Standards must be confirmed by a trained Silver Level Assessor or higher

  • Assessors must at a minimum record assessment judgements as entries in the on-line mark book on the INGOTs.org certification site.

  • Routine evidence of work used for judging assessment outcomes in the candidates' records of their day to day work will be available from their e-portfolios and on-line work. Assessors should ensure that relevant web pages are available to their account manager on request by supply of the URL.

  • When the candidate provides evidence of matching all the criteria to the specification subject to the guidance below, the assessor can request the award using the link on the certification site. The Account Manager will request a random sample of evidence from candidates' work that verifies the assessor's judgement.

  • When the Account Manager is satisfied that the evidence is sufficient to safely make an award, the candidate's success will be confirmed and the unit certificate will be printable from the web site.

  • This unit should take an average level 1 learner 30 hours of work to complete.

Assessment Method

Assessors can  score each of the criteria L, S, H. N indicates no evidence and is the default starting position. L indicates some capability but secure capability has not yet been achieved and some help is still required. S indicates that the candidate can match the criterion to its required specification. H indicates performance that goes beyond the expected in at least some aspects. Candidates are required to achieve at least S on all the criteria to achieve the unit.

Expansion of the assessment criteria

1. The candidate will use a spreadsheet to enter, edit and organise numerical and other data

1.1 Identify what numerical and other data is needed and how the spreadsheet should be structured to meet needs 

The candidate should be able to take a simple practical task and identify the data needed to put together a spreadsheet to meet the needs of the task.

Evidence: candidate originated spreadsheets and/or plans identifying the required data.
 
Additional information and guidance
The candidate's plans should show how they will develop a structured solution to presenting a combination of numbers, charts, and text structures through spreadsheet components such as cells, rows, columns, tabs, pages and their layout. Assessors can provide templates and guidance but the candidate should at least identify the data and how it will be presented for simple cases.  If candidates can describe how to structure a spreadsheet of significant complexity they are operating higher than at level 1. Typical Level 1 spreadsheets might be a model for times tables, simple balancing of accounts with sales and expenditures, producing a bar chart of rainfall data for each month of the year. 

1.2 I can enter and edit numerical and other data accurately

The candidate should collect data, check its accuracy and potential for bias in simple cases and enter the data into a spreadsheet accurately. They should demonstrate these skills across several spreadsheet problems.

Evidence: candidate originated spreadsheets and documentation
 
Additional information and guidance
They should show capability of copying data from one cell to another, replicating data in cells and adding rows and columns to the sheet. If they can go beyond entering and editing they are operating at a higher level than the basic level 1 requirements.

1.3 I can store and retrieve spreadsheet files effectively, in line with local guidelines and conventions

Candidates should show that they can save files to sensible places in a directory structure and retrieve and open them at a later time.
 
Evidence: candidate spreadsheet files and assessor observations.
 
Additional information and guidance
The candidate should use logical names for files and directories organising their data so that it is easy to find. They should demonstrate the capability of creating a new file, naming and renaming files, printing files, opening and saving files. Ability to use "save as" to save the file in an open format such as .odc, csv, indicates higher than Level 1 operation but all candidates should have the opportunity to find out why export in open standard formats is useful.  Encourage the use of information generated in a spreadsheet e.g. a chart or table to be used in other contexts e.g. a web page or blogs. Explain why attaching a spreadsheet file in a proprietary format for download is to be avoided if possible. (Spreadsheets are less problematic  than complex documents from an open standards point of view because it is possible to replicate data and formulae in different formats with precision. In most cases a spreadsheet in a proprietary format can be filtered into an open format without compromising the information. The more natural it becomes for people to demand open formats, the less likelihood of abuse of a monopolistic position by a particularly powerful supplier. This work can be linked to the collaborative technologies unit through use of on-line spreadsheets such as Google Docs, Editgrid, Zohosheet, NumSum.

2. The candidate will use appropriate formulas and tools to summarise and display spreadsheet information

2.1 I can identify how to summarise and display the required information

Candidates should show that they can identify the use of columns and rows in tables to organise and process their spreadsheet data. They should identify simple functions such as SUM to summarise total costs or similar attributes to data sets that might be provided for them. This process will include totalling, sorting and tabulating data.
 
Evidence: candidate originated spreadsheets and documentation
 
Additional information and guidance
Since at this level, "identify" is the operative word, the implication is that they will be provided with the basic structures in which they identify specific needs. If they can originate an entire sheet themselves and show that they can produce a precise and accurate sequence of instructions to summarise data, they are operating at higher than level 1 and it could be indicative of level 2 performance.  Level 1 examples will include, identifying that a formula is needed to add up the cost of five given items and display a total. Display the total discounted by 10% in a second cell, show the cost including VAT in a third cell. Assessors will need to provide structured guidance at this level using spreadsheet templates and clear and simple instructions. Candidates should appreciate that although the calculations appear to be instant the order of calculation matters and that accuracy in the figures is important too.

2.2 I can use functions and formulas to meet calculation requirements

Candidates should be provided with opportunities to use a range of spreadsheet functions including the arithmetic operators add, subtract, multiply, divide, sum, average, and round.  The formulae can be given as long as the candidate can then use them to meet the calculation requirements
 
Evidence: candidate originated spreadsheets and documentation
 
Additional information and guidance
They should use simple formulae to design models where they can make changes to variables and explain the impact. In keeping with level 1 they can be provided with structured templates as guidance. An example might be to provide a model of a multiplication table such that changing a single variable produces a different times table, getting the candidate to modify the given table in order to produce others. If they can tackle such problems successfully from a general brief they are operating at "Higher" level, if they can do so with structured guidance it is Level 1 (Silver).

2.3 I can use spreadsheet tools and techniques to summarise and display information

Candidates should use spreadsheets to summarise and display information from data that is provided for them or they have gathered themselves. They will provide a logical structure to their work so they can present it clearly to a familiar audience.
 
Evidence: Candidate originated spreadsheets and documentation
 
Additional information and guidance
Work in a spreadsheet can be shared collaboratively in Google Docs or other collaborative technologies to link into the collaborative technologies unit. An example of gathering their own data could be a survey of people's heights with responses classified by age, gender or some other grouping. They might show this in a table and produce borders and text styles to display the information effectively. They could use an appropriate chart embedded in the spreadsheet next to the tabulated data. In keeping with level 1 qualifications these results can be supported through instructions in a range of formats and the provision of structured support. They should appreciate that spreadsheets are most useful for numerical data and for relatively small data sets. For data that is very extensive and with a range of data types, database management systems are more appropriate.

3. The candidate will select and use appropriate tools and techniques to present spreadsheet information effectively

3.1 I can select and use appropriate tools and techniques to format spreadsheet cells, rows and columns

Candidates should be able to identify and select the appropriate spreadsheet options to format their work clearly and effectively. This will be adjusting row and column and cell widths, style attributes for tables and alignment options.
 
Evidence: Candidate originated spreadsheets and documentation
 
Additional information and guidance
In keeping with the description of level 1 qualifications, tasks should be well-defined and routine with appropriate guidance. If they plan and develop structured solutions to problems, combining several spreadsheet tools and techniques,  ensuring appropriate presentation, they are operating higher than the required level. They should be aiming to include use of numbers, currency, percentages, number of decimal places, font and alignment, borders and shading, height, width, borders and shading. Self-sufficiency in a wide range of formatting to produce clear and attractive presentations of data indicates "Higher" performance.

3.2 I can identify which chart or graph type to use to display information

Candidates should be able to identify appropriate charts to display their data. This should include histograms, bar charts, pie charts, line graphs. It will link to work in mathematics.
 
Evidence: Candidate originated spreadsheets and documentation, assessor observations.
 
Additional information and guidance
  • A histogram typically shows the quantity of points that fall within various numeric ranges (or bins). 
  • A bar chart uses bars to show frequencies or values for different categories. 
  • A pie chart shows percentage values in terms of the size of a slice of a pie. 
  • A line chart is a two-dimensional scatter plot of ordered observations where the observations are connected following their order. 
In the case of continuous experimental data they can either use "trend lines" e.g. in Excel or OpenOffice Calc or they can add a line manually to a scatter chart using a drawing program. Conceptually at this level the latter is likely to be more accessible as regression is mathematically at a higher level than expected here and using this feature of spreadsheet software is indicative of Level 2 and higher attainment. However, we do not want to encourage bad and incorrect practice by having candidates join up points on charts that represent continuous functions and the only reason for the scatter is experimental uncertainty in the measurements. Assessors might want to refer to the link
 
This gives background beyond the requirements of Level 2 but will help assessors less confident in mathematics to have appropriate expectations. In general, level 1 learners should be guided to identify the distinction between a chart for continuous variation and one for discrete data points in simple common examples. Continuous variation occurs in situations where there is a continuing change in one variable as a result of change in another. For example, distance travelled from a fixed point by a car travelling at constant speed. Here the distance varies continuously depending on time elapsed from the start. Sample measurements can be taken at any time and they will still fit closely to the same pattern. The appropriate chart is a scatter chart with a line of best fit to the data. The line represents the continuity of the data averaging the measurements because they are never precise. Candidates should definitely not use pie charts and bar charts to represent such situations. In a situation where the measurement is of a discontinuous variable, e.g. the rainfall pattern over a year, a bar chart or a pie chart is appropriate but not a best fit line. We can not make any assumptions about the pattern of rainfall within a month. All the rain might have come down on the first day, the last day or some random set of days.  For schools, it would be a good idea to co-ordinate the development of IT skills for drawing charts with work in mathematics and science. Balance is important and so there is a need to be careful that one type of chart is not being over-used leading learners to think that all data can be represented in that particular format.
 
There are also opportunities here to apply IT functional skills. A candidate could use a spreadsheet and chart drawing program to produce the chart and then use the image of the chart in a web page together with a table of values taken from the spreadsheet to present information to a wider audience, for example in a blog or e-portfolio. This would provide evidence of a range of the criteria and it is this flexibility and self-sufficiency that we are aiming to achieve. The difference between Level 1 and Level 2 is that at Level 2 the candidate should be self-sufficient in producing appropriate charts from their data whereas at level 1 candidates will need structured support , operating in routine contexts. They will need guidance when a new or unfamiliar context is presented. The level 1 candidate might simply show that they can select an appropriate chart for a particular familiar context from a limited choice provided by their assessor.

3.3 I can select and use appropriate tools and techniques to generate, develop and format charts and graphs

Candidates should show capability of producing the chart types they identify. They should be able to present information with title, axis titles, and legends for a specific purpose that has been practised and has a familiar associated context and target audience.
 
Evidence: from their files and web pages.

3.4 I can select and use appropriate page layout to present and print spreadsheet information

Candidates should show capability of selecting pre-determined spreadsheet page layouts to present information including size, orientation, margins, page numbers, date and time for a specific purpose and familiar audience.
 
Evidence: from their files and web pages.
 
Additional information and guidance
 
This implies the use of prepared spreadsheet templates which are provided as a vehicle for particular spreadsheet problems.

3.5 I can check spreadsheet information meets needs, using IT tools and making corrections as appropriate

Candidates should evaluate the quality of their solutions against given criteria including checking accuracy of numbers, formulas and any text; accuracy of results; suitability of charts and graphs.
 
Evidence: from their files and web pages
 
Additional information and guidance
This can be linked back to the evaluation criteria in Improving Productivity Using IT. If they can do this with their own criteria and without much guidance they are operating higher than the specified level and might be ready for L2 assessment. 

Moderation/verification

The assessor should keep a record of assessment judgements made for each candidate and make notes of any significant issues for any candidate. They must be prepared to enter into dialogue with their Account Manager and provide their assessment records to the Account Manager through the on-line mark book. They should be prepared to provide evidence as a basis for their judgements through reference to candidate e-portfolios. Before authorising certification, the Account Manager must be satisfied that the assessors judgements are sound.