Task 3 - Pivot table Follow the directions shown below to prepare a PivotTable. Step 1: Insert a new Pivot table in the Analysis worksheet using the "Pivot Table" function on the "Insert" ribbon, as follows: . Select cell A7 in the Analysis worksheet . Insert a PivotTable based on the (entire) table of data in the RawData worksheet . The new Pivot table should appear on the Analysis worksheet. Step 2: Populate the Pivot table, as follows: In the PivotTables Fields pane, drag-and-drop the Sector field into the ROWS area. . On the Pivot table, change the column heading from Row Labels to Sector. . On the Pivot table, use the drop-down menu to filter the column, so that only the 5 sector types shown in cell B3 are selected (i.e., corresponding to the Sector filter). . In the PivotTables Fields pane, create the following four items in the VALUES area (by dragging-and-dropping the appropriate fields into the VALUES area). They will later be used to answer questions Q1-Q4 o Number of unique transactions (using the COUNT function). Format the column with a comma separating the thousands value (i.e, "7654" would be displayed as "7,654"), and do not include any decimal places (i.e., "4567" would be displayed as "4,567" and not "4567.00") o Average of quantity purchased per transaction. Format the column with a ",000" separator (i.e., a comma separating the thousands value so that "7654" would be displayed as "7,654") and show two (2) decimal places (i.e., "4567" would be displayed as "4,567.00") o Average of unit price. Format the column with a currency format (i.e., a value of 1234 should be shown as $1,234.00), and show two (2) decimal places Total dollar value of transactions for the sector ( using the SUM function). Format the column with a currency format (i.e., 1234 should be show as $1,234.00), and show two (2) decimal places. . Insert a slicer to the pivot table to filter your data based on the state where the transaction was made. For the slicer values, select only the 5 states shown in cell B4Energy, Financials, Health Care, What is the average Industrials, Q2 transaction quantity Information (across sectors)? Row Label filter Technology New Jersey, New Mexico, New York, Which sector has the Q3 lowest (average) unit Slicer Locations North Carolina, North price? Which sector has the Q4 highest total transaction dollar value? Insert Pivot Table in Cell A7 below SECTOR -1 Count of TransactionID Sum of Qty Sum of UnitPrice Sum of Total Dollar Value 3750 $26.14 #HHHH### 3M Company 44 332300 3251.74 23648464.25 8625 $419.75 #HHHH#### Abbott Laboratories 52 373325 4245.31 27334627.5 4150 $47.45 #HHHH#### AbbVie 41 273775 4060.16 25434644.5 10700 $44.17 #HH##### Accenture pic 45 286175 3437.23 20047395.75 7925 $16.71 #HHHHHH## ACE Limited 51 320200 6025.5 45855554.25 7175 $58.49 #HHHHHH### 50 313250 26169853.5 9975 $516.31 HHHH##### Activision Blizzard 4173.05 7500 $33.87 #HHHHHHHH# Adobe Systems Inc 33 211875 2302.19 15101529.75 399400 4913.3 33365513.5 $18.66 #HHHHHHH## ADT Corp 62 11725 51 326650 3987.58 29378944 2900 $98.41 ##HHHH### Advance Auto Parts AES Corp 52 380525 3715.46 27034656 2750 $67.52 #HHHHHHHH 44 257175 3055 18097806.5 1175 $64.80 #HHHHHHH# Aetna Inc 66 391375 4414.48 24480633.5 11225 $37.97 # ## # # # ### Affiliated Managers Group Inc 49 322725 5138.81 32154665.5 365 $9.30 ## # # # # ### AFLAC Inc 324325 5115.94 37215021.75 3400 $24.36 #HHHHHHHH# Agilent Technologies Inc Sheet4 RawData Customers Stocks Sectors States Sheet1 Sheet2 Sheet3 Analysis Ready Accessibility: Investigate 100,940 8 FEB itv 4 27 Mid section is for coffee already grinded MacBook Airfiltor critaria? Energy, Financials, State Row Health Care, What is the average Ticker Industrials, Q2 transaction quantity Label Information (across sectors)? Sector filter Technology J Otv New Jersey, New Slicer Mexico, New York, Sector North Carolina, North Filters 4 Locations Sort 2+ Ascending Descending Sort by: Sum of Total Dollar Value 6 Insert Pivot Table in Cell A7 bel 7 SECTOR TY Count of TransactionID S Filter 3750 $26.14 #HHHHHHHH# 8 Energy 8625 $419.75 #HHHHH### 9 Grand Total By label: Choose One 4150 $47.45 #HHHHHHHH#1# 10 10700 $44.17 #HHHHHHHH Equals energy 11 7925 $16.71 #HHHHHHHH 12 Search 7175 $58.49 #HHHHHH!!# ROWS 13 9975 $516.31 ##HHHHHH# 14 CenterPoint Energy 7500 $33.87 #HHHHHHHH# : Sector Chesapeake Energy 11725 $18.66 #HHHHHHHH Cimarex Energy 2900 $98.41 #HHHH!# 2750 $67.52 #HHHHHHHH CMS Energy 1175 $64.80 #HHHHHHHH# CONSOL Energy Inc. 11225 $37.97 #HHHHHH## 3650 $9.30 #HHHHHHHH## Devon Energy Corp. 3400 $24.36 #HH###### DTE Energy Co. 5975 $118.32 #HHHHHHH## Duke Energy 5550 $33.17 HHHHHHII# Drag fields b Customers Stocks + Sheet2 Sectors States Sheet1 Clear Filter Ready Accessibility: Inves 100,939 8 FEB 5 zoom X 27 O - 4 W A T Mid section is for coflee already grinded MacBook AirSafari Edit View History Bookmarks Window Help coursehero.com A Online eTextbook - MIS 112 Online Spring 2023 (Sections 20... A Excel 2016 Bible Case Analysis a FINAL. pdf upload. pdf CA3_Instructions.pdf Case Analysis 3 FINAL.pdf upload.pdf 1 30 Helpfu Answer: Search this document a 3 /4 Fit to width ~ : (a) Task 3 Create a bar Is this document missing answers? first step. The Get Answers Crop or click to get answers and explanations from our expert tutors Price are the Total Dollar third stage. Task 3(a): (c) Task 3: You must firs Sum of Total Dollar state. Then, Average of Sector Count of Transaction Qty Average of Unit Price Value Answer Hampshire, (a) Task 3: North Caroli Energy 246 6,281.00 $47.51 $76,047,353.00 Create a bar graph make a line for each Financials 565 6,476.28 $69.02 $259,331,940.00 Oklahoma to ransaction ID as. Re Task Step. The (a) Task 3: Health Care 333 6,338.21 $110.58 $230,368,307.25 srageofiGly and erodea ban 1. Create a 2. Select the Industrials 397 6,534.38 $71.60 $184, 628,578.50 bottom. Information Technology 409 6,772.37 $72.31 $210,482,708.25 3. Select " Filter" men Grand Total 1,950 6,502.00 $74.62 $960,858,887.00 4. Select "N "Sum of To Task 3(b): the first rov an average 5. Choose "Average o Montana you an ave Nebraska sector). 6. Choose Nevada asblimps grice "Average New Hampshire give you a New Jersey 7. Select I View answer create a c New Mexico 26 itv