Fine Art Dealer Project Description: You are an analyst for an authorized Greenw
ID: 3667487 • Letter: F
Question
Fine Art Dealer
Project Description:
You are an analyst for an authorized Greenwich Workshop® fine art dealer (www.greenwichworkshop.com). Customers are especially fond of James C. Christensen’s art. The Subtotals worksheet contains a list of artwork released in 2010-2012. You want to calculate subtotals by Type of art (e.g. Limited Edition Canvas) for Issue Price and Est. Price. The Art worksheet contains artwork from 2004-2006. Studying this data will help you discuss value trends with art collectors.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
1
Start Excel.
2
In the Subtotals worksheet, sort the data by Type and then by Name of Art, both in alphabetical order.
3
In the Subtotals worksheet, use the Subtotals feature to identify the highest Issue Price and Est. Value by Type.
4
Use the Art worksheet to create a blank PivotTable on a new worksheet named PivotTable.
5
Include the Type, Release Date, and Issue Price fields in the PivotTable. Remove the Release Date field and add the Est. Value field to the PivotTable.
6
Modify the two VALUES fields to determine the Average Issue Price and Average Est. Value instead of the Sum. Change the custom name to Average Issue Price and Average Est. Value, respectively.
7
Format the two VALUES fields with Accounting Number type with zero decimal places.
8
Insert a calculated field on the right side of the PivotTable to calculate the percentage increase in value from the Issue Price to the Est. Value. Accept the default field name, Field1.
9
Format the calculated field with Percent type with two decimal places. Use the custom name Percentage Change.
10
Type Type in cell A3 and Overall Averages in the cell containing the text Grand Total.
11
Set a filter to display only sold-out art (indicated by Yes).
12
Apply Pivot Style Medium 5, display banded columns, and display banded rows.
13
Use the Art worksheet to create a PivotChart on a new sheet named PivotChart. Change the chart type to Clustered Bar.
14
Include the Type, Issue Price, and Est. Value fields. Set a filter to display only sold-out art (indicated by Yes) for the PivotChart.
15
Hide the field buttons in the PivotChart. Insert a chart title above the chart and type 2005-2007 Art.
16
Format the value axis with Accounting with zero decimal places. Apply 8-pt size to the category axis and value axis. Apply 7-pt size to the legend.
17
Adjust the size of the PivotChart for the range D1:K14.
18
Sort the data in the PivotChart’s PivotTable in reverse alphabetical order by Type. Type Art Type in cell A3 and type Overall Averages in the cell containing the text Grand Total.
19
Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotals, PivotTable, PivotChart, Art. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
RAW EXCEL DATA (Same as pictured above):
Step
Instructions
1
Start Excel.
2
In the Subtotals worksheet, sort the data by Type and then by Name of Art, both in alphabetical order.
3
In the Subtotals worksheet, use the Subtotals feature to identify the highest Issue Price and Est. Value by Type.
4
Use the Art worksheet to create a blank PivotTable on a new worksheet named PivotTable.
5
Include the Type, Release Date, and Issue Price fields in the PivotTable. Remove the Release Date field and add the Est. Value field to the PivotTable.
6
Modify the two VALUES fields to determine the Average Issue Price and Average Est. Value instead of the Sum. Change the custom name to Average Issue Price and Average Est. Value, respectively.
7
Format the two VALUES fields with Accounting Number type with zero decimal places.
8
Insert a calculated field on the right side of the PivotTable to calculate the percentage increase in value from the Issue Price to the Est. Value. Accept the default field name, Field1.
9
Format the calculated field with Percent type with two decimal places. Use the custom name Percentage Change.
10
Type Type in cell A3 and Overall Averages in the cell containing the text Grand Total.
11
Set a filter to display only sold-out art (indicated by Yes).
12
Apply Pivot Style Medium 5, display banded columns, and display banded rows.
13
Use the Art worksheet to create a PivotChart on a new sheet named PivotChart. Change the chart type to Clustered Bar.
14
Include the Type, Issue Price, and Est. Value fields. Set a filter to display only sold-out art (indicated by Yes) for the PivotChart.
15
Hide the field buttons in the PivotChart. Insert a chart title above the chart and type 2005-2007 Art.
16
Format the value axis with Accounting with zero decimal places. Apply 8-pt size to the category axis and value axis. Apply 7-pt size to the legend.
17
Adjust the size of the PivotChart for the range D1:K14.
18
Sort the data in the PivotChart’s PivotTable in reverse alphabetical order by Type. Type Art Type in cell A3 and type Overall Averages in the cell containing the text Grand Total.
19
Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotals, PivotTable, PivotChart, Art. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
RAW EXCEL DATA (Same as pictured above):
Name of Art Type Issue Price Est. Value Tie That Binds, The Limited Edition Print $ 250 $ 250 Tie That Binds, The Limited Edition Canvas $ 750 $ 750 Angel Unobserved Smallwork Canvas Edition $ 225 $ 541 Jonah Anniversary Edition Canvas $ 425 $ 425 Tempus Fugit Smallwork Canvas Edition $ 195 $ 195 Benediction Masterwork Anniversary Edition $ 995 $ 995 Benediction Anniversary Edition Canvas $ 495 $ 495 Golden Ball, The Limited Edition Canvas $ 325 $ 325 Pilates Smallwork Canvas Edition $ 275 $ 275 Oldest Angel, The Anniversary Edition Canvas $ 395 $ 395 Grace Open Edition Canvas $ 125 $ 125 Chess Match, The Museum Edition Canvas $ 2,950 $ 1,070 Chess Match, The Limited Edition Canvas $ 695 $ 852 Chess Match, The Limited Edition Print $ 225 $ 225 Butterfly Knight Smallwork Canvas Edition $ 225 $ 322 Shakespearean Fantasy Masterwork Canvas Edition $ 950 $ 1,301 Shakespearean Fantasy Limited Edition Canvas $ 495 $ 495 College of Magical Knowledge Personal Commission Anniversary Edition $ 950 $ 950 College of Magical Knowledge Personal Commission Anniversary Edition $ 495 $ 495 Nest, The Limited Edition Canvas $ 495 $ 495 Desirable Above All Other Fault Open Edition Canvas $ 195 $ 195 Three Wise Men in a Boat Limited Edition Canvas $ 295 $ 295 Hold to the Rod, the Iron Rod Limited Edition Print $ 175 $ 175 Arise and Shine Forth Masterwork Canvas Edition $ 1,250 $ 1,250 Pilgrim Angel Smallwork Canvas Edition $ 225 $ 225 Two Sisters Anniversary Edition Canvas $ 695 $ 695 Arise and Shine Forth Open Edition Canvas $ 395 $ 395 Arise and Shine Forth Poster $ 20 $ 20 One Light Anniversary Edition Canvas $ 245 $ 245 Guardian in the Woods Limited Edition Canvas $ 395 $ 395 Guardian in the Woods Limited Edition Print $ 195 $ 195 Man Taking a Leek on a Tiled Wall for a Walk Smallwork Canvas Edition $ 195 $ 195 Lawyer More than Adequately Attired in Fine Print, A Anniversary Edition Canvas $ 475 $ 475 Princess in the Tower Limited Edition Canvas $ 245 $ 245 Passage by Faith Limited Edition Print $ 165 $ 165 Passage by Faith Limited Edition Canvas $ 475 $ 475 Christmas Pig, The Smallwork Canvas Edition $ 195 $ 195Explanation / Answer
Below is the link for sheet.
https://drive.google.com/file/d/0B9P4w87kevGhUkMweWhsdFNxSkE/view?usp=sharing
Done till 5. Need more time.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.