This is the scenario :

Scenario With several recent environmental disasters, many metropolitan areas have been looking for ways to be more environmentally conscious AND perhaps to make some money. For several years, towns/cities in Northern Virginia (NOVA) have been running trial programs for recycling paper, bottles, and cans. Last year, twenty three NOVA towns plus the District of Columbia decided to hold a competition to determine the municipality that did the best job of recycling, with the focus on aluminum cans. It is an important program given the outdoor opportunities around the region, including the Appalachian Trail, the C&O Canal, the Potomac River, and the Chesapeake Bay, among others. To make the contest fair, the winning town/city will be the one that recycles the largest number per capita, (e.g. the number of cans recycled divided by the number of residents for that town/city).

Roberta Tambor (everyone calls her Robby) is the competition coordinator for the recycling project. Robby has provided you with a rough starting point in the spreadsheet which is the one below:

1.) First, Robby would like for you to alphabetize the list of participating towns/cities in the Excel worksheet by state (with Virginia appearing at the top). This data in the Excel spreadsheet is for Jan-Mar 2018. Display the numbers and create the formulas to compute the total recycling for each city, by the year (for the data given). This should be a whole number in standard numerical display. Make sure you break out Washington DC from the Northern Virginia (NOVA) towns. Include the additional data provided in Additional Data Text document below:

2.)To Determine the per capita recycling value to 1 decimal place. This will determine the contest winner for the first six months of 2018 (including Washington, DC). Use conditional formatting to determine the largest per capita value for the Jan-June timeframe (e.g. after the six month period, which city would win?)

3.)In addition, she wants to know a few statistics about the monthly recycling efforts, including the minimum, average, and maximum values for the number of cans recycled by each month, i.e., what was the smallest number of cans recycled, the average, and the largest for each month). Break this out by Northern Virginia towns/cities (as the Washington DC numbers would constitute the smallest, average and largest number for each month) to one decimal place using formulas (not conditional formatting).

4.)

Each town/city also makes some money from these recycling programs.

