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**.

Download 4. Profit and Loss in Month1 Spreadsheet

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 a*cross*) 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.