learnaboutwales.weebly.comlearnaboutwales.weebly.com/.../2/1/7/...v3_esw_l1.docx · web...

Edexcel BTEC Level 1Award/Certificate/Diploma in

IT Users (ITQ) (QCF)

Unit 127: Spreadsheet Software

Assignment 127

Contents

Index Page No

Learner details* 3

Learner tracker* 3

Learner declaration* 3

Aim and purpose 4

Unit introduction 4

Learning outcomes 5

Assessment and grading criteria 5

Unit contents 6

Delivery 7

Assessment 7

Assignment brief 8

Task 1 9

Task 2 9

Task 3 10

Task 4 10

Task 5 11

Task 6 12

Task 7 13

* Must be submitted with learner’s evidence.

© Learn About BTEC Level 1 IT Users Version 2 July 2012 of 14

Assignment 2 - Unit 127: Spreadsheet SoftwareLearner Name:

Assessor Name:

Issue Date:

Deadline Date:

Submission Date:

Learner TrackerAssignment 2 Assessment Criteria Completed Grade

Task 1

Task 2

Task 3

Task 4

Task 5

Task 6

Task 7

Learner Declaration

The learner declaration must be attached to the completed portfolio of evidence.

Learner Name:

I declare that the work contained in this portfolio of evidence is all my own work.

Learner Signed:

Date:

I declare that the work contained in this portfolio of evidence is all the work of the above learner.

Assessor Name:

Assessor Signed:

Date:


Unit 127: Spreadsheet Software

Unit code: T/502/4624

QCF Level: Level 1

Credit value: 3

Unit summaryThis unit is about the skills and knowledge required by an IT user to use a range of basic spreadsheet software tools and techniques to produce, present and check spreadsheets that are straightforward or routine. Any aspect that is unfamiliar will require support and advice from others. Spreadsheet software tools and techniques will be described as ‘basic’ because:

• the range of data entry, manipulation, formatting and outputting techniques are straightforward;• the tools, formulas and functions involved will be predetermined or commonly used (for example, sum, divide, multiply, take away and fractions); and• the structure and functionality of the spreadsheet will be predetermined or familiar.

Assessment requirements/evidence requirementsEvidence of achievement can be derived from a variety of sources. Learners who use their IT skills directly in their day-to-day work can prove their competence whilst doing so. Alternatively learners can use scenarios and knowledge tests - or a mixture of both - to demonstrate competence.

Assessment methodologyAll ITQ units may be assessed using any method, or combination of methods, which clearly demonstrates that the learning outcomes and assessment criteria have been met. BN026775 – Specification – Edexcel BTEC Entry Level 3 and Level 1 Award, Certificate and Diploma for IT Users (ITQ) (QCF) – Issue 1 – June 2011 © Edexcel Limited 2011 362. Whilst assessors are required to have a sound understanding of the unit requirements and be able to give appropriate feedback to learners, they do not have to be A1 qualified. However, ideally every assessor should have ITQ Level 3 or equivalent in order to be able to adequately assess at that level and below.


Learning outcomes and assessment criteriaIn order to pass this unit, the evidence that the learner presents for assessment needs to demonstrate that they can meet all the learning outcomes for the unit. The assessment criteria determine the standard required to achieve the unit.

On completion of this unit a learner should:

Learning outcomes Assessment criteria1. Use a spreadsheet to enter, edit andorganise numerical and other data

1.1 Identify what numerical and otherdata is needed and how the spreadsheet should be structured to meet needs.

1.2 Enter and edit numerical and otherdata accurately.

1.3 Store and retrieve spreadsheet fileseffectively, in line with local guidelinesand conventions where available.

2. Use appropriate formulas and tools tosummarise and display spreadsheetinformation

2.1 Identify how to summarise and display the required information.

2.2 Use functions and formulas to meetcalculation requirements.

2.3 Use spreadsheet tools and techniquesto summarise and display information.

3. Select and use appropriate tools andtechniques to present spreadsheetinformation effectively

