Learner achievement

Name of Centre:
Learner achievement (please circle) NB: All learner achievement is provisional until confirmed at the AVA Awards Board.
Level Achieved: L3
Achievement P M D
Resubmission? Y N

Title of Access to HE Diploma:
Unit title: Spreadsheets Unit code: CBA790
Learner: Tutor/Assessor:
Description of Assignment:
In this assignment you will demonstrate your skill in creating a complex spreadsheet model for a given scenario. Your worksheets and printouts should be clear and correctly formatted i.e. the display and alignment of text, formatting of currency, labelling of charts/graphs including titles and axis and use of colours. For each task you are required to produce screenshots to provide evidence of the assessment criteria. In addition, you should provide a typed explanation of how you carried out each task.
Scenario
Bosbourne Stationery Supplies is a new company selling a range of office supplies and stationery. The company requires a spreadsheet to administer its sales. You will need separate worksheets to record:
1. Customers
2. Products
3. Sales
4. Revenue forecasting

The customer worksheet should contain the following:
• Customer unique reference
• Name
• Address
• Contact phone number
• Email

The products worksheet should contain the following:
• Product code
• Category (e.g. Labels and tags, filing, desktop stationery etc.)
• Product description
• Unit description (e.g. weight, box of 10 etc.)
• Supplier reference
• Name of supplier
• Cost price per unit
• Retail mark-up percentage
The sales worksheet should contain the following:
• Date
• Customer reference
• Customer
• Product reference
• Product
• Supplier reference
• Supplier name
• Quantity sold
• Unit price ex VAT (Cost price + retail mark-up percentage)
• VAT @20%
• Total unit price including VAT
• Total Price (Quantity * Total unit price including VAT)
• Salesperson reference
• Salesperson
• Commission rate per unit sale @2%

Task 1
1. Create the customer, products and sales worksheet. The customer, product and supplier should be automatically entered using the LOOKUP function. (AC 2.2, AC 2.4)
2. Enter data for each worksheet:
• Customers: Create 20 customers.
• Products: Create 6 different products for the worksheet.
• Sales: Create a minimum of 20 sales for each month. Create 6 salespersons
3. Use names in referring to cells and ranges (AC 2.3)
4. Split the products table to facilitate viewing of Sales commission. (AC 1.1)
5. Enter data by filling data series (AC 1.2)
6. In the products worksheet hide the cost price and the retail mark-up percentage. In the
Sales worksheet hide the first quarter (January to March ) (AC 1.3)
7. Protect the cost price per unit cells.(AC 1.4)
8. Sort the Sales worksheet by Supplier, Product and cost price. (AC 1.5)
9. Print the sorted spreadsheet (AC 5.2)
10. Use cell references to create a complex formula with brackets. (AC 2.1)

Task 2
1. Create a revenue forecast for the next six months based on the sales quarter October to December. Analyse the previous results of the sales team to forecast future performance and revenue. Create at least one replicated formula that uses at least one logical function (e.g. IF, COUNTIF, AVERAGEIF, COUNTIF, AND, OR, NOT) (AC 2.2)
2. It is expected that all supplier costs will increase by 3.5% in the next financial year resulting in a predicted 4% decrease in sales. Create a separate sales forecast to show this. (AC 2.4)
3. Record and save macro to produce a sales invoice for a customer. (AC 3.1, AC 3.2)
4. Assign the macro to a shortcut key combination or a button. (AC 3.4)
5. Apply the macro (AC 3.3)
6. Update the workbook by importing information from a variety of sources (e.g. database, text file, internet) using a range of methods (e.g. file/open, copy and paste, OLE, other linking methods). (AC 4.1)
7. Export information to a range of destinations, including choosing appropriate and justified file formats, using appropriate file names and organising the exported data in the file system. (AC 4.2)

Remember you must:
• Proof-read your work making sure you input text and numerical information without error.
(AC 5.1)
• Print each spreadsheet in standard view and again with ‘Show Formulas’. (AC 5.3)
• Provide a typed explanation of how you carried out the task.
Date set: 22nd May 2017 Date for draft submission
(if applicable): Date for final
submission: 6th June 2017

