Ss2 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 †¢ 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 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 organise 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†¦ 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. 3 Combine and link data across worksheets 1. 4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available 2. 1 Identify which tools and techniques to use to analyse and manipulate data to meet requirements 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 3. 1 Plan how to present and fo rmat spreadsheet information effectively to meet needs 3. 2 Select and use appropriate tools and techniques to format spreadsheet cells, rows, columns and worksheets 3. 3 Select and format an appropriate chart or graph type to display selected information 3. 4 Select and use appropriate page layout to present and print spreadsheet information 3. 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 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 analyse 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, BountyShelf three: Twix, Milky bar, Aero, Double Decker, Lion Bar, Wispa January chocolate bar sales volume: |Mars |54 | |Dairy Milk |36 | |Double Decker |30 | |Twix |24 | |Caramel |48 | |Snickers |108 | |Boost |22 | |Bounty |51 | |Aero |32 | |Wispa |37 | Milky bar |29 | |Yorkie |55 | |Galaxy |60 | |Lion bar |37 | |Rolo |43 | February chocolate bar sales volume: |Mars |60 | |Dairy Milk |42 | |Double Decker |45 | |Twix |36 | |Caramel |57 | |Snickers |114 | Boost |35 | |Bounty |50 | |Aero |35 | |Wispa |48 | |Milky bar |62 | |Yorkie |70 | |Galaxy |55 | |Lion bar |40 | |Rolo |46 | 2. Format the price column of the spreadsheet so that it displays as currency. 3. Create a new column called Total sales.Calculate the total number of sales for January and February for each chocolate bar and populate the Total sales column. 4. 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 2. Create a line graph to compare the sales of all chocolate bars in January and February. Save the graph as a new w orksheet 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) 1. Reopen SS2_chocolate_complete (the spreadsheet you created in Part A and added charts to in Part B of this assessment). 2. Add a new column to this spreadsheet. You should add this column after the Total sales column and name it Re-order. 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. 4. 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 shopdata. 2. Complete the following table. For each task you must complete the action in Excel, and then complete the table with which functions/formula you used. The first task has been completed as an example. |Task |Use one of these three |Which |Type out the formula / one of the |What is the answer | | |functions to complete the |function did |formulas you used |to the initial task? | | |task |you use? | | |Calculate the total number of|SUM, AVERAGE, COUNT |SUM |=SUM(C2:C7) |350 | |sales for Shop 2 in February | | | | | | | | | | | | | | | | | | | | | | | |Identify the most popular |SUM, MIN, MAX |MAX |=MAX(B2:B7) |Snickers | |chocolate bar for Shop 1 in | | | | | |January | | | | | |Calculate the total number of|3D reference using SUM, 3D |SUM |=SUM(Shop1:Shop3! J5) | | |Mars bars sold by all shops |reference using COUNTIF, 3D | | | | | |reference using MIN / MAX. |*see note | | | | | | | Total=1687 | Remember to save your work in this document. *note: To do this task I used Sort AZ in every Shop with intention to get Mars bars in The position row 5, in every shop I did =SUM(B5:G5) and every result in J5. After I did SUM Part E (B1. 3, B2. 1, B2. 3, B1. 4) You will have downloaded some resource files to help you complete this part of the assessment. 1. From the resource files open the document shopdata. 2. For each of the three Shop worksheets, add a new column after June and name it Average. 3.Work out the average number of sales for each chocolate bar in Shop 2. Display the results in the column Average. 4. Sort the Shop 2 worksheet so that the chocolate bar with the least average sales appears at the top, and the chocolate bar with the most average sales appears at the bottom of the spreadsheet. 5. Go to the Shop 1 worksheet. 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 n ame: SS2_shopdata_complete Part F (B3. 1, B3. 2, B3. 3, B3. 4, B3. 5, B3. 6, B3. 7) 1. 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 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 colour 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. 3. Open the worksheet Chart 2.Add a suitable chart title, le gend, x-axis label and y-axis label to this chart. 4. 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 When you’re ready, go to www. vision2learn. com and send your files to your tutor via your My Study area.