Cumulative Spreadsheet Analysis
This spreadsheet assignment is a continuation of the spreadsheet assignment given in Chapter 2. If you completed that assignment, you have a head start on this one.
1. Refer back to the financial statement numbers for Skywalker Enterprises for 2008 (given in part 1 of the Cumulative Spreadsheet Analysis assignment in Chapter 2). Revise those financial statements by making the following changes:
• Change the paid in capital amount from $150 to $200.
• In the Equity section of the balance sheet, insert a treasury stock amount of –$60.
The remaining amount of the “other equity” mentioned in Chapter 2 is accumulated other comprehensive income.
• Increase amount of long term debt from $621 to $671.
• In the Asset section of the balance sheet, insert an intangible asset amount of $100.
Using the revised balance sheet and income statement, create spreadsheet cell formulas to compute and display values for the following ratios.
• Current ratio
• Debt ratio
• Asset turnover
• Return on assets
• Return on equity
2. Determine the impact of each of the following transactions on the ratio values computed in Question 1.Treat each transaction independently; that is, before determining the impact of each new transaction you should reset the financial statement values to their original amounts. The transactions that follow are assumed to occur on December 31, 2008.
(a) Collected $60 cash from customer receivables.
(b) Purchased $90 in inventory on account.
(c) Purchased $300 in property, plant, and equipment. The entire amount of the purchase was financed with a mortgage. Principal repayment for the mortgage is due in 10 years.
(d) Purchased $300 in property, plant, and equipment. The entire amount of the purchase was financed with new stockholder investments.
(e) Borrowed $60 with a short term loan payable. The $60 was paid out as a dividend to stockholders.
(f) Received $60 as an investment from stockholders. The $60 was paid out as a dividend to stockholders.
(g) The long term debt amount of $671 includes $90 in short term loans payable that Skywalker hopes to refinance. Skywalker has no explicit agreement with the bank to refinance the loan and does not expect to finalize the refinancing until the last quarter of 2009.
(h) During the first week in January 2009, Skywalker learned that, of the $459 reported as inventory as of December 31, 2008, $45 is completely obsolete and worthless. The inventory had become obsolete during the last quarter of 2008, but the facts had not been verified until early 2009.