Ss2 Assesment

Spreadsheet Software Level 2 (Credit value 4) sagacity You should use this file to land up your Assessment. The first thing you exigency to do is save a copy of this document, either onto your computer or a disk Then pass water through your Assessment, remembering to save your work regularly When youve 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 piddle sure it is clearly marked with your evoke, the course title and the Unit and Assessment number.Please musical note that this assessment document has 9 pages and is made up of 6 berths. image 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 collaborate all of the learning outcomes for this Unit. By completing this Assessment, you will cover the following learning outcomes The learn er will 1. Use a spreadsheet to enter, edit and organise numeric and other data 2. Select and use appropriate legislations and data analysis tools to construe requirements 3.Select and use tools and techniques to present and format spreadsheet information You will also gather the following assessment criteria The learner can 1. 1 appoint what numerical and other information is needed in the spreadsheet and how it should be structured 1. 2 drop off and edit numerical and other data accurately 1. 3 Combine and link data across worksheets 1. 4 Store and retrieve spreadsheet files effectively, in pull 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 moves 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 format spreadsh eet information effectively to meet needs 3. 2 Select and use appropriate tools and techniques to format spreadsheet cells, rows, newspaper columns and worksheets 3. 3 Select and format an appropriate graph or graphical record image to display selected information 3. 4 Select and use appropriate page layout to present and print spreadsheet information 3. Check information meets needs, use spreadsheet tools and making department of 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 gross revenue of chocolate legal professions victimization a spreadsheet. Part A (B1. 1, B1. 2, B2. 1, B2. 3, B1. 4) 1. Open Excel to force a spreadsheet about chocolate close off sales. The spreadsheet should contain the following data Price of t he chocolate bars ledge stance in the shop gross revenue of separately chocolate bar for January Sales of each chocolate bar for February Use the information below to create the spreadsheet. deep brown 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 hatful 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 hatful Mars 60 Dairy Milk 42 Double Decker 45 Twix 36 Caramel 57 Snickers 114 Boost 35 Bounty 50 Aero 35 W ispa 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 worksheet in your workbook. Give this new worksheet the name Ch art2 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. resume 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 imbibe 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 isnt required. 4. Once you put one across completed your IF statement for each chocolate bar, save the changes you soak up 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 ofSUM, AVERAGE, COUNT SUM =SUM(C2C7) 350 sales for memory 2 in February Identify the most popular SUM, MIN, MAX MAX =MAX(B2B7) Snickers chocolate bar for Shop 1 in January Calculate the total number of3D reference using SUM, 3D SUM =SUM(Shop1Shop3 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(B5G5) 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 edition edition 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) 1. Open the spreadsheet you created in Pa rt A of this assessment, which should be saved with the filename SS2_chocolate_complete. Using the Save as unction, create another file and weaken 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 competent title A legend should be applied, if appropriate. 3. Open the worksheet Chart 2.Add a suitable chart title, legend, 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 d awdler 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 commensurate 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_co mplete 4. SS2_shopdata_complete When youre ready, go to www. vision2learn. com and send your files to your tutor via your My Study area.