Topic outline

  • Introduction

    Limos R Us have asked you to create a spreadsheet that will model an order placed by a customer. The expected outcomes of the spreadsheet are:

    1. Enter personal data such as name and address
    2. Select the limo required from a list
    3. Once the limo is selected the limo information should appear on the page automatically
    4. Calculate whether the user can afford the limo they have ordered
    5. Graphs/charts that display the results of their order
    6. A way to send the invoice to the user.

  • AO1 - Develop a spreadsheet to meet the needs of an organisation

    General

    • Candidates will state user requirements.
    • They will create a functional spreadsheet that (mostly) meets the requirements of the user.
    • Relative or absolute cell referencing will be used.

    Pass

    • The spreadsheet will include formulas using at least two of +, -, * and /.
    • Functions from at least two different categories will be used.
    • The spreadsheet may contain only one sheet.

    Merit

    • The spreadsheet will include formulas using all of +, -, * and /.
    • Functions from at least three different categories will be used. This will include the use of one IF statement.
    • Most choices will be appropriate.
    • The spreadsheet will contain more than one sheet, linked by formulas.

    Distinction

    • Functions from at least four different categories will be used. This will include the use of one IF statement.
    • All choices will be appropriate.

  • AO2 - Format a spreadsheet to make it user friendly

    PASS

    • Candidates will use text and background colour and cell borders although these may not show the different types of cell in the most helpful way.
    • They will adjust row height or column width and will merge cells.
    • They will set the direction of text in a cell.
    • They will add at least one example of help for the user, such as an instruction on the sheet, a cell comment, an input message or validation, although this may not be of a high quality.

    MERIT

    • Candidates will use text and background colour and cell borders to distinguish between different types of cell (eg cells to input data, cells which automatically calculate).
    • They will adjust row height or column width, hide and show rows or columns and will merge cells.
    • They will set the direction of text in a cell and will set some text to wrap in a cell.
    • They will add help for a new user. This help will include at least one cell comment and appropriate validation in at least one row/column.
    • They will use conditional formatting.

    DISTINCTION

    • Candidates will appropriately use text and background colour and cell borders to distinguish between different types of cell (eg cells to input data, cells which automatically calculate).
    • They will adjust row height or column width, hide and show rows or columns and will merge cells.
    • They will set the direction of text in a cell and will set some text to wrap in a cell.
    • They will add sufficient help to enable a beginner to use the spreadsheet with ease.
    • This help will include suitable cell comments and validation with useful feedback to users.
    • They will set cells for input from a drop-down list.
    • They will use conditional formatting to make the output clearer.
    • They will use worksheet protection to prevent a user changing/deleting formulas whilst allowing data to be added/edited as needed by a user.

  • AO3 - Sort data and use simple filters

    PASS

    • Candidates will sort data using one field. 
    • They will filter data using one field. 
    • They will state the purpose of their sort and filter. 
    MERIT 
    • Candidates will sort data using at least two fields. 
    • They will state the purpose of their sort and filter. 
    DISTINCTION 
    • Candidates will sort data using at least two fields. 
    • They will filter data using at least two fields. 
    • They will customise at least one filter. 
    • They will state the purpose of their sort and filter(s).

  • AO4 - Carry out modelling activities using a spreadsheet

    PASS

    • Candidates will change different variables in their spreadsheet to make at least two predictions or decisions. 
    • They will state the results obtained. 
    MERIT
    • Candidates will change different variables in their spreadsheet to make at least two predictions or decisions. 
    • They will write about their investigations and the results they find. 
    DISTINCTION
    • Candidates will change different variables in their spreadsheet and make suitable predictions and decisions. 
    • They will write about their investigations and the results they find.
    • FileLesson PP File 65.7KB Powerpoint presentation

      Quick guide to explain what is required by AO4 and a example method.

  • AO5 - Analyse data using appropriate graphs/charts

    PASS

    • Candidates will create at least two different types of graph/chart. 
    • Graphs should be given appropriate titles and the data will be labelled. 
    MERIT
    • Candidates will create at least one example of each type of graph from line graph, bar chart and pie chart. 
    • At least one of these should compare values from different data sets. 
    • Graphs should be given titles and appropriate axis labels. 
    DISTINCTION
    • Candidates will create at least one good example of each type of graph from line graph, bar chart and pie chart. 
    • At least one of these should compare values from different data sets. 
    • Graphs should be given titles and axes will be appropriately scaled and labelled. 
    • The final graphs will be clear, show the data clearly and helpfully and be appropriate for the type of data plotted.
    • FileLesson PP File 383.6KB Powerpoint presentation

      Lesson Powerpoint showing how to create the charts.  Which charts should be used and how to produce the evidence.

  • AO6 - Create macros to automate procedures in a spreadsheet

    PASS

    • Candidates will record a simple macro to automate a sequence of at least two tasks. 
    • They will enable this macro to be run by either keyboard shortcut or a button on the sheet or on the toolbar. 
    • They will access the macro code and print it out. 
    MERIT
    • Candidates will record a macro to automate a sequence of at least two tasks. 
    • They will enable this macro to be run by both a keyboard shortcut and a button on the sheet or on the toolbar. 
    • They will access the macro code and print it out. 
    • They will describe what the macro does and how it can be run. 
    DISTINCTION
    • Candidates will record a macro to automate a sequence of more than two tasks. 
    • They will enable this macro to be run by both a keyboard shortcut and a button on the sheet or on the toolbar. 
    • They will access the macro code and print it out. 
    • They will describe what the macro does and how it can be run. 
    • They will annotate the macro code to show the function of at least three different lines of code.
    • FileGuidance Powerpoint File 824.4KB Powerpoint presentation

      Powerpoint to explain how to make the macros and how for distinction you might want to modify it for a unique file name everytime.

  • AO7 - Test the spreadsheet solution

    PASS

    • Candidates will test their spreadsheet solution, ensuring that it provides accurate results that meet the main user needs. 
    MERIT
    • Candidates will test their spreadsheet solution, ensuring that all formulas provide accurate results and that the spreadsheet meets the main user needs. 
    • The tests will cover most of the main areas of their spreadsheet (as shown in the KUS) and will be appropriate. 
    • Normal and abnormal data will be used in testing. 
    DISTINCTION
    • Candidates will test their spreadsheet solution, ensuring that it provides accurate results and meets user needs. 
    • The tests will cover all main areas of their spreadsheet (as shown in the KUS) and will all be appropriate. 
    • Normal, abnormal and extreme data will be used in testing.