SPREADSHEET PROJECT
 

 
         
         
   
LEARNING OBJECTIVES FOR EACH PART OF THE PROJECT
   
         
   
The learning objectives show you what you need to do or know at each level.
You should try to complete the work that will at least allow you to complete your target level.
You need to provide evidence in the form of annotated screenshots & explanations to show your achievements.
   
         
         
         
    1. The Calculator Model    
         
    Level 3    
    Enter data into the Calculator Model.
Create a 'Times Table' using Auto-fill to create number sequences.
   
    Level 4    
    Enter formulae into the Calculator Model.
Use the 'Fill Handle' to replicate formulae.
Use the 'SUM' function to add up a number of values.
   
         
   
   
         
         
         
    2. The Zoo Model    
         
    Level 3    
    Explore the Zoo Model to answer ‘What if ..... ?’ questions about feeding zoo animals.    
    Level 4    
    Explore the Zoo Model to answer your own ‘What if ..... ?’ questions.    
         
   
   
         
         
         
    3. The Cookie Model    
         
    Level 3    
    Enter data into the Cookie Model.
Explore the Cookie Model to answer ‘What if ..... ?’ questions about the cost of cookies.
   
    Level 4    
    Add formula to the Cookie Model to work out the costs of making cookies.
Explore the Cookie Model to answer ‘What if ..... ?’ questions about the cost of cookies.
Annotate Cookie Model screenshots to explain the formulae used.
   
    Level 5    
    Make the Cookie Model more efficient by using 'Absolute Cell Referencing' e.g. $D$5
Annotate Cookie Model screenshots to explain the formulae used.
   
    Level 6    
    Develop the Cookie Model - Ideas include:
(a) Calculate the selling price of cookies.
(b) Calculate VAT on the selling price.
(c) Use the 'IF' function to apply a bulk purchasing discount.
(d) Add fixed costs to the model separate from variable costs.
(e) Apply 'Goal Seek' to calculate the break even point and different profit values.
Annotate Cookie Model screenshots to explain the formulae used.
   
         
   
   
         
         
         
    4. The Disco Model    
         
    Level 3    
    Enter data into the Disco Model.
Explore the Disco Model to answer ‘What if ..... ?’ questions about the cost of running a disco.
   
    Level 4    
    Use ADDITION, SUBTRACTION, MULTIPLCATION, and DIVISION formula to make calculations.
Use SUM and ROUNDUP functions to make calculations.
Explore the Disco Model to answer ‘What if ..... ?’ questions about the cost of running a disco.
Format the Disco Model for currency.
Adding other values to the Disco Model.
Recalculate totals after additional values have been added.
Annotate Disco Model screenshots to explain the formula used and the calculations made.
   
    Level 5    
    Use 'Goal Seek' to answer ‘What if ..... ?’ questions about the cost of running a disco.
Creating bar and pie charts to display data.
Use the model to write report to explain what is required for the disco to go ahead.
Annotate Disco Model screenshots to explain the formula used and the calculations made.
   
         
   
   
         
         
         
    5. The Theatre Model    
         
    (a) Create a booking system for a theatre or concert arena.    
    (b) Create a seating plan that you can link different types of ticket to.    
    (c) Ticket types are: ADULT, CHILD, FREE.    
    (d) Calculate the number of each type of ticket sold, and the number of tickets not sold.    
    (e) Create a balance sheet to calculate the income from the show.    
    (f) Develop the Theatre Model into a full financial system.    
         
    Level 4    
    Create a numbered seating plan for the sitting area of a theatre.    
    Level 5    
    Use DATA VALIDATION LISTS to select different ticket types from drop down menus.
Use CONDITIONAL FORMATTING to add colour to the different ticket types.
Use the COUNTIF function to calculate the number of each ticket type sold.
Use the COUNBLANK function to calculate the number of tickets not sold.
Annotate Theatre Model screenshots to explain the formula used and the calculations made.
   
    Level 6    
    Use INPUT MESSAGES and ERROR ALERTS to check data that is added to the Theatre Model.
Link the number of tickets sold to a balance sheet to calculate the INCOME from a show.
Use the Theatre Model answer questions about running a show.
Annotate Theatre Model screenshots to explain the formula used and the calculations made.
   
    Level 7    
    Develop the Theatre Model by linking it to a financial model to calculate the show's PROFIT. Calculate the PROFIT from calculations of INCOME, VARIABLE COSTS & FIXED COSTS.
Use the model to explore the outcome of a range of different circumstances.
Annotate Theatre Model screenshots to explain the formula used and the calculations made.
   
         
   
   
oo oo   oo oo
             
             
         
   
Spreadsheet Model Levels at a Glance
   
   
- what you need to do to achieve a particular level!
   
       
    LEVEL 3    
    Enter data into a prepared model.
