Preparing New Forecasts This spreadsheet project is a continuation of the spreadsheet projects in earlier chapters. If you completed those spreadsheets, you have a head start on this one.

1. Handyman wishes to prepare a forecasted balance sheet and income statement for 2010. Use the original financial statement numbers for 2009 [given in part (1) of the Cumulative Spreadsheet Project assignment in Chapter 2] as the basis for the forecast, along with the following additional information:

a. Sales in 2010 are expected to increase by 40% over 2009 sales of $700.

b. Cash will increase at the same rate as sales.

c. The forecasted amount of accounts receivable in 2010 is determined using the forecasted value for the average collection period. For simplicity, do the computations using the end-of-period accounts receivable balance instead of the average balance.

The average collection period for 2010 is expected to be 14.08 days.

d. The forecasted amount of inventory in 2010 is determined using the forecasted value for the number of days’ sales in inventory (computed using the end-of-period inventory balance). The number of days’ sales in inventory for 2010 is expected to be 107.6 days.

e. The forecasted amount of accounts payable in 2010 is determined using the forecasted value for the number of days’ purchases in accounts payable (computed using the end-of-period accounts payable balance). The number of days’ purchases in accounts payable for 2010 is expected to be 48.34 days.

f. The $160 in operating expenses reported in 2009 breaks down as follows: $5 depreciation expense, $155 other operating expenses.

g. No new long-term debt will be acquired in 2010.

h. No cash dividends will be paid in 2010.

i. New short-term loans payable will be acquired in an amount sufficient to make Handyman’s current ratio in 2010 exactly equal to 2.0.

Note: These statements were constructed as part of the spreadsheet assignment in Chapter 7; you can use that spreadsheet as a starting point if you have completed that assignment.

Clearly state any additional assumptions that you make.

For this exercise, add the following additional assumptions:

j. The forecasted amount of property, plant, and equipment (PP&E) in 2010 is determined using the forecasted value for the fixed asset turnover ratio. For simplicity, compute the fixed asset turnover ratio using the end-of-period gross PP&E balance.

The fixed asset turnover ratio for 2010 is expected to be 3.518 times.

k. In computing depreciation expense for 2010, 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 2010 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.

Clearly state any additional assumptions that you make.

2. Repeat (1), with the following changes in assumptions:

a. Fixed asset turnover ratio is expected to be 6.000 times.

b. Fixed asset turnover ratio is expected to be 2.000 times.

3. Comment on the differences in the forecasted values of the following items in 2010 under each of the following assumptions about the fixed asset turnover ratio: 3.518 times, 6.000 times, and 2.000 times:

a. Property, plant, and equipment.

b. Depreciation expense.

c. Income tax expense.

d. Paid-in capital.

4. Return the fixed asset turnover ratio to 3.518 times. Now, repeat (1), with the following changes in assumptions:

a. Estimated useful life is expected to be 15 years.

b. Estimated useful life is expected to be 60 years.

5. Comment on the differences in the forecasted values of the following items in 2010 under each of the following assumptions about the estimated useful life of property, plant, and equipment: 30 years, 15 years, and 60 years.

a. Depreciation expense.

b. Income tax expense.