Unit 9: Spreadsheet Development - Authorised Assignment ...



|Assignment title |Reward Trip Information |

| |

|Assessor | |

| |

|Date issued | |

|Final deadline | |

|Duration (approx) |10 hours |

| |

|Qualification suite |BTEC Level 1/Level 2 First Award in Information and Creative Technology |

|covered |BTEC Level 1/Level 2 First Certificate in Information and Creative Technology |

| |BTEC Level 1/Level 2 First Extended Certificate in Information and Creative Technology |

| |BTEC Level 1/Level 2 First Diploma in Information and Creative Technology |

|Units covered |Unit 9: Spreadsheet Development |

|Learning aims covered|Learning aim A: Understand the uses of spreadsheets and the features available in spreadsheet software packages |

| |Learning aim B: Design a spreadsheet |

| |Learning aim C: Develop and test a spreadsheet |

| |Learning aim D: Review the finished spreadsheet |

| |

|Scenario |You are completing a work placement at the local upper school. You have been asked to produce a spreadsheet to manage |

| |information about the pupils reward trip. |

| | |

| |You will produce design documents for a spreadsheet solution to meet your manager’s needs as detailed below. |

| | |

| |Three worksheets are required: |

| | |

| |Worksheet 1: Trip and Tutor Details |

| |As a minimum, this spreadsheet should hold: |

| |Form Tutor including the house and form name |

| |Points table – If the students achieve 50 points or under they get no reward, between 50 and 100 they get an in school |

| |reward and 100 points and over an out of school trip. |

| |Trip information – Including the name of the trip, date, price and maximum numbers. |

| | |

| | |

| |Worksheet 2: Students and trips |

| |This work sheet is where the data on the pupils and the trips they have selected will be held. You will need to ensure you|

| |include the following: |

| |Pupil information – including their name, form, gender and year |

| |The total of achievement and behaviour points and their net total |

| |The type of activity they are eligible for |

| |Their choice of trip, price and if they have paid (Payment must be made at least 7 days before the trip) |

| |A discount of £5 can be applied if the student is in year 11 and have more than 200 net points. |

| | |

| |Calculations and tools/techniques |

| |Appropriate calculations and tools/techniques should be applied to the worksheet(s), e.g. cell formatting, functions, |

| |formulas, IF statements and macros. |

| | |

| |Data input |

| |The data entry should be user-friendly. |

| | |

| | |

| |Worksheet 3: Summary of information |

| |This should hold a summary of the totals of the trips. A series of calculations will be needed, as well as conversion of |

| |some data to charts, in order to generate management information. |

| | |

| |The worksheets can be stored in one spreadsheet file and connected using a menu. |

| | |

| |The solution should include a number of outputs that will help to manage the information. The developer is asked to design |

| |a series of examples to demonstrate what is possible. |

| | |

| |Your design documents should consider the formatting of inputs to the spreadsheet, what processes/calculations are |

| |required, and example outputs that the club can use to help manage the trip information. |

| |

|Task 1 |Uses and Features of Spreadsheet Software Packages |

| |Before you create the spreadsheet, the head teacher would like some information about how spreadsheets are used in the real|

| |world. |

| | |

| |He has asked you to review two different kinds of spreadsheets and to prepare a short presentation discussing the strengths|

| |and weaknesses of each of the spreadsheets. |

| | |

| |The two spreadsheets should be designed for different purposes; for example: |

| |cost modelling |

| |analysis of data |

| |tracking learners progress and recording results |

| |creating league timetables and match results |

| |stock control. |

| | |

| |Create the presentation using PowerPoint® slides and notes. Make sure the presentation describes the below. |

| |The purpose of both spreadsheets. |

| |The purpose of the tools and techniques of both spreadsheets, e.g. to aid usability, to increase productivity, to improve |

| |accuracy and to present output data. |

| |How the tools and techniques of both spreadsheets are used, e.g. cell replication and formatting, page setup and user |

| |interfaces. |

| | |

| |You should also: |

| |review how the features (e.g. functions, layout and structure) could improve productivity, accuracy and usability in both |

| |spreadsheets. |

| |Discuss the strengths and weaknesses of both spreadsheets. |

|Evidence you must |PowerPoint® slides with notes. |

|produce for this task|Copies of both spreadsheets. |

|Criteria covered by this task: |

