Your Aunt Laura has been collecting Fiesta dinnerware, a popular brand from The Homer Laughlin China Company, since it was reintroduced in 1986. Her collection has grown, and she enlisted you to help her maintain a list. So far, you and Aunt Laura entered data by color, item number, and item. Previously, you helped her research current replacement costs from Homer Laughlin’s Web site (www. fiestafactorydirect. com), Replacements, Ltd. (www. replacements. com), and eBay (www. ebay. com); however, you believe the retired colors may be worth more now. Laura is especially interested in the values of retired colors so that she can provide this information for her insurance agent. You will build a PivotTable and PivotChart to help her with the analysis. a. Open e05m2fiesta, and then save it as e05m2fiesta_LastnameFirstname.

b. Create a PivotTable on a new worksheet, and then name the worksheet Retired Colors.

c. Display the colors as row headings and the sum of the replacement values.

d. Add a report filter to display aggregates for retired colors only. Note that current colors do not have a retirement date.

e. Apply the Pivot Style Medium 12 to the PivotTable.

f. Add a calculated field named Updated Values that determines values based on a 15% increase over the replacement values. Do not add a new column to the original list; add it as a calculated field column in the PivotTable.

g. Format the values with Accounting Number Format with two decimal places. Create custom headings: Replacement and Updated. Change Row Labels in cell A3 to Retired Colors. Adjust the column widths appropriately and center the column labels.

h. Create a PivotChart from the PivotTable, and then change the chart to a bar chart. Make the following changes to the PivotChart:

• Move the chart below the PivotTable and adjust its size.

• Format the value axis to show whole values without decimal places.

• Sort the PivotChart data so that the most valuable color appears at the top and the least valuable color appears at the bottom of the bar chart. You need to explore how to perform the sort. Determine if you can do this from the PivotChart or if you have to sort the PivotTable.

• Apply the Layout 1 chart layout, and then enter an appropriate chart title. Hide the field buttons in the PivotChart.

i. Create a copy of the Retired Colors worksheet. On the duplicate worksheet, remove the Replacement Values field, display the Updated Values as a percentage of the total, and then sort the percentages with the largest first. Change the chart to a pie chart, apply Layout 1, and then move the chart to its own sheet named Retired Colors Chart. Change the title to Updated Replacement Values for Retired Colors. Optional: Change the pie slice colors to match the Fiesta color names.

j. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all worksheets except the Inventory worksheet. Adjust the margins and scaling to fit on one page.

k. Save and close the workbook, and submit based on your instructor’s directions.