unit 3 ocr nationals level 3 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Unit 3 OCR Nationals – LEVEL 3 PowerPoint Presentation
Download Presentation
Unit 3 OCR Nationals – LEVEL 3

Loading in 2 Seconds...

play fullscreen
1 / 31

Unit 3 OCR Nationals – LEVEL 3 - PowerPoint PPT Presentation


  • 149 Views
  • Uploaded on

Unit 3 OCR Nationals – LEVEL 3. Kick Start. OK, you asked for it. Remember there are NO Model Assignments for the Option Units, so I am winging it. For this Unit, however, I am also relying a little on the new textbook from PG, so we’ll see how that goes. Scenario.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Unit 3 OCR Nationals – LEVEL 3' - kenisha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ok you asked for it
OK, you asked for it
  • Remember there are NO Model Assignments for the Option Units, so I am winging it. For this Unit, however, I am also relying a little on the new textbook from PG, so we’ll see how that goes.
scenario
Scenario
  • A local restaurant owner would like to expand their business. To qualify for small business support they have to show that they are managing their finances well right now.
  • Time for drastic action – design a spreadsheet that covers income and expenses, and allows some level of modelling for additional financial planning
ao1 design spreadsheets to meet the needs of an organisation
AO1: Design spreadsheets to meet the needs of an organisation
  • You need to get the pen and paper out for this one! Design AT LEAST 2 linked spreadsheets for the organisation, sketches to include:
    • Purpose
    • Audience
    • Layout & formatting
    • Design sheets in form appearance/customised toolbars & buttons
    • Calculations required
    • Data entry messages
    • Data validation and other appropriate messages
ao1 hints
AO1 Hints
  • Be very clear in your mind about the user for this one: it helps if you can picture them!
  • Think about:
    • Keep the worksheets simple in size & number so they are easier to work with & understand
    • Avoid blank rows/columns if you can help it: many higher end functions will just stop if there’s a blank cell in the way.
    • Sort data into appropriate orders so that functions work more effectively
    • Keep names in two cells – one for first, one for last: it’s easier later.
    • Oh – there’s more, but I’ve run out of space here...
ao1 hints1
AO1 Hints
  • Sheets with the appearance of a form:
    • Excel 07 has the Template Wizard, so those of you with that can play and have fun.
    • The more pedestrian 03 version will need macros: we’ve met them before, for U3.
  • Calculations required
    • Follow protocols: formulae only refer to cells ABOVE them
    • If a formula requires LOTS of raw data, move it to a separate worksheet and link the data to the sheet holding the formula.
    • Formulae should be as simple as possible
    • REMEMBER there are over 300 different functions in Excel – this is your chance to play with your new toys...
ao1 hints2
AO1 Hints
  • Data Entry Messages
    • Data Validation – settings, as you know already
      • Valid and invalid data
      • Input Messages
      • Error Alert
ao1 grade guides
AO1 grade guides
  • For Pass – and therefore all the others:
    • At least two linked spreadsheets;
    • Design includes purpose, audience and spreadsheet layout
    • Designs are appropriate
    • Some calculations are defined.
ao1 grade guides1
AO1 grade guides
  • For Merit:
    • Spreadsheet designs are clear on the content
    • Most calculations are appropriate
    • Some data entry messages are identified
    • Note the level of detail for this!
  • For Distinction:
    • Customisation elements are clear
    • All calculations are clearly defined and appropriate
    • All data validation and error messages required are defined
ao2 produce spreadsheets according to the design
AO2: Produce spreadsheets according to the design
  • Linked spreadsheets
  • Relative, absolute, mixed cell references
  • Named cells and cell ranges
  • Cell formats
  • Functions
  • Macros
  • Customised Toolbars/menus
