Given the following data, answer the questions on the additional tabs. To receive credit, all work must be completed by using Excel functions and formulas. I will click on these cells to view your work. There are 103 total points on this assignment. Estimated Annual Sales for U.S. Electronic Shopping and Mail-Order Houses (NAICS 45411) - Total and E-commerce' Sales by Merchandise Line: 1999-2015 [Estimates are shown in millions of dollars and are based on data from the Annual Retail Trade Survey. Estimates have been adjusted using final results of the 2012 Economic Census.] Data Source: www.census.gov 2015 2014' 2013 2012' 2011 Merchandise Line Total E-commerce Total E-commerce Total E-commerce e Total E-commerce Total 1 E-commerce Total Electronic Shopping and Mail-Order Houses (NAICS 45411) ... 433,692 294,809 386,065 256,269 350,487 221,587 328,655 294,697 168,91 Books and magazines ... 12,583 11,648 11,879 10,828 11,444 10,249 10,991 9,778 10,160 8,773 Clothing and clothing accessorie 58,188 52,128 53,751 6,830 46,681 40,198 40,451 33,614 35,13 28,196 Computer hardware . S 17,459 29.527 16,884 26,981 15,330 25.640 14,240 24.948 13,973 Computer software .... 5 9,594 6,491 8,329 5,425 8,093 5,425 6,854 4,493 Drugs, health aids, and beauty aids 21,469 93,586 19,112 91,001 17,456 90,714 14,612 82,424 11,845 Electronics and appliances 0,016 26.050 28.158 24,234 27.541 23.727 26,573 22.580 24,622 20,975 ood, beer, and wine .. 9,841 7.579 8,636 6,349 7,783 5,267 7,070 4,934 5.985 3.984 Furniture and home furnishings 32,369 28,883 26,599 23,311 22,856 19,588 19,420 5,917 16,900 13,536 Music and videos ... 11.263 10,325 9.914 8.964 8,225 7.241 Office equipment and supplies 10,613 9.236 8,042 8,579 6,869 8,136 6,432 Sporting Goods ..... 13,104 11,681 10,910 9,347 9,914 7,927 8.506 6,356 6,982 5,103 Toys, hobby goods, and games 12,11 10.682 10,623 8.997 9.125 7,801 6,091 6,794 5,260 Other merchandise". 68,378 52,893 55,104 41,543 47,981 34,491 44,679 30,045 39,998 25,491 Nonmerchandise receipts" 29,153 26,374 25,500 22,317 21,546 17,721 20,224 16,052 17,532 13,617 Additional Information NA - Not Available S - Estimate does not meet publication standards because of high sampling variability (coefficient of variation is greater than 30%), poor response quality (total quantity response rate is less than 50%), or other concerns about the estimate's quality. Unpublished estimates derived from this table by subtraction are subject to these same limitations and should not be attributed to the U.S. Census Bureau. For a description of publication standards and the total quantity response rate, see http://www.census.gov/about/policies/quality/standards/standardf1.html. Footnotes E-commerce sales are sales of goods and services where the buyer places an order, or the price and terms of the sale are negotiated, over an Internet, mobile device (M-commerce), extranet, Electronic Data Interchange (EDI) network, electronic mail, or other comparable online system. Payment may or may not be made online. Includes other merchandise such as collectibles, souvenirs, auto parts and accessories, hardware, lawn and garden equipment and supplies, and jewelry. " Includes nonmerchandise receipts such as auction commissions, customer trainin tomer training, customer support, advertising, and shipping and handling. Revised Data Note: Sales estimates are shown in millions of dollars, consequently industry group estimates may not be additive. Note: Estimates are not adjusted for price changes. Additional information on confidentiality protection, sampling error, nonsampling error, sample design, and definitions can be found at . 2010 2009 2008 2007 2005 Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-Commerce Total E-commerce Total E-commerce Total 263,488 143,000 235,625 121,173 229,242 114,642 223,681 105,467 202,251 85,945 175,900 68,114 154,157 52,996 134,304 8,860 7,428 7,569 6,000 7,642 5,599 7,185 5,370 5,980 3,694 5,438 2,903 4,930 2,270 4,446 31,38 23,480 27,300 19,725 25,545 17,637 24,314 16,036 21,075 12,887 18,562 9,463 15,681 7,054 14,497 22,420 12,080 20,141 11,223 20,923 11,394 22,855 11,694 20,176 10,373 20,601 9.345 20,277 8,006 18,366 5,834 3,676 5.432 3.440 4.906 2.887 4.597 2.613 3,845 2.023 3,706 1,834 3,089 1,456 2,930 75,318 69,164 64,783 7,586 61,859 6,473 63,305 5,416 49,810 4,270 43,338 3,574 34,783 21,732 17,861 18,798 15,087 17,457 13,670 15,991 11,955 13.925 9,938 10,953 8,084 9.277 7,151 5,267 3,372 4,824 2.966 4.891 3.033 5.132 2,996 4,213 2,280 3,406 1,608 2,520 1,030 2,172 14,518 11,150 12,75 9.262 13,303 9,540 13.444 9,061 12,028 7,857 10,576 5,620 8,515 4,023 8,057 6,107 5,064 5.321 4.208 4.638 3,379 3.869 2,605 3,412 1,864 3.591 1,480 3,473 7,822 6,009 7,730 5,975 8,183 6,425 8,097 5,922 5,959 4,919 6,112 5,194 6,437 4.408 6,058 3,945 5,824 3.535 5,567 3,178 4,502 2,402 4,179 1,912 3,579 1,430 3.053 6,022 4,470 5.384 3.639 5,667 3,445 5,414 3,112 4,638 2,536 4,649 2,240 4,133 1,758 4.049 35,221 20,851 30,895 16,613 31,314 16,353 31,145 14,739 26,416 11,979 25,169 9,838 21,632 7,152 19,357 15,548 11,222 13,464 9,369 13,483 9,330 13,443 8,939 11,320 7,036 9,327 5,281 8,242 4,379 6,776 2003 2002 2001 2000 1999 E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce 41,962 122,214 33,764 114,749 21,221 113,790 21,221 94,282 11,615 1,994 4,267 1,787 4,137 1,644 4,297 1,759 3,434 1,439 5,501 14,477 4,545 15,712 3,325 15,415 2,262 13,535 909 6,580 18,718 6,079 21.128 5,419 27,221 6.0491 23,846 ,166 1,192 3,661 1,239 3,983 1.157 3,706 1.109 2,809 755 2,606 27,471 1,849 19,707 1,122 15,557 701 11,557 239 4,109 5.520 2.704 4,535 1 840 3.641 1,128 2,709 504 820 2,014 714 2,037 519 1.960 591 1,429 235 3,283 7.290 2,412 6,696 1,654 6,556 1,006 5,822 350 1,384 3.902 1,352 4,083 1,211 4.433 1,154 762 2,929 5.099 2,285 5,740 1.824 6,600 1,375 7,284 592 1,155 3.073 1,028 1,906 517 1,794 409 NA 1,610 3,775 1,259 3,138 896 3,174 806 2,162 383 5,548 17,539 4,062 17,676 2,997 16,249 1, 866 NA NA 3,251 5,408 2,449 4,271 1,763 3,187 1,006 1,126 304Question 1 (1 points] First, extract the necessary data from the first tab. Create a column to the right with the e-commerce data totals beginning with 1999 to 2015 Tip: In cell P7, enter the =, navigate to the Data sheet and click on cell Al12, then hit Enter. You will automatically be sent back to this worksheet and you will see 11,615 in cell P7. Question 2 (5 points]: TOTAL E-COMMERCE Construct a bar chart showing the total e-commerce dollars for years 1999 to 2015. Create a descriptive chart title and axis titles. YEAR DOLLARS [in millions of dollars) Make sure that both axes are labeled with appropriate data intervals and an axis title. Experiment with the formatting options to create a professional appearance and for clear interpretation of information - remember that the e-commerce totals are in millions of dollars. Question 3 (10 points): Using the given data on the first tab, complete the table below. Calculations using EXCEL functions and formulas must be used. Without using the words, mean, median, mode, range, and Standard deviation, explain what each result represents. Mean E-Commerce Total from 1999 to 2015 = Median E-Commerce Total from 1999 to 2015 = Mode E-Commerce Total from 1999 to 2015 = Range of E-Commerce Total from 1999 to 2015 = Standard Deviation of E-Commerce Totals from 1999 to 2015 = NOTE: You have been given population data, so use the correct XL function for the standard deviation.. Question 4 (2 points] Compare the mean e-commerce total to the median e-commerce total from Question 1, and complete this statement with the appropriate responses Since the mean e-commerce total is (, >=) the median e-commerce total, then the data set is likely to be skewed right, skewed left, or symmetric Question 5 (S points]: Use the EXCEL function QUARTILE.EXC function to calculate the following results. Explain the meaning of "lower quartile" and "upper quartile". Lower Quartile (Q1) = Upper Quartile (03)EInterquartile Range = Question 6 (10 points): In preparation for a box plot, gather the following results of the e-commerce data from 1999 to 2015. Explain the significance (do not state the formula] of the lower and upper fences. Minimum = Lower Quartile = Median = Upper Quartile = Max = Interquartile Range = Lower Fence = Upper Fence = List the outliers, if any. Question 7 (8 points]: Using the data from Question 1, construct a box plot. Identify and label all & results from Question 6 in the box plot. Verify the accuracy of the box plot with the results in Question 6. If QUARTILE EXC was used, results will be identical