The financial statement numbers and ratio assumptions used in constructing this series of cumulative spreadsheet projects in the text, starting in Chapter 2, are based on the actual experience of Home Depot. The original financial statement numbers in the spreadsheet project are adapted from Home Depot’s actual 1985 financial statements. The projected financial statements that you will prepare in (1) below are a projection of how Home Depot would have performed in the years after 1985 if Home Depot had not made significant changes to its operations.
1. Handyman wishes to prepare forecasted balance sheets, income statements, and statements of cash flows for five years—2013, 2014, 2015, 2016, and 2017. Use the original financial statement numbers for 2012 [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 2013 are expected to increase by 40% over 2012 sales of $700. Sales are expected to increase 40% in each year thereafter.
b. Cash will increase at the same rate as sales.
c. The forecasted amount of accounts receivable is determined using the forecasted value for the average collection period. The average collection period is expected to be 14.08 days. To make the calculations simpler, this value of 14.08 days is based on forecasted end-of-year accounts receivable rather than on average accounts receivable.
d. The forecasted amount of inventory is determined using the forecasted value for the number of days’ sales in inventory. The number of days’ sales in inventory 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.
e. The forecasted amount of accounts payable is determined using the forecasted value for the number of days’ purchases in accounts payable. The number of days’ purchases in accounts payable 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.
f. The $160 in operating expenses reported in 2012 breaks down as follows: $5 depreciation expense, $155 other operating expenses.
g. 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 each year exactly equal to 0.80.
h. No cash dividends will be paid in any year.
i. New short-term loans payable will be acquired in an amount sufficient to make Handyman’s current ratio exactly equal to 2.0 in each year.
j. The forecasted amount of property, plant, and equipment (PPE) 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 PPE balance. The fixed asset turnover ratio is expected to be 3.518 times.
k. In computing depreciation expense, use straight-line depreciation and assume a 30-year useful life with no residual value. Gross PPE acquired during the year is only depreciated for half the year. In other words, depreciation expense is the sum of two parts: (1) a full year of depreciation on the beginning balance in PPE, assuming a 30-year life and no residual value and (2) a half-year of depreciation on any new PPE acquired during the year, based on the change in the gross PPE balance.
l. 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 2013 is $15 and given that short-term loans payable at the end of 2012 were $10, total short-term interest expense for 2013 would be $0.75 [($10 × 0.06) + ($5 × 0.06 × 1/2)].

2. Repeat (1), with the following changes in assumptions:
Average collection period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .      906 days
Number of days’ sales in inventory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66.23 days
Fixed asset turnover . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .       3989 times
Gross profit percentage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .    27.55%
Other operating expenses/sales. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .   19.86%
Number of days’ purchases in accounts payable . . . . . . . . . . . . . . . . . . .50.37 days
After making these changes in ratio values, your spreadsheet may have negative amounts for Short-Term Loans Payable. This is impossible. Adjust your spreadsheet so that Short-Term Loans Payable is never less than zero. This will require a relaxation of the requirement that the current ratio be at least 2.0.
3. Discuss why Handyman has a projected current ratio of less than 2.0 in some years when using the ratios in (2).
4. Which company would you rather loan money to—a company with the projected financial statements prepared in (1) or a company with the projected financial statements prepared in (2)? Explain your answer.