ao2 hints
AO2 Hints
  • Linked spreadsheets
    • Well you did this for U3, so this should be OK, yes?
  • Named cells and ranges.
    • It’s easier to locate and work with a cell if it is called ‘total’ rather than ‘DE4’ or whatever. Choose the cell, then where the cell reference is shown, highlight and type in the name of the cell – NO SPACES!
    • You can do the sameto a group of cells, too,whoooo!
ao2 hints1
AO2 Hints
  • Cell formats
    • Include conditional formatting, just like for U3
    • Date and time as appropriate (NOW function)
    • Also consider:
      • Text alignment
      • Font formatting – bold/italic/underline/colour...
      • Use the STYLE function to create a consistent view
      • Cell colours and shading, borders.
      • Cell merging!
ao2 hints2
AO2 Hints
  • Functions to meet requirements:
    • Here’s a summary, but there ARE 300 of them so I’m just picking my top, ummm, 4, or 11...
      • References:
        • HYPERLINKS
        • LOOKUPS
        • MATCH
      • Mathematical:
        • COUNTIF (and all its subsidiaries!)
        • SUM
        • CEILING/FLOOR
      • Statistics:
        • AVERAGE/MAX/MIN...
        • RANK
        • COUNT
      • Logic:
        • AND/IF/FALSE/NOT/OR...
      • Note also we have the wonders of NESTED FUNCTIONS (!)
ao2 hints3
AO2 Hints
  • Nested functions
    • You can do as I do in my marksheet for you guys: bung in more than one function at a time. This is a more effective way to complete a task in one neat package, and it is a LOT easier if you are using named cells/ranges. You can squish up to 7 levels in each – but here’s one of the more straightforward of mine, identifying whether you’ve gained P,M or D:
ao2 hints4
AO2 Hints
  • Data Validation
    • Well you’ve done this a bazillion times so I won’t treat you like fools.
    • Try to be more imaginative in this than you were in U3, OK? You can calculate what is allowed based on the content of another cell, or use a formula to calculate the value, and this could again make your product more powerful.
ao2 hints5
AO2 Hints
  • Macros
    • We’ve already played with these on U3, but here you get to play with the Big Boys...
      • You can set styles for font
      • You can set formulae and functions using macros
      • You can set actions (like graphing)
      • Customising the menu/toolbars – more overleaf on this one because this is more fun (but don’t tell the Network guys, because I don’t know what they’ll let us do..!)
ao2 hints6
AO2 Hints
  • Menus/toolbars
    • Now this may well do our heads in, because I don’t as yet know what settings will stick, and what won’t. We’ll just have to play it by ear. What a surprise..!
    • Let’s start with some definitions:
      • MENUS are lists of commands: e.g. File menu. The 07+ versions of MS products are short on menus, but you’ll see them in 03.
      • TOOLBARS hold the buttons instead of lists of words: 07+ is hot on toolbars.
ao2 hints7
AO2 Hints
  • Customising
    • Well, you could change the lists on a menu? Bit simplistic, but can be essential if you’re sick of going through several stages to get to the same ‘format’ menu element, or whatever.
    • OR, change the buttons on the toolbar: there’s a standardised option to do that on all the MS products, or you could write your own macro and stuff it on a button and be even more impressive!
ao2 grade guides
AO2 grade guides
  • For Pass – and therefore all the others:
    • Produce the darn spreadsheets
    • They should look similar to the designs
      • Including use of:
        • Cell referencing (absolute/relative)
        • Named cells and ranges
        • Different cell formats
        • Functions
        • Macros
    • Some attempt to customise menus/toolbars
ao2 grade guides1
AO2 grade guides
  • For Merit:
    • Spreadsheet is close to designs
      • Including additional use of:
        • Multi stage functions
        • Data validation
        • Cell references across sheets
    • Customised a menu or toolbar
    • Note the level of detail for this!
  • For Distinction:
    • Design and spreadsheet are as one.
      • Including additional use of:
        • Nested functions
        • Customised error messages
    • Customised a menu and toolbar
