Document Sample

```					          Unit 8.4   Brimsham Green School
ICT Department

Models and presenting numeric data

Name

Form

ICT Teacher
Lesson 1: Revising modelling                                   Date_____________
Objective                What I am looking for                     I can
   Name the different             Name the features of a       I can identify cells, rows,
features of a                    spreadsheet                    columns, worksheets, cell
   Use a spreadsheet model          calculate the cost of a        and text labels in a
to calculate costs               school fete                    spreadsheet

   Change the variables in a      Change variables in a          Use the spreadsheet to
spreadsheet model                model                          find the cost of a school
   Use the ‘goal seek’ tool to    Use ‘goal seek’                 fete
solve problems                 Name the features of a         Change variables within a
   Recognise the features           good model                     spreadsheet
of a good model                State that a model is a
 Use goal seek
   Recognise that a model is        computer representation
used to represent a real         of a real situation
 State that a good model
life situation
should be easy to use, be
accurate, simulate a real
situation and allow you to

Starter: Revising modelling

In this unit we are going to build on the work you did last year

To start refresh you memory by completing the worksheet on
the next page.

2
Look at the screen shot of a zoo model above and answer these questions:

1. How much does it cost to feed the lions?

2. How much is spent in total each week?

3. Is the zoo overspending or under spending?

This model is created with a spreadsheet. Label these parts of a spreadsheet on the
screenshot above.

cell                 column                   row
label              numeric data                rule
save button       Graph Wizard button           Variable

Are spreadsheets always the best tools? Look at the chart below and tick the boxes
for each activity.

Spreadsheets are the best tool to help: True          False              Not sure
Draw pictures

Store numeric data

Draw charts and
graphs
Write reports

Make calculations

3
School fete
The file 8.4P1c School fete.xls is a financial model. It models the amount
of money that could be made at the annual school fete.

It includes ways of making money (income) at the fete, such as selling
teas and cakes. It also shows the costs (expenditure) of running the fete.
The model calculates the profit or loss being made when different
numbers of people visit the fete.
The model calculates the total profit/loss by using this formula:

Profit or loss = income – expenditure

The amount of money taken, and any overall profit, will depend on the
number of people coming to the fete and spending money. The more
people that come, the more profit will be made.

1   Which cell calculates the total
income?
2   Which cell calculates the total
expenditure?
3   Which cell calculates the
profit/loss?
4   What is the formula in this cell?

6   Change the amount of people coming to the fete to see how much
money will be made. Fill in the amounts in the table below

Estimated number of people         Projected profit or loss

100

200

300

400

4
1b What if?

Once a model has been constructed you can ask ‘What if…?’ questions of it by
changing some of the variables.

Try these questions.

8      What if each person buys 2 teas or coffees
rather than 1 tea or coffee? How much
profit will be made if 400 people attend?

9 What if the caretaker charges double and
the raffle prizes cost only £100? Will there
still be a profit if only 125 people attend?

10 If not, how many people would be needed
to make any profit?

11 The organisers think it might rain on the
day of the fete. They want to know the
lowest number of visitors who can attend
if they are not to lose any money. This
means the profit or loss is £0.

By looking at the figures in the table above,
estimate the number of people required
to attend.

12 Now use ‘trial and improvement’ to obtain
an exact value for the number of people
required. Keep changing B3 until you get
as close to £0 profit or loss as you can.

5
Goal Seek… help sheet

1   Open the school fete model in shared files.

We are going to find out how many people would need to come to fete in order
for us to make a profit of £5000.

2   Go to TOOLS then Select the GOAL SEEK.

3.    Now you need to enter the cell reference that you want to change them
amount in so that
will be the profit
cell D29.

Then you need to
set the target value
as £5000

Finally you need to
say which cell you
want to change,
which will be the
number of people
who attend.

4   Click on OK.

This will change the number of people that will need to attend to
513.307692307692. However you will need to round this number up to 514 as you
cant have part of a person!

6
Now use Goal seek to answer these questions:

1   What if you want a profit of £3000 and
only 350 people attend? How much should
you charge for the entrance fee?

2   What if you charge £0.50 for the cakes
and £1.50 for hot dogs or burgers? How
many people do you now need if you want
to break even?

3   What if each person buys 6 rides and 4
raffle tickets? How many people do you
now need if you want to make a profit
of £3500?

4   Write two ‘What if…?’ questions of your
them. Make sure you have worked out the

7
Lesson 1 Homework                    Date due _____________

In the next lesson, you will create a model based on mobile phones.

Collect information on the costs of running two types of mobile phone.
Identify similarities and differences between the packages.

Phone 1                Phone 2

Phone name

Cost of phone

Contract costs

Call charges

Other costs

8
Lesson 2: Developing a new model                                     Date __________

Objective                What I am looking for                       I can
   Identify variables in a          List some of the            List some of the variables
model                             variables needed in a          needed in a model of phone
   Use a spreadsheet model           model of phone tariffs         tariffs
to solve problems                Work out the best mobile      Use the spreadsheet model
   Construct parts of a              phone tariff using a           to find out which mobile
computer model using a            spreadsheet                    phone tariff is the best
   Use the ‘goal seek’ tool to       a spreadsheet model
 Add a formula to a model
solve problems                 Use ‘goal seek’                  to calculate the costs of
mobile phone tariffs

‘goal seek’

Mobile Phone Worksheet
Background
The spreadsheet shows the start of a mobile phone model that you are
going to develop. The estimated number of minutes used per month can be
entered in cell B5 as shown.

1       Ensure you have file 8.4P2b Mobyphone 1.xls loaded (as shown).

2       Change the number of minutes in cell B5. What happens to the
values in column F under the ‘minutes used’ heading? Why does
this happen?

9
3   What do cells E11 to E16 contain?

4   You need to create a formula in G11 that calculates the cost of
extra minutes used for the Talk 15 tariff. Work out what the
formula needs to calculate. Write it in words and then as a

5   Now enter this formula in G11. Remember the = sign at the start.

6   Copy your formula into G12 to G16. You could use the fill handle.
Check that the formula has been copied and that the cell
references have changed for each of the rows.

7   Add a formula in cell H10 to calculate the total cost per month
for the Talk 15 tariff. Check if it appears to be correct. Copy
the formula down into cells H11 to H16.

8   If you use 400 minutes per month, which tariff do you think
gives the best value? How much would you pay?

10
9    Use your model to complete the following table to test out your
prediction.

Estimated number of        Best-value tariff

minutes

100

200

300

400                        Talk 60

500

10   You have a budget of £20 per month. How many minutes would
this buy on the following plans? You could use Goal Seek… to help
you find out.

Tariff                       Number of minutes for

£20

Talk 15

Talk 30

Pay As You Go

Talk 60

11
Lesson 2 Homework                Date due _____________

What are the most important factors for you to think about when you
choose a phone?

12
Lesson 3: Using graphs                               Date ________________

Objective              What I am looking for                   I can
   Interpret data from         Use a bar chart to answer    Use a bar chart to
charts and graphs            questions                     answer questions
   Use absolute cell           State how graphs can be      Use absolute cell
referencing                  used to make comparisons      referencing in a model
   Review a computer model     Use absolute cell
 Say why some charts are
   Evaluate graphs and          referencing in a model
easier to use to make
charts                      Discuss the usefulness of     comparisons than table
   Discuss the advantages       a computer model
and disadvantages of                                      Describe how a computer
using ICT to draw graphs                                   model can be useful
and charts

Starter: Using charts
Teacher will show you a chart look at it and answer the questions below:

1. Write down three pieces of information that this graph
tells you.
a.

b.

c.

2. Which tariff would you prefer?

13
Background
The questions and tasks below focus on how the model can be extended
by using absolute cell references.

Load the file 8.4P3c Mobyphone 2 extended model.xls.

1 The model has been reorganised slightly so that you can investigate
other ways of analysing the data. What formula do you need to create
in F11? Try not to use 100 in the formula but make F10 an absolute
cell reference.

You can click on the red dots in cells F11 and G11 to check you are on
the right track.

2 Enter similar formulae for G11, H11, I11, J11 and K11. Copy each
formula down through the columns. Check that the absolute cell
references have been copied correctly.

3 Create a line graph that shows all of the tariffs at once for easy

• Select B10–B16 then F10–K16 so that minute headings are included.
• Select a suitable Chart Type for line graph – suggest first one.
(Chart Wizard Step 1)

14
• Select rows rather than columns in Chart Source Data for correct
data.                              (Chart Wizard Step 2)
• Remove the gridlines for easier viewing – Chart Options – Gridlines
tab.                               (Chart Wizard Step 3)
• Add a suitable title and label the axes – Chart Options – Titles tab.
(Chart Wizard Step 3)

4     Print out the chart and use it to answer these questions.

a   Which tariff offers the best value for
money if you use 200 minutes’ worth
of calls per month?
b   Which tariff offers the best value for
money if you use 450 minutes’ worth
of calls per month?
c   What is the best value if you can spend
£30.00 per month?

d   What is the best value if you can spend
£25.00 per month?

5     There are certain points on the graph where it is difficult to see
clearly. For example, look closely at the area around 200–300
minutes. Change the values in row 10 to 200, 220, 240, 260, 280,
and 300 to graph this area more clearly. Now redraw the graph.

You will need to rescale the y-axis. To do this:

•       double-click anywhere on the y-axis of the graph;

•       select the scale tab and change the minimum value from 0 to

20.

What does the graph tell you now?

15
What is the best value if you can afford
£24.00 per month?

6   Use your model to explore other areas in detail, for example, if you
use between 300 and 400 minutes each month.

16
Lesson 3 Homework                     Date due _____________

Decisions should be made by taking into account a wide range of
variables.

In today’s lesson, the only variable that was looked at was the cost, or
tariff.

If you were going to buy a mobile phone yourself, what other variables
would you take into account?

Hint: They don’t all have to be about cost!

17
Lesson 4: Investigating models                                   Date ________

Objective         What I am looking for                     I can
   Use a random model       Use the RANDBETWEEN          Use the
   Use an online model       function in a spreadsheet      RANDBETWEEN
   Review a model           Answer questions using         function in a
   Describe what a
generate random data
simulation is            Say what a simulation is
an online model

 Say what a simulation is

Starter: Tossing a coin
1. If you toss a coin 10 times will you get more heads than tails?
2. Do you think you would get the same result every time?

results of tossing a coin 10 times.
As you would expect these results change every time. In order to
simulate this a function called RANDBETWEEN has been used. This tells
the system to insert either a 1 or 2 at random and changes every time
F9 is pressed by using the formula =RANDBETWEEN(1,2)

18
Using RANDBETWEEN on your Mobile phone model.
As you will have discussed with your teacher people don’t always use the
same amount of minutes every month. Now we are now going to use the
RANDBETWEEN function to make the number of minutes used a random
number between 300 and 400.
1. Below write the formula you will need to enter:

2. Now enter the formula in the cell you normally enter the number of
minute into and press F9 to check it works.

3. Now you have made the number of minutes random you need to test
the model several times to see which tariff is best overall. Test the
model 20 times and keep a record of which tariff is best on the tally
chart below:

Tariff               Tally                                              Total
Talk 15
Talk 30
Talk 45
Talk 60
Timeframe Talk
Pay As you Go

4. Which tariff will be best overall?

Extension: To make it easier to read the model results create an appropriate graph
on a separate worksheet. Now change the Random numbers so that they are between
100 and 200 and test the model again using the graph to get the answers keep a tally
of the results.
Which Tariff is best overall if the minutes are between 100 and 200?

19
Using an online model

1. Open up ‘mobile phone links.doc’ from student shared files – ICT –
Year 9 – 8.4 and click on the link to betterdeal. Or load the web
browser and enter:
http://www.betterdeal.co.uk/personal.html

This is an online model to help people decide
on the best-value mobile phone tariff. To
begin using it click on this link:

The next screen will introduce to their service and explain how they can
save you money.

At the bottom of the
screen it will ask you if
you have you last
phone bill say NO and
then click on continue.

2. Fill in all the boxes but make sure the number of minutes used is
300.
Where it asks for the email address just type in any letters followed
by @hotmail.com e.g. jfiojfj@hotmail.com

3. The model will return a list of the best-value tariffs.

Which tariff came out the best value for you?

20
4.     Evaluate using the online model:
a. Did you find it easy to enter the information into the online model?

b. Did you find it easy to understand the results?

c. How could you improve the online model?

5. Compare the model you have been making with the online model:
a. What did you think worked better than your model?

b. What do you think worked worse than your model?

c. What aspects of the online model do you think you could use to
improve your model and how could you put them in?

Use the other side of the page if needed.

21
Lesson 4 Homework             Date Due _____________

What do you understand by the word simulation? You need to
mention some examples with your description.

Simulation

22
Lesson 5                                            Date ________________
Simulations and models
Objective               What I am looking for                     I can
   Define a simulation and a      Give a definition of a        Say what a simulation is
model                           model
   Explore a range of             Give a definition of a
 Say what a model is
simulations and models          simulation.
   Recognise the                  Explain how a model and
differences between a                                       Identify what makes a
simulation are different
simulation and a model                                         good model
   Explain the good and bad
   Review models and               features of a range of
simulations                     features of a range of
models

Using a model
1      Click on one of the group A websites from shared – ICT – Year 9
– 8.4 – ‘8.4P5a Weblinks and programs.doc’ or type in an address
from below

http://www.thetrainline.com/

http://www.flightline.co.uk/villas/villas.php

Brief for flightline website
I am a parent travelling alone with two children. I want to find a villa in
the Costa Blanca, I would like a pool, depending on how much the villa
costs. I would like to know how many choices I have if I only have £100
to spend for a week.
Number of choices:

2     What do you think will happen if you change some of the variables?
List some of the variables you could change and try them to see how you
can increase the number of choices.

23
3     Brief for the trainline.com website
Use the quick timetable on the left-hand side of the screen to begin
with:

I need to get from __________ to ___________ and back on ______
Please check the times for me. (You don’t need to write them down.)

There are some other variables you need to include.
I have a disabled child railcard and don’t want to change trains. I want
to travel during the day.
What is the fastest and cheapest journey I can make?

What if I decide that I don’t mind changing trains? (Before you check
this on the website, can you predict what might happen?)

Now check to see if your hypothesis (prediction) was correct.

Investigating a simulation
Load one of the group B programs from: Shared – ICT – Year 9 – 8.4 –
Make a note of the one you have chosen.

Play the game and find the answers to these questions.
1     What is being simulated?

2    List up to three variables.

24
3   Choose one variable and describe how it affects the outcome.

4   Change one of the variables. Can you predict the outcome?
Describe what it will be and then check if you were right.

25
Lesson 5 Homework                    Date due _____________
Write a brief account of the work you have done in this unit. Focus on
what you have learned about using models.

26
OVERALL UNIT ASSESSMENT
Below is a self-evaluation, write in each box if you think you have met the level and
how. Don’t copy your neighbour and be honest what ever you say is fine.
Level                         How you have met it         Teachers Comments
3 – I can use models and
simulations to find things
out or solve problems.
3 – I can describe how
models can be used in and
out of school.
4 – I can create models and
present information so that
they are appropriate for
the audience.
4 – I can use models and
simulations to explore
patterns in results
4 – I can predict results
based on what variables I
use.
5 – I have independently
investigated a model
changing variables to find
different results.
5 – I can evaluate my own
work and assess where
improvements could be
6 – I have evaluated and
refined my model to make
it easy to use with a clear
sense of audience and
purpose.
6 – I have evaluated
different models and
assessed their
effectiveness.
Comments: Did you enjoy it? What were you good at? What else do you need to learn?

27
Teacher’s assessment:
Quality of modelling (1 – 5)
(1 = Excellent, 2 = Good, 3 = Satisfactory, 4 = Unsatisfactory, 5 = Poor

Effort (1 – 5)
(1 = Excellent, 2 = Good, 3 = Satisfactory, 4 = Unsatisfactory, 5 =
Poor)

NC Level for Unit
(Years 8 pupils should be in the range 4 – 6)

Teacher’s Signature _________________________________

28

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 16 posted: 1/6/2011 language: English pages: 28
Description: Best Value Formula document sample
How are you planning on using Docstoc?