Objectives: This assignment combines knowledge from your accounting courses as well as related information discussed in this class. Upon completion of the assignment, you will be able to: 1. Construct a statement of cash flows based on information obtained from other financial statements. 2. Compute changes in value for all balance sheet items. 3. Identify the relationships between key financial statements and interpret your analysis results. This assignment was created because knowledge of the relationships between financial statements, along with the ability to interpret each statement, is required for managers to make sound business decisions. Protocols: The assignment must be completed in Excel and you may work with one partner in the same course section. If you work with a partner, please decide which partner is responsible for uploading the completed spreadsheet to Canvas; both partners should not upload separate spreadsheets. Record all dollar values as whole numbers (e.g., $1,250, not $1,250.00) and record all percentages with two decimal places (e.g., 1.14%, not 0.01). Make sure the uploaded file contains: A descriptive assignment title, both partners' names, and the date of creation, at the top of the spreadsheet. (2 points) A descriptive title for each part of the assignment. Do not simply write Question 2, Part 3, etc." (2 points) Consistent numerical formatting throughout the spreadsheet. (1 points) Note: Spreadsheets are created to aid managers in decision making. To enable the manager to ask, What if I changed this value?", it is imperative that you utilize formulas and functions when creating your spreadsheets. Use the information for the XYZ Company that appears below to perform the following tasks. 1. Recreate the balance sheet and compute the changes in value for all items (8 points) 2. Construct a statement of cash flows (8 points) and interpret the results (4 points). 3. What did you learn about the XYZ Company after making these computations (5 points)? Assets Cash Marketable Securities Accounts receivable Inventory Prepaid expenses Total current assets Gross plant and equipment Less: accumulated depreciation Total assets 2011 2012 $ 30,000 $ 28,000 12,000 12,400 84,000 66,000 102,000 168,000 2,400 2,200 230,400 276,600 632,000 660,000 (60,000) (120,000) $ 802,400 $ 816,600 Liabilities & Equity Accounts payable Accruals Notes payable Total current liabilities Long-term debt Common stockholders' equity Total liabilities and equity $ 96,000 $ 114,000 12,000 10,000 30,000 26,000 138,000 150,000 320,000 300,000 344,400 366,600 S 802,400 $ 816,600 XZY Company - Income Statement $ 1,200,000 920,000 280,000 $ 60,000 60,000 Sales Cost of goods sold Gross profit General and administration Depreciation Total operating expenses Operating income Interest expense Earnings before taxes Taxes paid Net income available to common stockholders Cash dividends Change in Retained earnings 120,000 160,000 20,000 140,000 54,200 85,800 63,600 22,200 $