Reference no: EM132654358
Data File needed for the Review Assignment: July.xlsx
Victoria needs to analyze the sales for July. She entered this data into a new workbook and wants you to sort and filter the data as well as create summary reports using the Subtotal command, pivotTables. and PivotCharts. Complete the following:
1. Open the July workbook located in the Excels5 > Review folder included with your Data files, and then save the workbook as July Sales Data in the location specified by your instructor.
2. In the Documentation worksheet, enter your name and the date.
3. In the Sales Data worksheet, freeze the top row so that the headers remain on the screen as you scroll.
4. Make a copy of the Sales Data worksheet, and then rename the copied worksheet as July Data. Hint: To make a copy of a worksheet. press and hold the Girl key as von drag the sheet tab to the right of the Sales Data sheet tab.
5. In the July Data worksheet, unfreeze the top row.
6. Create an Excel table for the sales data in the July Data worksheet.
7. Format the Excel table with Table Style Medium 4. and then change the Amount field to the Accounting format with two decimal places.
8. Rename the Excel table as JulyTbl.
9. Make the following changes to the lulyTbl table:
a. Add a record for 7/31/2017, Monday. 4. Group Home, 256.52.
b. Edit the record for Individual on 7/27/2017 by changing the Amount from 462.74 to 492.05.
c. Remove any duplicate records.
10.Make a copy of the July Data worksheet, and then rename the copied worksheet as Soil by Sale Date. In the Sort by Sale Date worksheet, sort the JulyTbl table by Sale Date, displaying the newest sales first, and then by Amount, displaying the largest amounts first.
11. Make a copy of the July Data worksheet, and then rename the copied worksheet as Sort by Day.
In the Sort by Day worksheet, sort the sales data by Day (use the custom list order of Sunday,
Monday,- for the chronological son), then by Business (A to Z), and then by Amount (smallest
to largest).
12. Make a copy of the July Data worksheet, and then rename the copied worksheet as Filter to Omit Restaurant. In the Filter to Omit Restaurant worksheet, filter the JulyTbl table to display the sales for all businesses except Restaurant.
13. In the Filter to Omit Restaurant worksheet, insert the Total row to calculate the average amount of sales for the filtered data. Change the label in the Total row to Average. Sort the filtered data by descending order by Amount.
14. Split the Filter to Omit Restaurant worksheet into two panes above the last row of the table. Display the sales records in the top pane, and display only the Total row in the bottom pane.
15. Make a copy of the July Data worksheet, and then rename the copied worksheet as Filter by Sales ID. In the Filter by Sales ID worksheet, insert a slicer for the Sales ID column. Move the slicer to row I. Format the slicer with Slicer Style Light 3. Change the slicers height to .9" and its width to 1.25". Use the slicer to display sales for Sales ID 3 and Sales ID S.
16. Make a copy of the July Data worksheet, and then rename the copied worksheet as July Subtotals. In the July Subtotals worksheet, convert the JulyTbl Table to a range. and then sort the range by the Business column in ascending order.
17. In the July Subtotals worksheet use the Subtotal command to calculate the total sales for each business in the Amount column. Display only die subtotal results. Widen columns as needed so
that all of the data is visible.
18. Based on the JulyTbl table in the July Data worksheet, create a PivotTable in a new worksheet that shows the total sales Amount by Day. Format the data area with the Currency Format.
Rename the worksheet with the PivotTable as PivotTableChart Sales by Day.
19. In the PivotTableChart Sales by Day worksheet, insert a Clustered Column PivotChart used on the PivotTable you created. Move the PivotChart to row 3. Remove the legend change the PivotChart title to Sales by Day of Week.
20. Based on the JulyTbl table in the July Data worksheet, create a PivotTable in a new worksheet that shows Amount by Sale Date. Add the Business field to the FILTERS area. Form PivotTable with Pivot Style Medium 4. Format the Amount field with the Accounting format with
two decimal places. Rename that worksheet as PivotTable by Sale Date.
21. In the PivotTable by Sale Date worksheet, insert a slicer for the Business field of the PivotTable.
Change the slicer height to 1.6" and the width to 1.5". Format the slicer with Slicer Style Dark 3.
Move the slicer to row 3.
22. Use the slicer to filter the PivotTable to display only the Restaurant and Group Home sales.
23. Based on the JulyTbl table in the July Data worksheet, create the Recommended PivotTable Sum of Amount by Sales ID and Business. Rename the worksheet as Recommended PivotTable.
24. Save the workbook, and then close it.
Attachment:- July.rar