Cumulative Spreadsheet Analysis
This spreadsheet assignment is a continuation of the spreadsheet assignments given in earlier chapters. If you completed those assignments, you have a head start on this one. Refer back to the instructions for preparing the revised financial statements for 2008 as given in (1) of the Cumulative Spreadsheet Analysis assignment in Chapter 3.
1. Skywalker wishes to prepare a forecasted balance sheet, a forecasted income statement, and a forecasted statement of cash flows for 2009. Use the financial statement numbers for 2008 as the basis for the forecast, along with the following additional information.
(a) Sales in 2009 are expected to increase by 40% over 2008 sales of $2,100.
(b) In 2009, Skywalker expects to acquire new property, plant, and equipment costing $240.
(c) The $480 in operating expenses reported in 2008 breaks down as follows: $15 in depreciation expense and $465 in other operating expenses.
(d) No new long term debt will be acquired in 2009.
(e) No cash dividends will be paid in 2009.
(f) New short term loans payable will be acquired in an amount sufficient to make Skywalker’s current ratio in 2009 exactly equal to 2.0.
(g) Skywalker does not anticipate repurchasing any additional shares of stock during 2009.
(h) Because changes in future prices and exchange rates are impossible to predict, Skywalker’s best estimate is that the balance in accumulated other comprehensive income will remain unchanged in 2009.
(i) In the absence of more detailed information, assume that the balances in the investment securities, long term investments, other long term assets, and intangible assets accounts will all increase at the same rate as sales (40%) in 2009.
(j) In the absence of more detailed information, assume that the balance in the other long term liabilities account will increase at the same rate as sales (40%) in 2009.
(k) The investment securities are classified as available for sale securities. Accordingly, cash from the purchase and sale of these securities is classified as an investing activity.
(l) Assume that transactions impacting other long term assets and other long term liabilities accounts are operating activities.
(m) Cash and investment securities accounts will increase at the same rate as sales.
(n) The forecasted amount of accounts receivable in 2009 is determined using the forecasted value for the average collection period. The average collection period for 2009 is expected to be 14.08 days. To make the calculations less complex, this value of 14.08 days is based on forecasted end of year accounts receivable rather than on average accounts receivable.
(o) The forecasted amount of inventory in 2009 is determined using the forecasted value for the number of days’ sales in inventory. The number of days’ sales in inventory for 2009 is expected to be 107.6 days. To make the calculations easier, this value of 107.6 days is based on forecasted end of year inventory rather than on average inventory.
(p) The forecasted amount of accounts payable in 2009 is determined using the forecasted value for the number of days’ purchases in accounts payable. The number of days’ purchases in accounts payable for 2009 is expected to be 48.34 days. To make the calculations easier, this value of 48.34 days is based on forecasted end of year accounts payable rather than on average accounts payable.
Clearly state any additional assumptions that you make.
2. Repeat part (1), with the following changes in assumptions:
(a) Number of days’ sales in inventory is expected to be 66.2 days.
(b) Number of days’ sales in inventory is expected to be 150.0 days.
3. Comment on the differences in the forecasted values of cash from operating activities in 2009 under each of the following assumptions about the number of days’ sales in inventory: 107.6 days, 66.2 days, and 150.0 days.
4. Is there any impact on the forecasted level of accounts payable when the number of days’ sales in inventory is changed? Why or why not?
5. What happens to the forecasted level of short term loans payable when the number of days’ sales in inventory is reduced to 66.2 days? Explain.