Explore a spreadsheet model to answer ‘What if ….. ?’ questions by changing particular values, e.g. “If I change a particular value, what will happen to a different value?”
   
         
    LEVEL 4    
    Understand and explain: INPUT, PROCESS, OUTPUT, VARIABLE, RULE/FORMULA
Enter formula into a spreadsheet model.
Understand why particular formulae are used.
Check that formulae are correct.
Use the ‘Fill Handle’ to replicate formulae.
Use a spreadsheet model to test a hypothesis, e.g. “If I require a particular value for something, how must other values change for me to obtain this?” by using ‘Trial and Error’.
Present information clearly in a chart.
Explain why the type of chart used is fit for purpose.
Explain the advantages of using a particular spreadsheet model.
   
         
    LEVEL 5    
    Create a spreadsheet model for a particular purpose.
Evaluate a spreadsheet model, suggest improvements, and then make improvements to the model.
Use and understand why I use absolute cell referencing to replicate formulae.
Develop a spreadsheet model by adding more formulae to answer more
‘What if ….. ?’ questions.
Use a spreadsheet model to test a hypothesis, e.g. “If I require a particular value for something, how must other values change for me to obtain this?” by using ‘Goal Seek’.
Understand and explain why ‘Goal Seek’ is used.
Create an appropriate report to explain what has been found out from investigating a spreadsheet model.
Report will include selected data from the spreadsheet model and suitable clear charts.
   
         
    LEVEL 6    
    Independently plan, create, evaluate, and improve a spreadsheet model.
Add more variables and more complex formulae to a spreadsheet model to make it more accurate and more realistic.
Add Data Validation Rules, Error Alerts, and Input Messages to limit errors in inputting data into the spreadsheet model.
Explain in detail the functioning and advantages of a spreadsheet model and how it can be adapted for a real life application.
Identify other sources of information to compare with the behaviour of the spreadsheet model.
Interrogate a spreadsheet model to investigate the feasibility of changing particular variables.
Create a detailed report to explain the implications of changing particular variables in a spreadsheet model and the impact this will have on the outcome expected.
   
         
         
             
 
SPREADSHEET RESOURCE CENTRE
 
             
 
TAKING YOUR WORK TO THE NEXT LEVEL
'I CAN' Sheet (YEAR 7) This is your AFL (Assessment for Learning) sheet that tells you what evidence you have to show to achieve a particular level, and what you have to do to get to the next level.
'I CAN' Sheet (YEAR 8) This is your AFL (Assessment for Learning) sheet that tells you what evidence you have to show to achieve a particular level, and what you have to do to get to the next level.
 
             
   
TEST YOUR KNOWLEDGE
Test your Formula Skills 1 Do the test then print screen your answers and print out!
Test your Formula Skills 2 Do the test then print screen your answers and print out!
Test your Formula Skills 3 Do the test then print screen your answers and print out!
Test your Formula Skills 4 Do the test then print screen your answers and print out!
Parts of a Spreadsheet Test your knowledge of parts of a spreadsheet.
Self Marking Spreadsheet A way to check you are entering formula correctly!
Quiz 1 Save your Team!
Quiz 2 Balloon Invaders!
Quiz 3 Breakout!
Quiz 4 Spot the Teacher!
Quiz 5 Bash the Word!
Quiz 6 Catch the Fruit!
Quiz 7 Racing Cubes!
Quiz 8 Maze!
Quiz 9 Dunk the Teacher!
Quiz 10 Hangman 1
Quiz 11 Hangman 2
Quiz 12 Tick-Tack-Toe 1
Quiz 13 Tick-Tack-Toe 2
Quiz 14 Twister!
Harder Quiz 1 Uses and Functions of Spreadsheets (LEVEL 5/6).
Harder Quiz 2 Formulae and Functions (LEVEL 5/6).
Harder Quiz 3 Modelling and Simulation (LEVEL 5/6).
 
             
 
IMPROVING YOUR LITERACY
Spreadsheet Glossary A copy of the spreadsheet key words and definintions below.
Wordsearch Game Do you know your Key words?
Wordsearch 1 Traditional Wordsearch! (NEEDS TO BE PRINTED)
Wordsearch 2 Traditional Wordsearch! (NEEDS TO BE PRINTED)
Crossword Traditional Crossword Puzzle! (NEEDS TO BE PRINTED)
Word Puzzle Use cell references to spell spreadsheets words ! (NEEDS TO BE PRINTED)
Cryptogram Decipher the sentence to find the coded message! (NEEDS TO BE PRINTED)
ICT Dictionary You can look up the meanings of words for all ICT (not just this project)
 
             
 
