Stretch tasks Modelling, charts and good design

Round 1: Quiz Blitz

1. Line chart Line charts show change over time.
2. Scatter graph A scatter graph plots two measurements against each other to show whether they are related.
3. Bar chart Bar or column charts compare separate categories.
4. False With 12 slices nobody can compare them. Pie charts work with three or four segments at most.
5. A title; Axis labels; A legend when there is more than one series Title, axis labels and a legend tell the reader what they are looking at. Decoration does not.
6. Change an input and see every result that depends on it update A model lets you ask what if questions in seconds.
7. Only the one input cell that holds the coach price In a well built model each input lives in one cell and every formula refers to it.
8. Conditional formatting Conditional formatting changes how a cell looks depending on its value.
9. Data validation Data validation restricts what can be entered, such as whole numbers from 1 to 100.
10. False Put the number in a labelled cell and refer to it. Then it can be seen, checked and changed in one place.
11. Decide the inputs and the outputs → Enter the inputs in labelled cells → Write formulas that use the input cells → Change an input to ask what if Plan, enter the inputs, build the formulas, then use the model.
12. what if What if questions are the reason models exist: change an input and watch the results.

Round 2: Quick Fire

1. • A line chart shows change over time
   • A pie chart shows parts of one whole, which temperatures are not
   Model answer

   A line chart shows how something changes over time, so the rise and fall of temperature is easy to see. A pie chart shows parts of one whole, and temperatures on different days do not add up to a whole.

2. • Inputs grouped together and clearly labelled
   • No numbers hidden inside formulas
   • Currency, percentages and dates formatted properly
   • Conditional formatting to highlight values
   • A frozen header row
   • Data validation
   • Borders and shading to show the structure
   Model answer

   Inputs are grouped together and clearly labelled, and no numbers are hidden inside formulas, so every value can be seen and changed in one place.

3. • It restricts what can be entered into a cell
   • It prevents invalid data and the errors it would cause
   Model answer

   Data validation restricts what can be entered into a cell, such as only whole numbers from 1 to 100. It prevents invalid data being typed in, and the errors that would follow.

4. • An input, such as the price of a cake or the cost of ingredients
   • An output, such as the profit or the total takings
   Model answer

   An input is the price of each cake. An output is the profit.

Round 3: Big Think

1. • Inputs such as the coach cost, entry fees and number of students
   • Formulas work out the total cost and the cost per student
   • Inputs can be changed to ask what if
   • Every result updates instantly
   • It shows whether a price would cover the costs or make a loss
   Model answer

   The model would hold inputs such as the coach cost, the entry fees and the number of students. Formulas would work out the total cost and divide it by the number of students to give the cost per student. Staff could then change an input, such as fewer students signing up, and ask what if, with every result updating instantly. That shows straight away whether a particular price would cover the costs or leave the school making a loss.

2. • Formulas can apply a growth rate year after year
   • Rates can be changed to compare different what if scenarios
   • A chart can show the trend
   • Results recalculate quickly and automatically
   • A model is only as good as its data and assumptions
   • Real events such as disease, disasters or migration cannot be predicted
   • A model is simplified, so some factors are left out
   • A reasoned conclusion
   Model answer

   A spreadsheet model is useful because formulas can apply a growth rate to the population each year, and the results recalculate automatically. Planners can change the birth or death rate to compare different what if scenarios, and a line chart can show the trend clearly. However, a model is only as good as the data and assumptions put into it, and it is a simplified version of reality, so many factors are left out. Real events such as disease, a natural disaster or people moving to or from the island cannot be predicted. Overall, the model is a useful guide to likely trends, but its predictions become less reliable the further ahead they go.

This or That

1. Pie chart A pie chart shows parts of one whole, as long as there are only a few slices.
2. Together at the top, clearly labelled Inputs kept in one labelled place are easy to find and change. Numbers buried in formulas get missed.
3. Nobody can tell what it shows A chart without a title and axis labels communicates nothing.
4. Freezing the header row Freezing the header row keeps it visible however far down you scroll.
5. A legend A legend tells the reader which colour belongs to which series.
6. Bar chart Bar charts compare separate categories. Scatter graphs show whether two things are related.
7. As 25% Format percentages, currency and dates properly so that they are easy to read.
8. To make its structure easy to see Good layout makes the structure visible. It does not change how fast anything is calculated.

Memory Match

• A 4 Bar chart: Comparing separate categories
• B 1 Line graph: Showing change over time
• C 6 Pie chart: Parts of one whole, with only a few categories
• D 5 Scatter graph: Whether two things are related
• E 3 Conditional formatting: Highlights important values automatically
• F 2 Data validation: Restricts what can be typed into a cell