data and pivot tables

Data Analysis Using Excel Pivot Tables Falls Department Stores, with corporate headquarters in Portland, Oregon, operates department stores in midsize towns in selected northwestern areas. Although the organization maintains a large computer system for its accounting operations, the sales department often downloads data to complete additional analysis of its operations. Daniel Partner, analyst for the corporate sales department, regularly downloads data by territories and product areas including automotive, electronics, garden centers, and sporting goods. He often presents reports based on his analysis of sales by product areas and territory, best and worst performing product group-periods, and total sales for certain regions and product groups. He asks you to help him compile and summarize the data. Complete the following: 1. Download the Falls Department Stores workbook and save the workbook as YourNameAssignment 4 in your disk. 2. In the Documentation sheet, enter the date and your name then save the worksheet. 3. Switch to Q3 worksheet, create an Excel table. Rename the Table as ProductSales. Format the Sales column in the Currency number format with no decimal places. Save the worksheet. 4. Switch to Q4 worksheet and sort the table in ascending order by Territories, then by Product Group, then by Year, and then by Month. Month should be sorted in Jan, Feb, Mar, ... order, not alphabetically. Save the worksheet. 5. Switch to Q5 worksheet. Display records for Automotive and Electronic products in 2010, excluding sales in Vancouver. Sort this data by Sales in descending order. Add a Total row and calculate the average sales for the filtered data. Change the label in the Total row to Average. Save the worksheet. 6. Switch to Q6 worksheet. Display subtotals for sales (Sum) by Territory. Save the worksheet. 7. Switch to Q7 worksheet. Display total sales for sporting products, in Vancouver, during 2010. Save the worksheet. 8. Switch to Q8 worksheet. Create a PivotTable similar to the one shown in following figure, displaying percentage of sales by Product Group, Territories, and Year. Set grand totals on for columns only. Use a tabular layout, inserting subtotals at the bottom of each Product Group. Rename the worksheet as Q8 Percent of Sales