The Fruit and Veggie People Ltd: SS2 Sales Data Spreadsheet Analysis

This assessment focuses on utilizing spreadsheet software to analyze sales data for The Fruit and Veggie People Ltd. The assignment requires creating and managing a spreadsheet, including entering and editing data, and saving files. It involves analyzing data by calculating totals, using IF statements, sorting and filtering, and applying functions to calculate averages and total income. Furthermore, the assessment covers data manipulation techniques, formatting spreadsheets, and presenting data effectively through formatting cells, rows, columns, and worksheets. Students are expected to use tools like currency formatting, font changes, borders, and page layout adjustments. The assessment also includes creating a chart and linking data across worksheets. This assessment is designed to evaluate the student's understanding of spreadsheet software and their ability to apply these skills to real-world business scenarios, focusing on data analysis and presentation to inform sales strategies.

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Unit SS2: Spreadsheet software
You should use this file to complete your Assessment.
How to complete and send your assessment:
 Save a copy of this document, either onto your computer or a disk
 Work through your Assessment, remembering to save your work regularly
 When you’ve finished, print out a copy to keep for reference
 Then, go to www.vision2learn.com and send your completed Assessment to your tutor via
your My Study area – make sure it is clearly marked with your name, the course title and
the Unit and Assessment number.
Please note that this Assessment document has 13 pages and is made up of 6 Parts.
© Creating Careers Ltd, 2014. All rights reserved. Page 1 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Learning outcomes
Throughout this Assessment, you will meet a number of different learning outcomes and
assessment criteria. Take a look at the information below to see which criteria you will meet in
which Parts of the Assessment.
Learning outcomes & Assessment criteria met
Part 1:
1.1 Identify what numerical and other information is needed in the spreadsheet and how it should
be structured
1.2 Enter and edit numerical and other data accurately
1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions
where available
Part 2:
1.1 Identify what numerical and other information is needed in the spreadsheet and how it should
be structured
1.2 Enter and edit numerical and other data accurately
1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions
where available
2.2 Select and use a range of appropriate functions and formulas to meet calculation requirements
2.3 Use a range of tools and techniques to analyse and manipulate data to meet requirements
Part 3:
2.1 Identify which tools and techniques to use to analyse and manipulate data to meet
requirements
2.2 Select and use a range of appropriate functions and formulas to meet calculation requirements
2.3 Use a range of tools and techniques to analyse and manipulate data and meet requirements
Part 4:
1.3 Combine and link data across worksheets
1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions
where available
3.1 Plan how to present and format spreadsheet information effectively to meet needs
3.2 Select and use appropriate tools and techniques to format spreadsheet cells, rows, columns
and worksheets
3.4 Select and use appropriate page layout to present and print spreadsheets
Part 5:
1.3 Combine and link data across worksheets
1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions
where available
3.3 Select and format an appropriate chart or graph type to display selected information
Part 6:
1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions
where available
3.5 Check information meets needs, using spreadsheet tools and making corrections as necessary
3.6 Describe how to find errors in spreadsheet formulas
3.7 Respond appropriately to any problems with spreadsheets
© Creating Careers Ltd, 2014. All rights reserved. Page 2 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Assessment
Scenario
You work for the Sales department of The Fruit and Veggie People Ltd, a company that sells
organic, ethically sourced fruit and vegetable boxes. You are responsible for overseeing the
production of a spreadsheet that will be used to analyse the summer sales of different fruits.
You have been given some initial content for your spreadsheet by the Head of the Sales
department to edit and prepare before it is used to analyse future sales strategies.
You have been asked to edit and prepare the spreadsheet, analysing the data so that it can
be easily interpreted by the rest of the Sales team.
© Creating Careers Ltd, 2014. All rights reserved. Page 3 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Part 1: Creating and managing a spreadsheet
(1.1, 1.2, 1.4)
In this Part of the Assessment, you will need to apply the knowledge you have gained in
Session 1 and 2 of your SS2 Unit.
You will need to download and save the resource file spreadsheet_content from the Unit
Assessment Page to complete this part of the Assessment.
1. Save this assessment document as SS2 Assessment in an appropriate location. Take a
screenprint of the file saved in this location and paste it in the space below:
2. What types of information might be included in a spreadsheet? List at least 4 types of
information.
Excel spreadsheet is the best way to enter and analyse the numerical data accurately and
easily. We can include various types of information in a excel spreadsheet. It can be a
numbers, text, images, charts, and hyperlinks. The information is entered in the cells in the
spreadsheet.
© Creating Careers Ltd, 2014. All rights reserved. Page 4 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
3. When you are creating your spreadsheet for this Assessment, what Excel tools / features
can you use to make sure that it is structured effectively? Include at least two examples in
your answer.
To structure the spreadsheet effectively we should make sure that we choose the correct
excel template and design and layout.
The first tool is planning the work and the information to be entered into the spreadsheet and
then choose the correct template that fit for the purpose. There are several predefined
templates available to choose.
The second tool is design and layout. It is very important to design the spreadsheet effectively
and to select the correct layout so that the spreadsheet is clear and serves the purpose.
4. Open Excel – you are going to create a spreadsheet about the sales of different fruits. The
spreadsheet should contain the following data:
 Name of product
 Price of the product
 Sales of each fruit or vegetable in June
 Sales of each fruit or vegetable in July
 Sales of each fruit or vegetable in August
 Sales of each fruit or vegetable in September