3.1 Select and use appropriate tools andtechniques to format spreadsheet cells, rows and columns.

3.2 Identify which chart or graph type touse to display information.

3.3 Select and use appropriate tools andtechniques to generate, develop andformat charts and graphs.

3.4 Select and use appropriate pagelayout to present and print spreadsheet information.

3.5 Check spreadsheet information meetsneeds, using IT tools and making


corrections as appropriate.

Unit content

1 Use a spreadsheet to enter, edit and organise numerical and other data

Enter and edit spreadsheet data: numbers; text; rows and columns eg add, delete, cells eg enter data, edit, clear; replicate; find and replace

Spreadsheet structure: layout; components eg cells, rows, columns, charts

Store and retrieve: files eg create, name, open, save, save as, find

2 Use appropriate formulas and tools to summarise and display spreadsheet information

Analysis and interpretation of spreadsheet data: information eg totals, summary; order eg display, sorting; methods eg lists, tables, graphs, charts

Functions and formulas: simple formulas eg add, subtract, multiply, divide; design formulas; common functions eg Sum, Average, Round

3 Select and use appropriate tools and techniques to present spreadsheet information effectively

Formatting techniques for spreadsheet cells: eg numbers, currency, percentages, decimal places, font, alignment, borders, shading

Formatting techniques for rows and columns in spreadsheets and tables:eg height, width, borders, shading

Formatting techniques for charts and graphs: chart type eg pie chart, bar chart, single line graph; titles; axis titles; legend

Page layout: eg size, orientation, margins, page numbers, date and time

Check spreadsheet data: accuracy eg numbers, text, formulas, results; suitability eg charts, graphs


Delivery

A practical approach to delivery is essential for this unit. Delivery should focus on both the format and the content of software as well as enabling learners to develop their technical knowledge and skills by using software tools and techniques. Much of the assessment evidence is likely to be produced during this process, and centres should consider what other supporting product evidence can be collected. This unit can be taught in conjunction with other units eg Unit 101: Improving Productivity Using IT,Unit 112: IT Software Fundamentals, Unit 120: Design Software, Unit 121: Imaging Software, Unit 123: Desktop Publishing Software, Unit 124: Multimedia Software, Unit 125: Presentation Software, Unit 128: Website Software and Unit 129: Word Processing Software.

Assessment

An holistic approach to teaching is suggested for this unit. Tutors should provide learners with a variety of scenarios, from which one should be selected. These scenarios should be of interest to the learners and should not be too ambitious but should enable them to meet all the assessmentcriteria. It is envisaged that only scenario be required in order for learners to fulfil what is needed to pass the unit. Tutors should encourage learners to capture and record evidence as anongoing process at each stage of development. Assessment evidence will primarily come in the form of printed annotated screen shots, highlighting the formatting and layout of work, where necessary. Evidence can also come in the form of observations, class discussions, peer assessment andwritten work. To achieve a pass grade in this unit, learners will need to meet all of the assessment criteria.

Essential resourcesLearners will need access to relevant software (Microsoft Excel or similar, Microsoft Word or similar, packages compatible to allow combining of information). Further useful resources would include sets of example spreadsheets with notes and solutions provided on a drive accessible to learners outsidenormal lesson time to give opportunity for independent study. It is probable that learning resource centres will also have purchased self-teach packages for spreadsheets and again access to these out of lesson time would be valuable.

Indicative resource materialsTextbooksFrye C – Excel 2007 Step by Step (Step by Step (Microsoft)) – with CD(Microsoft Press, 2007) ISBN-10 073562304XHarvey G – Excel 2007 for Dummies (John Wiley and Sons, 2006)ISBN-10 0470037377Websiteswww.bized.co.uk/learn/sheets/sheet_guide.htmwww.openoffice.org/product/calc.html

© Learn About BTEC Level 1 IT Users Version 1 2012 of 14

http://www.openoffice.org/product/calc.html

http://www.bized.co.uk/learn/sheets/sheet_guide.htm