|To achieve the criteria you must show that you are able to: |Unit |Criterion reference |

|Explain how spreadsheets are used for two different activities, and how the features are used |9 |2A.P1 |

|in the spreadsheets. | | |

|Review how the features in the spreadsheets could improve productivity, accuracy and |9 |2A.M1 |

|usability. | | |

|Discuss the strengths and weaknesses of the spreadsheets. |9 |2A.D1 |

| |

|Task 2 |Create a Design |

| |It’s now time to create your design. |

| | |

| |Investigate what information is typically involved in school trip applications and study the scenario. Consider what |

| |outputs are possible and useful to the school, and produce a summary of the purpose of your spreadsheets and your specific |

| |user requirements. |

| | |

| |Your design documentation should include a detailed: |

| |worksheet structure diagram indicating data validation (including lists), |

| |e.g. multiple worksheets, cell referencing, input messages, error messages, macros, cell protection and navigation between |

| |multiple worksheets. |

| |description of the tools/techniques to be applied, e.g. cell formatting, functions, formulas, calculations, IF statements |

| |description of the input and output data presenting the results/outcome. |

| | |

| |The design documentation should also provide: |

| |a description of the user requirements and intended purpose of the spreadsheet |

| |a description of some alternative design ideas, e.g. choice of calculations and style |

| |test plan and data, e.g. test, expected result, actual result |

| |design justification explaining how the design will meet the intended purpose and user requirements (note any constraints |

| |in the design) |

| |explain why alternative designs were rejected, and consider any constraints. |

|Evidence you must |Design documents, including: |

|produce for this task|a statement of intended purpose and list of user requirements |

| |detailed worksheet structure diagram outlining fields, calculations and functions, named ranges etc. |

| |input and output data descriptions – lists, charts and graphs |

| |a test plan and test data |

| |a short report justifying your final design decisions, and explaining why alternative design ideas were rejected and any |

| |constraints. |

|Criteria covered by this task: |

|To achieve the criteria you must show that you are able to: |Unit |Criterion reference |

|Describe the purpose and user requirements for the spreadsheet. |9 |2B.P2 |

|Produce a design for a spreadsheet, including: |9 |2B.P3 |

|worksheet structure diagram | | |

|how output data is to be presented | | |

|a test plan. | | |

|Produce detailed designs for a spreadsheet, including: |9 |2B.M2 |

|alternative solutions | | |

|detailed worksheet structure diagram | | |

|test data. | | |

|Justify final design decisions, including: |9 |2B.D2 |

|how the spreadsheet will fulfil the stated purpose and user requirements | | |

|any constraints to the design. | | |

|Task 3 |Create, test and review |

| |It is now time to create your spreadsheet solution. |

| | |

| |The spreadsheet will contain a user interface for data input and for the presentation of output data. You should include |

| |automated tools/techniques to improve productivity and ensure accuracy, including macros, links, named ranges and pivot |

| |tables. |

| | |

| |You should have either a chart or a graph to present the result/output from the spreadsheet solution. |

| | |

| |Onscreen user navigation and guidance used should be clear: |

| |input messages |

| |validation (including lists) |

| |error messages |

| |navigation prompts and guidance |

| |conditional formatting |

| |labels |

| |a commentary explaining the output presentation. |

| | |

| |Data for the spreadsheet should be sorted and the spreadsheet formatted appropriately to promote ease of use. |

| | |

| |It’s now time for you to test the functionality/tools/techniques of the spreadsheet and make changes to repair any faults |

| |identified by these tests. Changes to the spreadsheet should be documented; different versions from the various stages of |

| |development could be used to evidence this. |

| | |

| |Arrange a meeting with your tutor to demonstrate your spreadsheet solution. You should discuss the ease of use of the |

| |spreadsheet solution, both in terms of the storage, manipulation and analysis of data and the data output presentation. Use|

| |the discussion to review your designs and improve the spreadsheet. |

| | |

| |All of the ideas that are generated from testing, user feedback and reviewing the designs with your tutor should be |

| |considered when improving the spreadsheet. |

| | |

| |This discussion will be documented using a witness statement. |

| | |

| |Reminder |

| |Once complete, you should consider which elements of the design you would like to showcase in your digital portfolio (as |

| |part of Unit 3) and also think about how they will fit in with your portfolio design. You should save a copy, in an |

| |appropriate format, of the selected documents and/or files for inclusion in your digital portfolio at a later date. |

