Case Studies

QCA Scheme of Work - Unit 6B - Spreadsheet Modelling

In this unit children learn to use a spreadsheet to explore a mathematical model.
Children will be taught to use formulae in spreadsheets to answer 'what if ...?' questions. They will explore how changes in a spreadsheet affect results and identify simple rules.
Children will apply what they have learnt in this unit when exploring mathematical models.

Year 6 Subject link - Maths
With thanks to Combe Primary School

Overview

Introduction
Planning
Preparatory work - Introducing the project and investigating prices
Lesson 1 Starting to make a spreadsheet, adding formulae, making decisions on the lunches
Lesson 2 - Using the spreadsheet
Lesson 3 - Writing a report
Evaluation - Of the lesson
Assessment - Measured against QCA learning objectives and the NC level descriptors

Introduction

Most of the children had used Number Magic previously but had only used the prepared exercises. They knew how to make the table but not how to enter formulae. The whole program was new to a few children.

Context

The children were working on 4 of a bank of 6 School Share computers at one side of the classroom. Eight of the class were working on this project at this time. The rest of the children were doing other tasks in the same room. I

This is a plan for about 4 sessions, depending on how long the children take over the project. There is extension work planned for anyone who finishes early.

Medium Term Plan

QCA 6E Spreadsheet Modelling Scheme of Work
Spreadsheet Modelling unit
Oxfordshire Framework for a Scheme of work page 58
The full plan for the project Word pdf

NC ref

Learning Objective

Activities

Assessment Opportunities

2 a b c d

3a b

4a b c

5a b

To become competent in the use of Number Magic to build and use a spreadsheet model.
Think about what the model needs – how to lay it out. Learn how to or revise how to build a formula with the formula builder
Children will alter the model to play number games – 5 multi pack against 30 packets of crisps, etc to come up with the best deal.

To build a “model” spreadsheet with all of the gathered info. (This could be several models – each group or pair could share information but build a different model.) Start to work our what the best deal is for our lunchboxes – multi – packs v single packs, what sort of choc bar and large bottles of pop v small bottles v cartons.

Make a report on the best deal.

Children design a sheet to gather information
Are the children able to build a spreadsheet model, entering data and formulae?
Change of plan – 2 new sheets needed, are they able to lift information from spreadsheet or do they have to create new from scratch?
Cut and paste between applications to produce a report.

Preparatory work

The teacher had a very short session with the children so that she could tell them about the project and set them to start investigating the prices of what they wanted to use whilst in the local shops or supermarket. This research was to take place over the half term holiday.
Setting the scene: We are going on a school trip and have to take a picnic lunch. Mrs Jacobs says that she will make the sandwiches but we have to buy everything else to go in the lunch boxes and we have £1 per person. We will work in groups of 6.
Discuss what children need to find out e.g. – cost of chocolate biscuits, fruit, drinks, crisps. Children do a survey for homework of prices in a local shop or supermarket.
Children create an empty chart to fill prices in – decide whether thy are working in pairs or groups and who is going to collect what information.

Plan to get lots of information – crisps in large multi-packs, chocolate bars in multi-packs, drinks in big bottles, small bottles, cartons and multi packs.
The teacher made a worksheet for adding formulae in Number Magic in case the children found it difficult working on their own. In this situation there is no scope for just stopping the group and talking them through the process - they are too close to the rest of the class working on a different subject.

Lesson 1

A general discussion on which programme would be best for the purpose. A quick revision of making a spreadsheet - the children prompting the teacher on what to do. A teaching element on how to put in formulae by using the formula builder.

Children start to build model – in pairs. (There is be lots of other maths to sort out e.g. – how many glasses, flasks of pop are in a large bottle, is squash a better buy, how many multi packs of crisps or chocolate will be needed etc. so children moved to and from computers as necessary.)

Lesson 2 Using the Spreadsheet

Lesson 2 was setting a specific task to enable children to use their new skills to play with their model and make it work for them.

To open the session children thought they had to finish the work and present their best model printed off so that it could be shared with the others.

Suddenly there is a memo from the cook - orders please!!

And another memo from the office - Invoices please!

The children have to try to bring the required information from their spreadsheet to present to the various places.

News Bulletin: There has been a bad potato harvest and all potato products have gone up by 3p.

Lesson 3 Writing up a Report

Introduce the idea of incorporating all of the gathered information to write up a report. Remind children to use bullet points to keep it brief and cut their tables to be pasted into Word. Unfortunately a problem was found that meant the formatting in Number Magic is lost when pasted into Talking First Word so modifications had to be made. The reports are spoiled by the fact that the table transferred without formatting and brought all cells to the same dimensions - there was not time to do the formatting again in First Word! Report 1 Report 2 Report 3 Report 4

Evaluation of work completed

An interesting project very well done. The children were very well motivated and really got involved in the modelling aspect of spreadsheet work. Time was short - we could have done with another couple of sessions to really exploit the project to the full extent. Three groups worked very well and moved on quickly, the other group struggled with the language aspect of the work and the did not manage the reporting.

Assessment - measure against the QCA learning outcomes and NC Level Descriptors

most children have:	explored the effects of changing data in a spreadsheet (Level 4 - combined different forms of information from a variety of sources. They have used ICT to present information in different forms and shown they are aware of the intended audience... They use ICT based models and simulations to explore patterns and relationships)
some children have not made so much progress and have:	used a spreadsheet to calculate totals (Level 3 - used ICT to generate, develop, organise and present their work. They have shared and exchanged their ideas with others. They made appropriate choices when using ICT based models or simulations to help them find things out and solve problems)
some children have progressed further and have:	explored the effects of changing data in a spreadsheet; made predictions and used a spreadsheet to test them (Some more able children will have been working toward level 5. They have begun to refine and present information in different forms and styles for specific purposes and audiences. Assess the use of ICT in their work and reflect critically in order to make improvements in subsequent work)

Back to Good Practice Module information

Year 6 Subject link - Maths With thanks to Combe Primary School