Transcript Fit 4 Sport Ski Trip Spreadsheet

*
*
*Southlands are arranging a ski/snow boarding trip for
the pupils and staff.
*You have been asked to create the spreadsheet to
work out how much the trip will cost and what the
group discount will be.
*You need to start by typing up the information into a
spreadsheet, format it using borders, a little
shading/colour, different font style/bold etc
*You’ll then add to and change parts of the
spreadsheet to create a more complex financial
model.
*It must look professional as it is for adults!
*
*
*By the end of today’s lesson , you will be
able to:
*format numerical data and other
information to produce a well designed
spreadsheet
*use formulae, functions and variables to
carry out calculations and produce an
accurate and useful financial
spreadsheet
*
http://www.what2learn.com/ict-unit-8-4spreadsheet-modelling-starter-plenary/
*
* Everyone will:
* Create a very simple business spreadsheet from scratch
* Sufficient row/column headings
* Some formatting eg shading, gridlines, font size
* Most of you will:
* Create an effective business spreadsheet displaying accurate figures
It will contain a title and appropriate row/column headings.
* Use a range of formatting to improve the appearance of the
spreadsheet
* Some of you will:
* Create an effective business spreadsheet displaying accurate figures
from scratch with appropriate title/row/column headings.
* Use a range of formatting to improve professional appearance of
spreadsheet
*
* From Moodle, go to ICT & Computing>Year 9>Ski Trip project>Ski
Trip Spreadsheet>open and save into your area the Ski Trip
Spreadsheet template
* Also from Moodle, open and save into your area the Ski Trip
Spreadsheet report
* Finally from Moodle, open the Spreadsheet tasks
*Start working through task 1 parts A to E making sure
you are putting print screens and evidence into the
report to:
* Format your spreadsheet
* Use Formula to calculate costs, discount and final cost for
adults and for children
* Use functions to calculate the most expensive and the least
expensive of the trips, and the average cost.
* Print and annotate your first spreadsheet
*
*
*By the end of today’s lesson , you will be
able to:
*use formulae, functions and variables to
carry out calculations and produce an
accurate and useful financial
spreadsheet
*
http://www.bbc.co.uk/bitesize/quiz/q36368506
*
*Everyone will:
*Use at least 2 different calculations (formula or functions)
*Functions/formulas may not be efficient, but will work.
*Most of you will:
*Use at least 2 different arithmetic operators eg + or – or * or
/
*Use at least 1 function eg average, SUM, MIN, MAX
*Some of you will:
*Several different formulas will be used efficiently including
at least 2 different arithmetic operators eg + or – or * or /
*Use at least 2 functions eg average, SUM, MIN, MAX
*
* From Moodle, go to ICT & Computing>Year 9>Ski Trip project>Ski
Trip Spreadsheet>open and save into your area the Ski Trip
Spreadsheet template
* Also from Moodle, open and save into your area the Ski Trip
Spreadsheet report
* Finally from Moodle, open the Spreadsheet tasks
*Continue working through task 1 parts A to E making
sure you are putting print screens and evidence into the
report to:
* Format your spreadsheet
* Use Formula to calculate costs, discount and final cost for
adults and for children
* Use functions to calculate the most expensive and the least
expensive of the trips, and the average cost.
* Print and annotate your first spreadsheet
*
*
*By the end of today’s lesson , you will be able
to:
*adapt and enhance a spreadsheet model
*gather data for use in a model
*ask ‘what if’ questions, model different
scenarios, try out alternatives and explore
ideas
*interpret results and make recommendations
based upon them.
*
http://www.bbc.co.uk/bitesize/quiz/q23
766294
http://www.bbc.co.uk/schools/gcsebitesize/ict/modelling/
0spreadsheetsrev1.shtml
http://www.bbc.co.uk/schools/gcsebitesize/ict/modelli
ng/0spreadsheets_act.shtml
*
* Everyone will:
* Show evidence of modelling at least 2 different financial scenarios by
changing data
* Show the results of these changes
* Most of you will:
* Show evidence of modelling at least 2 different financial scenarios by
changing data
* Give an explanation of the results of these changes
* Some of you will:
* Evidence of modelling at least 3 different financial scenarios by changing
data
* Give a detailed explanation of the results of these changes
*
*From Moodle, go to ICT & Computing>Year 9>Ski Trip
project>Ski Trip Spreadsheet>open and save into your area
the Ski Trip Spreadsheet template
*Also from Moodle, open and save into your area the Ski Trip
Spreadsheet report
*Finally from Moodle, open the Spreadsheet tasks
*Complete task 2 all parts making sure you are
putting print screens and evidence into the report
*
1. Open Ski trip spreadsheet
2. Open word document with tasks in it
3. You will need evidence of Task A-E you need to add
this to your spreadsheet report from last lesson.
4. Save your spreadsheet and report ready for the next
set of tasks.
*
1. Open your Ski trip spreadsheet
2. Open the Spreadsheet tasks from Moodle or your area.
3. You will also need evidence of Task F-H –so you need to
add this to your spreadsheet report in the right places
from last lesson.
4. Complete task F Credit card charge & show evidence
with clear explanation of what you did and how it has
affected total cost.
5. Complete task G Set a budget & explain what you have
done.
6. Complete task H Free Places & explain what you have
done.
7. Print Spreadsheet report
8. Print final finished version of spreadsheet AND formula
with gridlines/column & row headings.
*
*
*
*By the end of today’s lesson , you will be
able to:
*Create informative graphs and charts to
present data effectively
*
* We’ve all made, used and looked at bar charts, line charts,
pie charts before, but how about info graphics?
* Information graphics or info graphics are graphic visual
representations of information, data or knowledge which
present complex information quickly and clearly.
* They can improve our understanding of data because they are
so nice to look at, easy to ‘read’ and it’s more straightforward
to see patterns and trends in the data.
* Info graphics can be created by hand using simple everyday
tools such as paper and pencil, but they are quicker and
easier to produce on the computer using graphics software or
specialised websites and tools. Eg - http://piktochart.com/
* Let’s have a look at some……..
*
*
* You are going to create some (more basic!) graphs and charts. If
we get time, we might think about how to represent the data as
an infographic!
* From Moodle, go to ICT & Computing>Year 9>Ski Trip project>Ski
Trip Spreadsheet>open Graphs tasks
* Create graphs for the 4 different tables, you may end up making 5
graphs as one table might need 2 different graphs.
* Peer review – swap places and look at your friend’s graphs. How
useful are they? Do they have a title? Axes labels? Sensible choice
of colours? Give feedback on the design and usefulness. You could
type this feedback straight into the spreadsheet next to the table
used to create the graphs.
* Return to your own computer and have a look at the feedback – do
you need to make any improvements? Make these improvements.
* Check with your teacher what you need to print out but make sure
your name is on anything you print!
*
http://www.hoodie
hut.co.uk/skihoodies-__
*
*By the end of today’s lesson , you
will be able to:
*create an original image to represent
your trip showing a good awareness of
audience and purpose and appropriate
use of vector graphics tools
*respect copyright
*
1.
2.
Copyright violation is a crime
If a photo does not have a copyright notice on it, it is not
copyrighted—so I can use it freely.
3. You don’t need permission, if you’re just using part of the image
and cropping it
4. Google is a great place to get free images
5. If I make up my own stories, but base them on another work, my
new work belongs to me.
6. Copyright infringement is not a problem if it is for educational
use.
7. I got the photo off the Internet, the owner’s own website,
Facebook, a ‘public’ website, , or whatever . . . so it is in the
'Public Domain and I am free to use it'.”
8. If I am not making money off the photos, I am not violating
copyright.
9. The creator owns copyright in his work at the moment it is
created and put in tangible form.
10.I can simply mail myself a copy of my work to prove copyright.
*
1.
2.
Copyright violation is a crime TRUE
If a photo does not have a copyright notice on it, it is not
copyrighted—so I can use it freely. FALSE
3. You don’t need permission, if you’re just using part of the image and
cropping it. FALSE
4. Google is a great place to get free images. FREE IN COST BUT NOT
FREE FROM COPYRIGHT
5. If I make up my own stories, but base them on another work, my new
work belongs to me. FALSE
6. Copyright infringement is not a problem if it is for educational use.
FALSE
7. I got the photo off the Internet, the owner’s own website, Facebook,
a ‘public’ website, , or whatever . . . so it is in the 'Public Domain
and I am free to use it'.” FALSE
8. If I am not making money off the photos, I am not violating
copyright. FALSE
9. The creator owns copyright in his work at the moment it is created
and put in tangible form. TRUE
10. I can simply mail myself a copy of my work to prove copyright. FALSE
(SEE 9)
*
You are going to create a logo to be used on all the
documents for your ski trip. It must:
*be created using only vector tools
*work in different sizes on-screen and in print
*be appropriate for the ski trip and reflect the
snow/ski/board/mountain etc
*be appropriate for the range of ages going on the trip
*
* Fill in your name on the Logo Feedback From and leave it in your place
with your logo open on screen.
* Swap places with your ‘test buddy’ and have a good look at their logo.
* Fill in the feedback form with your thoughts & suggestions on ways they
could improve it.
* Try to be positive and useful in your comments! Think about the sort of
thing you’d like someone to say to you and which would be helpful!
* Return to your place and read the comments from your test buddy.
* In a text box alongside your first version, type up any feedback comments
and improvements that you or your test buddy feel you could make.
* Copy and paste the logo onto the same page and make any changes you
think would improve it so that the 2 versions can be compared.
* Annotate this final version with your design ideas, own evaluation, and
any final thoughts on its suitability for audience and purpose
* Print your final version for marking. Could copy it onto a blank hoody
template if you have time!
*
*
*
* On your white boards, list 5 organisations that would use a
computer database to store information about you.
* For one of these, write down the fields (headings) you think
they would need to have in the database.
* Rally Robin – in pairs, take it in turns to list as many
organisations as you can think of which would have a
database.
*
* All the people signed up for the ski trip have been entered
into a database. There are 2 tables:
* one with personal information
* the other with information about the bookings and requirements
for the trip.
* In both tables, there is a field called ContractID which is the
reference number for their holiday booking.
* If all the information was in just one big table, this
would be
called a flat file database, but they have been separated so
that some data can be kept confidential.
* At the moment these 2 tables aren’t linked but they need to
be for the rest of the work we need to do. We can link them
using the ContractID as the primary key.
*
* A relational database has more than one table and the tables are linked using
key fields or primary keys. For example, a library database could have three
tables:
* customer - when a customer joins the library a record is created. It stores their
details such as their first name and surname and includes a unique Customer ID.
* book - each book in the library has a record. It stores details about the book,
such as the author and title and includes a unique book ID.
* lending - when a customer borrows a book, the lending table stores the
customer's unique ID and the book's unique ID in a record. The record could also
include additional information such as when the book was borrowed and when it's
due back.
* The customer and book ID are both examples of key or primary fields.
* Advantages
* The book's details and the customer's details need only be entered into the
database once.
* Because of this, mistakes are less likely to happen and if there were a mistake in
a customer's record, for example, correcting it will correct the mistake
database-wide.
* Duplication is avoided - this keeps the database's file size down.
* Details about books and customers are easily accessible using their unique IDs.
* Queries can be performed and reports generated, eg a list of books a customer
has borrowed since joining the library.
*
* Validation and verification are two ways to check that the
data entered into a computer is correct. Data entered
incorrectly is of little use.
* Validation is an automatic computer check to ensure that the
data entered is sensible and reasonable. It does not check
the accuracy of data.
* For example, a secondary school student is likely to be
aged between 11 and 16. The computer can be
programmed only to accept numbers between 11 and 16.
This is a range check.
* However, this does not guarantee that the number typed in is
correct. For example, a student's age might be 14, but if 11 is
entered it will be valid but incorrect.
*
* There are a number of validation types that can be used to check the data that
is being entered.
* Check digit - the last one or two digits in a code are used to check the other
digits are correct bar code readers in supermarkets use check digits
* Format check - checks the data is in the right format a National Insurance
number is in the form LL 99 99 99 L where L is any letter and 9 is any number
* Length check - checks the data isn't too short or too long a password which
needs to be six letters long
* Lookup table - looks up acceptable values in a table there are only seven
possible days of the week
* Presence check - checks that data has been entered into a field in most
databases a key field cannot be left blank
* Range check - checks that a value falls within the specified range number of
hours worked must be less than 50 and more than 0
* Spell check - looks up words in a dictionary when word processing
*
*Verification is performed to ensure that the data
entered exactly matches the original source.
*There are two main methods of verification:
*Double entry - entering the data twice and comparing
the two copies. A good example of this is when you are
setting a new password and you have to enter it twice
to check you’ve put the same thing and therefore
know what you want the new password to be!
*Proofreading data - this method involves someone
checking the data entered against the original
document.
*
*
*By the end of today’s lesson , you will be
able to:
*Create a well-designed data entry form
to allow easy, accurate data entry into
the database
*
* On mini white boards, sketch out
database table
a data entry form for this
*
* Before setting up a database the data must be collected. This can
be done using a data capture form.
* Data capture forms often use boxes or a set amount of spaces and
occasionally provide examples too. This is to make sure each field is
completed correctly.
* Data is often entered as a code in a database, for example in our
database, we just need people to say Yes or No (or tick or not tick)
if they want insurance or to hire something. Codes like this are used
because they:
* are quicker to type in
* use less disk space
* are easy to validate (check)
* make searching the database easier as data is entered in a standard
format eg people don’t write something like “maybe/sometimes/not
sure” if they are asked “do you like Winter?”
*
* Create a data entry form so that more records can be added when
people sign up for the trip.
* It must include the logo and a sensible title, make sure it includes
all the fields, Extension - include features to help the user enter
valid data eg drop down, include a key for coded fields
* Update your database report - Print screen and explain the
design of your first version of the data entry form.
* Peer review – swap places and enter data into your friend’s
database to test the form and give feedback on the form’s design.
You could type this feedback straight into your friend’s report.
* Improvements – make some improvements to your form to make it
easier to use, based on your friend's feedback.
* Update your report - Print screen and explain the final design,
and explain how testing and feedback was used to improve it,
*
*
*By the end of today’s lesson , you
will be able to:
*Enter and edit records using the data entry form
and by editing your table
*Understand the
relationship between the
database tables and the data entry form in the
database
*
On your white boards, write down why the following
organisations might need to make a change to their
database:
1. School
2. Vet
3. Doctor
4. Library
5. A charity
6. Amazon or other online shopping website
7. ITunes or other online music service
*
* Open your Ski trip database and log into Moodle.
* Make sure you have the Database Report document open too,
you should already have it from an earlier lesson – you will
need to take print screens to evidence your work today.
* From Moodle, go to ICT & Computing>Year 9>Ski Trip
project>Ski Trip Database>open Database tasks
* Complete the tasks: add, edit and delete entries in both
tables. Keep your Database Report updated with print screens
as evidence.
* Create address labels to send out the information about the
trip to people. From Moodle, go to ICT & Computing>Year
9>Ski Trip project>Ski Trip Database>open Address Labels and
follow instructions.
* Print for marking.
*
*
*By the end of today’s lesson , you
will be able to:
*search for information for a specified purpose
*sort records using single and multiple fields in
ascending and descending order
*understand the use of “logical operators” in
framing database queries
*
* On your whiteboard, draw a grid 3 by 3 and put some key words from
databases into it. Some examples are given below.
* Now your teacher will be the Bingo caller (something they’ve always
wanted to be!) and call out some key words.
* Cross them out if they appear in your grid. The winner is the first
with a full house!
*
* You should remember from Y8 that when you want to look for specific data in a
database it is called a query.
* When we look on the School SIMs database for your timetable, we search for you
by first or surname. Every web search you do on Google is a query because you are
typing in your search terms eg ski resorts Italy and it generates a list of results.
* A query can look at more than one field, and can be sorted into order. A database
may have millions of entries, but a query can narrow this down to just a few.
* You can also use more complex search terms to find data, these are sometimes
called Boolean.
* Here are some terms used in query language with examples for a car database:
* AND - use to find all records where the make is a Ford AND the price is less than
8000.
* OR - use to find all records where the make is a Ford OR Rover.
* NOT - use to find all records where the make is NOT Citroen.
* In query language, the above examples would look similar to this:
* [Make] = "Ford" AND [Price] < 8000
* [Make] = "Ford" OR [Make] = "Rover"
* [Make] = NOT "Citroen"
*
* Open your Ski trip database and log into Moodle.
* Make sure you have the a Word document open too, put the
title Database Queries
* From Moodle, go to ICT & Computing>Year 9>Ski Trip
project>Ski Trip Database>open Ski Trip Database queries
* Setup and run the queries. Keep your Word document updated
with print screens as evidence, read the top of the worksheet
to find out what you need to print screen!
* Print for marking.
*
*
*By the end of today’s lesson , you
will be able to:
*use reports to present information
clearly and apply editing,
formatting and layout techniques to
maximise clarity and sure suitability
fit for purpose and audience
*
*Look at the following print screens of
people’s reports from various Booking
Information queries.
*Reports need to present the data clearly
and fully, thinking about how easy the
layout and design is for the person
reading it.
*Note down on your white board 2 ways
they could be improved, and 1 thing that
you think they’ve done quite well.
*
*
*
* Open your Ski trip database
* Following your teacher’s instructions/ demonstration, create reports
for each of the queries you ran last lesson.
* Use the Design View to edit them to improve the layout, formatting,
and making sure that all data can be read in full.
* In the title, Each report must:
* present all information from the appropriate records, without cutting
any of it off
* include your logo and a meaningful title including put your name so
that if you print them, it is easy to know whose is whose!
* include an introduction clearly explaining what the report shows
* leave out unnecessary information
* be suitable for an adult audience at the appropriate business eg ski
hire
* Print at least 2 for marking (or print screen all and fit over 2 pages in
a Word document), depending on what your teacher tells you!
*
*
*By the end of today’s lesson , you
will be able to:
*Combine Word and Access to
produce mail merged letters
*
* The 1998 Data Protection Act (DPA) was passed by Parliament to control the
way information is handled and to give legal rights to people who have
information stored about them.
* It is a law designed to protect personal data stored on computers or in an
organised paper filing system. With more and more organisations using
computers to store and process personal information there was a danger the
information could be misused or get into the wrong hands.
* Most businesses have to register as users of personal data and our data is
then covered by the DPA. There are exemptions: mainly those who use data
for crime prevention, national security, tax collection, or mental health
reasons.
* The basic way it works is by setting up rules that people have to follow. It
does not stop companies storing information about people. It just makes
them follow rules and helps keep our data secure and confidential.
* If someone who is not entitled to see these details can obtain access without
permission it is unauthorised access.
* This can result in fines or prosecution.
*
*The Act has 8 principles, or rules. These have got
muddled up. On your white boards, match up the start
and end of the rules correctly.
* Data must be:
*1.processed fairly
*2.used for the purpose
*3. to purpose collected for
*4.kept accurate
*5.kept no longer
*6.processed within the
*7.kept
*8.kept only
A. rights of the data subjects
B. than necessary
C. and lawfully
D. to the purpose it was
collected for
E. within the EU
F. secure
G.and up-to-date
H. it was collected for
*
*
* Mail merge is a
software tool which allows you to create letters, mailing labels,
envelopes, reports (like your interims) or anything personalised by linking a main
document to a set of data or data source, such as our database.
* The main document is linked to the data source by fields of data, called merge
fields. You can create your own merge fields, specific to your data source from
the headings in your database.
* Mail merge allows companies to print and send out huge quantities of letters (and
emails) very quickly, which explains why it is so easy for them to send out so
much junk mail!
* Some examples of mail merge include:
* You might own a garage who has a database of all the cars it has serviced, and every
week you run a mail merge to create letters to send out to people whose cars are due
for their next service to remind them to get the car booked in.
* Once you’ve booked a holiday online, you might receive your travel documents/tickets
with your name and other details already printed on, using the data you have entered in
the booking form online which has gone directly into the travel company’s database.
* In school, we use mail merges to create your interim reports. Teachers enter the grades
into the SIMs database, this is mail merged with Word to create the report you actually
take home along with attendance data and your lates.
*
* You are going to create some confirmation letters to send out to
everyone who has signed up for the ski trip. You will use a query
in your database and letter that we’ve already typed up for you.
* Open your Ski trip database and log into Moodle.
* From Moodle, go to ICT & Computing>Year 9>Ski Trip project>Ski
Trip Database> save into your own area the Ski Trip Letter
* Also from Moodle, go to ICT & Computing>Year 9>Ski Trip
project>Ski Trip Database> open How to create a mail merge
letter from a database
* Follow the instructions very carefully, this means read them and
have a go before asking for help!
* You will end up with a set of letters with all the information
inserted in the right places – if you’ve done it right. DO NOT
print off the full set!! That would be very environmentally unfriendly! Check with your teacher how they want you to show
what you have done!
*
*
*By the end of today’s lesson , you
will be able to:
*Research suitable information and
source suitable images for use in your
leaflet
*Present a range of information –
text, images, graphs, maybe a table –
effectively for your target audience
*
On your whiteboard,
*List 3 advantages of a computer database
*List 3 disadvantages of a computer
database
*Think>Pair>Share your ideas
*
*
You are going to create a leaflet to advertise your ski trip.
* In Publisher, choose a Brochure Template – it’s a folding 2 sided
leaflet with 3 sections on each. Choose suitable colour scheme.
* Choose a resort for your ski trip and produce a brochure to tell
people all about the trip. You may need to research the following to
go into your leaflet, as well as using information from your
spreadsheet:
* Price of the trip itself (flight & accommodation), ski lift prices,
ski/board hire, what’s included eg food/wine
* Details about the resort – height, location, facilities, ski lifts,
restaurants and bars – both in the town and on the mountain
* Details and Pictures of your chosen accommodation there – choose a
chalet or hotel so you can copy and paste the text!
* Piste Map - map showing the ski runs
* Other appropriate pictures of snowboarding and skiing
www.crystalski.co.uk
www.thomsonski.co.uk
www.igluski.com
www.inghams.co.uk/ski
*
*By the end of today’s lesson , you
will be able to:
*Research suitable information and
source suitable images for use in your
leaflet
*Present a range of information –
text, images, graphs, maybe a table –
effectively for your target audience
*
*
You are going to create a leaflet to advertise your ski trip.
* In Publisher, choose a Brochure Template – it’s a folding 2 sided
leaflet with 3 sections on each. Choose suitable colour scheme.
* Choose a resort for your ski trip and produce a brochure to tell
people all about the trip. You may need to research the following to
go into your leaflet, as well as using information from your
spreadsheet:
* Price of the trip itself (flight & accommodation), ski lift prices,
ski/board hire, what’s included eg food/wine
* Details about the resort – height, location, facilities, ski lifts,
restaurants and bars – both in the town and on the mountain
* Details and Pictures of your chosen accommodation there – choose a
chalet or hotel so you can copy and paste the text!
* Piste Map - map showing the ski runs
* Other appropriate pictures of snowboarding and skiing
www.crystalski.co.uk
www.thomsonski.co.uk
www.igluski.com
www.inghams.co.uk/ski
*
* Fill in your name on the Leaflet Feedback From and leave it in
your place with your leaflet open on screen
* Swap places with your ‘test buddy’ and have a good look at their
leaflet.
* Fill in the feedback form with your thoughts, suggestions, and
ways they could improve it.
* Try to be positive and useful in your comments! Think about the
sort of thing you’d like someone to say to you!
* Return to your place and read the comments form your test
buddy.
* Make any changes you think would improve it.
* Make sure it is spell checked!
* Print your final version for marking.
*
* By the end of today’s lesson , you will be able to:
*
On your whiteboard,
*List 3 advantages of a computer database
*List 3 disadvantages of a computer
database
*Think>Pair>Share your ideas