|Evidence you must |Original spreadsheet. |

|produce for this task|Annotated reviewed spreadsheet (after testing). |

| |A commentary explaining the input and output presentation. |

| |Testing table and data. |

| |Feedback from others. |

| |Witness statement. |

|Criteria covered by this task: |

|To achieve the criteria you must show that you are able to: |Unit |Criterion reference |

|Develop a spreadsheet with a given realistic data set, containing a user interface for data |9 |2C.P4 |

|input and presentation of output data. | | |

|Test the spreadsheet for functionality and purpose and repair any faults, documenting any |9 |2C.P5 |

|changes made. | | |

|Refine the spreadsheet to improve usability and accuracy using onscreen user navigation and |9 |2C.M3 |

|guidance. | | |

|Gather feedback from others on usability, and use it to improve the spreadsheet, testing the |9 |2C.M4 |

|additional functionality and repair any faults. | | |

|Refine the spreadsheet using automated tools/techniques to improve productivity, accuracy and |9 |2C.D3 |

|presentation of output data. | | |

| |

|Task 4 |Review |

| |It’s now time to complete a review of the finished spreadsheet solution. |

| | |

| |Evaluate how the spreadsheet solution meets the user requirements, and fits the intended purpose based on the user |

| |feedback. |

| |Evaluate the final spreadsheet against the initial designs. |

| |Justify any changes that were made. |

| |Make at least three recommendations for how you can further improve the spreadsheet. |

| | |

| |You do not need to implement these improvements. |

|Evidence you must |Evaluation report. |

|produce for this task| |

|Criteria covered by this task: |

|To achieve the criteria you must show that you are able to: |Unit |Criterion reference |

|For the final spreadsheet, explain how the final spreadsheet is suitable for the user |9 |2D.P6 |

|requirements and purpose. | | |

|Review the extent to which the final spreadsheet meets the user requirements and purpose while|9 |2D.M5 |

|considering feedback from others. | | |

|Evaluate the final spreadsheet against the initial designs and justify any changes that were |9 |2D.D4 |

|made, making recommendations for further improvements to the spreadsheet. | | |

| |

| |

|Sources of |Textbooks |

|information |Allman, E. et al. (2012) BTEC First in Information and Creative Technology Student Book, Pearson Education Ltd, 978 1 44690|

| |187 8 |

| |Textbook is designed for this qualification with a strong vocational focus. |

| |Holden, G. and Short, P. (2010) Excel 2010 in Simple Steps, FT Prentice Hall, 978 0 27373 613 4 |

| |Not expensive but useful step-by step-approach to the software. All examples use Excel 2010. |

| |Johnson, S. (2010) Brilliant Excel 2010, FT Prentice Hall, 978 0 27373 609 7 |

| |This text covers the more advanced topics within the specification, in particular the data tools features. Not expensive |

| |and all examples are in Excel 2010. |

| | |

| | |

| |Note to assessors |

| |  |

| |We are committed to ensuring that teachers/tutors and learners have a choice of resources to support their teaching and |

| |study. |

| |  |

| |We would encourage them to use relevant resources for your local area such as local employers, newspapers and council |

| |websites. |

| |  |

| |Resources from various publishers are available to support delivery and training for all Pearson and BTEC qualifications so|

| |that learners and teachers/tutors can select those that best suit their needs. |

| |  |

| |Above are some examples of textbooks and websites. Further useful resources may be found at |

| |resources/Pages/default.aspx. |

| |

|If you have not achieved the Level 2 criteria, your work will be assessed to determine if the following Level 1 criteria have been met. |

|To achieve the criteria you must show that you are able to: |Unit |Criterion reference |

|Identify how spreadsheets are used for two different activities and how the features are used in |9 |1A.1 |

|the spreadsheets. | | |

|Identify the purpose and user requirements for the spreadsheet. |9 |1B.2 |

|With guidance, produce a design for a spreadsheet including: |9 |1B.3 |

|worksheet structure diagram. | | |

|With guidance, develop a spreadsheet with a given realistic dataset. |9 |1C.4 |

|With guidance, test the spreadsheet for functionality and purpose, and repair any faults, |9 |1C.5 |

|documenting any changes made. | | |

|For the final spreadsheet, identify how the final |9 |1D.6 |

|spreadsheet is suitable for the purpose. | | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download