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, income statement, and statement of cash flows 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. In 2010, Handyman expects to acquire new property, plant, and equipment costing $80.

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

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

e. No cash dividends will be paid in 2010.

f. 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.

Construction of the forecasted statement of cash flows for 2010 involves analyzing the forecasted income statement for 2010, along with the balance sheets for 2009 (actual) and 2010

(forecasted).

For this exercise, the current assets are expected to behave as follows:

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

b. The forecasted amount of accounts receivable in 2010 is determined using the forecasted value for the average collection period (computed using the end-of-period Accounts Receivable balance). The average collection period for 2010 is expected to be 14.08 days. This is from the Chapter 6 spreadsheet.

c. 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. This is from the Chapter 7 spreadsheet.

d. The forecasted amount of accounts payable in 2010 is determined using the forecasted value of 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. This is from the Chapter 7 spreadsheet.

Clearly state any additional assumptions that you make.

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

a. The average collection period is expected to be 9.06 days with days’ sales in inventory remaining at 107.6 days and days’ purchases in payables remaining at 48.34 days.

b. The average collection period is expected to be 20 days with days’ sales in inventory remaining at 107.6 days and days’ purchases in payables remaining at 48.34 days.

c. Days’ sales in inventory are expected to be 66.2 days with the average collection period remaining at 14.08 days and days’ purchases in payables remaining at 48.34 days.

d. Days’ sales in inventory are expected to be 150 days with the average collection period remaining at 14.08 days and days’ purchases in payables remaining at 48.34 days.

Comment on the forecasted values of cash from operating activities in 2010 under each of the scenarios given in (2).