Required information [The following information applies to the questions displayed below.] Chapman Realty Company is seeking your help in analyzing its sales across counties and property types over the last four months. During this time period the company's 12 realtors sold 99 properties (residential, commercial, and land) across six counties for a total of $30,637,161. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on PivotCharts in Excel. Click here for a brief tutorial on Conditional Formatting in Excel. Required: 1. Summarize each county's cumulative sales during the four-month period: a. Using PivotTable, calculate the cumulative sales for each county during the four-month period. b. Using PivotChart, create a bar chart that shows each county's cumulative sales during the four-month period. Save your completed worksheet to your computer and then upload it here by clicking "Browse. Next, click Save. ExcelAnalytics_SalesAnalysis of Business Segments2_Template (2) Q Search Sheet Sort & Filter + Share Find & Select Check for Updates Home Insert Draw Page Layout Formulas Data Review View Calibri (Body) 11 A+ A- E+ Wrap Text General Paste BIU Merge & Center $ % > .00 .00 +.0 Conditional Format Formatting as Table Cell Styles Insert Delete Format G H 1 J K L M N 0 P Q R S T U Realtor Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. fx B Selling Price C107 1 Month Type Realtor RealtorID County 2 October 5 157,500 Commercial David Pullen 101 McDowell 3 January $ 88,800 Commercial David Pullen 101 Rutherford 4 November $ 67,875 Commercial David Pullen 101 Buncombe 5 November $ 53,938 Commercial David Pullen 101 Rutherford 6 October $ 2,989,500 Commercial David Willis 102 Buncombe 7 November $ 2,027,375 Commercial David Willis 102 Henderson 8 January 5 505,000 Commercial David Willis 102 McDowell 9 January $ 242,500 Land David Willis 102 McDowell 10 January $ 236,300 Commercial David Willis 102 Polk 11 October $ 127,250 Residential David Willis 102 Polk 12 November $ 108,125 Commercial David Willis 102 Buncombe 13 October $ 100,000 Land David Willis 102 Polk 14 November $ 98,750 Residential David Willis 102 McDowell 15 January $ 64,400 Residential David Willis 102 Henderson 16 November $ 62,719 Commercial David Willis 102 Buncombe 17 December $ 51,359 Commercial David Willis 18 January $ 44,258 Land David Willis 19 October $ 3,014,750 Commercial Hal Keefer 102 Polk 102 Henderson 103 Rutherford 20 January 5 717,000 Commercial Hal Keefer 103 Cleveland 21 October $ 585,000 Commercial Hal Keefer 103 Rutherford 22 December $ 288,422 Commercial Hal Keefer 103 Cleveland 23 November $ 24 December $ 272,625 Land 204,469 Land Hal Keefer 103 Rutherford Hal Keefer 103 Cleveland 26 December 25 November $ $ 27 November $ 83,625 Residential Hal Keefer 103 Polk 49,831 Commercial Hal Keefer 103 McDowell 28 December $ 47,500 Residential 536,844 Commercial Hal Keefer 103 Cleveland Jon Dolan 104 McDowell 29 January $ 81,117 Commercial Jon Dolant 104 Buncombe 30 December $ 50,158 Commercial Jon Dolan 104 Buncombe 31 January $ 45,680 Land Jon Dolan 104 Buncombe 32 December $ 41,875 Commercial Jon Dolan 104 Buncombe 33 October $ 403,500 Commercial Arturo James 105 Buncombe 34 November $ 368,938 Commercial Arturo James 105 McDowell 35 October $ 36 January $ PivotTable 365,060 Land 287.000 Commercial PivotChart Arturo James 105 Rutherford Arturo James 105 Buncombe Conditional Formatting EA 6-2, Chapman Realty + H 100% ExcelAnalytics_SalesAnalysis of Business Segments2_Template (2) Home Insert Draw Page Layout Formulas Data Review View Calibri (Body) 11 A+ A- E+ Wrap Text General Paste BIU Merge & Center $ % > .00 .00 +.0 Conditional Format Formatting as Table Cell Styles Delete Format Q Search Sheet Insert Sort & Filter + Share Find & Select Check for Updates C107 Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. fx A B D G H 1 J K L M N P Q R S T U 36 January $ 37 October $ 287,000 Commercial 275,000 Residential Arturo James 105 Buncombe Arturo James 105 Henderson 38 December $ 218,000 Commercial Arturo James 105 Buncombe 39 October $ 40 November $ 41 January 42 December $ 214,500 Residential 98,156 Commercial 66,000 Residential 57,031 Commercial Arturo James 105 McDowell Arturo James 105 Buncombe Arturo James Arturo James 43 January 44,916 Residential Arturo James 44 January $ 22,000 Land Arturo James 45 December 950,000 Commercial Marcus Jackson 46 January $ 164,211 Commercial Marcus Jackson 47 October $ 151,500 Residential Marcus Jackson 48 January $ 102,105 Residential Marcus Jackson 49 December $ 60,316 Commercial 50 January $ 51 November 52 January $ 53 January 48,516 Residential 43,750 Residential 445,000 Commercial 245,000 Commercial Marcus Jackson Marcus Jackson Marcus Jackson Megan Cooper 105 Buncombe 105 Buncombe 105 Henderson 105 Polk 106 Henderson 106 Buncombe 106 Cleveland 106 Rutherford 106 Palk 106 Buncombe 106 Palk 107 Buncombe Megan Cooper 54 October $ 70,000 Land Megan Cooper 55 November $ 69,375 Residential Megan Cooper 107 Buncombe 107 Rutherford 107 Palk 56 November $ 57 November 55,000 Commercial Megan Cooper 107 Henderson 50,906 Land Megan Cooper 107 Henderson 58 December $ 47,344 Land Megan Cooper 107 Buncombe 59 October $ 354,600 Land Melanie Aaron 108 Rutherford 60 November $ 61 November $ 62 January 176,250 Commercial 118,650 Residential $ 60,559 Residential Melanie Aaron Melanie Aaron Melanie Aaron 108 Cleveland 108 Palk 108 Buncombe 63 December $ 59,663 Residential Melanie Aaron 108 Buncombe 64 January $ 50,279 Land Melanie Aaron 108 Rutherford 65 January $ 66 October $ 67 November 68 November $ 69 November $ 70 November $ 71 Janu 47,270 Commercial 2,671,500 Commercial 697,875 Commercial 202,530 Land Melanie Aaron 108 Buncombe Eun Jung Bae 109 Henderson Eun Jung Bae 109 Buncombe Eun Jung Bae 109 Henderson 80,633 Commercial 79,325 Land Eun Jung Bae 109 Cleveland Eun Jung Bae 109 Cleveland PivotTable idential PivotChart 100 Ball Conditional Formatting EA 6-2, Chapman Realty + 100% ExcelAnalytics_SalesAnalysis of Business Segments2_Template (2) Q Search Sheet Insert Sort & Filter + Share Find & Select Check for Updates Home Insert Draw Page Layout Formulas Data Review View Calibri (Body) 11 A+ A- E+ Wrap Text General Delete Paste BIU Merge & Center $ % > .00 .00 +.0 Conditional Format Formatting as Table Cell Styles Format C107 Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. fx A B E G H 1 J K L M N 0 P Q R S T U 70 November $ 79,325 Land Eun Jung Bae 109 Cleveland 71 January $ 67,500 Residential Eun Jung Bae 109 Polk 72 January $ 54,539 Land Eun Jung Bee 73 December $ 45,453 Residential Eun Jung Bae 74 January $ 75 November $ 1,150,000 Residential 1,033,688 Land Mitch Davis Mitch Davis 76 October $ 221,750 Commercial Mitch Davis 109 McDowell 109 Buncombe 110 Rutherford 110 Cleveland 110 Polk 77 October $ 197,300 Residential Mitch Davis 110 Henderson 78 December $ 122,234 Residential Mitch Davis 79 October $ 95,750 Residential Mitch Davis 110 Buncombe 110 Polk 80 January $ 42,840 Commercial Mitch Davis 110 Cleveland 81 October $ 312,500 Residential Nicole Ashby 111 McDowell 82 January $ 83 November $ 84 November $ 85 November $ 86 January 87 November $ 88 November $ 89 December $ $ 90 October $ 91 October $ 163,500 Commercial 156,313 Commercial 130,875 Commercial 121,265 Residential 95,000 Land 85,438 Commercial 74,063 Commercial 69,078 Commercial 1,355,750 Commercial 970,500 Commercial Nicole Ashby 111 McDowell Nicole Ashby 111 Henderson Nicole Ashby 111 Buncombe Nicole Ashby 111 McDowell Nicole Ashby 111 Buncombe Nicole Ashby 111 Henderson Nicole Ashby Nicole Ashby Pete Jacobs 111 Buncombe 111 Polk 112 Cleveland Pete Jacobs 112 Buncombe 92 October $ 505,250 Commercial Pete Jacobs 112 Cleveland 93 January $ 486,800 Commercial Pete Jacobs 112 Polk 94 January $ 95 January $ 96 November $ 97 November $ 98 November $ 99 January 100 December 5 $ 138,150 Land 129,000 Residential 61,813 Residential 54,688 Commercial 45,969 Residential 41,420 Commercial 40,938 Land Pete Jacobs 112 Cleveland Pete Jacobs 112 Buncombe Pete Jacobs 112 Buncombe Pete Jacobs 112 Polk Pete Jacobs Pete Jacobs 112 Rutherford 112 Polk Pete Jacobs 112 Henderson 101 102 103 104 105 PivotTable PivotChart Conditional Formatting EA 6-2, Chapman Realty 100%