Unit 52 Spreadsheet Modelling

· Aim and purpose

To enable learners to use complex spreadsheet modelling in order to support organisational activities such as credit control, sales forecasting and stock analysis.

· Unit introduction

Spreadsheets are key software for many businesses and organisations, helping them to keep track of numerical information and analyse it quickly and more easily than with paper records.

Accounting and finance use spreadsheets to record the transactions made by organisations. They have replaced manual pages in ledgers, where income and expenditure are organised into rows and columns. Users can make use of inbuilt functionality to help them to understand the data without needing specialist mathematical skills.

Utilities such as ordering, sorting and filtering will show the same data in different ways. Charts and graphs help to display information more visually. Complex calculations can be carried out using library functions or users can choose to create their own formulae.

One of the main advantages of spreadsheet software is that it can be customised with buttons and macros. IT practitioners can use many features, for example to restrict user access to whole workbooks, spreadsheets or parts of spreadsheets.

Spreadsheets can be saved in a number of different formats. The most useful format is comma separated value (csv), as this particular format can be read by many applications which means that data created in one type of spreadsheet software can be exported easily to other programs. This technology enables organisations to be more knowledgeable about their own activities.

This, in turn, allows managers to make decisions more quickly which can lead to organisations gaining competitive advantage.

As IT practitioners, learners will need to be able to use spreadsheet software competently as well as being able to support users as part of a technical or helpdesk role.

· Learning outcomes

On completion of this unit a learner should:

1. Understand how spreadsheets can be used to solve complex problems
2. Be able to develop complex spreadsheet models
3. Be able to automate and customise spreadsheet models
4. Be able to test and document spreadsheet models.

Unit content

1         Understand how spreadsheets can be used to solve complex problems

Use of spreadsheets: manipulating complex data; presentation to requirements; supporting decision making eg analysis of data, goal seeking, scenarios, regression, data mining

Complex problems: types eg cash flow forecasting, budget control, what-if scenarios, sales forecasting, payroll projections, statistical analysis, trend analysis

Interpretation: methods eg comparisons of totals, trend analysis

2         Be able to develop complex spreadsheet models

Complexity: multiple worksheets (with links); complex formulae eg at least two-step process; large data sets; cells linkage; data entry forms eg menu systems, list boxes, drop-down boxes, event controls; data validation; error trapping; lookup tables; nested IF functions; templates; cell protection

Formulae: relative references; absolute references; logical functions eg IF, AND, OR, NOT, SUMIF; correct operators

Structure and fitness for purpose: formatting eg integer, real, date, currency, text; styling eg bold, italics, borders, shading, column alignment, consistency; context

Features and functions: named ranges; file sharing; tracking changes; security issues; user interface; add-ins; built-in functions eg cell functions, lookup functions, text functions, statistical function; finding data

Refine: improving efficiency eg shortcuts, aiding navigation; formatting eg fonts, page orientation, header and footer, print area, use of colour, conditional formatting

3         Be able to automate and customise spreadsheet models

Sorting and summarising data: use of sub-totals and facilities eg pivot tables; sorting data on multiple fields; filtering data sets

Tools: charts and graphs eg titles; labels eg axis scales, colours, annotation; select appropriate type eg line, bar, column, pie, xy (scatter)

Presenting: combining information eg table of data and chart; maintaining data eg between worksheets, workbooks, packages

Analysing and interpreting data: convert data eg charts, graphs; lists eg filtering, sorting; trends; patterns; data analysis; results; conclusions

Customisation: restricting data entry eg hiding; protecting; modifying toolbars; modifying menus; checking data eg data validation, range checking, not NULL; error messages

Automation: methods eg macros, ActiveX control, Control Toolbox, Visual Basic

4         Be able to test and document spreadsheet models

Test: manual calculations eg formula, functions; data entry forms; validation; calculations; correct outcomes eg layout, values; suitability for client; user testing; test plans using normal, extreme and erroneous data

Feedback: methods eg surveys, questionnaire, interview; analyse results; make recommendations

Alternative formats: converting to eg xls, csv, txt, xms, xml, html

Documentation: user documentation eg instructions, guide, troubleshooting; technical documentation eg hardware resources, software resources; instructions; calculations eg formula, functions used; validation procedures

Assessment and grading criteria

In order to pass this unit, the evidence that learners presents for assessment needs to demonstrate that they can meet all the learning outcomes for the unit. The assessment criteria for a pass grade describe the level of achievement required to pass this unit.

Assessment and grading criteria
To achieve a pass grade the	To achieve a merit grade	To achieve a distinction
evidence must show that	the evidence must show	grade the evidence must
the learner is able to:	that, in addition to the pass	show that, in addition to the
	criteria, the learner is able	pass and merit criteria, the
	to:	learner is able to:
