Exp19_Excel_Ch05_HOEAssessment_Fine_Art

profileexpmen
Exp19_Excel_Ch05_HOEAssessment_Fine_Art.zip

Exp19_Excel_Ch05_HOEAssessment_Fine_Art_Instructions.docx

Grader - Instructions Excel 2019 Project

Exp19_Excel_Ch05_HOEAssessment_Fine_Art

Project Description:

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 help you discuss value trends with art collectors

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp19_Excel_Ch05_HOEAssessment_FineArt.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

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.

6

3

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.

6

4

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.

5

5

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 Sold Out. 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 Art Type.

5

6

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.

5

7

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 Average Issue Price and Average Est. Value, respectively.

8

8

Format the two Values fields with Accounting number type with zero decimal places.

5

9

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.

4

10

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.

5

11

Insert a slicer for the Type field, change the slicer height to 2 inches, change the button width to 2 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.

7

12

Display the Totals sheet. Insert a calculated field named Field1 to determine difference between the two values, Est. Value and Issue Price. Change the custom name to Value Increase.

6

13

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.

6

14

With the PivotTable on the Totals sheet displayed, select Light Blue, Pivot Style Medium 6 and display banded rows.

5

15

Display the Porcelains sheet. Create a relationship between the PORCELAINS table using the Code field and the CODES table using the Code field.

5

16

Create a blank PivotTable from inside the PORCELAINS table. Add this data to the Data Model. Name the worksheet Porcelain Pivot and name the PivotTable Porcelain Values.

3

17

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.

6

18

Create a clustered column PivotChart from the Porcelain Values PivotTable. Cut the PivotChart and paste it in cell A7.

4

19

Add a chart title and type Porcelain Values. Bold the title. Change the value axis maximum bounds to $3,000.

5

20

Change the PivotChart height to 2.5 inches and the width to 3.7 inches.

2

21

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.

2

22

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.

0

23

Save and close Exp19_Excel_Ch05_HOEAssessment_FineArt.xlsx. Exit Excel. Submit the file as directed

0

Total Points

100

Created On: 08/22/2019 1 Exp19_Excel_Ch05_HOEAssessment - Fine Art 1.0

Taskent_Exp19_Excel_Ch05_HOEAssessment_FineArt.xlsx

Subtotals

Title Type Status Issue Price Est. Value Change
Low Tech Limited Edition Canvas Sold Out $ 595 $ 695 16.8%
Tempus Fugit Smallwork Canvas Edition Sold Out $ 195 $ 379 94.4%
Once Upon a Time Masterwork Sold Out $ 1,750 $ 4,695 168.3%
Poofy Guy on a Short Leash Limited Edition Print Available $ 160 $ 165 3.1%
Six Bird Hunters in Full Camouflage Anniversary Edition Canvas Sold Out $ 395 $ 695 75.9%
Royal Processional Masterwork Available $ 1,250 $ 1,300 4.0%
One Light Anniversary Edition Canvas Sold Out $ 245 $ 595 142.9%
Pilates Smallwork Canvas Edition Sold Out $ 275 $ 450 63.6%
Listener Limited Edition Print Sold Out $ 195 $ 245 25.6%
City on a Hill Limited Edition Canvas Available $ 395 $ 395 0.0%
Fish in a Toucan Mask Limited Edition Canvas Sold Out $ 495 $ 815 64.6%
Portrait with Red Berries Limited Edition Print Sold Out $ 495 $ 245 -50.5%
Chess Match Masterwork Sold Out $ 2,950 $ 2,950 0.0%
Guardian in the Woods Limited Edition Canvas Sold Out $ 395 $ 795 101.3%
Superstitions Masterwork Available $ 950 $ 1,235 30.0%
Place of Her Own, A Masterwork Sold Out $ 1,450 $ 2,295 58.3%
Isabella Limited Edition Print Sold Out $ 150 $ 413 175.3%
Benediction Anniversary Edition Canvas Sold Out $ 495 $ 650 31.3%
College of Magical Knowledge Personal Commission Masterwork Sold Out $ 950 $ 1,275 34.2%
Shakespearean Fantasy Masterwork Available $ 950 $ 1,163 22.4%
First Rose Smallwork Canvas Edition Sold Out $ 195 $ 495 153.8%
Three Clowns Limited Edition Canvas Available $ 650 $ 650 0.0%
Bride, The Limited Edition Print Sold Out $ 145 $ 380 162.1%
Isabella Limited Edition Canvas Sold Out $ 395 $ 650 64.6%
Resistance Training Limited Edition Canvas Sold Out $ 295 $ 499 69.2%
Living Waters Limited Edition Canvas Available $ 395 $ 395 0.0%
Men and Angels Limited Edition Print Sold Out $ 135 $ 370 174.1%
Cecelia Limited Edition Print Sold Out $ 195 $ 890 356.4%
Candleman Limited Edition Canvas Sold Out $ 395 $ 520 31.6%
Return of the Fablemaker, The Masterwork Sold Out $ 995 $ 1,100 10.6%
Michael the Archangel Battles the Dragon While Almost Nobody Pays Any Attention Masterwork Sold Out $ 1,450 $ 1,960 35.2%
Man Who Minds the Moon Anniversary Edition Canvas Sold Out $ 395 $ 630 59.5%
False Magic Limited Edition Canvas Sold Out $ 225 $ 395 75.6%
Interrupted Voyage Limited Edition Canvas Sold Out $ 395 $ 945 139.2%
Burden of the Responsible Man, The Anniversary Edition Canvas Sold Out $ 425 $ 1,684 296.2%
Responsible Woman, The Anniversary Edition Canvas Sold Out $ 650 $ 2,797 330.3%

