Please help me this question,I just have 4 hours to finish.Thank you
Analytical Assignments AA 7-45 Cumulative Spreadsheet Project Preparing New Forecasts This spreadsheet assignment is a continuation of the spreadsheet assignments given in carlier chap- ters. 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 Cumula- tive 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. The forecasted amount of accounts receivable in 2013 is determined using the fore- casted 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. c. The $160 in operating expenses reported in 2012 breaks down as follows: S5 depre- ciation 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. Note: 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). The number of days' sales in inventory for 2013 is expected to be 107.6 days. ii. The forecasted amount of accounts payable in 2013 is determined using the forecast ed 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 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 inven- tory: 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. Operating Activities 318 Part 2 Analytical Assignments AA 6-51 Cumulative Spreadsheet Project Creating a Forecasted Balance Sheet and Income Statement This spreadsheet assignment is a continuation of the spreadsheet assignments given in earlier chap- ters. 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 Cumula- tive 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. In 2013, Handyman expects to acquire new property, plant, and equipment costing $80. C. The $160 in other operating expenses reported in 2012 includes $5 of depreciation expense. d. No new long-term debe will be acquired in 2013. e. No cash dividends will be paid in 2013. f. 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. Note: These statements were constructed as part of the spreadsheet assignment in Chapter 4. You can use that spreadsheet as a starting point if you have completed that assignment. For this exercise, the current assets are expected to behave as follows: i. Cash and inventory will increase at the same rate as sales. ii. The forecasted amount of accounts receivable in 2013 is determined using the forecasted value for the average collection period. For simplicity, do the compu- tations 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. Receivables: Selling a Product or Service Chapter 6 259 Clearly state any additional assumptions that you make 2. Repeat (1), with the following change in assumptions: a. Average collection period is expected to be 9.06 days. b. Average collection period is expected to be 20.00 days. 3. Comment on the differences in the forecasted values of accounts receivable in 2013 under cach of the following assumptions about the average collection period: 14.08 days, 9.06 days, and 20.00 days. Under which assumption will Handyman's forecasted cash flow from operating activities be higher? Explain. Analytical Assignments AA 2.51 Cumulative Spreadsheet Project Creating a Balance Sheet and Income Statement Starting with this chapter, each chapter in this text will include a spreadsheet assignment based on the financial information of a fictitious company named Handyman. The first assignments are simple in this chapter you are asked to do little more than set up financial statement formats and input some numbers. In succeeding chapters, the spreadsheets will get more complex so that by the end of the course, you will have constructed a spreadsheet that allows you to forecast operating cash flow for five years in the future and adjust your forecast depending on the operating parameters that you think are most reasonable. So, let's get started with the first spreadsheet assignment. 1. The following numbers are for Handyman Company for 2012: Financial Statements: An Overview Chapter 2 6 5 50 31 Short-Term Loans Payable...... Long Term Debt................ $207 Interest Expense Income Tax Expense. Capital Stock Retained Earnings (as of 1/1/12). Cash 10 Receivables.. Dividends. Sales 700 Accumulated Depreciation... Accounts Payable.... 74 Inventory 153 Property. Plant, & Equipment ....... Cost of Goods Sold. ................... 519 O ther Operating Expenses.... Your assignment is to create a spreadsheet containing a balance sheet and an income statement for Handyman Company 2. Handyman is wondering what its balance sheet and income statement would have looked like if the following numbers were changed as indicated: 199 160 Change From To Sales.. $700 $730 Cost of Goods Sold. ..... 519 550 Other Operating Expenses..................... ...... . 160 160 165 Create a second spreadsheet with the numbers changed as indicated. Note: After making these changes, your balance sheet may no longer balance. Assume that any discrepancy is eliminated by increasing or decreasing Short-Term Loans Payable as much as necessary. Analytical Assignments AA 4.57 Cumulative Spreadsheet Project Preparing Forecasts This spreadsheet assignment is a continuation of the spreadsheet assignments given in earlier chap- ters. 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 state- ment numbers for Handyman Company for 2012 (given in part (1) of the Cumulative Spreadsheet Project assignment in Chapter 2]. With these historical numbers for 2012 as a starting point, Handyman wishes to prepare a forecasted balance sheet and a forecasted income statement for 2013. In preparing the forecasted financial statements for 2013, consider the following additional information: a. Sales in 2013 are expected to increase by 40% over 2012 sales of $700. b. In the forecasted balance sheet for 2013, cash, receivables, inventory, and accounts payable will all increase at the same rate as sales (40%) relative to 2012. 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 experi- ence a 40% increase in the levels of its current operating assets and liabilities. c. In 2013, 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 2013 can be computed as accumulated depreciation as of the end of 2012 plus the forecasted depreciation expense for 2013. c. 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 2013 exactly equal to 2.0. f. No new long-term debt will be acquired in 2013. (continued) Completing the Accounting Cycle Chapter 4 175 g. No cash dividends will be paid in 2013. Remember that the amount of retained eam- ings 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 2013 is the amount nec essary to make the forecasted balance sheet balance such that forecasted total assets equal forecasted total liabilities. A key reason for preparing forecasted financial state- ments is to identify in advance whether any additional financing will be required. i. The $160 in operating expenses reported in 2012 breaks down as follows: 55 depre- ciation expense, $155 other operating expenses. j. In the forecasted income statement for 2013. cost of goods sold and other operating expenses will both increase at the same rate as sales (40%) relative to 2012. This is an- other 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 prop- erty, plant, and equipment the company has. In 2012, Handyman had $5 of depreci- ation expense on $199 of property, plant, and equipment, meaning that depreciation was equal to 2.5% (55/5199) of the amount of property, plant, and equipment. It is expected that the same relationship will hold in 2013. I. Interest expense depends on how much interest-bearing debt a company has. In 2012, 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 2013, 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. Handy- man's income tax rate in 2012 was 33% (54/512). 2. Repeat (1) assuming that forecasted sales growth in 2013 is 20% instead of 40%. Clearly state any assumptions that you make. Analytical Assignments AA 7-45 Cumulative Spreadsheet Project Preparing New Forecasts This spreadsheet assignment is a continuation of the spreadsheet assignments given in carlier chap- ters. 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 Cumula- tive 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. The forecasted amount of accounts receivable in 2013 is determined using the fore- casted 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. c. The $160 in operating expenses reported in 2012 breaks down as follows: S5 depre- ciation 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. Note: 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). The number of days' sales in inventory for 2013 is expected to be 107.6 days. ii. The forecasted amount of accounts payable in 2013 is determined using the forecast ed 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 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 inven- tory: 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. Operating Activities 318 Part 2 Analytical Assignments AA 6-51 Cumulative Spreadsheet Project Creating a Forecasted Balance Sheet and Income Statement This spreadsheet assignment is a continuation of the spreadsheet assignments given in earlier chap- ters. 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 Cumula- tive 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. In 2013, Handyman expects to acquire new property, plant, and equipment costing $80. C. The $160 in other operating expenses reported in 2012 includes $5 of depreciation expense. d. No new long-term debe will be acquired in 2013. e. No cash dividends will be paid in 2013. f. 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. Note: These statements were constructed as part of the spreadsheet assignment in Chapter 4. You can use that spreadsheet as a starting point if you have completed that assignment. For this exercise, the current assets are expected to behave as follows: i. Cash and inventory will increase at the same rate as sales. ii. The forecasted amount of accounts receivable in 2013 is determined using the forecasted value for the average collection period. For simplicity, do the compu- tations 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. Receivables: Selling a Product or Service Chapter 6 259 Clearly state any additional assumptions that you make 2. Repeat (1), with the following change in assumptions: a. Average collection period is expected to be 9.06 days. b. Average collection period is expected to be 20.00 days. 3. Comment on the differences in the forecasted values of accounts receivable in 2013 under cach of the following assumptions about the average collection period: 14.08 days, 9.06 days, and 20.00 days. Under which assumption will Handyman's forecasted cash flow from operating activities be higher? Explain. Analytical Assignments AA 2.51 Cumulative Spreadsheet Project Creating a Balance Sheet and Income Statement Starting with this chapter, each chapter in this text will include a spreadsheet assignment based on the financial information of a fictitious company named Handyman. The first assignments are simple in this chapter you are asked to do little more than set up financial statement formats and input some numbers. In succeeding chapters, the spreadsheets will get more complex so that by the end of the course, you will have constructed a spreadsheet that allows you to forecast operating cash flow for five years in the future and adjust your forecast depending on the operating parameters that you think are most reasonable. So, let's get started with the first spreadsheet assignment. 1. The following numbers are for Handyman Company for 2012: Financial Statements: An Overview Chapter 2 6 5 50 31 Short-Term Loans Payable...... Long Term Debt................ $207 Interest Expense Income Tax Expense. Capital Stock Retained Earnings (as of 1/1/12). Cash 10 Receivables.. Dividends. Sales 700 Accumulated Depreciation... Accounts Payable.... 74 Inventory 153 Property. Plant, & Equipment ....... Cost of Goods Sold. ................... 519 O ther Operating Expenses.... Your assignment is to create a spreadsheet containing a balance sheet and an income statement for Handyman Company 2. Handyman is wondering what its balance sheet and income statement would have looked like if the following numbers were changed as indicated: 199 160 Change From To Sales.. $700 $730 Cost of Goods Sold. ..... 519 550 Other Operating Expenses..................... ...... . 160 160 165 Create a second spreadsheet with the numbers changed as indicated. Note: After making these changes, your balance sheet may no longer balance. Assume that any discrepancy is eliminated by increasing or decreasing Short-Term Loans Payable as much as necessary. Analytical Assignments AA 4.57 Cumulative Spreadsheet Project Preparing Forecasts This spreadsheet assignment is a continuation of the spreadsheet assignments given in earlier chap- ters. 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 state- ment numbers for Handyman Company for 2012 (given in part (1) of the Cumulative Spreadsheet Project assignment in Chapter 2]. With these historical numbers for 2012 as a starting point, Handyman wishes to prepare a forecasted balance sheet and a forecasted income statement for 2013. In preparing the forecasted financial statements for 2013, consider the following additional information: a. Sales in 2013 are expected to increase by 40% over 2012 sales of $700. b. In the forecasted balance sheet for 2013, cash, receivables, inventory, and accounts payable will all increase at the same rate as sales (40%) relative to 2012. 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 experi- ence a 40% increase in the levels of its current operating assets and liabilities. c. In 2013, 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 2013 can be computed as accumulated depreciation as of the end of 2012 plus the forecasted depreciation expense for 2013. c. 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 2013 exactly equal to 2.0. f. No new long-term debt will be acquired in 2013. (continued) Completing the Accounting Cycle Chapter 4 175 g. No cash dividends will be paid in 2013. Remember that the amount of retained eam- ings 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 2013 is the amount nec essary to make the forecasted balance sheet balance such that forecasted total assets equal forecasted total liabilities. A key reason for preparing forecasted financial state- ments is to identify in advance whether any additional financing will be required. i. The $160 in operating expenses reported in 2012 breaks down as follows: 55 depre- ciation expense, $155 other operating expenses. j. In the forecasted income statement for 2013. cost of goods sold and other operating expenses will both increase at the same rate as sales (40%) relative to 2012. This is an- other 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 prop- erty, plant, and equipment the company has. In 2012, Handyman had $5 of depreci- ation expense on $199 of property, plant, and equipment, meaning that depreciation was equal to 2.5% (55/5199) of the amount of property, plant, and equipment. It is expected that the same relationship will hold in 2013. I. Interest expense depends on how much interest-bearing debt a company has. In 2012, 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 2013, 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. Handy- man's income tax rate in 2012 was 33% (54/512). 2. Repeat (1) assuming that forecasted sales growth in 2013 is 20% instead of 40%. Clearly state any assumptions that you make
