Preparing New Forecasts
This spreadsheet assignment is a continuation of the spreadsheet assignments given 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. See item (l) for the assumption concerning the amount of new long-term debt that
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.
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 depreciated for only 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 

Note: These statements were constructed as part of the spreadsheet assignment in
Chapter 9; 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:
l. New long-term debt will be acquired (or repaid) in an amount sufficient to make
Handyman’s debt ratio (total liabilities divided by total assets) in 2010 exactly equal
to 0.80.
m. Assume an interest rate on short-term loans payable of 6.0% and on long-term debt of
8.0%. Only a half-year’s interest is charged on loans taken out during the year. For
example, if short-term loans payable at the end of 2010 are $15 and given that shortterm
loans payable at the end of 2009 were $10, total short-term interest expense for
2010 would be $0.75 [($10  0.06)  ($5  0.06  1/2)].
Clearly state any additional assumptions that you make.
2. Repeat (1), with the following changes in assumptions:
a. The debt ratio in 2010 is exactly equal to 0.70.
b. The debt ratio in 2010 is exactly equal to 0.90.
3. Prepare a table displaying the forecasted values of long-term debt and paid-in capital in
2010 under each of the following assumptions about the debt ratio: 0.70, 0.80, and
0.90. The sum of these two items can be viewed as the total amount of long-term financing
(both debt and equity) received from outsiders. Comment on why the total of
these two items is not the same under each debt ratio assumption.