Assignment brief

This assignment comprises of all the assessment requirements for the completion of Unit 127: Spreadsheet Software.

This assignment is made up of 1 scenario and 7 tasks. You MUST complete all tasks successfully to gain full unit accreditation.

Time allowanceThe recommended time allowance for this assignment is 20 guided learning hours. This will give you approximately 3 hours per task.

Health and safety You are responsible for maintaining the safety of others as well as yourself. You are asked to work safely at all times. You will not be allowed to continue with this assignment if you compromise any of the Health and Safety requirements.

EvidenceAll evidence MUST be of your own work and signed by you and your assessor. It is good practice to submit research evidence to support your own work.

Evidence requirement list

Task Evidence Assessment Criteria1 Planning documents N1.1.1, N1.1.2, ICT1.1.1

2 Planning documents/Research (2 sources)/Survey N1.1.3, C1.2.1, ICT1.2.1

3 Planning documents N1.2.1, N1.2.2, ICT1.1.2, ICT1.2.3

4 Planning documents 1.1/1.2

5 Screen-prints/annotation Spreadsheet final copy Spreadsheet formula view

1.2/1.3/2.2/2.3/3.1 & N1.2.2

6 Screen-prints/annotation Spreadsheet draft copies/ evidence of checking

3.2/3.3/3.4/3.5, N1.3.1& ICT1.2.3

7 Report draft/Report final N1.3.2, C1.1.3, C1.3.1, ICT1.2.3, ICT1.3.1, ICT1.3.2


Assignment 127 - Unit 127 Spreadsheet SoftwareScenario

Working as an apprentice, you are starting to earn regular money. Your take home pay is £40 per week for the first 4 weeks, after which you get a 25% increase. Your outgoings are becoming more expensive every week and to help you manage your money, you have to plan, design and create a weekly budget spreadsheet. Show all your income and all your outgoings, calculate what you have spent and identify where you can make savings over a period of 6 months.

Task 1

Based on the scenario above create a plan describing how you will tackle your project. As part of your plan you must include a list of all your incomings (wages and any other money you receive) and outgoings (food, bills, rent, travel, clothing, mobile etc.).

Discuss your project with your tutor and confirm how you will tackle it.

Evidence Required (Spider gram etc...)N1.1.1, N1.1.2, ICT1.1.1

Task 2

You are required to gather source information to conduct your project, for this you must have ICT based information and non ICT based.

Source 1: Pay slip, Utility bills, internet/bank statement etc...Source 2: Survey Comparison (comparing results of income/expenditure with other users)Note: At least one of your sources must contain a table, chart, graph or diagram

After compiling your source information provide a brief statement explaining how your obtained information meets the purpose of your task.

N1.1.3, C1.2.1, ICT1.2.1


Task 3

Sketch a draft plan on A4 paper, showing how you are going to layout your budget using information from sources 1 & 2, what items you are likely to include, what calculations you will use in your budget and where. In your draft plans carry out your calculations showing your workings out.

As part of your calculations you must include:a) Amounts or sizesb) Scales or proportionc) Handling statistics

Suggested methods of appropriate calculations could be:a) Total income/expenditureb) Percentage of leisure expenditure compared to incomec) Conducting a survey and comparing your results

N1.2.1, N1.2.2, ICT1.1.2, ICT1.2.3

Task 4

Show what page layout (e.g. Portrait or Landscape) you require to best show your information and the page margins you require.

Ensure you have covered both points below in order to complete the task.

Identify what numbers and other data is required, and how the spreadsheet should be structured to meet needs. Spreadsheet structure: layout; components e.g. cells, rows, columns, charts.

Identify how to display the required information. Page layout: e.g. size, orientation, margins, page numbers, date and time. Functions and formulas: simple formulas e.g. add, subtract, multiply.

(1.1/1.2)


Task 5

Taking screen prints of any tools/techniques used create your budget spreadsheet, using your draft design as your plan. Enter all your information into the spreadsheet using different formatting techniques such as fill colour, gridlines and formulas/calculations to display your figures appropriately. Don’t forget to save your work at regular intervals.