• The initial value of each recycled aluminum can is currently 2.50 cents. However, aluminum prices fluctuate. Therefore, in addition to this basic assessment of a revenue rate of 2.50 cents/can, Robby wants you to build/modify the spreadsheet so that she can compare two additional revenue rates, 2.53 cents/can and 2.47 cents/can (1.5 pt). The results for all three rates should be displayed in the spreadsheet at the same time. Complete this spreadsheet so that Robby can assess the success and value of the recycling effort (i.e., the potential revenue) for each month (for all towns/cities combined) and in total for the combined six months recycling effort. Make sure this is displayed in a monetary format. [Note: These reimbursement values do sometimes fluctuate as the recycling market evolves. Therefore, you should set up the spreadsheet so that the revenue value(s) and related calculations can be easily and quickly adjusted/updated to reflect market fluctuations.

W1-Spring 2019-Recycling Cans (3) -Excel File Home Insert Draw Page Layout Formulas Data Review View HelpTell me what you want to do H F6 Recycling Aluminum Cans Contest Population Feb Total Per Capita 6 Ashburn, VA 7 Vienna, VA 8 Washington, DC 9 Leesburg, VA 10 Fairfax, VA 11 Oakton, VA 12 Reston, VA 13 Middleburg, VA 14 Annandale, VA 15 Falls Church, VA 16 Chantilly, VA 17 Lovettesville, VA 18 Great Falls, VA 19 Washington, VA 20 McLean, VA 21 Arlington, VA 22 Merrifield, VA 23 Springfield, VA 24 Burke, VA 25 Fairfax Station, VA 26 Centreville, VA 27 Alexandria, VA 28 Manassas, VA 29 Purcellville, VA 43511 30550 35469 16370 21452 22654 658893 251013 375680 47673 55891 45011 23973 22452 21321 34166 24765 26667 58404 23546 76231 1231 41008 4567847123 23341 23039 26005 19931 2341 15427 10267 15678 1041 48115 11366 14870 42765 29456 761490 85123 33190 26917 56723 1025 23145 18934 29997 2814 18987 980 20454 86701 18754 28912 89256 90231 85963 9785 12354 1150 1051 1007 13508 13564 1801 2421 952 980 224906 90586 89456 15212 17423 16245 30484 29456 26456 59469 121364 57236 17894 94469 71256 71135 112354 175126 10230 37821 12145 11432 1101 148892 9845 982 Sheet1 O Type here to search April May June 79016 80531 85481 Washington, D.C. Arlington, VA 85818 55149 60060 Vienna, VA Chantilly, VA 30195 35997 45091 Leesburg, VA 90631 92541 89528 Ashburn, VA Oakton, VA Great Falls, VA 23687 35097 35198 Fairfax, VA Washington, VA 942 McLean, VA Lovettesville, VA Middleburg, VA 750 Annandale, VA 12456 17896 23461 Falls Church, VA Reston, VA Burke, VA Centreville, VA 91354 85126 79456 Fairfax Station, VA Alexandria, VA 12132 12256 17645 Merrifield, VA 15123 15244 17154 Springfield, VA 21456 36456 2291.2 Manassas, VA12789 13012 12542 Purcellville, VA 19873 13058 19381 55012 59072 59408 19875 29870 23951 33081 36085 29839 29810 27714 28975 791 850 6318 10297 8075 760 525 34135 14562 14235 21567 15342 18342 13564 12236 12256 12563 10845 9945 1203 1402 1103 W1-Spring 2019-Recycling Cans (3) -Excel File Home Insert Draw Page Layout Formulas Data Review View HelpTell me what you want to do H F6 Recycling Aluminum Cans Contest Population Feb Total Per Capita 6 Ashburn, VA 7 Vienna, VA 8 Washington, DC 9 Leesburg, VA 10 Fairfax, VA 11 Oakton, VA 12 Reston, VA 13 Middleburg, VA 14 Annandale, VA 15 Falls Church, VA 16 Chantilly, VA 17 Lovettesville, VA 18 Great Falls, VA 19 Washington, VA 20 McLean, VA 21 Arlington, VA 22 Merrifield, VA 23 Springfield, VA 24 Burke, VA 25 Fairfax Station, VA 26 Centreville, VA 27 Alexandria, VA 28 Manassas, VA 29 Purcellville, VA 43511 30550 35469 16370 21452 22654 658893 251013 375680 47673 55891 45011 23973 22452 21321 34166 24765 26667 58404 23546 76231 1231 41008 4567847123 23341 23039 26005 19931 2341 15427 10267 15678 1041 48115 11366 14870 42765 29456 761490 85123 33190 26917 56723 1025 23145 18934 29997 2814 18987 980 20454 86701 18754 28912 89256 90231 85963 9785 12354 1150 1051 1007 13508 13564 1801 2421 952 980 224906 90586 89456 15212 17423 16245 30484 29456 26456 59469 121364 57236 17894 94469 71256 71135 112354 175126 10230 37821 12145 11432 1101 148892 9845 982 Sheet1 O Type here to search April May June 79016 80531 85481 Washington, D.C. Arlington, VA 85818 55149 60060 Vienna, VA Chantilly, VA 30195 35997 45091 Leesburg, VA 90631 92541 89528 Ashburn, VA Oakton, VA Great Falls, VA 23687 35097 35198 Fairfax, VA Washington, VA 942 McLean, VA Lovettesville, VA Middleburg, VA 750 Annandale, VA 12456 17896 23461 Falls Church, VA Reston, VA Burke, VA Centreville, VA 91354 85126 79456 Fairfax Station, VA Alexandria, VA 12132 12256 17645 Merrifield, VA 15123 15244 17154 Springfield, VA 21456 36456 2291.2 Manassas, VA12789 13012 12542 Purcellville, VA 19873 13058 19381 55012 59072 59408 19875 29870 23951 33081 36085 29839 29810 27714 28975 791 850 6318 10297 8075 760 525 34135 14562 14235 21567 15342 18342 13564 12236 12256 12563 10845 9945 1203 1402 1103