Reference no: EM132396704

Assignment

You are an analyst for an authorized Greenwich Workshop fine art dealer. Customers are especially fond of James C. Christensen's art. You prepared a list of his artwork: Title (title of each piece of art). Type (the-- medium, such as Limited Edition Print or Anniversary Edition Canvas). Edition Size (how many copies were produced for purchase). Release Date (the month and year the art was released), Issue Price (the original retail price when the art was released). and Est. Value (the estimated current market value). Studying the data will help you discuss value trends with art collectors.

Sort, Subtotal, and Outline Data

You want to organize data to facilitate using the Subtotal feature to display the average issue price and estimated value by Type.

a. Open e05c1FineArt and save it as e05c1FineArt_LastFirst.

b. Click the Subtotals sheet tab. Sort the data by Type and further sort it by the Title, both in alphabetical order.

c. Use the Subtotal feature to insert subtotal rows by Type to identify the highest Issue Price and Est. Value.

d. Collapse the data by displaying only the subtotals and grand total rows.

e. Set a print area for the range D1:1247. Set the scaling to fit to one page.

Create a PivotTable

You want to create a PivotTable to analyze the art by Type, Issue Price, and Est. Value. In addition, you will rename the worksheet and the PivotTable.

a. Click the Christensen sheet tab and create a blank PivotTable on a new worksheet.

b. Customize the value fields by completing the following steps:

• Change the custom names to Average Issue Price and Average Est. Value, respectively for the two value fields.

• Apply Currency number Fermat with 0 decimal places to the two value fields.

c. Insert a calculated field to determine percent change in values between the Est. Value and Issue Price.

d. Customize the calculated field by completing the following steps:

• Change the custom name to Percent Change in Value.

• Apply Percentage number format with 2 decimal places.

e. Select the range B3:D3 and apply these formats: wrap text. Align Right horizontal alignment. 30 row height. and 10 column widths.

f. Type Type of Art in cell A3 and type Average of All Art in cell A18.

Filter the PivotTable and Apply a Style

You want to focus on average values for sold-out art because these pieces typically increase in value on the secondary market. The Sold Out column indicates Yes if the art is sold.out. blank if the art is still available, or limited Availability if the art is still available but in limited supply In addition, you want to narrow the list to particular types. After filtering the data. you will apply a different style to the PivotTable.

a. Set a litter to display only-sold-out art ( indicated by Yes).

b. Set a Type-filter to filter out these types hand Colored and further sort it by the Tme, both to alphabetical order.

c. Use the Subtotal feature to insert subtotal rowsby Type to identify the highest Issue Price and Est. Value.

d. Collapse the data by displaying only the subtotals and grand total rows.

e. Set a print area for the range D1:1247. Set the scaling to fit to one page.

Create a PivotTable

You want to create a PivotTable to analyze the art by Type. Issue Price. and Est. Value. In addition, you will rename the worksheet and the PivotTable.

a. Click the Christensen sheet tab and create a blank PivotTable on a new worksheet.

b. Name the worksheet Sold Out.

c. Use the Type and Issue Price fields. enabling Excel to determine where the fields go.

d. Add the Est. Value field to the VALUES area.

e. Name the PivotTable Average Price by Type.

Change Value Field Settings and Create a Calculated Field

Excel displays the sum of the values by Type. However. you want to calculate the average values for each art type. In addition, you will calculate the percentage change from the Issue Price to the Est. Value. Finally. you will format the values and enter clear headings in the PivotTable.

a. ModilY the value fields to determine the average issue Price and average Est. Value 1w type. wrap text, Align Right horizontal alignment, 30 row height, and 10 column widths.

b. Type Type of Art in cell A3 and type Average of All Art in cell A18.

Filter the PivotTable and Apply a Style

You want to focus on average values for sold-out art because these pieces typically increase in value on the secondary market. The Sold Out column indicates Yes if the art is sold out, blank if the art is still available, or Limited Availability if the art is still available but in limited'supply. In addition. you want to narrow the list to particular types. After filtering the data. you will apply a different style to the PivotTable.

a. Set a filter to display only sold-out art (indicated by Yes).

b. Set a Type filter to filter out these types Hand Colored Print. Limited Edition Hand Colored Print, Open Edition Canvas. Open Edition Print. and Poster.

c. Apply Pivot Style Light 23.

d. Display banded columns.

Insert a Slicer and Timeline

You want to preserve the original PivotTable but create a duplicate so that you can filter data by a timeline and slicer. You will insert a slicer for the Sold Out field and a timeline for the Release Date field. You will then use these elements to filter the data to ;how only sold-out art for the years 2000 to 2005.

a. Copy the Sold Out worksheet, move the duplicate sheet tab to the tell of the Sold Out sheet tab. and rename the new sheet Types.

b. Reset the Sold Out filter and then remove the Sold Out field from the FILTERS area.

c. Insert a slicer for the Sold Out field and complete the following steps to customize the slicer:

• Change the slicer height to 1.5".

• Apply Slicer Style Dark 1.

• Click the Yes slicer button to filter the PivotTable to list averages for only sold-out art.

d. Insert a timeline for the Release Date field and complete the following steps:

• Change the time period to YEARS.

• Set the timeline to filter to display 2000 to 2005.

• Change the timeline width to 4".

Create a PivotChart

To help interpret the consolidated values of the art, you want to create a PivotChart. You realize that displaying both monetary values and percentages on the same chart is like mixing apples and oranges. If you modify the PivotChart. you will change the PivotTable: therefore, you will copy the Sold Out worksheet and then create a PivotChart from the duplicate worksheet.

a. Copy the Sold Out worksheet. move the duplicate sheet tab to the kit of the Types sheet tab. and rename the new sheet PivotChart.

b. Create a PivotChart selecting the Clustered Bar type.

c. Modify the PivotChart by completing the following steps:

• Move the PivotChart so that the topeleft corner starts in cell A14.

• Change the chart height to 3.5" and the width to 6.5".

• Hide the field buttons in the PivotChart.

• Remove the Percentage Change field.

d. Add a chart title and type Values for Sold-Out Christensen Art.

e. Set the upper limit of the value axis to 3000.

f. Change the chart style to Style 12.

g. Select the category labels in the PivotChart and sort the labels from Z to A.

Finalizing Your Workbook

You will finalize your workbook by adding a footer to the work¬sheets you changed and Created.

a. Create a footer on all worksheets (except Christensen) with your name, the sheet name code, and the file name code.

b. Save and close the file. Based on your instructor's directions, submit e05c1FineArt_LastFirst.

Attachment:- e06c1Manufacturing.rar