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. If needed, review the spreadsheet assignment for Chapter 4 to refresh your memory on how to construct forecasted financial statements.
1. Handyman wishes to prepare a forecasted balance sheet and income statement for 2013.
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.
b. Cash will increase at the same rate as sales.
c. Th e forecasted amount of accounts receivable in 2013 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 2013 is expected to be 14.08 days.
d. In 2013, Handyman expects to acquire new property, plant, and equipment costing $80.
e. Th e $160 in operating expenses reported in 2012 breaks down as follows: $5 depreciation expense, $155 other operating expenses.
f. No new long-term debt will be acquired in 2013.
g. No cash dividends will be paid in 2013.
h. New short-term loans payable will be acquired in an amount sufficient to make Handyman’s current ratio in 2013 exactly equal to 2.0.
These statements were constructed as part of the spreadsheet assignment in Chapter 6. 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:
i. The forecasted amount of inventory in 2013 is determined using the forecasted value for the number of days’ sales in inventory (computed using the end-of-period inventory balance). Th e number of days’ sales in inventory for 2013 is expected to be 107.6 days.
ii. Th e forecasted amount of accounts payable in 2013 is determined using the forecasted value for the number of days’ purchases in accounts payable (computed using the end-of-period accounts payable balance). Th e number of days’ purchases in accounts payable for 2013 is expected to be 48.34 days.
2. Repeat (1), with the following changes in assumptions:
a. Number of days’ sales in inventory is expected to be 66.2 days.
b. Number of days’ sales in inventory is expected to be 150.0 days.
3. Comment on the differences in the forecasted values of cash from operating activities in 2013 under each of the following assumptions about the number of days’ sales in inventory: 107.6 days, 66.2 days, and 150.0 days.
4. Is there any impact on the forecasted level of accounts payable when the number of days’ sales in inventory is changed? Why or why not?
5. What happens to the forecasted level of short-term loans payable when the number of days’ sales in inventory is reduced to 66.2 days? Explain.