Totals

Row Labels Sum of Issue Price Sum of Est. Value
Anniversary Edition Canvas $ 2,605 $ 7,051
Limited Edition Canvas $ 5,125 $ 7,269
Limited Edition Print $ 1,115 $ 2,708
Masterwork $ 12,695 $ 17,973
Smallwork Canvas Edition $ 665 $ 1,324
Grand Total $ 22,205 $ 36,325

Art

Title Type Status Release Date Issue Price Est. Value
Low Tech Limited Edition Canvas Sold Out Nov-14 $ 595 $ 695
Tempus Fugit Smallwork Canvas Edition Sold Out Apr-10 $ 195 $ 379
Once Upon a Time Masterwork Sold Out Mar-04 $ 1,750 $ 4,695
Poofy Guy on a Short Leash Limited Edition Print Available Aug-04 $ 160 $ 165
Six Bird Hunters in Full Camouflage Anniversary Edition Canvas Sold Out Nov-13 $ 395 $ 695
Royal Processional Masterwork Available Jan-05 $ 1,250 $ 1,300
One Light Anniversary Edition Canvas Sold Out May-12 $ 245 $ 595
Pilates Smallwork Canvas Edition Sold Out Oct-10 $ 275 $ 450
Listener Limited Edition Print Sold Out Mar-06 $ 195 $ 245
City on a Hill Limited Edition Canvas Available May-15 $ 395 $ 395
Fish in a Toucan Mask Limited Edition Canvas Sold Out Mar-14 $ 495 $ 815
Portrait with Red Berries Limited Edition Print Sold Out Jun-03 $ 135 $ 245
Chess Match Masterwork Sold Out Feb-11 $ 2,950 $ 2,950
Guardian in the Woods Limited Edition Canvas Sold Out Jun-12 $ 395 $ 795
Superstitions Masterwork Available Feb-13 $ 950 $ 1,235
Place of Her Own, A Masterwork Sold Out Aug-08 $ 1,450 $ 2,295
Isabella Limited Edition Print Sold Out Oct-03 $ 150 $ 413
Benediction Anniversary Edition Canvas Sold Out Jul-10 $ 495 $ 650
College of Magical Knowledge Personal Commission Masterwork Sold Out Jul-11 $ 950 $ 1,275
Shakespearean Fantasy Masterwork Available May-11 $ 950 $ 1,163
First Rose Smallwork Canvas Edition Sold Out Apr-09 $ 195 $ 495
Three Clowns Limited Edition Canvas Available Oct-15 $ 650 $ 650
Bride, The Limited Edition Print Sold Out May-05 $ 145 $ 380
Isabella Limited Edition Canvas Sold Out Oct-03 $ 395 $ 650
Resistance Training Limited Edition Canvas Sold Out Apr-07 $ 295 $ 499
Living Waters Limited Edition Canvas Available Apr-13 $ 395 $ 395
Men and Angels Limited Edition Print Sold Out Sep-06 $ 135 $ 370
Cecelia Limited Edition Print Sold Out Sep-05 $ 195 $ 890
Candleman Limited Edition Canvas Sold Out Nov-16 $ 395 $ 520
Return of the Fablemaker, The Masterwork Sold Out Aug-09 $ 995 $ 1,100
Michael the Archangel Battles the Dragon While Almost Nobody Pays Any Attention Masterwork Sold Out Apr-06 $ 1,450 $ 1,960
Man Who Minds the Moon Anniversary Edition Canvas Sold Out Sep-14 $ 395 $ 630
False Magic Limited Edition Canvas Sold Out Apr-08 $ 225 $ 395
Interrupted Voyage Limited Edition Canvas Sold Out Apr-16 $ 395 $ 945
Burden of the Responsible Man, The Anniversary Edition Canvas Sold Out Nov-07 $ 425 $ 1,684
Responsible Woman, The Anniversary Edition Canvas Sold Out Aug-06 $ 650 $ 2,797

Summary

Summary Stats Est. Value
Average Anniversary Edition Canvas
Grand Average

Porcelains

Porcelain Title Code Issue Est. Value
The Gift of Charity O 85 117
The Gift of Knowledge O 85 195
Celeste LE 65 70
Lawyer More Than Adequately Attired LE 595 995
Queen Mab LE 135 165
The Pear Balancer LE 160 250
The Miniature Artist O 95 125
The Fish Wizard LE 160 295
The Gift of Music O 75 85
Jack Be Nimble O 295 351
Wetland Bird Hunter LE 250 455
Scholar LE 375 728
Description Code
Open Edition O
Limited Edition LE