Q1. Based on the data set on the left, create a pivot table to summarize what is the total sales revenue for each sales channel across four quarters in 2012 Q2. Based on the pivot table, create a pivot chart (line chart) and a pivot chart (column chart) to show total sales revenue for each sales channel across four quarters (use insert PivotChart function) Q3. Which pivot chart provides better visualization to compare sales revenue in different sales channel across four quarters? 142 1991 188 Distributor Name Devin Abbott Aphrodite Brennan Guinevere Key Zahir Fields Deacon Craig Brynne Mcgowan Lani Sweet Noble Warner Levi Douglas Jelani Odonnell Jared Sandoval Hiroko Acevedo Rhona Clarke Tad Mack Rama Goodwin Keaton Wolfe Samuel Ayala Doris Williams Ingrid Bush Nell Maddox Benedict Byrd Ethan Gregory Ursula Mcconnell Fletcher Jimenez Isadora Mcclure Liberty Mcbride Noble Gilbert Maxine Gentry Melinda Cobb Yael Carter Kay Buckley Athena Fitzpatrick Joy Vazquez Amery Frazier Buckminster Hopkins George Best Maxwell Parker Lance Little Gwendolyn Walton Isaac Wolf Celeste Pugh Oprah Ellis Emerson Beard Renee Padilla Maite Henson Ivory Chang Clark Weaver Ima Cummings Adria Kaufman Nyssa Quinn Amir Alexander Imogene Bradshaw Gwendolyn Mccarty Bell Prince Katelyn Joseph Robert Juarez Jerry Alvarado India Gilbert Iliana Porter Deanna Santana Ivor Mclaughlin Latifah Wall Anika Tillman Paul Duke Sawyer Stokes Xerxes Smith Wanda Garza Anjolie Hicks Asher Weber Mercedes Humphrey Hayes Rollins Josiah Yates Winifred Cantu Germaine Kidd Kenyon Joyce Joel Rivers Colby Knapp Country Sales Channel France Online Malawi Direct Colombia Retail Canada Direct Mongolia Retail Finland Online Vanuatu Online Burkina Faso Retail Tanzania, United Rep Online Albania Retail Botswana JOnline Burundi Online Zimbabwe Online Ilceland Retail Tunisia Online French Southern Ter Retail Brazil Retail Trinidad and Tobago Online Montserrat Direct Azerbaijan Online Mauritania Online Tuvalu Retail Hungary Online Chad Retail Indonesia Retail Fiii Online United States Online Panama Retail Uruguay Retail Malaysia Direct Malta Direct Reunion Retail Korea Retail Georgia Retail Sierra Leone JOnline Western Sahara Retail Falkland Islands (Mal Retail Croatia Direct Cuba Retail Panama Online Gabon Online Dominican Republic Retail Niue Retail Yemen lonline Bangladesh Online Tonga Retail Palau Retail Philippines Online Bouvet Island Retail Cocos (Keeling) Islar Online Liberia Retail Niger Direct Madagascar Online Guinea Retail Slovenia Retail Svalbard and Jan Ma Retail Korea, Republic of Online Denmark Retail Poland LOnline Solomon Islands Retail United States Minor Retail Guadeloupe JOnline Burkina Faso Retail Puerto Rico Online I Malta Online Panama Online Kyrgyzstan Retail Turks and Caicos Isla Direct Macedonia Retail Turkey Online Nepal Online Bangladesh Online Kazakhstan Online Niger Retail Canada Online Australia Retail Pakistan Online Date Sold 8/29/12 8/24/12 8/17/12 6/10/12 8/2/12 6/18/12 6/24/12 8/13/12 8/18/12 8/18/12 8/26/12 6/25/12 1/7/12 8/25/12 8/11/12 1/15/12 6/22/12 B/15/12 6/14/12 6/28/12 1/7/12 1/4/12 8/12/12 1/31/12 6/22/12 1/3/12 8/13/12 8/10/12 1/23/12 2/11/12 2/17/12 2/27/12 6/17/12 17/22/12 6/23/12 8/12/12 7/19/12 16/15/12 7/9/12 17/27/12 8/11/12 17/4/12 8/25/12 RIB/12 7/24/12 7/22/12 16/17/12 16/8/12 7/13/12 8/3/12 8/21/12 7/23/12 18/3/12 7/7/12 8/21/12 6/1/12 8/18/12 16/8/12 777/12 6/1/12 7/2/12 17/12/12 17/12/12 6/28/12 8/5/12 7/6/12 6/7/12 7/31/12 8/22/12 7/3/12 18/1/12 8/23/12 | 12/22/12 6/27/12 6/17/12 6/11/12 12/29/12 Quantity Unit Price Revenue 208 $14.50 3,016 197 $14.50 2,856.5 176 $14.50 2,552 168 $14.50 2,436 166 $14.50 2,407 157 $14.50 2,276.5 $14.50 2,059 193 $9.99 1,928.07 205 $9.00 1,845 $9.00 1.791 1881 $9.00 1,692 $9.00 1,692 167 $9.99 1,668.33 163 $9.99 1,628.37 1701 $9.00 1,530 1021 $14.50 1.4791 160 $9.00 1,440 204 $6.99 1,425.96 141 $9.99 1,408.59 147 $9.00 1323 1841 $6.99 1,286.16 141 $9.00 1,2691 135 $9.00 1,215 1761 $6.50 1.1441 184 $6.00 1104 731 $14.50 1,058.5 116 $9.00 1,044 | 95 $9.99 949.05 203 $4.50 913.51 203 $4.50 9135 1961 $4.50 882 125 $6.99 873.75 189 $4.50 850.5 1261 $6.50 8191 2011 $3.99 801.99 178 $4.50 801 108 $6.99 754.92 125 $6.00 750 165 $4.50 742.5 105 $6.50 682.5 1701 $3.99 678.3 $6.00 678 150 $4.50 69 $9.00 l 6211 95 $6.50 617.51 95 $6.50 617.5 135 $4.50 607.5 93 $6.50 604.51 1341 $4.50 6031 151 $3.99 602.49 100 $6.00 6001 85 $6.99 594.15 84 $6.99 587.16 $6.99 573.18 $4.50 567 137 $3.99 546.63 1791 $3.00 537 37 $14.50 536.5 89 $6.00 534 131 $3.99 522.69 77 $6.50 500.5 82| $6.00 492 109 $4.50 490.5 122 $3991 486.781 106 $4.50 731 $6.50 474.51 67 $6.99 468.33 71 $6.50 461.51 153 $3.00 459 47 $9.00 423 65 $6.50 422.5 42 $9.99 419.58 $6.50 4161 104 $3.99 414.96 T 41 $9.99 409.59 $9.99 409.59 135 $3.00 405 675 82 126 477 64 411 129 85 116) 1121 48 501 43 181 65 41 771 57| 22 Vance Campos Lael Gould Jane Hernandez Dara Cunningham Colette Sargent Shea Cortez Cyrus Whitley Eleanor Hopper Forrest Macdonald Desirae Perkins Barrett Mckinney Basil Vang Noel Key Ebony Mercer Isaac Cooper James Spencer Clark Orr Phillip Perkins Uriel Benton Aretha Patton Thomas Barnes Victoria Solis Arsenio Knowles Ryder Conner Roary Dixon Silas Battle Leonard Cardenas Brittany Burris Petra Mckenzie Angela Wise Syrian Arab Republic Online El Salvador Online Macedonia Online Saint Helena Retail Norfolk Island Online India Online Denmark Retail Madagascar Online New Caledonia Retail Chile Online Syrian Arab Republic Online Moldova Online Gambia Retail Cape Verde Online Netherlands Antilles Online Burkina Faso Online Indonesia Retail Nigeria Online South Africa Retail Bouvet Island Online Mayotte Online Palau Online Malaysia Retail Virgin Islands, British Direct Saudi Arabia Retail Niue Online Madagascar Retail Palau Retail Morocco Retail Moldova Online 7/11/12 7/5/12 6/3/12 7/9/12 6/21/12 7/9/12 7/17/12 6/15/12 6/12/12 12/27/12 6/13/12 17/11/12 4/27/12 12/14/12 12/13/12 4/3/12 4/29/12 12/12/12 12/5/12 7/29/12 7/6/12 4/27/12 4/10/12 7/28/12 4/24/12 12/17/12 7/24/12 4/30/12 7/1/12 4/21/12 $3.00 $4.50 $3.00 $3.00 $3.99 $6.50 $6.00 $6.50 $14.50 $3.99 $6.99 $6.00 $3.00 $3.99 $9.99 $6.99 $3.00 $6.50 $9.99 $9.99 $3.99 $4.50 $3.00 $4.50 $4.50 $3.00 $4.50 $6.99 $4.50 $3.00 387 382.5 348|| 3361 319.2 3121 3001 279.51 261 259.35 258.63 246 231 227.43 219.78 209.71 189 1431 139.86 | 129.87 111.72 721 601 541 45 42 40.5| 34.95 31.5 301 30 63 221 14 131 28 161 201 121 10 14 5 7 10 Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Problem 7 Q1. Based on the data set on the left, create a pivot table to summarize what is the total sales revenue for each sales channel across four quarters in 2012 Q2. Based on the pivot table, create a pivot chart (line chart) and a pivot chart (column chart) to show total sales revenue for each sales channel across four quarters (use insert PivotChart function) Q3. Which pivot chart provides better visualization to compare sales revenue in different sales channel across four quarters? 142 1991 188 Distributor Name Devin Abbott Aphrodite Brennan Guinevere Key Zahir Fields Deacon Craig Brynne Mcgowan Lani Sweet Noble Warner Levi Douglas Jelani Odonnell Jared Sandoval Hiroko Acevedo Rhona Clarke Tad Mack Rama Goodwin Keaton Wolfe Samuel Ayala Doris Williams Ingrid Bush Nell Maddox Benedict Byrd Ethan Gregory Ursula Mcconnell Fletcher Jimenez Isadora Mcclure Liberty Mcbride Noble Gilbert Maxine Gentry Melinda Cobb Yael Carter Kay Buckley Athena Fitzpatrick Joy Vazquez Amery Frazier Buckminster Hopkins George Best Maxwell Parker Lance Little Gwendolyn Walton Isaac Wolf Celeste Pugh Oprah Ellis Emerson Beard Renee Padilla Maite Henson Ivory Chang Clark Weaver Ima Cummings Adria Kaufman Nyssa Quinn Amir Alexander Imogene Bradshaw Gwendolyn Mccarty Bell Prince Katelyn Joseph Robert Juarez Jerry Alvarado India Gilbert Iliana Porter Deanna Santana Ivor Mclaughlin Latifah Wall Anika Tillman Paul Duke Sawyer Stokes Xerxes Smith Wanda Garza Anjolie Hicks Asher Weber Mercedes Humphrey Hayes Rollins Josiah Yates Winifred Cantu Germaine Kidd Kenyon Joyce Joel Rivers Colby Knapp Country Sales Channel France Online Malawi Direct Colombia Retail Canada Direct Mongolia Retail Finland Online Vanuatu Online Burkina Faso Retail Tanzania, United Rep Online Albania Retail Botswana JOnline Burundi Online Zimbabwe Online Ilceland Retail Tunisia Online French Southern Ter Retail Brazil Retail Trinidad and Tobago Online Montserrat Direct Azerbaijan Online Mauritania Online Tuvalu Retail Hungary Online Chad Retail Indonesia Retail Fiii Online United States Online Panama Retail Uruguay Retail Malaysia Direct Malta Direct Reunion Retail Korea Retail Georgia Retail Sierra Leone JOnline Western Sahara Retail Falkland Islands (Mal Retail Croatia Direct Cuba Retail Panama Online Gabon Online Dominican Republic Retail Niue Retail Yemen lonline Bangladesh Online Tonga Retail Palau Retail Philippines Online Bouvet Island Retail Cocos (Keeling) Islar Online Liberia Retail Niger Direct Madagascar Online Guinea Retail Slovenia Retail Svalbard and Jan Ma Retail Korea, Republic of Online Denmark Retail Poland LOnline Solomon Islands Retail United States Minor Retail Guadeloupe JOnline Burkina Faso Retail Puerto Rico Online I Malta Online Panama Online Kyrgyzstan Retail Turks and Caicos Isla Direct Macedonia Retail Turkey Online Nepal Online Bangladesh Online Kazakhstan Online Niger Retail Canada Online Australia Retail Pakistan Online Date Sold 8/29/12 8/24/12 8/17/12 6/10/12 8/2/12 6/18/12 6/24/12 8/13/12 8/18/12 8/18/12 8/26/12 6/25/12 1/7/12 8/25/12 8/11/12 1/15/12 6/22/12 B/15/12 6/14/12 6/28/12 1/7/12 1/4/12 8/12/12 1/31/12 6/22/12 1/3/12 8/13/12 8/10/12 1/23/12 2/11/12 2/17/12 2/27/12 6/17/12 17/22/12 6/23/12 8/12/12 7/19/12 16/15/12 7/9/12 17/27/12 8/11/12 17/4/12 8/25/12 RIB/12 7/24/12 7/22/12 16/17/12 16/8/12 7/13/12 8/3/12 8/21/12 7/23/12 18/3/12 7/7/12 8/21/12 6/1/12 8/18/12 16/8/12 777/12 6/1/12 7/2/12 17/12/12 17/12/12 6/28/12 8/5/12 7/6/12 6/7/12 7/31/12 8/22/12 7/3/12 18/1/12 8/23/12 | 12/22/12 6/27/12 6/17/12 6/11/12 12/29/12 Quantity Unit Price Revenue 208 $14.50 3,016 197 $14.50 2,856.5 176 $14.50 2,552 168 $14.50 2,436 166 $14.50 2,407 157 $14.50 2,276.5 $14.50 2,059 193 $9.99 1,928.07 205 $9.00 1,845 $9.00 1.791 1881 $9.00 1,692 $9.00 1,692 167 $9.99 1,668.33 163 $9.99 1,628.37 1701 $9.00 1,530 1021 $14.50 1.4791 160 $9.00 1,440 204 $6.99 1,425.96 141 $9.99 1,408.59 147 $9.00 1323 1841 $6.99 1,286.16 141 $9.00 1,2691 135 $9.00 1,215 1761 $6.50 1.1441 184 $6.00 1104 731 $14.50 1,058.5 116 $9.00 1,044 | 95 $9.99 949.05 203 $4.50 913.51 203 $4.50 9135 1961 $4.50 882 125 $6.99 873.75 189 $4.50 850.5 1261 $6.50 8191 2011 $3.99 801.99 178 $4.50 801 108 $6.99 754.92 125 $6.00 750 165 $4.50 742.5 105 $6.50 682.5 1701 $3.99 678.3 $6.00 678 150 $4.50 69 $9.00 l 6211 95 $6.50 617.51 95 $6.50 617.5 135 $4.50 607.5 93 $6.50 604.51 1341 $4.50 6031 151 $3.99 602.49 100 $6.00 6001 85 $6.99 594.15 84 $6.99 587.16 $6.99 573.18 $4.50 567 137 $3.99 546.63 1791 $3.00 537 37 $14.50 536.5 89 $6.00 534 131 $3.99 522.69 77 $6.50 500.5 82| $6.00 492 109 $4.50 490.5 122 $3991 486.781 106 $4.50 731 $6.50 474.51 67 $6.99 468.33 71 $6.50 461.51 153 $3.00 459 47 $9.00 423 65 $6.50 422.5 42 $9.99 419.58 $6.50 4161 104 $3.99 414.96 T 41 $9.99 409.59 $9.99 409.59 135 $3.00 405 675 82 126 477 64 411 129 85 116) 1121 48 501 43 181 65 41 771 57| 22 Vance Campos Lael Gould Jane Hernandez Dara Cunningham Colette Sargent Shea Cortez Cyrus Whitley Eleanor Hopper Forrest Macdonald Desirae Perkins Barrett Mckinney Basil Vang Noel Key Ebony Mercer Isaac Cooper James Spencer Clark Orr Phillip Perkins Uriel Benton Aretha Patton Thomas Barnes Victoria Solis Arsenio Knowles Ryder Conner Roary Dixon Silas Battle Leonard Cardenas Brittany Burris Petra Mckenzie Angela Wise Syrian Arab Republic Online El Salvador Online Macedonia Online Saint Helena Retail Norfolk Island Online India Online Denmark Retail Madagascar Online New Caledonia Retail Chile Online Syrian Arab Republic Online Moldova Online Gambia Retail Cape Verde Online Netherlands Antilles Online Burkina Faso Online Indonesia Retail Nigeria Online South Africa Retail Bouvet Island Online Mayotte Online Palau Online Malaysia Retail Virgin Islands, British Direct Saudi Arabia Retail Niue Online Madagascar Retail Palau Retail Morocco Retail Moldova Online 7/11/12 7/5/12 6/3/12 7/9/12 6/21/12 7/9/12 7/17/12 6/15/12 6/12/12 12/27/12 6/13/12 17/11/12 4/27/12 12/14/12 12/13/12 4/3/12 4/29/12 12/12/12 12/5/12 7/29/12 7/6/12 4/27/12 4/10/12 7/28/12 4/24/12 12/17/12 7/24/12 4/30/12 7/1/12 4/21/12 $3.00 $4.50 $3.00 $3.00 $3.99 $6.50 $6.00 $6.50 $14.50 $3.99 $6.99 $6.00 $3.00 $3.99 $9.99 $6.99 $3.00 $6.50 $9.99 $9.99 $3.99 $4.50 $3.00 $4.50 $4.50 $3.00 $4.50 $6.99 $4.50 $3.00 387 382.5 348|| 3361 319.2 3121 3001 279.51 261 259.35 258.63 246 231 227.43 219.78 209.71 189 1431 139.86 | 129.87 111.72 721 601 541 45 42 40.5| 34.95 31.5 301 30 63 221 14 131 28 161 201 121 10 14 5 7 10 Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Problem 7