Content for the spreadsheet can be found in the resource file spreadsheet_content.
Enter this data into the first worksheet of your spreadsheet. Make sure that your
spreadsheet contains appropriate column headings.
5. Rename the worksheet that you have used as: Sales Data.
When you are happy with your work, save your spreadsheet as SS2_sales_spreadsheet and
paste a screenprint of your finished spreadsheet in the space below. Make sure it shows all of
the Sales Data worksheet. Use more than one screenprint if necessary.
© Creating Careers Ltd, 2014. All rights reserved. Page 5 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Before After
Remember to save the work you’ve done on your SS2 Assessment.
© Creating Careers Ltd, 2014. All rights reserved. Page 6 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Part 2: Analysing data A
(1.1, 1.2, 1.4, 2.2, 2.3)
In this Part of the Assessment, you will need to apply the knowledge you have gained in
Session 3 of your SS2 Unit.
1. Open the document SS2_sales_spreadsheet that you created and saved in Part 1 of this
Assessment.
2. Create a new column called Total Sales. Calculate the total number of sales for June,
July, August and September for each product.
3. Add another new column to your spreadsheet, name it: ‘Reorder product?’
4. In this new column, create an IF statement to show whether new stock needs to be
ordered for each product.
If the Total Sales are More Than 2000, then YES, the product does need to be
reordered.
If the Total Sales are Less Than 2000, then NO, the product does not need to be
reordered.
5. Using the Sort & Filter tool, sort the spreadsheet by Name of product from A – Z, with
products beginning with ‘A’ at the top of the list, then products beginning with ‘B’ and so
on.
6. Identify and use an appropriate function to calculate the average number of sales in each
month. Carry out this function in your spreadsheet, and then type one of the formulas you
used below:
I will add another row in the spreadsheet with row heading: Average number of sales. To
calculate the average sales in the month June, I am going to use the formula
=Average(C6:17) and so on to the consecutive months.
© Creating Careers Ltd, 2014. All rights reserved. Page 7 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Finally, add another new column to your spreadsheet and name it Total Income. In this new
column, use the price and total sales information to calculate the total income for each
product.
When you are happy with your work, save the changes you have made to your
SS2_sales_spreadsheet and paste a screenprint of your finished spreadsheet in the space
below. Make sure it shows all of the Sales Data worksheet. Use more than one screenprint if
necessary.
Remember to save the work you’ve done on your SS2 Assessment.
© Creating Careers Ltd, 2014. All rights reserved. Page 8 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Part 3: Analysing data B
(2.1, 2.2, 2.3)
In this Part of the Assessment, you will need to apply knowledge you have gained in Session
3 of your SS2 Unit.
You will need to download and save the resource file fruitandveggie_data from the Unit
Assessment Page to complete this Part of the Assessment.
1. Open the resource file fruitandveggie_data. It contains further sales information that you
have been asked to analyse.
2. Using this spreadsheet to help you, complete the following table by identifying which tools /
techniques you would use to meet the requirements. The first task has been completed as
an example.
Task Choose ONE of these tools /
techniques to complete the
task
Which tool / technique
should you use?
Calculate the total number of
sales made from January –
May.
AVERAGE
SUM
MAX
SUM
Manipulate spreadsheet data
so that only products with
monthly sales of less than
£400 are displayed
SORT DATA
FILTER
LINE GRAPH
FILTER
Add a Total Sales figure for
each product in column G of
the spreadsheet
SUM FUNCTION
MAX FUNCTION
FILTER
SUM FUNCTION
Manipulate spreadsheet data
so that all products are
displayed in order of
popularity (most to least)
based on total sales
SORT DATA
SUB TOTALS
AVERAGE FUNCTION
SORT DATA
Remember to save the work you’ve done in this SS2 Assessment document.
© Creating Careers Ltd, 2014. All rights reserved. Page 9 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Part 4: Formatting and presenting data
(1.3, 1.4, 3.1, 3.2, 3.4)
In this Part of the Assessment, you will need to apply knowledge you have gained in Session
1 & 4 of your SS2 Unit.
1. You are going to start to format your spreadsheet. Before you begin, identify at least three
techniques that you could use when formatting your spreadsheet. How will this enhance
the appearance and meaning of your spreadsheet?
The three techniques that are used to format the spreadsheet are: 1. Numerical formatting,
2. Formatting cells, 3. Rows and columns formatting.
Numerical formatting is used to display the numerical data so that it can fit purpose. For
example, for money we use the currency symbol before the numerical data using currency
format.
In cell formatting we can change the style, colour, font, and size of the text in cell.
In the third technique we can wrap the text for clear display and can change the cell width and
height of the row and column. We can also freeze and hide the row or column of our choice.
3. Open the document SS2_sales_spreadsheet that you saved in Part 2 of this
Assessment.
4. Make sure all of the data in your spreadsheet appears to 2 decimal places.
5. Format the Price and Total Income columns of your spreadsheet so that they display as
currency (£).
6. Change the font of the Sales Data worksheet to Arial, size 11.
7. Apply bold formatting to all the text in Row 1 and Column A.
8. Change the colour of Row 1 to Red.
9. Adjust the column width so that all data and headings appear on one line.
10. Add appropriate borders and shading to your spreadsheet.
11. Use the Page Area tool to set the area that you would like to print.
12. Change the Page Orientation to landscape so that your data fits onto one page.
13. Add Page Numbers to your spreadsheet.
14. Copy the first 6 columns of your spreadsheet (showing the Average sales) and Paste it
as a Linked Object into a new worksheet.
15. Rename this new worksheet Average Sales.
© Creating Careers Ltd, 2014. All rights reserved. Page 10 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
When you are happy with your work, save the changes made to your
SS2_sales_spreadsheet and paste a screenprint of your finished spreadsheet in the space
below.
Make sure it shows both the Sales Data and Average Sales worksheets. Use more than one
screenprint if necessary.
Average sales
Remember to save the work you’ve done on your SS2 Assessment.
© Creating Careers Ltd, 2014. All rights reserved. Page 11 of 16
Amended July 2014

Essential IT Skills (Level 2)
Spreadsheet Software Level 2 (Credit value 4)
Assessment
Part 5: Charts and graphs
(1.3, 1.4, 3.3)
In this Part of the Assessment, you will need to apply knowledge you have gained in Session
1 & 5 of your SS2 Unit.
1. Open the document SS2_sales_spreadsheet that you saved at the end of Part 4 in this
Assessment.
2. Create a column chart to compare the Total Sales of each type of fruit.
3. Cut and paste the graph into a new worksheet. Rename this new worksheet Chart 1.
4. Open the worksheet Chart 1:
 Give the X-Axis (horizontal axis) the name Product
 Give the Y-Axis (vertical axis) the name Total Sales
 Give the chart a suitable title.
5. Finally, go to your Sales Data worksheet and type the text Sales Chart into a cell in
column A. Create a hyperlink using this text which links through to the Chart 1 worksheet.
When you are happy with your work, save the changes to your SS2_sales_spreadsheet and
paste a screenprint of your finished spreadsheet in the space below. Make sure it shows all of
your Chart 1 worksheet. Use more than one screenprint if necessary.
© Creating Careers Ltd, 2014. All rights reserved. Page 12 of 16
Amended July 2014