Module 3-6 Spreadsheet Exercise Note that the weekly spreadsheet exercise is the last exercise designed to help you complete your Excel assessment. This week, we will focus on creating pivot tables. A pivot table is a powerful built-in data-analysis feature in Excel. It analyses, summarises, and manipulates data in large lists, worksheets or other collections. It is called a pivot table because fields can be moved within the table to create different type of summary lists, providing a “pivot”. Additional Case Study Information: The organisers of Santos Tour Down Under wanted to have separate spreadsheet of photos, stickers, and calendars sold. These photos, stickers and calendars contain highlights of the Tour Down Under event and photos of South Australia. It hired several sales representative to promote this product. The country, product, month and the amount of sales for each representative is recorded. Instructions: ⦁ Use the Excel sheet you created in Week 5. ⦁ Created a tab called Product Sales and copy the worksheet provided in your worksheet. (Product Sales.xlsX) ⦁ Your teacher will give you additional data you need to add to your worksheet. Each student will be given a different set of data. If you haven’t received it, please contact your teacher. ⦁ Read the Problem Solving Skills in Excel. Pdf. (pages 10-13) and Pivot Table Source Data Checklist. pdf ⦁ You can also watch this video for more details on how to create pivot tables: https://youtu.be/9NUjHBNWe9M ⦁ Save your spreadsheet . ⦁ Select the worksheet Product Sales. Click any cell in the list. ⦁ Insert a pivot table ⦁ In the pivot table builder – select the COUNTRY from the field name and drag it to the column. Select the PRODUCT from the field name and drag to the ROW and select the SALE form the field name and drag to the values. Your pivot table should look like this: ⦁ Create the chart using the data in the pivot table. ⦁ Your final worksheet should look like this: ⦁ Now create another pivot table and chart, showing the total amount of sale per sales representative. In the pivot table builder, the rows should have the sales representative, columns should have the month and the values contain the sum of the sales. Your final pivot able should look like this: NOTE THAT TO BE ABLE TO PRODUCE A CHART THAT LOOKS LIKE THIS, YOU HAVE TO SELECT THE CHART AND SWITCH ROW/COLUMN. ⦁ Save your Excel workbook. ⦁ Document either in Excel or in a Word document the step you undertook in producing the Spreadsheet. Answer the questions provided to your by your teacher. ⦁ Save your document. ⦁ BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive. ⦁ YOU SHOULD NOW HAVE THE FINAL EXCEL SPREADSHEET ASSESSMENT THAT YOU HAVE TO SUBMIT ON WEEK 7. Attach the Word Document containing the answers to the questions and steps you undertook in producing the spreadsheet.