Level 2 Designing and creating spreadsheets (7266/7267-023) e-Quals Assignment guide for Candidates Assignment B (This assignment can be used as evidence of achievement of 7574-227 Spreadsheet software) e-Quals 07 TEKSTA

City& Guilds www.cityandguilds.com/e-quals07 September 2011 Version 6.0 About City & Guilds City & Guilds is the UK's leading provider of vocational qualifications, offering over 500 awards across a wide range of industries, and progressing from entry level to the highest levels of professional achievement. With over 8500 centres in 100 countries, City & Guilds is recognised by employers worldwide for providing qualifications that offer proof of the skills they need to get the job done. City & Guilds Group The City & Guilds Group includes City & Guilds, ILM (the Institute of Leadership & Management) which provides management qualifications, learning materials and membership services, NPTC which offers land-based qualifications and membership services, and HAB (the Hospitality Awarding Body). City & Guilds also manages the Engineering Council Examinations on behalf of the Engineering Council. Equal opportunities City & Guilds fully supports the principle of equal opportunities and we are committed to satisfying this principle in all our activities and published material. A copy of our equal opportunities policy statement is available on the City & Guilds website. Copyright The content of this document is, unless otherwise indicated, O The City and Guilds of London Institute 2010 and may not be copied, reproduced or distributed without prior written consent. However, approved City & Guilds centres and learners studying for City & Guilds qualifications may photocopy this document free of charge and/or include a locked PDF version of it on centre intranets on the following conditions: centre staff may copy the material only for the purpose of teaching learners working towards a City & Guilds qualification, or for internal administration purposes learners may copy the material only for their own use when working towards a City & Guilds qualification The Standard Copying Conditions on the City & Guilds website also apply. Please note: National Occupational Standards are not © The City and Guilds of London Institute. Please check the conditions upon which they may be copied with the relevant Sector Skills Council. Publications City & Guilds publications are available on the City & Guilds website or from our Publications Sales department at the address below or by telephoning +44 (0)20 7294 2850 or faxing +44 (0)20 7294 3387. Every effort has been made to ensure that the information contained in this publication is true and correct at the time of going to press. However, City & Guilds' products and services are subject to continuous development and improvement and the right is reserved to change products and services from time to time. City & Guilds cannot accept liability for loss or damage arising from the use of information in this publication. City & Guilds 1 Giltspur Street London EC1A 9DD +44 (0)20 7294 2800 F +44 (0)20 7294 2400 www.cityandguilds.com learnersupport@cityandguilds.com Contents Unit 023 - Designing and creating spreadsheets Level 2 (This assignment can be used as evidence of achievement of 7574-227 Spreadsheet software) Assignment B Introduction - Information for Candidates Candidate instructions Level 2 Assignment B V6.0 Designing and creating spreadsheets (7266/7267-023) 2 3 1 2 Level 2 Designing and creating spreadsheets (7266/7267-023) Assignment B Introduction - Information for Candidates About this document This assignment comprises all of the assessment for Level 2 Designing and creating spreadsheets (7266/7267-023). This assignment can be used as evidence of achievement of 7574-227 Spreadsheet software. Health and safety You are responsible for maintaining the safety of others as well as your own. You are asked to work safely at all times. You will not be allowed to continue with an assignment if you compromise any of the Health and Safety requirements. Time allowance The recommended time allowance for this assignment is two hours. Level 2 Assignment B V6.0 Designing and creating spreadsheets (7266/7267-023) Level 2 Designing and creating spreadsheets (7266/7267-023) (This assignment can be used as evidence of achievement of 7574-227 Spreadsheet software) Candidate instructions Time allowance: Two hours The assignment: This assignment is made up of four tasks ● Scenario 1 ● You work as the club secretary for a small swimming club. The club manager has asked you to amend existing membership data and to produce some information on membership fees. Read all of the instructions carefully and complete the tasks in the order given. 2 Task A - Importing files and rearranging data Task B - Adding formulas and functions Task C -Adding data Task D - Producing charts Task A - Importing Files and rearranging data 3 Create a new folder in your work area/ removable media and name it Swimming Club. Open a blank spreadsheet and import the file Squads.csv starting at cell A1. The supplied file is a comma separated value file (csv) and the first row contains headings. Apply text formatting to make the column headings stand out and wrap text in columns B, C,F and G with suitable alignment to ensure that they do not become too wide. Move column G so that it comes before column C. Insert an additional column after the moved column and insert the title Membership Fee in your new column D. Insert a new column after the Pool Location column and add the title Training Days. Format columns C and D to currency to 2 decimal places, showing the local currency symbol. Format column I to number zero decimal places. Continued over... Level 2 Assignment B V6.0 Designing and creating spreadsheets (7266/7267-023) 3