Exercise 1: Sport Retailers You have organized the data for sportswear retailers into an Excel table. And you want to check some information of the Sport Retailer. Complete the following: 1 Rename initial Data file provided SportsRetailers.xlsx with the name 4-Ex1-Sportswear- Retailers-YourName.xlsx and save the file in the Chapter 7 folder. 2. Create a PivotTable report from the data in the SportsRetailers worksheet that list the number of companies that are in each zip code name the worksheet as PivotTable Sport Report-1. 3. Create a bar chart of the list and select only the first five zip codes. 4. Create a PivotTable report from the data in the SportsRetailers worksheet that list the number of companies that are in each city, name the worksheet as PivotTable Sport Report-2. 5. Create a bar chart of the list and select in Step 4. 6. Create a PivotTable report from the data in the SportsRetailers worksheet that list the number of Companies that are in for each State and city, name the worksheet as PivotTable Sport Report-3. Filter by states and select the state OR 7. Create a bar chart of the list and select in Step 7. 8. Add your name and Current Date at the end of the exercise 9. Save and close the 4-Ex1-Sportswear-Retailers-YourName.xlsx workbook. 1 WEEK # 4 - EXERCISES CHAPTER # 7 PIVOT TABLES

Exercise 2: Importing and Analyzing Data for Johnson Equipment Johnson Equipment, the medium-sized laboratory equipment manufacturing company where you work, is in the process of acquiring Sloan Manufacturing, a smaller equipment manufacturer in the same industry. Then, you will use Excel to organize and summarize the data. Complete the following: 1. Rename initial Data file provided Sloan.xlsx with the name 4-Ex2-Johnson-Sloan- YourName.xlsx and save it in the Chapter 7 folder. Rename Sheet1 as Sloan. 2. Create a PivotTable report from the data in the Sloan worksheet that list the Sum of Total Products (rename as Total Products) for each Category name the worksheet as PT- Report-1. Format the Total Products Column as Number with no decimals 3. Create a PivotTable report from the data in the Sloan worksheet that list the Maximum of January thru June for each Product and filter by Category name the worksheet as PT- Report-2. 4 Add a column with the maximum of the 1 Semester. Select only one Category to test it. Use an IF to avoid have value zeroes if no data. 5- Add the recommended bar chart 6. Add your name and Current Date at the end of the workbook. 7. Save and close the 4-Ex2-Johnson-Sloan-YourName.xlsx workbook. 2 WEEK # 4 - EXERCISES CHAPTER # 7 PIVOT TABLES

Exercise 3: Pacific Sales Barbara received some additional data from the distributor that she needs to analyze and give to the marketing group. The data is stored in excel file Pacific.xlsx. The data includes the company name, address, phone number, first order date, and last order date for each retailer located in the Pacific Northwest. There is a field that identifies the total number of orders placed by each retailer for TheZone products. The distributors database also includes total sales by month, from January 2015 through March 2016. Complete the following: 1. Rename initial Data file provided Pacific.xlsx with the name 4-Ex3-Pacific-Region- YourName.xlsx. 2. Create a PivotTable report from the data in the Pacific worksheet in a worksheet named PivotTable Report-1. 3. Add the TotalSales field to the VALUES area and format its values as currency. 4. Assuming a report date of 4/1/2016 in cell AC1, add the DaysSinceLastOrder field to the ROWS area. After adding the DaysSinceLastOrder field to the PivotTable report, group records in this field into groups of 100, starting with the value 0. 5. Add the StateProv field to the COLUMNS area, and then add the CompanyName field to the VALUES area to count the number of companies in each state. 6. Use the Value Field Settings dialog box to customize the field names in the PivotTable report as Total Sales and Number of Companies. Print the worksheet showing the PivotTable Report and task pane. 7. Drag the currently assigned fields between the FILTERS, COLUMNS, and ROWS areas to create the PivotTable shown in Figure 7.41. 8. Insert a PivotChart in the PivotTable Report worksheet. Use a Combo chart with Total Sales shown as a Clustered Column chart type and Number of Companies shown as a Line chart type on the secondary axis. (Hint: Select the Secondary Axis check box for the Number of Companies data series in the Insert Chart dialog box.) 9. Add your name and date at the end of the exercise 3 WEEK # 4 - EXERCISES CHAPTER # 7 PIVOT TABLES 10. Save and close the 4-Ex3-Pacific-Region-YourName.xlsx workbook.