Preparing 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. Refer back to the balance sheet and income statement created using the financial statement numbers for Handyman Company for 2009 [given in part (1) of the Cumulative Spreadsheet Project assignment in Chapter 2]. With these historical numbers for 2009 as a starting point, Handyman wishes to prepare a forecasted balance sheet and a forecasted income statement for 2010. In preparing the forecasted financial statements for 2010, consider the following additional information:

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

b. In the forecasted balance sheet for 2010, cash, receivables, inventory, and accounts payable will all increase at the same rate as sales (40%) relative to 2009. These increases occur because, with the planned 40% increase in the volume of business and no plans to significantly change its methods of operation, Handyman will probably also experience a 40% increase in the levels of its current operating assets and liabilities.

c. In 2010, Handyman expects to acquire new property, plant, and equipment costing $80.

d. Accumulated depreciation is the cumulative amount of depreciation expense that Handyman has reported over its years in business. Thus, the forecasted amount of accumulated depreciation for 2010 can be computed as accumulated depreciation as of the end of 2009 plus the forecasted depreciation expense for 2010.

e. New short-term loans payable will be acquired in an amount sufficient to make Handyman’s current ratio (current assets divided by current liabilities) in 2010 exactly equal to 2.0.

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

g. No cash dividends will be paid in 2010. Remember that the amount of retained earnings at the end of any year is the beginning retained earnings amount plus net income minus dividends.

h. In this exercise, the forecasted amount of paid-in capital is the “plug” figure. In other words, the forecasted balance in paid-in capital at the end of 2010 is the amount necessary to make the forecasted balance sheet balance such that forecasted total assets equal forecasted total liabilities. A key reason for preparing forecasted financial statements is to identify in advance whether any additional financing will be required.
i. The $160 in operating expenses reported in 2009 breaks down as follows: $5 depreciation expense, $155 other operating expenses.
j. In the forecasted income statement for 2010, cost of goods sold and other operating expenses will both increase at the same rate as sales (40%) relative to 2009.
This is another way of saying that the amount of these expenses, relative to the amount of sales, will probably stay about the same year to year unless Handyman plans to significantly change the way it does business.
k. The amount of Handyman’s depreciation expense is determined by how much property, plant, and equipment the company has. In 2009, Handyman had $5 of depreciation expense on $199 of property, plant, and equipment, meaning that depreciation was equal to 2.5% ($5/$199) of the amount of property, plant, and equipment. It is expected that the same relationship will hold in 2010.
l. Interest expense depends on how much interest-bearing debt a company has. In 2009, Handyman reported interest expense of $9 on long-term debt of $207. (Note:
To simplify this exercise, we will ignore interest expense on the short-term loan payable.) Because Handyman is expected to have the same amount of long-term debt in 2010, our best guess is that interest expense will remain the same.
m. Income tax expense is determined by how much pretax income a company has.
And, the most reasonable assumption to make is that a company’s tax rate, equal to income tax expense divided by pretax income, will stay constant from year to year.
Handyman’s income tax rate in 2009 was 33% ($4/$12).
2. Repeat (1) assuming that forecasted sales growth in 2010 is 20% instead of 40%. Clearly state any assumptions that you make.