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) 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, new property, plant, and equipment acquisitions will be in accordance with the information in (q).
(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 Investment Securities, Long-Term Investments, and Other Long-Term Assets will all increase at the same rate as sales (40%) in 2009.The balance in Intangible Assets will change in accordance with item (r).
(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 simpler, 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 simpler, this value of 48.34 days is based on forecasted end of- year accounts payable rather than on average accounts payable.
(q) The forecasted amount of property, plant, and equipment (PP&E) in 2009 is determined using the forecasted value for the fixed asset turnover ratio. The fixed asset turnover ratio for 2009 is expected to be 3.518 times. To make the calculations simpler, this ratio of 3.518 is based on forecasted end-of-year gross property, plant, and equipment balance rather than on the average balance. (Note: For simplicity, ignore accumulated depreciation in making this calculation.)
(r) Skywalker has determined that no new intangible assets will be acquired in 2009. Intangible assets are amortized according to the information in (t).
For this exercise, make the following additional assumptions:
(s) In computing depreciation expense for 2009, use straight-line depreciation and assume a 30-year useful life with no residual value. Gross PP&E acquired during the year is only depreciated for half the year. In other words, depreciation expense for 2009 is the sum of two parts: (1) a full year of depreciation on the beginning balance in PP&E, assuming a 30-year life and no residual value, and (2) a half year of depreciation on any new PP&E acquired during the year, based on the change in the gross PP&E balance.
(t) Skywalker assumes a 20-year useful life for its intangible assets. Assume that the
$100 in intangible assets reported in 2008 is the original cost of the intangibles.
Include the amortization expense with the depreciation expense in the income statement.
Clearly state any additional assumptions that you make.
2. Assume the same scenario as (1), and show the impact on the financial statements with the following changes in assumptions:
(a) Estimated useful life of property, plant, and equipment is expected to be 15 years.
(b) Estimated useful life of property, plant, and equipment is expected to be 60 years.
3. Comment on the differences in the forecasted values of cash from operating activities in 2009 under each of the following assumptions about the estimated useful life of property, plant, and equipment: 15 years, 30 years, and 60 years. Explain exactly why a change in depreciation life has an impact on cash from operating activities.
On August 15, 2015, Low Rider Automotive established a line of credit at its bank, with interest at 8.75% p.a. This line of credit was used to purchase $5000 of inventory...Apr 20 2020
The Surgeon General has long since declared that cigarettes are hazardous to people's health. Since this is common knowledge, is it unethical for the tobacco industry t...Apr 12 2020
How would Jim Logan decide which foreign markets he would attempt to enter? Should he initially focus on one or many foreign markets? >Apr 22 2020
How are adjusting entries completed? Is there a short-hand way to remember or a technique? What accounts get adjusted?Sep 17 2020
What is the equation of a constant-elasticity demand function that has an own-price elasticity of -2 and passes through the point (200, 15)? Q(P) = ______May 15 2020
Assessing simultaneous changes in CVP relationshipsVanhorn Company sells tennis racquets; variable costs for each are $75, and each is sold for $105.Vanhorn incurs $270,0...Jun 24 2020
Find the shortest distance between the lines whose vector equations are r = (1-t)i+(t-2)j+(3-2t)k and r = (s+1)i +(2s-1) j -(2s+1)k >Aug 05 2020
The file P03_55.xlsx lists the average salary for each MLB team from 2004 to 2011, along with the number of team wins in each of these years.a. Create a table of correlat...May 08 2020
Oil in an engine is being cooled by air in a cross-flow heat exchanger, where both fluids are unmixed. Oil (cph = 2047 J/kg.K) flowing with a flow rate of 0.026 kg/s ente...Jul 20 2020