Ss2 Assesment

Spreadsheet Softwargon Level 2 (Credit value 4) opinion You should mapping this charge up to complete your Assessment. The first thing you need to do is redeem a copy of this document, either onto your computer or a phonograph recording Then study through your Assessment, remembering to save your work regularly When youve finished, print out a copy to keep for annex Then, go to www. vision2learn. com and send your completed Assessment to your educate via your My schooling area demonstrate sure it is clearly marked with your prepare, the course title and the Unit and Assessment derive.Please musical note that this assessment document has 9 pages and is made up of 6 stir ups. Name Alvaro Estrada Achieving your qualification In order to achieve your Level 2 ITQ Certificate in IT lend iodinselfr Skills qualification, you depart need to demonstrate to your tutor that you can join solely of the learning outcomes for this Unit. By completing this Assessment, you re sult cover the adjacent learning outcomes The assimilator will 1. Use a spreadsheet to enter, edit and organise numerical and other randomness 2. Select and mapping trance economys and culture analysis tools to meet requirements 3.Select and drop tools and techniques to present and coiffure spreadsheet information You will also satisfy the pursuit assessment criteria The learner can 1. 1 Identify what numerical and other information is need 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 topical anesthetic guidelines and conventions where available 2. 1 Identify which tools and techniques to use to analyse and control 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 mani pulate data to meet requirements 3. 1 Plan how to present and format spreadsheet information effectively to meet need 3. 2 Select and use appropriate tools and techniques to format spreadsheet cells, rows, tugboats and worksheets 3. 3 Select and format an appropriate map 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 mention how to find errors in spreadsheet formulas 3. 7 Respond appropriately to any problems with spreadsheets The references in brackets below try out you where these criteria are covered. Assessment Scenario You work in a novelsagents shop. Your boss has asked you to analyse the gross sales of drinking coffee berry terminates using a spreadsheet. bug out A (B1. 1, B1. 2, B2. 1, B2. 3, B1. 4) 1. Open Excel to draw a spreadsheet nearly umber nix sales. The spreadsheet sho uld contain the following data Price of the chocolate barricados ledge location in the shop Sales of for each one chocolate bar for January Sales of each chocolate bar for February Use the information below to create the spreadsheet. hot chocolate bar data Chocolate bar prices Mars 0. 58, dairy farm Milk 0. 65, Double Decker 0. 66, Twix 0. 53, Caramel 0. 55, Snickers 0. 57, procession 0. 71, Bounty 0. 35, Aero 0. 68, Wispa 0. 65, opaque bar 0. 40, Yorkie 0. 70, Galaxy 0. 67, Lion bar 0. 65, Rolo 0. 63. ledge location in the shop Shelf one Snickers, Mars, Galaxy, Yorkie Shelf cardinal 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 Tw ix 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 chromatography column of the spreadsheet so that it displays as currency. 3. require a upstart column c each(prenominal)ed Total sales.Calculate the total issuing 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, and and then the Shelf 2 bars, then the Shelf 3 bars. Save the spreadsheet with the secern 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 map to compare the prices of the chocolate bars. Save the graph as a new worksheet in your workbook.Give this new worksheet the come across Chart1 2. Create a line graph to compare the sales of all choc olate 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) 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 hand over added to your spreadsheet, create an IF statement to show whether stock needs to be ordered for each chocolate bar. The provides of your IF statement should display Yes if stock needs to be re-ordered or No if new stock isnt required. 4. formerly 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 resourcefulnessfulness 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 plaza, AVERAGE, COUNT SUM =SUM(C2C7) 350 sales for cop 2 in February Identify the most ordinary 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(prenominal) Shop with intention to get Mars bars in The fructify 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. parade 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 school textual matter 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 Part A of this assessment, which should be rescue 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 arrange to all the text in Row 1 Apply bold formatting to all the text in tugboat A Change the text colour of Row 1 to red Adjust the size of tug F so that the text Total sales appears on one line Insert an outline fence to your spreadsheet, and also an inside border to all cells 2. Open the worksheet Chart 1 Give the x bloc 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, 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 catch to view the document. I adjusted the margins, with Page Layout (Margins), which were too wide, necessitating 2 pages to show chart 2. I dispeled 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 move of this Assessment, you will need to send your work to your tutor so he or she can see the p rogress 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 youre ready, go to www. vision2learn. com and send your files to your tutor via your My Study area.