HW #1, Excel #1 – Fall 2019 – Recycling Aluminum Cans –8 pts 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. This 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). Cecilia Tambor (everyone calls her “Sissy”) is the competition coordinator for the recycling project. Sissy has provided you with a rough starting point in the spreadsheet “HW1 – Fall 2019 – Recycling Cans.xls.” She has also provided you with additional data as identified in the text document of “Additional Data”. • • • First, Sissy 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 2019. 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 (1 pt). Determine the per capita recycling value to 1 decimal place. This will determine the contest winner for the first six months of 2019 (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? (1 pt). 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). (1.5 pt). Each town/city also makes some money from these recycling programs. • • • The initial value of each recycled aluminum can is currently 2.25 cents. However, aluminum prices fluctuate. Therefore, in addition to this basic assessment of a revenue rate of 2.25 cents/can, Sissy wants you to build/modify the spreadsheet so that she can compare two additional revenue rates, 2.21 cents/can and 2.27 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 Sissy 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. (Think about what this means for your design!)]. Figure out a way to have the spreadsheet automatically mark or indicate the NOVA city with the highest amount of recycling by month (1 pt). Finally display a column chart showing the NOVA cities recycling for February and April (1 pt). Homework Evaluation • All requested data fields should be used/calculated. Use the spreadsheet to do the calculations and to fill in the results. (In other words, just typing in the calculated values is not making use of the capabilities of the spreadsheet and will result in a major deduction in points.) Work toward a good layout for the data (e.g. 20,000 instead of 20000, $14,000 instead of 14000). Make sure all fonts are the same (size and type). (1 pt) • Make sure any labels you add are helpful/useful in explaining the cell content. (Note: Although Sissy has asked you to do this work, you should assume that others who are not as familiar with the contest will also look at the results and will need to be able to make sense of the output data without a lot of explanation.) Recycling Aluminum Cans Contest City Population Jan Feb March Ashburn, VA 43511 30550 35469 42765 Vienna, VA 16370 21452 22654 29456 658893 251013 375680 761490 Leesburg, VA 47673 55891 45011 85123 Fairfax, VA 23973 22452 21321 33190 Oakton, VA 34166 24765 26667 26917 Reston, VA 58404 23546 76231 56723 Middleburg, VA 1051 1231 1007 1025 Annandale, VA 41008 45678 47123 23145 Falls Church, VA 13508 23341 13564 18934 Chantilly, VA 23039 26005 19931 29997 Lovettesville, VA 1801 2751 2421 2941 Great Falls, VA 15427 10267 15678 18987 952 1041 1254 1543 McLean, VA 48115 17450 14870 20454 Arlington, VA 224906 90586 89456 86701 Merrifield, VA 15212 17423 16245 18754 Springfield, VA 30484 29456 26456 28912 Burke, VA 59469 121364 57236 89256 Fairfax Station, VA 17894 72167 71256 73421 Centreville, VA 71135 112354 175126 85963 Alexandria, VA 148892 10230 9845 9785 Manassas, VA 37821 12145 11432 12354 Purcellville, VA 982 1101 1204 1150 Washington, DC Washington, VA Total Per Capita April May June Washington, D.C. 79016 80531 85481 Arlington, VA 85818 55149 60060 Vienna, VA 19873 13058 19381 Chantilly, VA 30195 35997 45091 Leesburg, VA 90631 87541 69520 Ashburn, VA 55012 59072 59408 Oakton, VA 19875 20870 23951 Great Falls, VA 23687 35097 35198 Fairfax, VA 33081 36085 29839 Washington, VA 942 791 850 McLean, VA 29810 27714 28975 Lovettesville, VA 6318 10297 8075 Middleburg, VA 750 525 760 Annandale, VA 12456 17896 23461 Falls Church, VA34135 14562 14235 Reston, VA 21567 15342 18342 Burke, VA 13564 12236 12256 Centreville, VA 91354 85126 79456 Fairfax Station, VA 72563 80845 79945 Alexandria, VA 12132 12256 17645 Merrifield, VA 15123 15244 17154 Springfield, VA 21456 36456 22912 Manassas, VA 12789 13012 12542 Purcellville, VA 1203 1402 1103
Purchase answer to see full attachment