My spreadsheet software assesment

Spreadsheet Software Level 2 (Credit value 4)

Assessment You should use this file to complete your Assessment.

• The first thing you need to do is save a copy of this document, either onto your computer or a disk
• Then work through your Assessment, remembering to save your work regularly
• When you’ve finished, print out a copy to keep for reference

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 9 pages and is made up of 6 Parts. Name: Alvaro Estrada Achieving your qualification In order to achieve your Level 2 ITQ Certificate in IT User Skills qualification, you will need to demonstrate to your tutor that you can meet all of the learning outcomes for this Unit. By completing this Assessment, you will cover the following learning outcomes: The learner will…

1. Use a spreadsheet to enter, edit, and organize numerical and other data
2. Select and use appropriate formulas and data analysis tools to meet requirements
3. Select and use tools and techniques to present and format spreadsheet information You will also satisfy the following assessment criteria: The learner can…
4. Identify what numerical and other information is needed in the spreadsheet and how it should be structured
5. Enter and edit numerical and other data accurately
6. Combine and link data across worksheets
7. Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
8. Identify which tools and techniques to use to analyze and manipulate data to meet requirements
9. Select and use a range of appropriate functions and formulas to meet calculation requirements
10. Use a range of tools and techniques to analyze and manipulate data to meet requirements
11. Plan how to present and format spreadsheet information effectively to meet needs
12. Select and use appropriate tools and techniques to format spreadsheet cells, rows, columns, and worksheets
13. Select and format an appropriate chart or graph type to display selected information
14. Select and use appropriate page layout to present and print spreadsheet information
15. Check information meets needs, using spreadsheet tools and making corrections as necessary
16. Describe how to find errors in a spreadsheet formula
17. Respond appropriately to any problems with spreadsheets The references in brackets below show you where these criteria are covered. Assessment Scenario You work in a newsagents’ shop. Your boss has asked you to analyze the sales of chocolate bars using a spreadsheet.

Part A (B1. 1, B1. 2, B2. 1, B2. 3, B1. 4)1. Open Excel to create a spreadsheet about chocolate bar sales. The spreadsheet should contain the following data:

• Price of the chocolate bars
• Shelf location in the shop
• Sales of each chocolate bar for January
• Sales of each chocolate bar for February Use the information below to create the spreadsheet.

Chocolate bar data Chocolate bar prices: Mars: 0. 58, Dairy Milk: 0. 65, Double Decker: 0. 66, Twix: 0. 53, Caramel: 0. 55, Snickers: 0. 57, Boost: 0. 71, Bounty: 0. 35, Aero: 0. 68, Wispa: 0. 65, Milky bar: 0. 40, Yorkie; 0. 70, Galaxy: 0. 67, Lion bar: 0. 65, Rolo: 0. 63. Shelf location in the shop Shelf one: Snickers, Mars, Galaxy, Yorkie Shelf two: Dairy Milk, Caramel, Boost, Rolo, Bounty.

Calculate the total number of sales for January and February for each chocolate bar and populate the Total sales column.  Sort the spreadsheet by Shelf location so that all of the Shelf 1 chocolate bars appear at the top of the spreadsheet, then the Shelf 2 bars, then the Shelf 3 bars. Save the spreadsheet with the name: SS2_chocolate_complete.

Part B (B1. 1, B1. 2, B1. 4)

1. Reopen SS2_chocolate_complete (the spreadsheet you created in Part A of this assessment), and create a column chart to compare the prices of the chocolate bars. Save the chart as a new worksheet in your workbook.

Give this new worksheet the name: Chart1

Create a line graph to compare the sales of all chocolate bars in January and February. Save the graph as a new worksheet in your workbook. Give this new worksheet the name: Chart2 Remember to save your work within the file: SS2_chocolate_complete Part C (B1. 4, B2. 1, B2. 3).

If the total sales of a chocolate bar are more than 100, then stock needs to be re-ordered. In the Re-order column that you have added to your spreadsheet, create an IF statement to show whether stock needs to be ordered for each chocolate bar. The results of your IF statement should display Yes if stock needs to be re-ordered or No if new stock isn’t required. Once you have completed your IF statement for each chocolate bar, save the changes you have made to your SS2_chocolate_complete spreadsheet. Part D (B1. 3, B2. 2) You will have downloaded some resource files to help you complete this part of the assessment. From the resource, files open the spreadsheet shop data.

Type the text Shop 2 in a cell in column A. Create a hyperlink using this text which links to the Shop 2 worksheet. Save the file with the name: SS2_shopdata_complete Part F (B3. 1, B3. 2, B3. 3, B3. 4, B3. 5, B3. 6, B3. 7). Open the spreadsheet you created in Part A of this assessment, which should be saved with the filename SS2_chocolate_complete. Using the Save as an unction, create another file and give it the file name SS2_chocolate2_complete.

• Apply bold formatting to all the text in Row 1
• Apply bold formatting to all the text in Column A
• Change the text color of Row 1 to red
• Adjust the size of Column F so that the text ‘ Total sales’ appears on one line
• Insert an outline border to your spreadsheet, and also an inside border to all cells 2. Open the worksheet Chart 1
• Give the x-axis the name chocolate bar type
• Give the y-axis the name customer price
• Change the chart type from Column to Bar
• Give this chart a suitable title
• A legend should be applied, if appropriate.

Add a suitable chart title, legend, x-axis label, and y-axis label to this chart. Check the formatting, spelling, and data accuracy of all your work, and respond appropriately to any problems. Describe how you did this. Using Print Preview to view the document. I adjusted the margins, with Page Layout (Margins), which were too wide, necessitating 2 pages to show chart 2. I checked the layout for the first chart and resized the second chart to fit the page. Next, I renamed sheet 1&2 to chart 1&2 on the spreadsheets ss2_chocolate_complete and on ss2_chocolate2_complete respectively. I did this by right-clicking on the sheet tab, using Rename I typed in the correct name (chart 1& chart 2).

In Review, I used the Spelling & Grammar option to check the whole documents & corrected mistakes. I checked all formulas manually to be sure they are working automatically. Remember to save the spreadsheet with the file name: SS2_chocolate2_complete Sending work Once you have completed all parts of this Assessment, you will need to send your work to your tutor so he or she can see the progress you have made.

Send the following files:

1. This document with completed sections.
2. SS2_chocolate_complete.
3. SS2_chocolate2_complete.
4. SS2_shopdata_complete.