IST $310$

Excel Assignment $7$

$20$ Points

$1.$ Download and save the Excel $7$ Template.xlsx file as

La

You are a regional manager for Jayhawk Depot, a national chain of home renovation stores. You are analyzing the weekly sales data for one of the retail stores located in Seattle, Washington. The sales data is reported by department and manager.

$2.$ Create a PivotTable using the data in the Sales worksheet. Place the PivotTable in a new worksheet named PT$1.$ The PivotTable should help you analyze the sales by department. Change the number format of the sales data to Currency with two decimal places, and sort the sales data so that the department with the highest sales amount appears first.

$3.$ Copy the PT$1$ worksheet to a new worksheet named PT$2.$ Notice that you are creating a copy of the entire PT$1$ worksheet, not just the actual PivotTable! Rearrange the fields in the PivotTable to analyze department sales by quarter. Use conditional formatting to apply a yellow fill color to the cells that represent the highest quarterly sales amount in each of the four quarters.

$4.$ Copy the PT$2$ worksheet to a new worksheet named PT$3.$ Clear the conditional formatting. Add the Manager field to the PivotTable to analyze each department's quarterly sales performance by manager. Type the name of the manager who had the highest sales in the Flooring Department during the third quarter in cell A$1.$

$5.$ Copy the PT$3$ worksheet to a new worksheet named PT$4.$ Rearrange the fields in the PivotTable to analyze each manager's quarterly sales performance by department. Type the name of the department that represents the highest overall sales amount for Bill Smoot in cell A$1.$

$6.$ Copy the PT$4$ worksheet to a new worksheet named PT$5.$ Delete the text in cell A$1.$ Remove the Date and Department fields from the PivotTable. Add a slicer for the Department field and a timeline for the Date field. Use the slicer and the timeline to filter the PivotTable to show the total sales amount for each manager for the Kitchen and Bath and Plumbing Departments for April $2015$ through October $2015.$

$10.$ Arrange the worksheets in this order $($if they aren't already$),$ and make the PT$1$ worksheet the active worksheet:

$11.$ Save and close your file, and then submit your Excel file on Canvas.