Homework #1 (Excel #1) – Fall 2014 – Recycling Aluminum Cans –8 pts
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 (2013), fourteen towns plus the District of Columbia decided to hold a

competition to determine the municipality that can do 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 – Fall 2014 – Recycling Cans.xls .”
•    First, Al would like for you to alphabetize the list of participating towns/cities in an Excel worksheet.  Display the numbers and create the formulas to

compute the total recycling for each city by year.  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 NOVA.  Include the additional data provided in Additional Data Word document (1 pt).
•    Determine the per capita recycling value to 1 decimal places.  This will determine the contest winner for the first six months of 2013 (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, who wins?) (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 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 (1.5 pt).

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 (1 pt).
•    Finally display a column chart showing the NOVA cities recycling for March and May (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 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.)

Additional Recycling Data for April, May and June 2013

(Note that the data below may not be in the same order as the data in the spreadsheet!)

April    May    June

Washington, DC    789016    800531    850481
Arlington    185818    95149    90060
Vienna    19,873    13,058    99,381
Chantilly    30195    35997    45091
Leesburg    97631    107541    119590
Ashburn    55012    59072    59408
Oakton    19875    29870    23951
Great Falls    23,687    35,097    35,198
Fairfax    33081    36085    29839
McLean    29,810    27,714    28,975
Lovettesville    4,318    1,297    3,075
Middleburg    1864    2525    7106
Annandale    12456    17896    23461
Falls Church    34135    14562    14235
Reston    21567    15342    18342

Excel Assignment.

Marks            WangQiuxiang652675
1) Do income statements for                    2014        8
2) Do Balance Sheets for                    2014        8
3) Do a pro forma Income Statement for                    2015        10
4) Do a pro forma Balance Sheet for                    2015        10
5) Do Cash Flow Statement for                    2014        10
6) Do ratios for                2013    2014        10
7) Do pro forma Ratios for                    2015        10
8) Do a pro forma cash budget for                    2015        14
9) Do breakeven questions for                 2014    2015        10
10) Do the theory problem below                            10
Total                            100

Theory Problem
It is June        2015        You work at this company.
One of the owners would like to sell some of her shares so she can buy a cottage.
She is offering to sell you 100,000 shares for                      $3.50     each
Assume you have the money to buy them.
Do you buy them ?
Give me reasons for your answer based on such things as book value, return on
investment, trends, cash flow, SME Benchmarking and so on.
2014 Data
WangQiuxiang652675            2014        Other Info        2014        2013        2015
Accounts payable             $980,882         Common Stock         400,000     shares     400,000     shares     400,000     shares
Accounts receivable             $630,628
Amortization expense             $38,000
Beginning Inventory             $760,000         Notes Payable        current
Cash             $343,828         Sales on Credit        90%    percent of net sales
Common stock             $100,000
Ending Inventory             $964,831         Share Price    2013     $1.60     per share    Share Prices based on accounting department’s estimates.
Freight In             $39,660         Share Price    2014     $2.75     per share
Interest expense             $63,401
GIC’s             $300,000         The directors have decided that no new shares will be issued/sold in 2015
Mortgage payable             $911,115
Net Purchases             $2,757,002
Net Sales             $5,717,665
Notes payable             $200,000         Tax rate        40%    Of Net Income    Taxes on the year’s profits are paid in December
Prepaid expenses             $42,089         Amortization Expense        4%     declining balance
Plant and Equipment             $3,900,000
Salaries             $883,718
Utilities             $2,879
Insurance             $2,787
Transport             $20,694
Repairs & Maintenance             $9,585
Advertising             $26,810
Misc. Expense             $49,701
Direct Wages and Expenses             $1,135,500
Comon Stock Dividends             $60,000

2015 Projected Data
Net Sales    12%    increase        Mortgage payments for the year:    2014        2015        2016
Direct Wages & Expenses    11%    increase            Interest    Principal    Interest    Principal    Interest    Principal
Salaries    1.00%    increase            4,499.65    1,989.21    4,378.51    2,110.35     4,250.79     2,238.07
4,489.83    1,999.03    4,368.09    2,120.77     4,240.11     2,248.75
Utilities    2.50%    increase            4,479.96    2,008.90    4,357.61    2,131.25     4,229.43     2,259.43
Insurance    3.00%    increase            4,470.04    2,018.82    4,347.09    2,141.77     4,218.74     2,270.12
Transport    6.00%    increase            4,460.06    2,028.80    4,336.51    2,152.35     4,208.06     2,280.80
R&M    2.00%    increase            4,450.05    2,038.81    4,325.88    2,162.98     4,197.38     2,291.48
Advertising     $4,000     decrease            4,439.98    2,048.88    4,315.20    2,173.66     4,186.70     2,302.16
Misc    2.00%    decrease            4,429.86    2,059.00    4,304.46    2,184.40     4,176.01     2,312.85
Freight-in    10.00%    increase            4,419.69    2,069.17    4,293.68    2,195.18     4,165.33     2,323.53
Net Purchases    11%    increase            4,409.47    2,079.39    4,282.84    2,206.02     4,154.65     2,334.21
Common Stock Dividends     $20,000     increase            4,399.20    2,089.66    4,271.94    2,216.92     4,143.96     2,344.90
Amortization Expense    4.00%    declining balance            4,388.88    2,099.98    4,260.99    2,227.87     4,133.28     2,355.58
Capital purchases     $30,000     new roof in June to be added to plant & equipment            53,336.67    24,529.65    51,842.80    26,023.52    50,304.45    27,561.87

