My theme park spreadsheet lessons have always been a very popular resource on my blog so I thought I'd share all the new materials that I've made to teach the unit this year:
This year when I was delivering my 'theme park spreadsheets' project with Year 6, I found when I gave them more independence in completing the work a few children needed that bit extra challenge at the end when they had completed everything. I therefore produced a set of six mini challenge cards to help consolidate/teach them new spreadsheet skills:
To just to complete my series of blog posts on theme park spreadsheets, I thought I'd post a copy of my planning for the unit of work, along with the PowerPoint slideshow I display on the IWB:
In the final theme park spreadsheet lesson, the children need to create a spreadsheet that calculates their park's overall profit over a six-month period.
in the first column, the children have to use fill series to create a number series going from one to six to label the six months of the year their park is open for;
in the second column, I have listed the percentage increase in profit on the previous month - this is 10% for each month, with the first month needing no increase as their are obviously no prior values;
in cell C4, the children need to enter their park's profit for month one, copied from their note-taking sheet (and changed to currency format);
in cell C5, the children need to enter a formula which increases this profit by 10% - =C4*1.1;
the formula in C5 then needs to be replicated using fill down into the rows for months three to six;
in cell C10, the children need to input an =SUM formula to add up all the profit earned in each month;
in cell C11, the children need to put in how much money they still have left in the bank which they didn't spend in lesson one when building their park, copied from their note-taking sheet (and changed to currency format);
in cell C12, the children then finally need to enter a formula that adds together the money in the bank with the profit their park's made over six months to determine what its overall profit is.
Having done this, I then ask the children to create a bar chart, following the chart wizard, to show how the end-of-month profit increased over the six month period. I try to let them do this fairly independently (just giving them some guidance about the need to use suitable titles) as they will hopefully now be quite familiar with the options it gives them and what buttons they have to click on.
Next, I let them spend a short time (between five and 10 minutes) improving the appearance of their graph and spreadsheet to make it look more attractive and easy to read. I try to encourage them to choose complementary colours for the text/cell fill colours and discourage them from changing the font style (as it's too much of a time-wasting activity in my opinion - spreadsheets work best using just a standard font like Arial).
By this point, the children have spent five lessons working out how much profit their virtual theme park has made. I don't announce whose park has made the most though straightaway - I prefer to collect in everyone's note-taking sheets and analyse the numbers on them carefully myself to check that they are (or at least appear to be) sensible before letting them know whose profit value is the largest (I had a bad experience with a child cheating a couple of years ago).
Teaching spreadsheets through an exciting context enables children to become keen to develop their skills. Getting them to design a model of a theme park and then calculate the profit it makes is a really fabulous idea - especially when they want to compete with their friends to see who makes the most money!
During these lessons the children have learnt and used a wide range of valuable skills which they will hopefully be able to apply to much more complex problems when they go to secondary school, including how to: enter formuale, replicate formulae/cell values, change cell formats and construct different graphs for different purposes.
The key to teaching spreadsheets is to make them appear fun to do - the children all really loved creating them to calculate the finances of their own virtual theme park and I would certainly recommend it as a great project to do improve their spreadsheet modelling skills at the end of KS2.
Earlier this week I was asked if I could I explain how to go about creating a sreadsheet for designing a model of a theme park in, like I use with Year 6 in ICT.
Depending on the spreadsheet software that you are using, these instructions will likely differ slightly although hopefully the key principles should still all be the same.
I've recorded a 15 minute video tutorial of me making a simple version of the spreadsheet model - if you are unfamiliar with constructing formulae I recommend you view the video in full screen so that you can see everything clearly as it is fairly complicated.
EDIT - Just to be clear, I do not ask children to create the spreadsheet (it is much too complex for KS2) - they just select options from the drop-down box and observe the changes to the money left available to spend. These instructions are provided for people who wish to create a similar activity for their pupils but use a different software package or want to change it somehow (e.g. make the park cover a smaller/larger area).
Having worked out the money each theme park visitor spends in a day and how much it costs to run the theme park for a day, for the fourth lesson I ask the children to use these values to work out how much profit the park makes over a 30 day period if a specified number of people visit each day.
As usual, I get the children to work across the spreadsheet one column at time:
In the first column, the children need enter the number for which day it is. Rather than typing this in manually on each row though, I teach them how to use Edit > Fill Series to automatically input an arithmetic sequence with a step value of 1 down each row.
In the second column I've put in the number of visitors that attend the park each day.
In the third column, the children need to type in the amount of money a theme park visitor spends in a day. They must enter this as a normal number (e.g. 45.5) before then changing the format of the cell to currency (e.g. so it becomes £45.50). This means that when it is filled down (i.e. duplicated) into the cells underneath then this same format is applied automatically.
In the fourth column, the children need to input a formula that multiplies the number of visitors by the amount each spends to calculate the total income received. This can be easily filled down into the cells underneath to quickly see how the money spent changes during the 30 days.
In the fifth column, the children need to type in the amount that the park costs to run each day - again this needs to be changed to currency format before being filled down into the cells underneath. Getting this value off the note-taking sheet I created saves lots of messing around finding and opening up old spreadsheet files here.
In the sixth column, the children need to enter a formula that calculates the theme park's profit (income from visitors - running costs) which can then be filled down into the cells underneath to find out how this changes during the month. A discussion about why any negative numbers that appear here is sometimes needed - this means that the park has made a loss on that day.
Finally, in the bottom cell, the children need to enter an =SUM formula that finds the total of each day's profit/loss to determine whether the park has made a profit or loss at the end of one month of business.
By the end this, most of the children seem to demonstrate that they are becoming fairly confident at: entering formula to solve money problems, change cell formats to currency and replicate cell values/formulae to save lots of repeated typing - this is of course all a good thing!
Next, I like to let them spend a little time improving the appearance of their spreadsheet by altering: cell fill colours, border styles, font colours etc. to make it look more attractive and easier to read, before then asking them to draw a graph of what it shows.
Rather than drawing a pie chart though, this week I ask them to draw a bar chart to graphically represent how the daily profit changes over the 30-day period. This is the most suitable graph because it can clearly show changes over time of discrete data (the daily profit is an end-of-day figure that would be wrongly shown as being a continuously changing figure if shown as a line graph).
When creating a bar chart in Excel, whilst it is fairly straightforward to make following the wizard, you just need to remember to un-tick the box to say that you don't want a legend to be shown (it's unnecessary) and to add suitable titles to both the X axis (which goes across) and the Y axis to describe what they show.
Once created, we then have the usual discussion about what it shows and why it is more useful than just studying a table of numbers. I ask them questions like: What does the scale along the x/y-axis show?When did you make the most/least profit? What was the maximum/minimum profit you made? Can you see and describe any trends in the graph?
To end the lesson, I lastly ask the children to record their first month's profit or loss on their note-taking sheet before then pointing out that we will find out whose park has made the most money overall in the first year of opening next time.
This lesson is fairly similar to the previous one, however this time the children are creating a spreadsheet to calculate how much each visitor spends in their theme park.
Using my pre-prepared template file, I again ask the children to work across each column to enter the required formulae/numbers:
The first column simply lists the different places in the park where visitors can spend money.
The second column requires the children to input how many opportunities each visitor has to spend their money at each type of place. The entrance fee will only be paid once, so the children need to put the number 1 in cell B4. For: cafes, shops and ice cream stands these values can be easily copied from the note-taking sheets each child has.
The third column details the amount of money a visitor will spend at each place. Whilst the amounts for the: cafe, shop and ice cream stand are already there, the children need to input their park entry fee by themselves. To work out their entry fee to charge visitors, I ask them to consider how many attractions they actually have for people to ride on in their theme park. I also ask the children to change the format of these cell values to currency here so that formulae dependent on them (i.e. in the next column) will automatically apply it to their results to save the children the hassle later.
The fourth column displays the total amount of money spent by a visitor at each place - children need to enter a formula into the top cell (D4) to multiply the money spent on the entry fee by the number of opportunities to spend it, before then using fill down to quickly replicate it down for the other facilities in the park.
The final cell (D8) requires the children to find the total of all the money spent at each place in their park to determine how much each visitor spends - whilst they can do this using a long addition, I try to encourage them to use an =SUM() formula. This answer needs to be written down on the note-taking sheet for future reference.
Once the spreadsheet table has been completed, similar to the last lesson, I then ask the children to create a pie chart using the wizard to illustrate how the money spent by each visitor is divided up (shared) between the different places it is spent. The children always seem to do this with growing confidence - I get them to: put a sensible title at the top, display the money spent values next to each segment and also allow them to change the colour of both the text labels and the sectors for effect. I also ask them to consider what the pie chart shows (Which place do visitors spend the most/least money at?) and how it compares to examining a table of data (Which is easier to see how the money spent by visitors is divided up and why - a pie chart or a table?).
Finally, I finish the lesson by letting the children spend a short time improving the appearance of their table by changing: font colours, cell fill colours, border styles as well as using: bold, italic and underline. I obviously highlight to them the benefits of choosing contrasting colours and making titles/header rows stand out. I tend to discourage them from changing the font style/size (except for the title) in spreadsheets as it not only often makes the data much harder to comprehend, but also often means that cell widths/column heights have to be adjusted to show all the cell contents - a job which is much easier said than done!
As this activity is basically just a tweaked version of the previous lesson, it enables the children to recap/practice many of the key skills it taught (e.g. changing cell formats, replicating formulae etc.) whilst also allowing them to develop some independence in their spreadsheet work.
Here's a copy of the instruction sheet I've wrote to accompany this lesson to enable you to let your children work through it independently if they want to:
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 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!
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).
Having introduced basic spreadsheet skills to Year 5 using this series of lessons, when the children move up it into Year 6 some consideration needs to be given as to how these skills are going to then be built upon so as to not repeat stuff which they are already familiar and confident in doing.
A few years ago I had the rather fabulous idea of asking them to design a theme park and to then create various spreadsheets to calculate the profit it makes after a year in business (based on the running costs of the rides in it and the money spent by people visiting it). This worked extremely well as it enabled them to apply their spreadsheet skills in quite a fun (and fairly meaningful) scenario whilst also helping them to appreciate the benefits associated with performing calculations with big numbers using ICT at the same time too - notably speed and accuracy. Since then I've tweaked and refined the project into a nice five week block.
For the first lesson, I ask the children to use a spreadsheet template I've created to design a virtual model of their theme park. They are given half a million pounds to spend on putting various rides and amenities into it to make the best park they can which will hopefully make a high profit.
To inspire them with ideas and get them eager to do the project, I show them this video tour of the Islands of Adventure theme park in Florida.
Having watched it, we then discuss what four elements contribute to making a good theme park experience, notably: rollercoasters, gentle rides, cafes/shops and scenery. This helps them to understand that the best parks contain a mixture of the four to create an exciting experience for visitors to enjoy.
Next, I introduce them to the spreadsheet they will be working in to design their parks. Whilst it is pretty simplistic, the children really do love using it - the park consists of a rectangular-shaped grid, into which they simple choose what to put in which cell where by selecting their required item from a drop-down list. The cost of each item is automatically taken away from their £500, 000 budget and the colour of the cell changes too to help visually highlight what sort of item they have put in.
(The workings of the spreadsheet are fairly complex - it involves conditonal formulae and conditional formatting - so I wouldn't recommend you unprotect sheet unless you really know what you are doing.)
The items available to place in the theme park obviously vary in price but also in the number of grid cells they take up. For example, a roller coaster costs £50,000 and needs to take up 12 squares whereas a smaller 'ride' costs £25,000 and needs to take up just half that space - 6 squares. I display all this information on the IWB for the children to refer to.
To prompt them to work at a good pace (and not waste time noseying at others' designs), I set a time limit for this activity of about 25-30 minutes. I also regularly remind the children about designing both an attractive and appealing theme park during the lesson - basically encouraging them to include a mixture of attractions which are joined up together with paths. As the idea is to see whose park makes the most profit, the children need to decide how they how are going to divide up the spending of their loan up and indeed, how much of it they are going to leave unspent in the bank too!
We discuss ways to edit and improve their parks using the delete button on the keyboard to try out alternative designs and to see the effects that this has on their budgets. I point out how this activity is a good example of a spreadsheet model as it is a simplified representation of a real-life scenario, enabling you to easily change the layout of attractions and instantly observe the effects this has on the spending money available.
Once their theme park design is finished, I then ask the children to view the 'prices' tab (showing how many of each attraction they've bought) and get them to record these numbers onto a quick note-taking sheet I made. This serves two purposes - firstly to act as a backup in case one child accidentally doesn't save their work properly (as happened the first year I did the project!) and secondly to enable them to quickly refer to important data in future lessons without having to open and flick between different Excel files (which they found was a bit of an uncessary nuisance in the past).
The lesson isn't particular difficult to deliver so long as you emphasise all the design requirements clearly enough to the children. What it manages to achieve however is much more important - it not only gets the children all excited about using spreadsheets but also gives them some financial data they've created themselves to manipulate in the forthcoming weeks (personalising their work so they can take greater ownership of it).
Recent Comments