Reference no: EM13313480

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions
1 Start Excel. Download, save, and open the file named Exploring_e05_Grader_EOC.xlsx.

2 Sort the data on the Subtotals worksheet first by the Type column, then by the Art column, both in alphabetical order.

3 Subtotal the data at each change in Type. Identify the highest issue price, estimated value, and percent increase.

4 Add an automatic outline to the Subtotals worksheet. Group the first and last name columns. Collapse the names columns, and then collapse columns created by the outline (G, H, and I).

5 Create a PivotTable on a new worksheet using the data on the Christensen worksheet in the range A1:J19. Use the Type, Est. Value, and Issue Price fields and use the default locations in the PivotTable Layout.

6 Modify the Issue Price and Est. Value values to determine the average issue price and average estimated market value by type.

7 Insert a calculated field named % Change in Value to determine the percent change in values by type. Format the values in the new column as percentages with two decimal places. Format the values in the Average of Issue Price and Average of Est. Value columns as accounting values with no decimal places.

8 Enter the custom name Average Issue Price for the issue price column. Enter Average Est. Value for the estimated value column. Change Row Labels in the first column to Art Type.

9 Set a report filter to display only sold-out art. Filter the table to omit Hand Colored Print types.

10 Apply the Pivot Style Medium 5 style to the PivotTable. Display banded columns and banded rows.

11 Use the data on the Christensen worksheet in the range A1:J19 to create a PivotChart on a new sheet. Use the Type, Issue Price, and Est. Value fields and the default locations. Find the average issue price and average estimated value.

12 Change the chart type to Clustered Bar. Move the PivotChart to below the PivotTable so that the top left corner aligns with the top left corner of cell A13. Resize the PivotChart so that the bottom right corner aligns with the bottom right corner of cell D27. Hide all of the field buttons in the chart.

13 Set a report filter to display only sold-out art in the new PivotTable. Filter the table to omit Hand Colored Print types from the PivotTable.

14 Insert the chart title Sold-Out Art above the chart and reduce the font size of the title to 14 pt. Format the numbers in the PivotTable range B4:C10 to Accounting with zero decimal places.

15 Sort the new PivotTable in reverse alphabetical order by Row Label. Change Row Labels in cell A3 to Art Type. Enter Overall Averages in cell A10.

16 Rename the original PivotTable worksheet to PivotTable. Rename the second PivotTable/PivotChart worksheet to PivotChart.

17 Save the workbook. The order of the worksheets should be Subtotals, PivotTable, PivotChart, and Christensen. Close the workbook and then exit Excel. Submit the workbook as directed.