1 / 31

Unit 3 OCR Nationals – LEVEL 3

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.

kenisha
Download Presentation

Unit 3 OCR Nationals – LEVEL 3

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Unit 3 OCR Nationals – LEVEL 3 Kick Start

  2. 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.

  3. 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

  4. 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

  5. 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...

  6. 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...

  7. AO1 Hints • Data Entry Messages • Data Validation – settings, as you know already • Valid and invalid data • Input Messages • Error Alert

  8. 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.

  9. 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

  10. 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

  11. 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!

  12. 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!

  13. 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 (!)

  14. 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:

  15. 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.

  16. 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..!)

  17. 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.

  18. 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!

  19. 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

  20. 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

  21. 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???

  22. 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!

  23. 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

  24. 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’!

  25. 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.

  26. 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

  27. 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

  28. 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.

  29. 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

  30. 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...

  31. 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

More Related