days since each company has placed an order with TheZone in a column named Steps To Success: Level 2 Barbara received some additional data from the distributor that she needs to analyze and give to the marketing group. The data is stored in an Access database. 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 The Zone products. The distributor's database also includes total sales by month, from January 2015 through March 2016. Complete the following: 6. Assuming a report date of 4/1/2016 in cell ACI, calculate the number of 1. Create a new workbook, and save it as Pacific Sales.xlsx in the Chapter 7 folder. 2. Import the data contained in the Pacific Region table of the Pacific.accdb Access database located in the Chapter 7 folder into a worksheet named Pacific Region. 3. Sort the data first by state/Province, then by city, and then by company name in ascending order. 4. Calculate the total sales since January 2015 for each company in a column labeled TotalSales. Format the result and all of the sales data from January 2015 through March 2016 as currency. 5. Hide the original columns containing financial information to simplify the worksheets appearance. DaysSinceLastOrder. Format the values as a Number with zero decimal places. 472 Organizing Data for Effective Analysis Chapter 7 7. Calculate the number of fractional years that each company has been ordering from The Zone in a column named Relationship Years. Format the values as a Number with two decimal places. 8. Create a Pivot Table report from the data in the Pacific Region worksheet in a work- sheet named Pivot Table Report 9. Add the TotalSales field to the VALUES area and format its values as currency. 10. Add the DaysSince Last Order field to the ROWS area. After adding the DaysSinceLastOrder field to the Pivot Table report, group records in this field into groups of 100, starting with the value 0. 11. Add the State Prov field to the COLUMNS area, and then add the Company Name field to the VALUES area to count the number of companies in each state. 12. Use the Value Field Settings dialog box to customize the field names in the Pivot Table report as Total Sales and Number of Companies. Print the worksheet showing the Pivot Table Report and task pane. 13. Drag the currently assigned fields between the FILTERS, COLUMNS, and ROWS areas to create the Pivot Table shown in Figure 7.41. 7 Level 3 days since each company has placed an order with TheZone in a column named Steps To Success: Level 2 Barbara received some additional data from the distributor that she needs to analyze and give to the marketing group. The data is stored in an Access database. 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 The Zone products. The distributor's database also includes total sales by month, from January 2015 through March 2016. Complete the following: 6. Assuming a report date of 4/1/2016 in cell ACI, calculate the number of 1. Create a new workbook, and save it as Pacific Sales.xlsx in the Chapter 7 folder. 2. Import the data contained in the Pacific Region table of the Pacific.accdb Access database located in the Chapter 7 folder into a worksheet named Pacific Region. 3. Sort the data first by state/Province, then by city, and then by company name in ascending order. 4. Calculate the total sales since January 2015 for each company in a column labeled TotalSales. Format the result and all of the sales data from January 2015 through March 2016 as currency. 5. Hide the original columns containing financial information to simplify the worksheets appearance. DaysSinceLastOrder. Format the values as a Number with zero decimal places. 472 Organizing Data for Effective Analysis Chapter 7 7. Calculate the number of fractional years that each company has been ordering from The Zone in a column named Relationship Years. Format the values as a Number with two decimal places. 8. Create a Pivot Table report from the data in the Pacific Region worksheet in a work- sheet named Pivot Table Report 9. Add the TotalSales field to the VALUES area and format its values as currency. 10. Add the DaysSince Last Order field to the ROWS area. After adding the DaysSinceLastOrder field to the Pivot Table report, group records in this field into groups of 100, starting with the value 0. 11. Add the State Prov field to the COLUMNS area, and then add the Company Name field to the VALUES area to count the number of companies in each state. 12. Use the Value Field Settings dialog box to customize the field names in the Pivot Table report as Total Sales and Number of Companies. Print the worksheet showing the Pivot Table Report and task pane. 13. Drag the currently assigned fields between the FILTERS, COLUMNS, and ROWS areas to create the Pivot Table shown in Figure 7.41. 7 Level 3