Excel Assignment.

Marks            WangQiuxiang652675
1) Do income statements for                    2014        8
2) Do Balance Sheets for                    2014        8
3) Do a pro forma Income Statement for                    2015        10
4) Do a pro forma Balance Sheet for                    2015        10
5) Do Cash Flow Statement for                    2014        10
6) Do ratios for                2013    2014        10
7) Do pro forma Ratios for                    2015        10
8) Do a pro forma cash budget for                    2015        14
9) Do breakeven questions for                 2014    2015        10
10) Do the theory problem below                            10
Total                            100

Theory Problem
It is June        2015        You work at this company.
One of the owners would like to sell some of her shares so she can buy a cottage.
She is offering to sell you 100,000 shares for                      $3.50     each
Assume you have the money to buy them.
Do you buy them ?
Give me reasons for your answer based on such things as book value, return on
investment, trends, cash flow, SME Benchmarking and so on.
2014 Data
WangQiuxiang652675            2014        Other Info        2014        2013        2015
Accounts payable             $980,882         Common Stock         400,000     shares     400,000     shares     400,000     shares
Accounts receivable             $630,628
Amortization expense             $38,000
Beginning Inventory             $760,000         Notes Payable        current
Cash             $343,828         Sales on Credit        90%    percent of net sales
Common stock             $100,000
Ending Inventory             $964,831         Share Price    2013     $1.60     per share    Share Prices based on accounting department’s estimates.
Freight In             $39,660         Share Price    2014     $2.75     per share
Interest expense             $63,401
GIC’s             $300,000         The directors have decided that no new shares will be issued/sold in 2015
Mortgage payable             $911,115
Net Purchases             $2,757,002
Net Sales             $5,717,665
Notes payable             $200,000         Tax rate        40%    Of Net Income    Taxes on the year’s profits are paid in December
Prepaid expenses             $42,089         Amortization Expense        4%     declining balance
Plant and Equipment             $3,900,000
Salaries             $883,718
Utilities             $2,879
Insurance             $2,787
Transport             $20,694
Repairs & Maintenance             $9,585
Advertising             $26,810
Misc. Expense             $49,701
Direct Wages and Expenses             $1,135,500
Comon Stock Dividends             $60,000

2015 Projected Data
Net Sales    12%    increase        Mortgage payments for the year:    2014        2015        2016
Direct Wages & Expenses    11%    increase            Interest    Principal    Interest    Principal    Interest    Principal
Salaries    1.00%    increase            4,499.65    1,989.21    4,378.51    2,110.35     4,250.79     2,238.07
4,489.83    1,999.03    4,368.09    2,120.77     4,240.11     2,248.75
Utilities    2.50%    increase            4,479.96    2,008.90    4,357.61    2,131.25     4,229.43     2,259.43
Insurance    3.00%    increase            4,470.04    2,018.82    4,347.09    2,141.77     4,218.74     2,270.12
Transport    6.00%    increase            4,460.06    2,028.80    4,336.51    2,152.35     4,208.06     2,280.80
R&M    2.00%    increase            4,450.05    2,038.81    4,325.88    2,162.98     4,197.38     2,291.48
Advertising     $4,000     decrease            4,439.98    2,048.88    4,315.20    2,173.66     4,186.70     2,302.16
Misc    2.00%    decrease            4,429.86    2,059.00    4,304.46    2,184.40     4,176.01     2,312.85
Freight-in    10.00%    increase            4,419.69    2,069.17    4,293.68    2,195.18     4,165.33     2,323.53
Net Purchases    11%    increase            4,409.47    2,079.39    4,282.84    2,206.02     4,154.65     2,334.21
Common Stock Dividends     $20,000     increase            4,399.20    2,089.66    4,271.94    2,216.92     4,143.96     2,344.90
Amortization Expense    4.00%    declining balance            4,388.88    2,099.98    4,260.99    2,227.87     4,133.28     2,355.58
Capital purchases     $30,000     new roof in June to be added to plant & equipment            53,336.67    24,529.65    51,842.80    26,023.52    50,304.45    27,561.87