need help chapter excel homework

nyvpvnew
timer Asked: Mar 15th, 2015

Unformatted Attachment Preview

Office 2013 – myitlab:grader – Instructions Exploring Excel 05 H1 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 Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named exploring_e05_grader_h1_start.xlsx. 0 2 In the Subtotals worksheet, sort the data by Type and then by Name of Art, both in alphabetical order. 5 3 In the Subtotals worksheet, use the Subtotals feature to identify the highest Issue Price and Est. Value by Type. 5 4 Use the Art worksheet to create a blank PivotTable on a new worksheet named PivotTable. 5 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. 5 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. 10 7 Format the two VALUES fields with Accounting Number type with zero decimal places. 5 8 Insert a calculated field on the right side of the PivotTable to calculate the percent change in values between the Est. Value and the Issue Price. 5 9 Format the calculated field with Percent type with two decimal places. Use the custom name Percentage Change. 5 Updated: 07/17/2013 1 E_CH05_EXPV2_H1_Instructions.docx Office 2013 – myitlab:grader – Instructions Step Exploring Excel 05 H1 Points Possible Instructions 10 Type Type in cell A3 and Overall Averages in the cell containing the text Grand Total. 5 11 Set a filter to display only sold-out art (indicated by Yes). 5 12 Apply Pivot Style Medium 5, display banded columns, and display banded rows. 10 13 Use the Art worksheet to create a PivotChart on a new sheet named PivotChart. Change the chart type to Clustered Bar. 5 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. 10 15 Hide the field buttons in the PivotChart. Insert a chart title above the chart and type 20052007 Art. 5 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. 5 17 Adjust the size of the PivotChart for the range D1:K14. 5 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. 5 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. 0 Total Points Updated: 07/17/2013 2 100 E_CH05_EXPV2_H1_Instructions.docx Name of Art Tie That Binds, The Tie That Binds, The Angel Unobserved Jonah Tempus Fugit Benediction Benediction Golden Ball, The Pilates Oldest Angel, The Grace Chess Match, The Chess Match, The Chess Match, The Butterfly Knight Shakespearean Fantasy Shakespearean Fantasy College of Magical Knowledge Personal Commission College of Magical Knowledge Personal Commission Nest, The Desirable Above All Other Fault Three Wise Men in a Boat Hold to the Rod, the Iron Rod Arise and Shine Forth Pilgrim Angel Two Sisters Arise and Shine Forth Arise and Shine Forth One Light Guardian in the Woods Guardian in the Woods Man Taking a Leek on a Tiled Wall for a Walk Lawyer More than Adequately Attired in Fine Print, A Princess in the Tower Passage by Faith Passage by Faith Christmas Pig, The Type Limited Edition Print Limited Edition Canvas Smallwork Canvas Edition Anniversary Edition Canvas Smallwork Canvas Edition Masterwork Anniversary Edition Anniversary Edition Canvas Limited Edition Canvas Smallwork Canvas Edition Anniversary Edition Canvas Open Edition Canvas Museum Edition Canvas Limited Edition Canvas Limited Edition Print Smallwork Canvas Edition Masterwork Canvas Edition Limited Edition Canvas Anniversary Edition Anniversary Edition Limited Edition Canvas Open Edition Canvas Limited Edition Canvas Limited Edition Print Masterwork Canvas Edition Smallwork Canvas Edition Anniversary Edition Canvas Open Edition Canvas Poster Anniversary Edition Canvas Limited Edition Canvas Limited Edition Print Smallwork Canvas Edition Anniversary Edition Canvas Limited Edition Canvas Limited Edition Print Limited Edition Canvas Smallwork Canvas Edition Issue Price $ 250 $ 750 $ 225 $ 425 $ 195 $ 995 $ 495 $ 325 $ 275 $ 395 $ 125 $ 2,950 $ 695 $ 225 $ 225 $ 950 $ 495 $ 950 $ 495 $ 495 $ 195 $ 295 $ 175 $ 1,250 $ 225 $ 695 $ 395 $ 20 $ 245 $ 395 $ 195 $ 195 $ 475 $ 245 $ 165 $ 475 $ 195 Est. Value $ 250 $ 750 $ 541 $ 425 $ 195 $ 995 $ 495 $ 325 $ 275 $ 395 $ 125 $ 1,070 $ 852 $ 225 $ 322 $ 1,301 $ 495 $ 950 $ 495 $ 495 $ 195 $ 295 $ 175 $ 1,250 $ 225 $ 695 $ 395 $ 20 $ 245 $ 395 $ 195 $ 195 $ 475 $ 245 $ 165 $ 475 $ 195 Art Type Dusk Limited Edition Canvas St. Brendan The Navigator Limited Edition Canvas St. Brendan The Navigator Limited Edition Print Once Upon a Time Masterwork Anniversary Edition Enoch Altarpiece framed, The Limited Edition Canvas Messenger, The Limited Edition Print Poofy Guy on a Short Leash Limited Edition Canvas Poofy Guy on a Short Leash Limited Edition Print St. Nicholas of Myra Limited Edition Canvas Twilight Limited Edition Canvas Twilight Limited Edition Print Royal Processional, The Masterwork Anniversary Edition Saint with White Sleeves Limited Edition Canvas Saint with White Sleeves Limited Edition Print Bride, The Limited Edition Canvas Bride, The Limited Edition Print Madonna with Two Angeles framed Limited Edition Canvas Cecelia Masterwork Canvas Edition Cecelia Limited Edition Print Pink Ribbon, The Open Edition Print Finding Fish Litho Hand Colored Print Gift for Mrs. Claus, The Anniversary Edition Pink Ribbon, The Limited Edition Canvas If Pigs Could Fly Limited Edition Canvas Listener, The Limited Edition Canvas Listener, The Limited Edition Print Michael the Archangel Battles the Dragon While Almost Nobody Limited Pays Edition Any Attention Canvas Michael the Archangel Battles the Dragon While Almost Nobody Masterwork Pays Any Canvas Attention Edition Michael the Archangel Battles the Dragon While Almost Nobody Limited Pays Edition Any Attention Print Responsible Woman, The Anniversary Edition Canvas Men and Angels Limited Edition Canvas Men and Angels Limited Edition Print Angel with Epaulet Limited Edition Canvas Release Date Jan-04 Jan-04 Jan-04 Mar-04 Jun-04 Jun-04 Aug-04 Aug-04 Aug-04 Oct-04 Oct-04 Jan-05 Mar-05 Mar-05 May-05 May-05 Jun-05 Aug-05 Sep-05 Sep-05 Oct-05 Oct-05 Oct-05 Jan-06 Mar-06 Mar-06 Apr-06 Apr-06 May-06 Aug-06 Sep-06 Sep-06 Dec-06 Sold Out Limited Availability Yes Limited Availability Yes Yes Limited Availability Limited Availability Yes Yes Limited Availability Yes Yes Limited Availability Yes Yes Limited Availability Yes Yes Yes Limited Availability Limited Availability Yes Limited Availability Limited Availability Yes Limited Availability Yes Yes Yes Yes Issue Price $ 495 $ 250 $ 140 $ 1,750 $ 1,595 $ 775 $ 495 $ 160 $ 260 $ 495 $ 160 $ 1,250 $ 395 $ 150 $ 475 $ 145 $ 595 $ 995 $ 195 $ 30 $ 775 $ 425 $ 250 $ 325 $ 650 $ 195 $ 775 $ 1,450 $ 175 $ 650 $ 375 $ 135 $ 150 Est. Value $ 495 $ 250 $ 140 $ 3,920 $ 1,595 $ 775 $ 495 $ 178 $ 260 $ 495 $ 173 $ 1,250 $ 419 $ 150 $ 600 $ 251 $ 595 $ 1,070 $ 767 $ 30 $ 1,066 $ 488 $ 250 $ 325 $ 650 $ 281 $ 775 $ 1,450 $ 175 $ 1,448 $ 1,495 $ 248 $ 173
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors