please help! please send a link of your final excel sheet to Kaitlin.nicole011@gmai l.com or attach screenshots with step by step on here. thanks!
ou are an analyst for an authorized Greenwich Workshop ne art dealer (www.greenwichworkshop.com). Customers are especially fond of James C. Christensen's art. You prepa 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 pur- chase), 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 e05c1 FineArt and save it as e05c1FineArt LastFirst. 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. Collapse the data by displaying only the subtotals and grand total rows. d. e. Set a print area for the range D1:1247. Set the scaling t fit to one page Create a PivotTable You want to create a PivotTable to analyze the art by Type. issuc Price, and Est. Value. In addition, you will rename the worksheet and the PivotTable a. Click the Christensen sheet tab and sreate a biank PivotTable on a new workshect b. Name the worksheet Sold Out. c. Use the Type and Issue Price fcis enabing 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. Modify the value fields to determine the average Issue Price and average Est. Value by type. 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 .Apply Currency number format with 0 decimal places Insert a calculated field to determine percent change in fields. to the two value fields. c. 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. Type Type of Art in cell A3 and type Average of All Art in cell A18. f. 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 dupli cate 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 show only sold-out art for the years 2000 to 2005 Copy the Sold Out worksheet, move the duplicate sheet tab to the left of the Sold Out sheet tab, and rename th a. new sheet Types. b. Reset the Sold Out filter and then remove the Sold Out field from the FILTERS area. Insert a slicer for the Sold Out field and complete th following steps to customize the slicer: . Change the slicer height to 1.5" c. Modify the PivotChart by completing the following c. Move the PivotChart so that the top-left corner star Apply Slicer Style Dark 1 Click the Yes slicer button to filter the PivotT list averages for only sold-out art. yotTable to Change the chart height to 3.5" and the width to 6.5 in cell A14 Hide the field buttons in the PivotChart. : Remove the Percentage Change field d. Add a chart title and type Values for Sold-Out 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" 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 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 ap and oranges. If you modify the PivotChart, you will change the PivotTable; therefore, you will copy the Sold Out worksheet and Y then create a PivotChart from the duplicate worksheet the labels from Z to A. You will finalize your workbook by adding a footer to the work sheets you changed and created. Create a footer on all worksheets (except Christensen) with your name, the sheet name code, and the file name code a. Copy the Sold Out worksheet, move the duplicate sheet a. tab to the left of the Types sheet tab, and rename the new sheet PivotChart. b. Create a PivotChart selecting the Clustered Bar type. b. Save and close the file. Based on your instructor's directions, submit e05clFineArt LastFirst. ou are an analyst for an authorized Greenwich Workshop ne art dealer (www.greenwichworkshop.com). Customers are especially fond of James C. Christensen's art. You prepa 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 pur- chase), 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 e05c1 FineArt and save it as e05c1FineArt LastFirst. 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. Collapse the data by displaying only the subtotals and grand total rows. d. e. Set a print area for the range D1:1247. Set the scaling t fit to one page Create a PivotTable You want to create a PivotTable to analyze the art by Type. issuc Price, and Est. Value. In addition, you will rename the worksheet and the PivotTable a. Click the Christensen sheet tab and sreate a biank PivotTable on a new workshect b. Name the worksheet Sold Out. c. Use the Type and Issue Price fcis enabing 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. Modify the value fields to determine the average Issue Price and average Est. Value by type. 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 .Apply Currency number format with 0 decimal places Insert a calculated field to determine percent change in fields. to the two value fields. c. 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. Type Type of Art in cell A3 and type Average of All Art in cell A18. f. 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 dupli cate 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 show only sold-out art for the years 2000 to 2005 Copy the Sold Out worksheet, move the duplicate sheet tab to the left of the Sold Out sheet tab, and rename th a. new sheet Types. b. Reset the Sold Out filter and then remove the Sold Out field from the FILTERS area. Insert a slicer for the Sold Out field and complete th following steps to customize the slicer: . Change the slicer height to 1.5" c. Modify the PivotChart by completing the following c. Move the PivotChart so that the top-left corner star Apply Slicer Style Dark 1 Click the Yes slicer button to filter the PivotT list averages for only sold-out art. yotTable to Change the chart height to 3.5" and the width to 6.5 in cell A14 Hide the field buttons in the PivotChart. : Remove the Percentage Change field d. Add a chart title and type Values for Sold-Out 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" 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 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 ap and oranges. If you modify the PivotChart, you will change the PivotTable; therefore, you will copy the Sold Out worksheet and Y then create a PivotChart from the duplicate worksheet the labels from Z to A. You will finalize your workbook by adding a footer to the work sheets you changed and created. Create a footer on all worksheets (except Christensen) with your name, the sheet name code, and the file name code a. Copy the Sold Out worksheet, move the duplicate sheet a. tab to the left of the Types sheet tab, and rename the new sheet PivotChart. b. Create a PivotChart selecting the Clustered Bar type. b. Save and close the file. Based on your instructor's directions, submit e05clFineArt LastFirst
