Spreadsheet modelling using Excel
Year 6

Objectives

Pupils were asked to carry out an investigation to develop a spreadsheet that would calculate a Christmas present shopping list. Pupils had previous knowledge of the basic functions of spreadsheets:

	How to calculate using formulae
	How to format cells
	Understand what happens when data is changed

The activity objectives were for pupils to:

	use a variety of formulae to calculate total prices, individual prices, percentage discount / increases
	use copy formulae to one cell to other cells
	use the spreadsheet to draw a graph
	present worksheets and graphs to reflect the Christmas theme

Activity Description

The activity was spread over 2 lessons totalling 3 hours. Pupils were introduced to graphing techniques. They were encouraged to choose suitable types of graphical interpretation. They were also allowed to format graphs and axis using pictures derived from the Internet. This included searching on the Internet.

At the beginning of the first lesson, the teacher introduced the task in hand. The pupils were informed of the functions the spreadsheet must carry out. No discussion took place on the variety of formula required.

Pupils were encouraged to think about the types of presentation they used both on their worksheet and graphs. Higher ability pupils were informed that their spreadsheets must calculate a percentage increase / decrease from a pre-Christmas price to a sale price.

Commentary

In the first example, Annie has developed the formula correctly, and independently. She decided to use a colour scheme and also changed the fonts where necessary. Annie did have problems when attempting to graph the data as initially the axis data was not being shown correctly. She discussed this with the teacher and he told her to experiment with the layout of the graph using the 'series' function. Annie was very pleased with the outcome.

Annie's work shows these aspects of Level 4: interpret findings and question plausibility; add to and combine different sources of information; use ICT to present information and are aware of different audiences.

Annie's work is moving towards Level 5 - select information required and organise it in a form suitable for processing; explore the effect of changing the variables in a spreadsheet; assess the use of ICT and to reflect critically.

Clare and Rebecca, both of whom had carried out spreadsheet work as part of their maths set work, understood the variety of formula required. They knew how to copy formulae into cells and decided on a green and red Christmas colour scheme. They also experimented with the format of their graph, ignoring the fact that the axis were incorrect.

Clare and Rebecca's work is certainly Level 4 but their emphasis on concentrating on the colours in the graph rather that its content would not allow for them to move towards a Level 5.

Susie is in the lowest set for Mathematics and in theory should have found the activity difficult. She does show good ICT skills, and she developed the following spreadsheet independently. She did have problems with graphing the data, primarily due to her axis being incorrectly formatted. After a discussion with her teacher, she experimented with the series data labels and arrived at the following graph. Susie experimented with her background for the graph; she eventually downloaded the clip off the Internet and inserted it using the fill effect.

Susie's work is a clear Level 4. She is aware of her audience, she can use a spreadsheet to combine different sources of information and is aware of her audience. Susie may have problems moving to the next level as her mathematical skills may not be fully developed to allow her to organise raw data.