P1	explain how		D1 discuss how organisations can use interpretation methods to analyse data
spreadsheets can be
used to solve complex
problems
P2	develop a complex spreadsheet model to meet particular needs	M1 refine a complex spreadsheet model by changing rules and values
P3 use formulae, features and functions to process information
P4 use appropriate tools to present data	M2 analyse and interpret data from a spreadsheet model
P5 customise the spreadsheet model to meet a given requirement
P6  use automated features in the spreadsheet model to meet a given requirement	M3 compare different automation methods
P7 test a spreadsheet model to ensure that it is fit for purpose		D2 evaluate a spreadsheet model incorporating feedback from others and make recommendations for improvements.
P8 export the contents of the spreadsheet model to an alternative format
P9	produce user documentation for a spreadsheet model.	M4 produce technical documentation for a spreadsheet model.

Essential guidance for tutors

Delivery

This unit assumes learners have a basic understanding of spreadsheets and spreadsheet terminology. The unit should be delivered in a room containing computers so that learners can work through sample exercises or other source materials. Using practical examples, the activities undertaken in this unit should, if possible, be contextualised so learners gain the maximum benefit from learning about concepts.

Tutors should expect that the majority of time will be allotted to practical tasks, which will require, for most of the tasks and exercises, each learner to have access to a computer with some form of spreadsheet software installed.

For most of the practical work, it is strongly recommended that the tutor provide learners with a prepared spreadsheet. Ideally, these spreadsheets could be created for a specific business scenario. For instance, a spreadsheet could be created with a business’s financial management in mind. Spreadsheets are normally designed to use advanced formulae and make use of many of the spreadsheet’s advanced functions.

Another example of practical spreadsheet use is as a research tool for recording and analysing statistical information.

For the directed research exercises, the tutor could also give learners material to help them in directed research tasks. This material could come from a variety of sources, including websites specialising in advanced use of spreadsheet software, and from textbooks within the centre.

However, the tutor could also recommend a list of suitable textbooks that learners could obtain from local libraries, which could also help in research exercises. Most spreadsheet programs come with ‘Help’ functions that contain extensive ‘How To’ guides, and the tutor could also recommend that learners use these as research aids.

Outline learning plan

The outline learning plan has been included in this unit as guidance and can be used in conjunction with the programme of suggested assignments.

The outline learning plan demonstrates one way in planning the delivery and assessment of this unit.

Topic and suggested assignments/activities and/assessment

Introduction to the unit

Using spreadsheets to solve problems: whole-class exercise – tutor presentation on how to use spreadsheets for complex taskswhole-class exercise – begin using the spreadsheet to solve complex problemsindividual exercise – from tutor-prepared materials learn advanced formulae and use on a spreadsheetdirected research – find out the different ways in which a spreadsheet can be used to interpret complex data sets.

Assignment 1 – Embracing Complexity

Complex models: whole-class exercise – tutor presentation on creating and using complex spreadsheets with advanced formulae, features and functionsindividual exercise – from tutor-prepared material look at higher-level formulae, features and functionswhole-class exercise – tutor presentation on formatting and stylingwhole-class exercise – tutor presentation on how to automate and customise spreadsheet models using macros, Active X control, Visual Basicwhole-class exercise – tutor presentation on what sorting and summarising data means, why it’s needed and how it can be donewhole-class exercise – tutor leads lesson on charts/graphs and how to manipulate themindividual exercise – learner works from tutor-prepared materials to understand how to automate and customise spreadsheets.

Topic and suggested assignments/activities and/assessment

Assignment 2 – Making it Presentable

Test and document: individual exercise – learner works from tutor-prepared materials to test spreadsheet models and document test planswhole-class exercise – tutor leads lesson on methods for capturing feedback and how to interpret the results from feedbackindividual exercise – learner works from tutor-prepared materials to produce user and technical documentationwhole-class exercise – learn about conversion to other file formats.

Assignment 3 – Going to the Next Level

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 achieve 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 achieve 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 achieve 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.

Programme of suggested assignments

The table below shows a programme of suggested assignments that cover the pass, merit and distinction criteria in the assessment and grading grid. This is for guidance and it is recommended that centres either write their own assignments or adapt any Edexcel assignments to meet local needs and resources.

Criteria covered	Assignment title	Scenario	Assessment method
P1, D1	Embracing Complexity	A company has asked you to provide information on how spreadsheets can be used to solve complex problems, and how to use tools to analyse data.	Theory/practical exercises
P2–P6, M1–M3	Making it Presentable	You have convinced the company that spreadsheet models are the best way to analyse data. The company wants you to develop a complex spreadsheet to meet their needs. Exploit the full use of spreadsheet software by using advanced formulae, features and functions, and present data using charts and graphs. The company is now looking to take its work on sophistication even further, exploiting the software as much as it can with automated features.	Portfolio

Criteria covered	Assignment title	Scenario	Assessment method
P7–P9, M4, D2	Going to the Next Level	The company would like to feel comfortable knowing that the spreadsheet model has been fully tested. Perform testing of the spreadsheet model. The company would also like to train their staff on the use of the spreadsheet model.	Portfolio

Links to National Occupational Standards, other BTEC units, other BTEC qualifications and other relevant units and qualifications

This unit forms part of the BTEC in IT sector suite. This unit has particular links with the following unit titles in the IT suite:

Level 1	Level 2	Level 3
	Unit 27: Spreadsheet Modelling