Unit 52:Spreadsheet Modelling

Unit 52:Spreadsheet Modelling

Unit code:Y/601/6637

QCF Level 3:BTEC Nationals
Credit value:10
Guided learning hours:60

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 evidence must show that the learner is able to:

To achieve a merit grade the evidence must show that, in addition to the pass criteria, the learner is able to:

To achieve a distinction grade the evidence must show that, in addition to the pass and merit criteria, the learner is able to:

P1 explain how spreadsheets can be used to solve complex problems

D1 discuss how organisations can use interpretation methods to analyse data

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


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 businesss 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 tasks •      whole-class exercise begin using the spreadsheet to solve complex problems
•     individual exercise from tutor-prepared materials learn advanced formulae and use on a spreadsheet
•     directed 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 functions
•     individual exercise from tutor-prepared material look at higher-level formulae, features and functions
•     whole-class exercise tutor presentation on formatting and styling

•     whole-class exercise tutor presentation on how to automate and customise spreadsheet models using macros, Active X control, Visual Basic
•     whole-class exercise tutor presentation on what sorting and summarising data means, why it’s needed and how it can be done
•     whole-class exercise tutor leads lesson on charts/graphs and how to manipulate them

•     individual 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 plans
•     whole-class exercise tutor leads lesson on methods for capturing feedback and how to interpret the results from feedback
•     individual exercise learner works from tutor-prepared materials to produce user and technical documentation
•     whole-class exercise learn about conversion to other file formats.

Assignment 3 Going to the Next Level


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


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

P2P6, 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.


Criteria covered

Assignment title


Assessment method

P7P9, 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.


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

Essential resources

Learners will need access to appropriate hardware and software.

 Employer engagement and vocational contexts

The use of vocational context is essential in the delivery and assessment of this unit. Learners will require access to computer equipment to enable them to gain a practical awareness and enable them to apply their knowledge and understanding in a practical situation.

There is a range of organisations that may be able to help to centres engage and involve local employers in the delivery of this unit, for example:

•     Learning and Skills Network www.vocationallearning.org.uk •    Local, regional business links www.businesslink.gov.uk
•     National Education and Business Partnership Network www.nebpn.org

•     Network for Science, Technology, Engineering and Maths Network Ambassadors Scheme – www.stemnet.org.uk

•     Work-based learning guidance www.aimhighersw.ac.uk/wbl.htm

•     Work Experience/Workplace learning frameworks Centre for Education and Industry (CEI University of Warwick) www.warwick.ac.uk/wie/cei.

Indicative reading for learners


Day A Mastering Financial Mathematics with Excel (Financial Times Prentice Hall, 2005) ISBN 0764597809

Hart-Davis G How to Do Everything with Microsoft Office Excel 2003 (McGraw-Hill Education, 2003) ISBN 0072230711

Heathcote R Further Excel 2000-2003 (Payne-Gallway Publishers, 2004) ISBN 1904467768

Koneman P Advanced Projects for Microsoft Excel 2000 (Prentice Hall, 2000) ISBN 0130885444

Simonn J Excel Data Analysis, 2nd Edition (Hungry Minds Inc US, 2005) ISBN 0764597809

Zapawa T Excel Advanced Report Development (Hungry Minds Inc US, 2005) ISBN 0764588117