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.