Extension date
(if agreed): Signed by Tutor/Assessor to agree extension:
Date
submitted:
Internal Moderation: Yes / No
Date: Signed by internal moderator: ON TIME / LATE
Learner declaration:
? The explanations and evaluations in this work have been developed and written by me.
? I have not submitted material copied from the Internet, text books or other sources in place of my own thinking and writing.
? When I have referred to the work of others I have done so to discuss, comment on or argue their ideas.
? I have kept quotation and paraphrasing to an absolute minimum and only to support points I have made.
? I understand that referencing the names of authors whose ideas I have used without including my own interpretation of those ideas, does not meet the assessment criteria and cannot attract the Pass, Merit or Distinction grades.
? I have not copied the work of my peers.
Learner comments: (please use this space to comment on any aspect of the assignment when handing in your work)
Signature: Date:
TO THE LEARNER: Please attach this assignment brief to any written work you are handing in for assessment, or submit the brief as instructed.
YOUR WORK CANNOT BE ASSESSED UNLESS YOU HAVE SIGNED AND SUBMITTED THIS FORM

Level 3
Unit title: Spreadsheets
This assignment addresses the following Assessment Criteria from the unit:
AC The learner can:
1.1 Split a table to facilitate viewing.
1.2 Enter data by filling data series.
1.3 Hide rows and columns.
1.4 Protect specified cells.
1.5 Sort data by column to include more than one column.
2.1 Use cell references to create a complex formula with brackets.
2.2 Create at least one replicated formula that uses at least one logical function (e.g. IF, COUNTIF, AVERAGEIF, COUNTIF, AND, OR, NOT) Create formulae with a reference function (e.g. LOOKUP).
2.3 Use names in referring to cells and ranges.
2.4 Create links between different spreadsheets.
3.1 Record or create a simple spreadsheet macro.
3.2 Save a macro.
3.3 Use a macro
3.4 Bind a macro to a shortcut key combination or a button.
4.1 Import information from a variety of sources (e.g. database, text file, internet) using a range of methods (e.g. file/open, copy and paste, OLE, other linking methods).
4.2 Export information to a range of destinations, including choosing appropriate and justified file formats, using appropriate file names and organising the exported data in the file system.
5.1 Input text and numerical information without error.
5.2 Print a specified section of a spreadsheet.
5.3 Print spreadsheet numbers and formulae.

Level 3 units only:
Learners achieve a Pass if they meet all Level 3 Assessment Criteria for a unit. You will achieve a Merit or Distinction by meeting the following Grade Descriptors. Your tutor will give you feedback for all three grades.
Grade Descriptor Merit Distinction
GD3: Application of Skills The student, student’s work or performance: generally selects appropriate methods and applies appropriate techniques with very good levels of precision and accuracy. The student, student’s work or performance: consistently selects appropriate methods and applies appropriate techniques with excellent levels of precision and accuracy.
GD7: Quality The student, student’s work or performance: taken as a whole demonstrates a very good response to the demands of the brief/assignment. The student, student’s work or performance: taken as a whole demonstrates an excellent response to the demands of the brief/assignment.
Grade Guidance: Learners must carefully read the guidance below which is linked to the components above
MERIT:
GD3: Application of skills
To achieve a Merit, you will demonstrate a very good understanding of spreadsheet design layout and will be able to select and use appropriate tools and functions to create spreadsheets which meet the assignment brief. Your explanations will show that you can analyse data in a specific context by the application of appropriate spreadsheet techniques and methods.

GD7: Quality
Overall you will produce spreadsheets which are accurate, relevant and clearly linked to the tasks set. You are able to select and use appropriate formatting to display spreadsheets.
DISTINCTION:
GD3: Application of skills
To achieve a Distinction, you will demonstrate an in-depth understanding of spreadsheet design layout and are able to select and use appropriate tools and functions to create spreadsheets which meet the assignment brief. Your explanations are detailed will show that you have an excellent understanding of how to analyse data in a specific context by the application of appropriate spreadsheet techniques and methods.