ao3 use spreadsheet to process numerical data and present required information
AO3: use spreadsheet to process numerical data and present required information
  • So there’s a surprise – it has to DO something.
  • How?
    • Use PivotTables?
      • A PivotTable sorts and summarises data, combining and comparing large amounts of data – and then creates a new table which you can sort and rotate – and it’s relatively easy to create using the Wizard! 
    • Using the ‘what-if’ element in 07, you could use Goal Seek when you know the result that you need, but not how to get there. So for example, you need a 50p profit – so what’s the price to the client???
ao3 use spreadsheet to process numerical data and present required information1
AO3: use spreadsheet to process numerical data and present required information
  • You could also use Solver (Tools – Solver and set the parameters)
  • You could use the advanced filtering options
  • Presenting the results:
    • Cell formatting to make it easy to read?
    • Cell width and height/merge&centre
    • Graphs & Charts
    • HARD COPY (yeah – what you see you gotta be able to print: headers/footers/titles/cells & row titles)
    • Report Manager!
ao3 grade guides
AO3 grade guides
  • For Pass – and therefore all the others:
    • Data processing is appropriate for requirements
    • Print spreadsheets showing
      • all data
      • Fit on the page(s)
      • Cell contents visible
      • Headers/footers
      • Graph/charts
      • PivotTables
      • Filtered results
      • Macros
ao3 grade guides1
AO3 grade guides
  • For Merit:
    • Good use of functions/formulae
    • Printouts are clear and easy to understand
  • For Distinction:
    • Results of analysis are easy to understand: not just the ‘before’ but also the ‘after’!
ao4 produce user documentation and technical information
AO4: Produce user documentation and technical information
  • Well you’ve just done this for U4 so here goes:
    • Ideally your user guide should cover the vast majority of the processes, including screenshots and explanations so that a fairly clued-up user could use the spreadsheet without you
    • Your technical guide should cover ALL details of numerical processing methods, screenshots of macros, data validation, details of software and hardware required.
ao4 grade guides
AO4 grade guides
  • For Pass – and therefore all the others:
    • User guide covers some of the processes so a relatively clued-up user could get on with your product
    • Tech guide identifies
      • Numerical processing methods used
      • Macros
      • Data validation
ao4 grade guides1
AO4 grade guides
  • For Merit:
    • User Guide includes text & screenshots covering some of the processes so a relatively clued-up user could make decent use of the spreadsheet
    • Tech Guide gives details of
      • Numerical processing methods used
      • Macros
      • Data validation
  • For Distinction:
    • User Guide does the lot using text & screenshots
    • Tech Guide covers additional info on hardware and software, and any other resources required
ao5 test the spreadsheet
AO5: Test the spreadsheet
  • Usual stuff – test it using the full range of valid and invalid data, check for all messages – and make ALL necessary changes so it’s wonderful. Oh, and it works.
ao5 grade guides
AO5 grade guides
  • For Pass – and therefore all the others:
    • Simple test plan covers that the spreadsheet does what it should from the spec.
  • For Merit
    • Evidence provided that the plan adequately tests the functionality of the spreadsheet (i.e. how well does it do what it should?)
    • Make some changes to make it work as it should.
  • For Distinction
    • A detailed plan testing ALL aspects of the spreadsheet, showing valid & invalid input, expected input, and any error messages
    • Make all changes so it works as it should
ao6 evaluate the spreadsheet
AO6: Evaluate the spreadsheet
  • To what extent does it do as you said it would? And, no, just like all the others, you cannot just type, ‘I done it good!’
  • You also need to suggest improvements and refinements, and the greater the detail the better the grade...
ao6 grade guides
AO6 grade guides
  • For Pass – and therefore all the others:
    • A brief and not always accurate evaluation in relation to user needs. (I done it right)
  • For Merit
    • A detailed and accurate evaluation showing how well it meets user needs.
    • A detailed description of improvements for the user.
  • For Distinction
    • A comprehensive evaluation of how well it does the do.
    • Detailed improvements and recommended refinements