ACTIVITIES
Calculator Model Use a spreadsheet as a calculator.
Zoo Model Interrogate the Zoo Model to answer the Zoo Keeper's Questions.
Cookie Model Help a Baker by creating a model to calculate the cost of making cookies.
Disco Model Run your own school disco to make a profit.
 
             
 
INFORMATION & HELP
Parts of a Spreadsheet Iintroduction to the parts of a spreadsheet.
Spreadsheet Video Help Video tutorials to help you use a Microsoft Excel spreadsheet (VIDEO SET 1)
Spreadsheet Video Help Video tutorials to help you use a Microsoft Excel spreadsheet (VIDEO SET 2)
Spreadsheet Information A mini website to give you all sorts of information about spreadsheets.
 
             
 
HOMEWORKS (You need to print these out)
Homework 1 Reading a Spreadsheet.
Homework 2 Designing a Spreadsheet (LEVEL 4).
Homework 3 Designing a Spreadsheet (LEVEL 5).
 
             
             
             
   

SPREADSHEET GLOSSARY:


Absolute Cell Referencing:
Sometimes you will need to refer to the same specific cell on a worksheet. Absolute references are fixed and never change even if you move or copy a formula. They are created by putting a $ sign in front of the row or column part of the cell reference to be fixed, e.g. $D$5, $D5, or D$5. Active Cell: Is the cell ready for data entry. A black border surrounds the active cell. You may change the active cell the using your cursor keys or using your mouse (pointing and clicking).

Auto Fill:
Excel is able to recognise simple numeric patterns. Suppose you are creating a worksheet and you need to add 2, 4, 6 and so forth to a column. Rather than typing all the numbers to 20, Excel can ‘auto fill’ them for you as long you tell it the pattern to begin with by highlighting a short sequence of numbers and dragging them. Auto Sum: Because the most common calculation is the totalling of data, Excel has a button on the toolbar called Auto Sum. Auto Sum writes a formula that uses the SUM function.

Calculations:
Formulae are a method of writing your own calculations. When you change data used in a formula, the formula automatically re-calculates, e.g. the formula =B2+C2 always adds what is in cell B2 to what is in cell C2. Cell: Where a row and column meet. Data or formulae can be added to a cell.

Cell Reference (sometimes called Relative Cell Reference):
Identifies the cell - the column letter and row number, e.g. A5 or B4.

Column:
Are labelled alphabetically across the top, beginning with “A” in the left corner. Conditional Formatting: A conditional format, allows you to only format cells that meet certain conditions, i.e. above, below or between a specific value or set of values.
Formatting could be colour fill, type, size or boldness of text.

Filtering:
Filtering allows you to display only the records that share specific criteria, or field values. Within Excel Auto filter allows you to perform this task. When you turn on Auto filter, filter arrows which look like down arrows, will appear next to the column headings. You click an arrow to display a list of values in that field, and then select a value to use as a condition.

Formulae (Plural) Formula (Singular):
Equations that calculate a value. In spreadsheets a formula begins with an “=” sign, e.g. = B2*C2 multiplies cell B2 by cell C2.

Formula Bar:
Appears directly below the toolbar in the worksheet and displays a formula when the cell of a worksheet contains a calculated value.

Functions:
Not all calculations are simple. Excel’s many functions it is made much easier to carry out calculations. Functions are built in formulas that perform complex maths for you. You enter the function name and any extra information that the function requires and Excel performs the calculations. E.g. to add the values in 6 cells together you could use the formula =B1+B2+B3+B4+B5+B6, however, an easier way of doing it would be with the SUM function, =SUM(B1:B6)

Protecting Cells, Worksheets and Workbooks:
It is possible to protect individual cells an entire worksheet or workbook from amendments by another user. Passwords can also be applied for security.

Range:
A selected group of cells is called a range. In a range, all cells touch each other and form a rectangle.

Row:
Are numbered down the left-hand side, starting with “1” in the top left corner.

Status Line:
Displays both the co-ordinates and contents of the highlighted cell.

Styles:
A style is a collection of formatting attributes such as font, size, bold, italic, cell shading etc. When you apply a style to a cell, all of the formatting attributes contained as part of that style are applied to the cell.

Template:
There may be occasions when you want to save the general layout of a workbook for future use. Creating a template can do this.

Validation:
Data validation is where entering data into a cell is restricted to a particular data type, data value, or formula.

Worksheet:
A whole spreadsheet page.

Workbook:
A number of worksheets that can be linked together in a spreadsheet file.