GD7: Quality
Overall your work demonstrates accuracy and attention to detail. Your explanations of the techniques and methods used are detailed and justified. Formatting has been used creatively to enhance the layout of the spreadsheets making them clear for the user to use and interpret data.
Part A: Feedback on credit level
AC Credit achieved (L3) Location of evidence Tutor/Assessor comments on assessment criteria
(you could also indicate on the work itself where each AC is met)
1.1 Task 1

1.2 Task 1
1.3 Task 1
1.4 Task 1
1.5 Task 1
2.1 Task 1
2.2 Task 2
2.3 Task 1
2.4 Task 1
3.1 Task 2
3.2 Task 2
3.3 Task 2
3.4 Task 2
4.1 Task 2
4.2 Task 2
5.1 Task 1, Task 2
5.2 Task 1
5.3 Task 1, Task 2
Level achieved Tutor/Assessor’s signature: Date:

Resubmission (if applicable) If any of the assessment criteria for this assignment have not been met at Level 3, a resubmission may be permitted. Resubmission must follow the QAA guidelines and be permitted only once.
Requirements for resubmission/new Task set:

Date Set: Date due: Date Submitted:

Feedback on resubmission:

Level achieved
after resubmission: Tutor/Assessor’s signature: Date:

Part B: Feedback on grading (Applicable only if all assessment criteria achieved at Level 3)
Grade Descriptor Tutor/Assessor comments against grade descriptors Grade indicator
(P/M/D)
Please enter the final grade on page 1 based on this grade profile e.g. PPM=P
GD3: Application of Skills
GD7: Quality
Tutor/Assessor’s reason for final grade decision (if applicable):

Areas for development (how will the learner be able to use and improve on what they have learnt on this unit and the skills that they have used in their further studies?)

Customer Details to be processed into a spreadsheet workbook:

1. Ink Print (Account No IP320) 39 Highbury Rise, Barton Stacey, Andover, SP21 5HR

2. Thorne Estate Agents (Account No IP321) Plumstead Way, Fontwell, West Sussex, BN19 1TW

3. Cadbury’s (Account No IP322) Long Lane, Newbury, Berks, RG20 6TX
4. Coco |Powder (Account No IP727) 180 Milton Road, Havant, Hampshire, PO14 2DB

5. Unigate Dairies(Account No IP728) 85 Streadfast Place, Fareham, Hampshire, PO14 2DB

6. Sugar and Spice (Account No IP729) Leigh Road, Dorking, Surrey, RH6 1QW

7. Staples (Account No IP730) Whitehorse Lane, Thornton Heath, Croydon, CR0 8NN

8. Citroens Motors (Account No CIP004) 39 High Bridge Road, Highbury, Kent. HK11 5QL.

9. Land Rovers specialist (Account No IP085) Newton Way, 2 Newton Road. Mayfield, London, NW5 6GG

10. Mini Sports(Account No IP010) 5 Elms End, South end, Maidstone, Kent,
MK4 4 CC

11. Ryman’s (Account No IP555) Streatham High Street, Streatham, London, SW16 8ME

12. British Gas (Account No IP056) Po Box 13, London, WC1 2QQ

13. London Electricity (Account No IP035) Keen Street, London, EC1 6TT

14. Rulers and Rulers (Account No IP320) 39 Mearway, Guildford, Kent, KT9 8WW

15. Wright & Son (Account No IP321) 115 Sidney Way, Cowden Road Kent, KT11 5AA

16. Staples (Account No IP320) 25 Ridgway Road, Hampton, London, SW1A 1PP
17. Paper Mate (Account No IP001) Carpenters Corner, Cambridge, Cambridgeshire , CA10 6QP

18. Tesco (Account No IP002) 288 High Street, Newbury, Kent, KT2 6LL

