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


The learning objectives for this session are that by the end of it you should:
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. This is a link to the index of the spreadsheet models the groups constructed. 
At the Institute
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.
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 'bestbuy’ 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 considerIs 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? 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 email. 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. 
A key misconception in this area is of the nature of a computerbased 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. 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. The task set today was openended 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 openended 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. Other Resources 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. 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. 
This page is maintained
by Tim Brosnan. Please send any comments to: t.brosnan@ioe.ac.uk Last updated on 11th October 2001 . 