Add a function/formula to automatically the percentage of leisure expenditure against income.

Add a column to the right of your spreadsheet to display the average income after 6 months. Format the cells to Currency (£) and to two decimal places.

Check your spreadsheet for accuracy using a spell check tool (don’t forget to take a screen print), print out a draft copy and check the calculations using a calculator.

Save your spreadsheet naming it “Budget Draft” into your Unit 127 folder.

Ensure you have covered all 5 points below in order to complete the task.

Enter and edit numerical and other data accurately: layout; components e.g. cells, rows, columns, charts.

Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available: files e.g. create, name, open, save, save as, find.

Use functions and formulas to meet calculation requirements: Functions and formulas: simple formulas e.g. add, subtract, multiply.

Use spreadsheet tools and techniques to summarise and display information: information eg totals, summary; order e.g. display, sorting; methods such as lists, tables, graphs, charts.Functions and formulas: simple formulas e.g. add, subtract, multiply, divide; design formulas; common functions such as Sum, Average, Round.

Select and use appropriate tools and techniques to format spreadsheet cells, rows and columns: e.g. numbers, currency, percentages, decimal places, font, alignment, borders, shading e.g. height, width, borders, shading.

(1.2/1.3/2.2/2.3/3.1)

N1.2.2


Task 6Reopen your “Budget Draft” spreadsheet and using graph paper provided by your Skills Tutor, create a series of graphs showing different ways to display your information and showing where your money is being spent. Choose one of your graphs and recreate it using Excel. Remember to include a graph title and relevant axis titles.

Add a header and footer to your spreadsheet that includes a title and your name and save it as “Spreadsheet Final”.

Print out your budget making sure it fits on one A4 sheet and all information is visible.

Print out a second version showing all the formula view, making sure it fits on one A4 sheet and all information is visible.

Print out your graph on a separate A4 sheet and write your name on the top.

Sign your work, annotate all of your screen prints and save to your “Unit 127” folder.

Ensure you have covered all 5 points below in order to complete the task.

Identify which chart or graph type to use to display information: chart type e.g. pie chart, bar chart, single line graph; titles; axis titles; legend.

Select and use appropriate tools and techniques to generate, develop and format charts and graphs: chart type e.g. pie chart, bar chart, single line graph; titles; axis titles; legend.

Select and use appropriate page layout to present and print spreadsheet information: e.g. size, orientation, margins, page numbers, date and time.

Check spreadsheet information meets needs, using IT tools and making corrections as appropriate: accuracy e.g. numbers, text, formulas, results; suitability for use in charts, graphs.

Check spreadsheet data using an on screen calculator to ensure your formulas are correct and evidence through screen prints

(32./3.3/3.4/3.5)

N1.3.1, ICT1.2.3


Task 7

Based on your project you will need to produce a report to present your findings to colleagues. Your report must include:

a) How you plannedb) Reasons why chose specific calculationsc) Describe what you found out from your resultsd) How you checked your answerse) A copy of the chart you produced and results explainedf) A suitable imagej) A tableh) A final conclusioni)You have checked your work for spelling and grammatical errors

Evidence Requirements: A draft copy of your report checked for spelling, grammar and accuracy of content and identifying

changes to be made. Evidence that your draft report has been emailed to your tutor/assessor as an attachment Evidence that you have read and received a reply from your tutor/assessor A final copy with suitable amendments and ICT based developments e.g. formatting, editing…..

N1.3.2, C1.1.3, C1.3.1, ICT1.2.2, ICT1.3.1, ICT1.3.2

End of assignment


OBSERVATION RECORD

Learner name:Qualification:Unit number & title:Description of activity undertakenEvidenceCriteria

Learner name:Learner signature: Date:Assessor name:Assessor signature: Date:


learnaboutwales.weebly.comlearnaboutwales.weebly.com/.../2/1/7/...v3_esw_l1.docx · web...

Documents