Theme Park Spreadsheets - Lesson 2

Having designed their theme parks in the previous, rather fun lesson, this time I get the children to do some more substantial work by asking them to create a spreadsheet to calculate its running costs.

I already cover the basics of spreadsheets in Year 5 (cell referencing, entering simple formulae etc.) so this lesson mostly serves a gentle recap of all the skills they've previously been taught.

I give them a template spreadsheet that I've created to work in - this is mostly done to save time as asking a whole class of children to simultaneously put table headings and specific costings into particular cells without anyone getting stuck/making mistakes would be quite a miracle I would predict!

Download Running Costs Spreadsheet

To get them to complete the spreadsheet accurately, I explain how it is best achieved by working through it as though it is being - i.e. from left to right across the columns:

• The first column just lists all the attractions in the theme park which cost money to run (so paths and lakes aren't included).
• The second and third columns describe the staffing and maintenance costs for each of these types of attractions. As these are just normal numbers to start with, I show the children how to highlight them and format their number type to currency to quickly convert them to money values with pound signs (£) in front of them.
• The fourth column requires the previous two costs to be added together to calculate the total running cost for each attraction. I remind the children of the benefits of using formulae (including efficiency and accuracy) and show them how to easily construct an addition formula to work out the running cost of a roller coaster by: typing equals (=), clicking on the first cell to add (to save having to work out its reference manually), typing add (+) and then clicking on the second cell to add. I then teach them how to use 'fill down' to quickly replicate this formula into the cells underneath to perform the same calculation for the other attractions. As these formulae are working with numbers which are already in currency format, their answers are automatically shown as currency too (saving children the job of having to change it).
• The fifth column simply requires them to type in how many of each attraction they have in their park. Since they have jotted down these numbers on the note-taking sheet I gave them last time, they can easily copy them onto the screen without having to mess around finding their work on the server.
• The sixth column requires the children to input a multiplication formula to work out how much all their different types of attractions cost to run. Similar to the fourth column, this is calculated by entering a simple formula which is then replicated down into the cells underneath.
• Lastly, in the 'total' cell, the children need to enter a formula which finds the total of all the individual attraction costs above to determine what their park's overall running cost is. I encourage them to use an =SUM formula here and then ask them to write this figure down on their note-taking sheet to allow for easy reference in future lessons.

Once this process has been followed, the spreadsheet is functionally complete and so I therefore like to let the children spend a short time improving its appearance to make it look more attractive. This includes: changing font colours, changing cell fill colours and aligning numbers to the right-hand-side of columns etc. I obviously encourage them to apply a sensible, consistent design that enables their spreadsheet to be read easily.

Next, I teach them how to use the chart wizard to graphically represent some of the data in their spreadsheet to make it easier to interpret - I ask them to create a pie chart to show how the total running cost of the theme park is divided up between the different types of attractions within it. This is a pretty straightforward task to do as the wizard guides the children through the process - this is no bad thing though as it hopefully helps them realise how simple it actually is to create graphs in a spreadsheet program.

Once drawn, I then let the children again spend a little time making it look nicer (e.g. changing font colours, text sizes, sector fill colours etc) - which they always love doing - before then asking them some questions to interpret what it shows, such as 'Which item takes  up the biggest/smallest proportion of your overall running cost?' We also discuss the benefits of using graphs to present tables of numbers - specifically how patterns/trends/statistics in a series of numbers can be identified more easily when numbers are shown visually.

As an addition, for those that want it, I've also created this set of instructions that aim to remind the children of all the steps which they need to follow to complete the lesson successfully - particularly useful if you want to get them to work independently as apposed to running through the spreadsheet as a whole class step-by-step.

This lesson works well because it not only has a clear progression route through it that the children like following but also because it teaches them some new skills too which they realise will help them do spreadsheet calculations more quickly in future (e.g. fill down).

Comments

[Martin Nickolls]

Simon, you are an absolute diamond...started this unit with my Y6 classes last week and they are loving it...you've brought light into a previously pretty dull bit of the curriculum!
Thanks for your ideas, your time and your generosity with all.