19. Unigate Diaries(Account No IP009) Leigham Court Road, Streatham, London, SW9 8AA

20. Computer World (Account No IP022 ) Surry Gates, 11 Surrey Road, Surrey SU2 4FJ
The products worksheet should contain the following:

1. Computer paper 3300 – A4 Printing Paper £4.99 per box of 100
2. Office stapler 3301- Stapler £1.09 each
3. Black Pens 3302 – Biro £2.99 per box of 10
4. A4 paper 3303 – A4 Lined writing pads £3.49 per batch of 5
5. Blue Pens 3304 – Biro £2.99 per box of 10
6. Red Pens 3305 – Biro £2.99 per box of 10
Suppliers Details:
Supplier Details Product Code Amount (EX VAT) VAT Total
Estella Barker: 101 Mark Street London. E1 5PP

Ernst Wolff : 896 Unit 1, Broadsquare Road. Birmingham B66 5NO

Mapari, Vaja & Shah: 2552 Clapton High Street. Edinburgh. EH7 0LD

Dunning Bradshaw: 52 Main Avenue. London. SW6 3NL

20 Boxes of printing paper

100 Staplers
55 A4 lined Writing Pads
1000 Red Pens (Biro)
Customer Details Product Code Amount (EX VAT) VAT Total
Ink Print (Account No IP320) 39 Highbury Rise, Barton Stacey, Andover, SP21 5HR

Thorne Estate Agents (Account No IP321) Plumstead Way, Fontwell, West Sussex, BN19 1TW

Cadbury’s (Account No IP322) Long Lane, Newbury, Berks, RG20 6TX

Coco |Powder (Account No IP727) 180 Milton Road, Havant, Hampshire, PO14 2DB

Unigate Dairies(Account No IP728) 85 Streadfast Place, Fareham, Hampshire, PO14 2DB

Sugar and Spice (Account No IP729) Leigh Road, Dorking, Surrey, RH6 1QW

Staples (Account No IP730) Whitehorse Lane, Thornton Heath, Croydon, CR0 8NN

Citroens Motors (Account No CIP004) 39 High Bridge Road, Highbury, Kent. HK11 5QL.

Land Rovers specialist (Account No IP085) Newton Way, 2 Newton Road. Mayfield, London, NW5 6GG

Mini Sports(Account No IP010) 5 Elms End, South end, Maidstone, Kent,
MK4 4 CC

Ryman’s (Account No IP555) Streatham High Street, Streatham, London, SW16 8ME

British Gas (Account No IP056) Po Box 13, London, WC1 2QQ

London Electricity (Account No IP035) Keen Street, London, EC1 6TT

Rulers and Rulers (Account No IP320) 39 Mearway, Guildford, Kent, KT9 8WW

Wright & Son (Account No IP321) 115 Sidney Way, Cowden Road Kent, KT11 5AA

Staples (Account No IP320) 25 Ridgway Road, Hampton, London, SW1A 1PP
Paper Mate (Account No IP001) Carpenters Corner, Cambridge, Cambridgeshire , CA10 6QP

Tesco (Account No IP002) 288 High Street, Newbury, Kent, KT2 6LL

Unigate Diaries(Account No IP009) Leigham Court Road, Streatham, London, SW9 8AA

Computer World (Account No IP022 ) Surry Gates, 11 Surrey Road, Surrey SU2 4FJ
Computer Paper (50 Boxes)

Stapler ( 150)
Black Biro (1050)
A4 Paper ( 600 batches)
Blue Pens ( 50 boxes)
Red Pens ( 120 boxes)
Computer Paper (109 boxes)

Stapler ( 225)
Black Biro (850)
A4 Paper ( 75 batches)

Blue Biro ( 20 boxes)
Red Pens (60 boxes)
A4 Printing papeer (230 boxes)

Staplers (89)
Black Pens ( 81 boxes)
A4 paper ( 45 batches)

Blue Pens ( 70 boxes)
Red Pens (33 boxes)
Black Pens (23 boxes)
A4 paper ( 29 batches)