Resource Material for the IT PGCE:
making and evaluating spreadsheet models


This page gives activities and resources for the 'Developing ideas and making things happen' session in which you construct a model to advise on which mobile phone is most appropriate for given users. The links to 'modelling in chemistry' and 'case studies of modelling in the classroom' are extensions to this session.


Index    
Introduction    
Learning objectives About this session Products of the session
At the Institute    
Task Timetable Links to other sessions
In the classroom    
Misconceptions Classroom examples Teaching point
Other Resources    
DfEE Schemes of work Software links and tutorials Books/Papers

Introduction

Learning objectives

The learning objectives for this session are that by the end of it you should:

  1. be able to evaluate the limitations and usability of a spreadsheet model;
  2. understand the need for pupils to start with simple models which can be augmented if time and skill permit;
  3. be able to adapt a task to suit pupils of varying mathematical ability;
  4. understand how this activity can be extended to be the basis of projects at different levels.

Return to the top of the pageReturn to the top of the page

About this session

One important use of computers is for making computational models. In addition to being able to use software to make a model it is important that pupils can evaluate the effectiveness of different kinds of modelling and their appropriateness in a range of circumstances. It is also important that when embarking on the construction of a model (say for a GCSE or A level project) the scope of what they wish to do fits with both their level of skill and the time and resources available, and that you can set tasks appropriate to the pupils' level of mathematical abilty. In this session, we will use Excel to make a model for a specific purpose - advising on mobile phone use - and then discuss issues involved in pupils doing this in the classroom. This discussion will include - but not be confined to - the issues raised above.

Return to the top of the pageReturn to the top of the page

Products of the session

This is a link to the index of the spreadsheet models the groups constructed.

Return to the top of the pageReturn to the top of the page


At the Institute

The task

The task is to construct a spreadsheet (in our case Excel) model to give advice to the four potential mobile phone users listed below on which tariff they should use.

  • User 1: Used heavily, more than one hour a day Monday to Friday, on business hours for local calls.
  • User 2: Used regularly, about four hours a week between evenings and weekends for local calls.
  • User 3: Used to stay in touch with a partner when away from home, Monday to Friday after work for national calls for about three hours a week.
  • User 4: Kept in the car for use only in emergencies.

Because there are so many different tariffs, you will use your worksheet to explore only one network and then compare your findings with those from groups exploring other networks.

Data

You can find information on the current tariffs of each of the four network using the links below.

The worksheet

How you construct your worksheet is completely up to you so long as it uses the data to calculate a 'best-buy’ option for each of the four users.

When you have finished, give your data to groups exploring other networks and integrate their information to your workbook.

You can download an example of a workbook that selects the best option from all the networks which was made by a BT in a previous year.

Questions to consider

Is the model only applicable to these four people or can it be used to help a wider range of potential users? What other factors would then have to be taken into account?

How sensitive is the link between data and results? Does the suggested choice change when the usage pattern changes a little? What are the implications of this?

How can you display your data so that it is easily understandable to a user?

How realistic a model does it use? For example it assumes that use is independent of cost. What other assumptions are made? Evaluate these.

How could this activity be adapted for pupils of different mathematical ability?

How could this activity be extended to form the basis for a project at KS3, GCSE and 'A' level?

Return to the top of the pageReturn to the top of the page

The timetable

At 9:30 we will divide into groups to construct the models. At 12:00 there will be a discussion of the issues met while completing the activity - so the models need to be finished by then. If required, this discussion can continue via e-mail.

Return to the top of the pageReturn to the top of the page

Links to other sessions

This session links to the one on data validation. It also links to the session on datalogging since in that session, the captured experimental data is compared with the predictions of scientific models.

Return to the top of the pageReturn to the top of the page


In the classroom

Misconceptions

A key misconception in this area is of the nature of a computer-based model. For example, when I worked on the 'Tools for Exploratory Learning' project, we found that many children regarded the computer representation as 'the real thing', saying, for example that something 'must be right because the computer says so'. So when teaching it is important that you explicitly get pupils to think about the range of utility of models they use and/or construct - of the basis of the rules etc. used in its construction. One key way of achieving this to to examine ways in which the model is different from the object/event being modelled - as well as the ways in which they are similar.

The book "Learning with Artificial Worlds" by Mellar et al. (IoE library reference Loz Butb MEL) which derives from the work of the 'Tools' project is a good source of the misconceptions pupils have about the nature of computer models.

Return to the top of the pageReturn to the top of the page

Classroom examples

I have written a number of examples of how spreadsheets can be used in 'A' level chemistry, and (as part of a large EU funded project) Fani Stylianidou has conducted a number of case studies of teachers using computer modelling in science. Although these were science teachers many of the issues raised in her work are of general concern.

In addition, Sadaf Sajid has written a series of worksheets to show pupils how to produce and use the 'If' function, Lookup Tables, Combo Boxes and Conditional Formatting.

See the 'Modelling' page on DigitalBrain's London Grid for Learning site - and decide how much of this is really applicable to KS4.

Return to the top of the pageReturn to the top of the page

Teaching point

The task set today was open-ended in that it allowed you to produce models of varying complexity. In this it was another example of differentiation by outcome. However, there is a problem with tasks of this sort. If you planned to construct an 'all singing and dancing' model you would not have had time. If however you started with a simple model and then augmented it you would have had time - and would have produced a working product.

It is important that pupils get a sense of achievement - that they complete tasks and can see a product. If the task is as open-ended as the one set today, then if pupils plan at the outset to produce a complex product they can often run out of time and have no useable product at the end of the lesson. This will lessen their motivation. It is important therefore to ensure that they set themselves tasks that they can complete (given their ability and the time available). Simple models can always be augmented if time permits.

Return to the top of the pageReturn to the top of the page


Other Resources

The DfEE Schemes of Work

You should look at the following units of the DfEE schemes of work:

Key Stage 2: unit 3D, Exploring simulations; unit 5A, Graphical modelling; 5D, Introduction to Spreadsheets; 6B, Spreadsheet modelling;

Key Stage 3: unit 4 Models - rules and investigations.

Return to the top of the pageReturn to the top of the page

Books/Papers

The book "Learning with Artificial Worlds" by Mellar et al. (IoE library reference Loz Butb MEL) is an excellent introduction to the issues involved in different types of modelling activities.

The website of the'ModellingSpace' (sic) project has an excellent collection of links to relevant people, projects, papers, resources and software. The project is part of the EU's IST programme.

Return to the top of the pageReturn to the top of the page


Return to IT PGCE Home PageBack to PGCE Information Technology Home Page
Back to Institute of Education Home PageBack to Institute of Education Home Page

This page is maintained by Tim Brosnan. Please send any comments to: t.brosnan@ioe.ac.uk
Last updated on 11th October 2001 .