The Lesson
Whole Class Teaching
Tell the pupils that they are going on a class visit to study Victorian
art work as part of their project. Explain that the pupils' task will
be to work out the cost per person for the trip. You may decide to set
different groups of pupils more or less items to cost in their spreadsheet;
these may be purely fictional as in the example or may be based on real
information.
Brainstorm the variables with the pupils:
Q. What are the different items that we will need to consider when
working out how much the trip will cost for each individual?
Transport (two small coaches or one large coach) entry fee (cost for pupils,
cost of adults, are any adults free)? How many pupils and adults are going
on the visit? Does the entrance fee include the cost of a guided tour,
does it include entry to any other building? etc.
Q Is there an additional charge for reserving somewhere to eat?
At this point the teacher could show the ready prepared word document
with example costings. Manor
Visit.doc or use costings for a real trip to be planned.
Main Activity
On this occasion we shall assume that pupils have had no previous experience
with spreadsheets. If this is not the case the teacher may wish pupils
to build their own model from scratch or alter the model provided, manor.xls.
Teacher demonstrates how to open the prepared spreadsheet called manor.xls
and asks questions in order to draw the pupils' attention to various features;
firstly how to identify cell references. In cell B4 the spreadsheet shows
the cost for hiring a coach - £140
Q Who can tell me the cell references for some other items?
D12 shows the total cost, D15 shows the cost per person.
Q How can you tell how many pupils are signed up to go on this trip?
Expect pupils to point out that the number of pupils is shown in cell
C5. Don't accept an answer that gives a number contained within a cell
e.g. 29 - that number could change, whereas the cell reference does not.
Q What happens if we change the number of pupils in C5 and press enter.
Expect the pupils to notice that the contents of cell C5 changes as you
press enter, and the cost per person and total cost both change.
Q Which other cells changed when I altered C5? You may need to undo
and redo to give all pupils the chance to notice.
Draw pupils' attention to the contents of the formula bar above the title,
by clicking in different cells containing data. For instance, when in
cell D5 the formula bar displays =B5*C5.
Q Look at the formula bar whilst I click on cell D5, what do you notice?
It is important for the pupils to realise that cell D5 contains the result
of a calculation, on this occasion the contents of cell B5 times C5, rather
than just a number.
At this point give the pupils the opportunity to open the spreadsheet
Manor.xls on their computer. Tell the pupils to change their spreadsheet
so that thirty pupils and eight adults are coming on this trip.
Q On your spreadsheet change the numbers of pupils to 30 and adults
to 8. Record the total cost and cost per person on your small whiteboards
(rough paper or class notebooks could be used). At the signal show your
results.
To allow pupils more practice now direct them to change some prices and
predict the effect on the cost per person.
Q If the coach is £150 how does this affect the total?
It goes up by £10.
Q If the cost of entry for pupils is £3.00 what affect does this
have on the cost per person?
It goes up by 43p.
Explain to the pupils that £250 is the maximum this trip should
cost.
Q In what ways can they alter the model so that the total cost is no
more than £250?
Pupils will be able to alter only a few variables the number entering
the maze or the adventure playground, this could be pupils only, or they
could decide that no-one will enter one or the other.
This lesson is only an introduction. If pupils have had more experience
the teacher could list, on a board, more variables, complete with costs
so that the pupils enter additional information and formulae into the
spreadsheet e.g. hiring a room, cost of a guide, kitchen demonstration
etc.
Work here can be extended for more able pupils to compare quotes from
different coach companies.
Q If more than one class was to go on the trip in what ways would this
affect our model?
Remind pupils to format the cells for currency (select the cells, click
on Format and select Number. Once the number section is open look for
Currency to two decimal places; £###.##)
You may also need to remind pupils how to put in a formula. Formulae must
begin with the equals sign so that the computer knows that a formula is
about to be entered. This is then followed by the calculation to be performed
e.g. =B6 * C6 . This can either be done by typing the cell references
directly or, once the equals sign has been entered, by clicking on the
required cell.
Plenary
We have used a spreadsheet to calculate the cost of a school trip.
Q How else might you have done this?
Pencil and paper, calculator etc.
Q What advantages does a spreadsheet have over these other methods?
Easy to alter the model. No need to record the results or remember them
- always visible. Can save the model and adapt for a different trip or
activity.
|
