Basic of Data Analytics in Excel

Assignment #4 -Data Analysis Using Excel Pivot Tables (see attached file for data)

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.
9. Switch to Q8 worksheet. Create a PivotChart of Total Sales By Product Group. Insert this chart in a chart sheet. Rename this chart sheet as Sales by Product Group. Create a second PivotChart of total Sales by Territory. Change the chart type to Pie and follow the attached layout. Insert this chart in a chart sheet. Rename this chart sheet as Sales by Territory.
10. Using the following figure as a guide, create a PivotTable to show four calculations: minimum, maximum, average, and total Sales categorized by Territories and Product Group and filtered by Year. Display the results for 2010. Rename the worksheet as Q10 Statistical Summary.
11. Save and close the workbook. Submit the finished workbook into Assignment 4 area. Use "Your_Name_Assignment4" as the title for your submission.