You are an analyst for a fine art dealer. Customers are especially fond of James C. Christensen’s art. You prepared a list of his artwork:

You are an analyst for a 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 you discuss value trends with art collectors Start   Excel. Download and open the file named . Grader has automatically added your last   name to the beginning of the filename. Before using the Subtotal   command, the data must be sorted by categories. On the Subtotals worksheet, perform a three-level sort by Status,  then by   Type, and finally by Title, all in alphabetical order. You want to subtotal data to   identify the highest issue prices,  estimated values, and percentage change by   the Status category and the  Type of art work. Use the Subtotals feature to insert subtotal rows by Status to  identify the   highest (max) Issue Price, Est. Value,   and Change. Then  add a second-level subtotal by Type using the same function   and  columns. You want to apply an outline so   that you can collapse values used  for the formula in column F. Then you will   collapse the subtotaled  rows. Apply an auto outline and click the collapse button above column F.  Collapse   the data by displaying only the subtotals and grand total  rows. Set a print   area for the range B1:F48. A PivotTable can give additional   perspective to data. You will use the Art worksheet to create a recommended   PivotTable. Use the Art worksheet to create the recommended PivotTable called Sum of   Issue Price by Type on a new worksheet named .  Mac users create a PivotChart   that includes the Status field in the  Filters area, the Est. Values field in   the Columns area, the Type  field in the Rows area, and the Issue Price field   in the Values area. Name the PivotTable . Currently, the PivotTable   contains the Issue Price field. You will  add the Est. Value field to compare   the difference between total value  of the art based on issue price and   estimated value today. Add the Est. Value field below the Sum of Issue Price in the Values area. You want to display the average   values instead of the sum of the values in each category. Modify the two Values fields to determine the average Issue Price and average   Est. Value by type. Change the custom name to and , respectively. Format the two Values fields   with Accounting number type with zero decimal places. The Summary sheet is designed to   display two key averages from the PivotTable on the Summary sheet. Display the Summary sheet. In cell B2, insert the GETPIVOTDATA  function that   references cell C4 on the PivotTable in the Sold Out  sheet. In cell B3,   insert the GETPIVOTDATA function that references  cell C9 on the PivotTable in   the Sold Out sheet. Some art is still available, but   most art is sold out. You want to create a filter to focus on the sold-out   art. Display the Sold Out sheet. Add the Status field from the field  list to the   Filters area. Set a filter to display only art that is  Sold Out. Insert a slicer for the Type   field, change the slicer height to inches, change the button width to inches, and apply the Light   Blue, Slicer Style Dark 5. Cut the slicer and paste it in cell A11. Note, depending upon the Office version used, the style name may be Slicer   Style Dark 1. Display the Totals sheet. Insert   a calculated field named Field1 to determine difference between the two   values, and .   Change the custom name to . With the PivotTable on the   Totals sheet displayed, change the Sum  of Issue Price and Sum of Est. Value   fields to show values as  percentage of column totals. With the PivotTable on the   Totals sheet displayed, select Light Blue, Pivot Style Medium 6 and display   banded rows. Display the Porcelains sheet.   Create a relationship between the  PORCELAINS table using the Code field and the   CODES table using the  Code field. Create a blank PivotTable from   inside the PORCELAINS table. Add this data to the Data Model. Name the   worksheet and name the PivotTable . Display all tables in the   PivotTable Fields List task pane. Add the  Description from the CODES table to   the Rows area and the Issue and  Est. Value fields as Values from the   PORCELAIN table. Format the two  value fields with Accounting number format   with zero decimal places. Create a clustered column   PivotChart from the Porcelain Values PivotTable. Cut the PivotChart and paste   it in cell A7. Add a chart title and type . Bold   the title. Change the value axis maximum bounds to $3,000. Change the PivotChart height to inches and the width to inches. Create a footer on all   worksheets (except Art) with your name in  the left section, the sheet name   code in the center section, and the  file name code in the right section. Ensure that the worksheets are   correctly named and placed in the  following order in the workbook: Subtotals,   Totals, Sold Out, Art,  Summary, Porcelain Pivot, Porcelains. Save and close .   Exit Excel. Submit the file as directed

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.


Click Here to Make an Order Click Here to Hire a Writer