Determine the per capita recycling value
ssignment – Spring 2016 – Recycling Aluminum Cans
Scenario – With several recent environmental disasters (e.g., BP oil spill, Fukushima Daiichi Nuclear Power Plant, Hurricanes Katrina, Irene and Sandy, etc.), 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 have been running trial programs for recycling paper, bottles, and cans. Last year, twenty one 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, i.e., the number of cans recycled divided by the number of residents for that town/city.
Ali Tambor (everyone calls her “Al”) is the competition coordinator for the recycling project. Al has provided you with some data (and a rough starting point for the spreadsheet) in the file “HW1 – Spring 2016 – Recycling Cans.xls .”
• First, Al would like for you to alphabetize the list of participating towns/cities in an Excel worksheet according to the state. Display the numbers and create the formulas to compute the total recycling foreach city by the year (for the data given). This should be a whole number in the standard display as discussed in the Excel data validation handout. Make sure you break out Washington DC from the Northern Virginia (NOVA) towns. Include the additional data provided in Additional Data.
• Determine the per capita recycling value to 1 decimal place. This will determine the contest winner for the first six months of 2015 (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?
• 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 for 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.
Each town/city also makes some money from these recycling programs.
• The initial value of each recycled aluminum can is currently 3 cents. However, aluminum prices fluctuate. Therefore, in addition to this basic assessment of a revenue rate of 3 cents/can, Al wants you to build/modify the spreadsheet so that she can compare two additional revenue rates, 2.81 cents/can and 3.23 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 Al 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.
• Finally display a column chart showing the NOVA cities recycling for March and May.
• 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).
• Make sure any labels you add are helpful/useful in explaining the cell content. (Note: Although Al 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.)
