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.xIsx. 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 distributor's database also includes total sales by month, from January 2015 through March 2016. Complete the following: 1. Rename initial Data file provided Pacific.xIsx with the name 4-Ex3-Pacific-Region- YourName.xIsx. 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. 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 Figure e Example 1 StateProv KAIn Row Labels " Total Sales Number of Companies 0-99 $35,528,524.20 427 100-199 $40,974,059.41 606 6 200-295 $29,346,447.00 700 200-399 $12,048,537.87 599 Grand Total $118,097,568.48 2332 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 10. Save and close the 4-Ex3-Pacific-Region-YourName.xIsx workbook. 3