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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- microsoft excel project
- unit 9 spreadsheet development authorised assignment
- south puget sound community college
- sample budget notes and guidance
- after action report sample under secretary of
- sample cost and price analysis
- training and education implementation plan
- personal budget excel assignment
- college research mini project
- rock creek schools