Relevant LINKS
BACK TO ITQ UNITS
Handbook home page
Overview (Under Development)
The candidate can understand, create and use a reasonably complex spreadsheet system to solve some real world problems. They need to be able to use complex formulae and input controls to make sure the data is fi for purpose and can be analysed as required. They need to be able to format the spreadsheet and use the more advanced tools to present the data in the best format and they need to quality control the system.
A work activity will typically be ‘non-routine or unfamiliar’ because the task or context is likely to require some preparation, clarification or research to separate the components and to identify what factors need to be considered. For example, time available, audience needs, accessibility of source, types of content, message and meaning, before an approach can be planned; and the techniques required will involve a number of steps and at times be non-routine or unfamiliar.
Example of context – an example might be a sales forecasting system for a local company with a medium number of products and services.
Assessor's guide to interpreting the criteria
General Information
QCF general description for Level 3 qualifications
- Achievement at QCF level 3 (EQF Level 4) reflects the ability to identify and use relevant understanding, methods and skills to complete tasks and address problems that, while well defined, have a measure of complexity. It includes taking responsibility for initiating and completing tasks and procedures as well as exercising autonomy and judgment within limited parameters. It also reflects awareness of different perspectives or approaches within an area of study or work.
-
Use factual, procedural and theoretical understanding to complete tasks and address problems that, while well defined, may be complex and non-routine.
-
Address problems that, while well defined, may be complex and non-routine. Identify, select and use appropriate skills, methods and procedures. Use appropriate investigation to inform actions. Review how effective methods and actions have been.
-
Take responsibility for initiating and completing tasks and procedures, including, where relevant, responsibility for supervising or guiding others. Exercise autonomy and judgement within limited parameters information and ideas
Requirements
-
Standards must be confirmed by a trained Platinum 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 and files 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 3 learner 50 hours of work to complete.
Assessment Method
Assessors can score each of the criteria N, L, S or H. N indicates no evidence. L indicates some capability but some help 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 a S on all the criteria to achieve the full award.
Expansion of the assessment criteria
1. Candidates will use a spreadsheet to enter, edit and organise numerical and other data
1.1 I can identify what numerical and other information is needed in the spreadsheet and how it should be structured
Candidates should be able to demonstrate a clear plan about what they intend to solve by creating a spreadsheet.
Evidence: will be provided candidate's portfolios and assessor observations and feedback.
Additional information and guidance
Tied up with this criterion is the need for candidates to find a suitable project in order to build their project around. It would be worth approaching local businesses or school departments in order to gain enough complexity of numerical and other data to justify a L3 quality project. In some cases, the companies might be reluctant to release sensitive numerical data, so the assessors might need to support candidates in their process. Once they have found a suitable project, they should then put together some rough plans and design layouts in order to show roughly what they are dealing with and how they might address the fix. Some of these rough sketches might include diagrams about what kind of data might be handled, what sort of outputs there could be and what they might look like etc. The structure should include some of the complexity, such as linked or vlookup tables or other complex elements. These should not be used for the sake of getting marks however and a good project should be chosen so that it can incorporate these.
1.2 I can enter and edit numerical and other data accurately
Candidates need to demonstrate that they can enter data and be aware of inaccuracies in their entry process, making changes where necessary.
Evidence: will be provided by quality of finished work and assessor feedback.
Additional information and guidance
The majority of errors, particularly in complex financial systems, is in the entry phase. It is easy to misread a number or enter a wrong number into a spreadsheet and therefore end up with inaccurate output as a result. It is also easy, though not as easy, to put in some wrong data into a formula so have the wrong output. There are ways and means to minimise this, but the best way is to be careful and methodical when entering data. Candidates should be able to display this and have some method by which they can double check their work and be able to edit it to fix any errors before the final output is generated. A certain amount of mathematical knowledge may be required, especially in entering BIDMAS based calculations, so some cross-departmental work with Maths would be useful.
1.3 I can combine and link data from different sources
Candidates should be able to work with data across multiple sources and be able to link these effectively.
Evidence: will be provided by quality of completed work and assessor observations.
Additional information and guidance
Most modern spreadsheet applications are able to merge multiple workbooks into one single workbook, or merge across several different sheets. These advanced functions should be incorporated into the candidate's project as appropriate. Data can be linked in one sheet frm another sheet in order to present more consolidated information, such as a related chart in some data. The data might also be mixed, i.e text and numbers.
1.4 I can store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
Candidates should be able to access their spreadsheets efficiently in line with where the files are.
Evidence: will be provided by assessor observations.
Additional information and guidance
Depending on who the spreadsheet is for and where therefore it is going to be used, the candidates should have some good understanding of where they are storing their files and how to get them back when working on them. This is important if they are designing it for a local company as they will need to be comfortable with the same process. A local company is likely to have a different set of guidelines for this process. Candidates need to reference this somewhere in their work.
2. Candidates will select and use appropriate formulas and data analysis tools and techniques to meet requirements
2.1 I can explain what methods can be used to summarise, analyse and interpret spreadsheet data and when to use them
Candidates should be able to explain what methods they will use to work on their data. These should also be justified and not just for the sake of it.
Evidence: will be provided by the finished system and assessor feedback.
Additional information and guidance
The idea here is to show that students can research, evaluate and decide what are the best methods to use in their spreadsheets to solve the problems and make sure the answers that are created are worthwhile. They need to show the different methods they might use to summarise the findings. The different ways to summarise can be explored to see which ones make the most sense and are the clearest. The same data can be summarised in different ways and each way will give a slghtly different emphasis and may not convey the required meaning. They might be abe to negotiate some options with the people they are designing the spreadsheet for and test out some alternatives. Likewise, the analysis will depend on what is required in terms of understanding. What are they trying to find out from the collected data? Do they have some ideas that they are trying to prove by using the data, or are they looking for patterns that will help them make decisions? Are these decisions now, or are they for the future? All of these will require different kinds of analysis and data manipulation. Once the data has been analysed, how will it be presented and what elements will be used to aide interpretation. Sre totals enough? What kind of labeling will help with interpretation. If you use graphs and charts, what is the best way to set these up so that the interpretation is clear and unambiguous?
2.2 I can select and use a wide range of appropriate functions and formulas to meet calculation requirements
Candidates should be able to use a wide range of functions and formulas to make the system work effectively and efficiently.
Evidence: will be provided by the finished system and assessor observations. Possibly from customer feedback.
Additional information and guidance
Once the candidates have identified the type of data they need to summarise and interpret, they should then be able to think about the ways this can happen. There are many complex forms and functions in spreadsheet applications and there are many ways to construct complex formulas for working out things like taxation and compound interest, for example. The functions and formula used will depend on the nature of the project, but candidates should be encouraged to try many types, even if they don't use them, so that they are familiar with the advanced features of spreadsheets. The help systems built into the applications offer clear examples about how formulae and functions work. The functions should include, but not be limited too, lookup tables, conditional formatting, pivot tables, macros, 3D formulas, absolute and relative referencing, nested if statements, and statistical functions.
2.3 I can select and use a range of tools and techniques to analyse and interpret data to meet requirements
Candidates should be able to demonstrate advanced skills in data analysis and interpretation.
Evidence: will be provided by finished system and customer feedback.
Additional information and guidance
Candidates should be able to perform basic statistic analysis on their derived data. These will include, but not be limited to, basic statistical analysis, correlations, frequencies, regression tests, and variance. Interpretation should be consistent with L3 maths.
2.4 I can select and use forecasting tools and techniques
Candidates should be able to effectively use the built in forecasting tools and techniques.
Evidence: will be provided by assessor feedback and student portfolios.
Additional information and guidance
Forecasting is an essential element of any business and in financial markets it is a high paying affair. By using your client's data, you should be able to predict, with some degree of accuracy, what is likely to make you money in the future. If you are creating a sales spreadsheet for a local company, your system should be able to tell them something like, e.g. based on last year's sales of item X, you will need Y number of the item in stock so as to have enough to sell over the Summer months etc. Graphs output as trend lines will be able to show this graphically, but it is the way that the data is gathered and analysed that will make this trend line as accurate as possible and candidates will need to show an awareness of how to choose the right data and ho to manage it effectively.
3. Candidates will use tools and techniques to present, format and publish spreadsheet information
3.1 I can explain how to present and format spreadsheet information effectively to meet needs
Candidates should be able to explain what methods of presentation and formatting they will use and why.
Evidence: will be provided by the finalised system.
Additional information and guidance
The overall layout of the spreadsheet, including labeling, colour schemes, tab names etc, will assist in the clarity of presentation and the ability for it to be used effectively and efficiently. Candidates should show in a design phase what methods and techniques they intend to use, as well as show these in the final design. They can use a variety of advanced techniques such as conditional formatting of cells to give colour and clarity to key data, as well as clear names on columns and rows and any charts produced.
3.2 I can select and use appropriate tools and techniques to format spreadsheet cells, rows, columns and worksheets effectively
Candidates should be able to use all the appropriate tools to make their spreadsheet as professional as possible.
Evidence: will be provided by the finalised system as well as customer feedback.
Additional information and guidance
As with above, but in more detail at the cell level. How can the spreadsheet be made to be more clear and concise, especially with large and complex sheets. Good techniques need to be displayed to choose the right fonts and colours and the layout, i.e. by turning row and column headers by X degrees to make them easier to read and stand out, by locking columns and rows so that scrolling is effective.
3.3 I can select and use appropriate tools and techniques to generate, develop and format charts and graphs
Candidates should be able to produce clear and effective graphs and charts.
Evidence: will be provided by finalised product and assessor or customer feedback.
Additional information and guidance
The wrong kind of chart of graph can make clear information suddenly opaque and irrelevant. Candidates must show a clear understanding of how to present different data in the right graphical way. The carts need to be taken from the right data sets to produce what is required and be well labeled for extra clarity.
3.4 I can select and use appropriate page layout to present, print and publish spreadsheet information
Candidates should be able to complete the presentation of data in terms of printing or presenting.
Evidence: will be provided by customer or assessor feedback.
Additional information and guidance
In most cases the customer will dictate what the output format will be, particularly if they need to present some fo the information in either digital or printed forms. Some data will lend itself to a landscape layout, some to portrait and candidates need to show that they are aware of the best method. If in digital format, they should test the system to make sure that the system looks good in the presentation medium chosen.
3.5 I can explain how to find and sort out any errors in formulas
Candidates should be able to explain how they dealt with problems.
Evidence: will be provided by candidate portfolios and reflections.
Additional information and guidance
No system will be perfect and there will always be errors to fix and sort out. The key is to be able to show that they were dealt with in a professional and methodical way. Candidates should probably keep a log of their problems and fixes. Any system needs to be well documented as there will always be someone else who needs to look after it and they will never know what you did to create it unless you show them. Some display of rough working of complex formulas will show how they were constructed and if any problems occurred in their construction, how they were fixed and modified can be shown. If candidates keep a digital log such as an ePortfolio, this can be used for this purpose.
3.6 I can check spreadsheet information meets needs, using IT tools and making corrections as necessary
Candidates should be able to use basic tools to make sure the finished product is fit for purpose.
Evidence: will be provided by guidelines and portfolios.
Additional information and guidance
Some basic tools will be using spell checking to make sure there are no mistakes. Candidates also need to have some basic checklist of needs from the client and make sure these are met throughout. This could be a consistent colour or font, no spelling errors and an easy to use interface that makes sense and is not overly complicated. It needs to do the job of calculating and analysing, but should still look and feel good. Any problems found need to be fixed and commented on for further developments.
3.7 I can use auditing tools to identify and respond appropriately to any problems with spreadsheets
Candidates should be able to keep track of the overall development so that it can be reproduced.
Evidence: will be provided by guidelines and portfolios.
Additional information and guidance
Some tracking system for auditing would be useful. candidates could combine this with Unit 3 where they use a collaborative on-line tool to track their work and get sign off from their clients at each stage. there are plenty of free project management tools available such as
Wrike. This will allow tracking and auditing functions.
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 dialog 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 and through signed witness statements associated with the criteria matching marks in the on-line markbook. Before authorizing certification, the Account Manager must be satisfied that the assessors judgements are sound.
