Assessment

At this level, assessment is probably suited to assignments in the form of a mini project, where learners can apply all the principles of the unit to one business problem.

To achieve a pass grade, learners must meet the nine pass criteria listed in the assessment and grading criteria grid.

For P1, learners will need to explain how spreadsheets can be used to solve complex problems, and they should provide examples to support their explanation.

For P2, learners will need to develop a complex spreadsheet model, where ‘complex’ requires that the spreadsheet contains some aspects of the following range: multiple worksheets (with links), complex formulae, for example at least two-step process, large data sets, cells linkage, data entry forms, for example menu systems, list boxes, drop-down boxes, event controls, data validation, error trapping, lookup tables, nested IF functions, templates, and cell protection.

For P3, learners should solve a complex problem using formulae and functions in the spreadsheet. This might best be through a supplied scenario such as a cash flow forecast, a budgeting problem, ‘what if’ analysis, payroll projections or another similar scenario. This should include some aspects of the following range: relative references, absolute references, logical functions, for example IF, AND, OR, NOT, SUMIF, correct operators, named ranges, file sharing, track changes, security issues, user interface, add-ins, built-in functions, for example cell functions, lookup functions, text functions, statistical function and finding data.

P4 requires learners to create charts and graphs from numeric data sets. This can be either the same data used in different graphical images, or a number of different charts or graphs created from different data. Tutors should ensure that learners have created charts and graphs that are fit for purpose, they should contain appropriate titles, labels, axis scales and suitable colours, and that the chart or graph should be of the appropriate type. This would be achieved most successfully by giving learners a user need that requires them to select an appropriate graphical image from a possible range.

For P5, learners must customise the spreadsheet model. Examples of customisation include restricting data entry, for example hiding information, protecting worksheets and cells, modifying toolbars and menus, checking data, for example data validation, range checking, not NULL and display error messages.

For P6, learners should be able develop the spreadsheet model further by implementing automated features, such as macros, Active X control, Control Toolbox or Visual Basic.

For P7, learners should check the accuracy of the spreadsheet model. For example, evidencing that they have checked the spreadsheet model both in terms of the required functionality, accuracy of calculations, data validation, and to appropriate levels of detail (columns for example to two decimal places). Evidence should be in the form of test plans.

P8 could be evidenced through an observation and witness statement where learners demonstrate converting a spreadsheet file to an alternative format, and importing the converted file into the relevant software, for example an html file opened and printed successfully from a browser.

For P9, learners must produce user documentation with instructions on how to use the spreadsheet model, especially when navigating with user interfaces.

To achieve a merit grade, learners must meet all the pass and the four merit criteria.

M1 builds on P2 by requiring learners to refine their complex spreadsheet model to improve efficiency. Examples include introducing shortcuts, or other methods to aid navigation, as well as improving the presentation by applying different styles and formatting techniques. The spreadsheet model must be presentable and user friendly.

For M2, learners can use the graphs or charts they have developed for P4 as a method of analysing and interpreting data from their spreadsheet model. Alternatively, learners could use sub-totals or pivot tables, data sorting and data comparison (trends for example) techniques to analyse data. Learners will need to demonstrate that they are using these techniques to interpret the complex spreadsheet model.

M3 builds on P6 by requiring learners to compare different automation methods including macros, Active X control, Control Toolbox and Visual Basic.

M4 builds on P9 by requiring learners to produce technical documentation which includes the required hardware and software resources, instructions and an explanation of calculations used in the spreadsheet model.

To achieve a distinction grade, learners must meet all the pass, merit criteria and two distinction criteria.

D1 builds on P1 by discussing further how organisations can use interpretation methods to analyse data.

Finally, for D2, learners must evaluate their spreadsheet model and consider feedback from others. Learners should be able to reflect on their performance in building a spreadsheet model, and what hurdles they have overcome to achieve the desired result. Did the spreadsheet model meet the given requirements? What did other people think of the spreadsheet model? Learners must also include sensible recommendations for improvements.