all questions please with formal, its qz
A B D E F. G H I J K L 1 Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $500,000 in 2021. 2 Prepare a 2021 Pro Forma Income Statement and a 2021 Pro Forma Balance Sheet using the data from the 2020 and 2019 financial statements 3. to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure. 4 The 2019 and 2020 statements are given on the next worksheets. 5 6 Use % of sales method and the following assumptions to create the pro-formas: 7 1 The firm has forecasted sales growth of 15%--that is, sales in 2021 are expected 15% higher than in 2020 8 2 The cost of goods sold (COGS) in 2021 is expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 9 3 The selling and G&A expenses in 2021 are expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 10 4 Depreciation will increase due to a new capital investment described in the point seven below. 11 5 The tax rate is expected at about 24%, in line with the previous two years 12 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at the two-year arithmetic average of the 13 proportion of these items in relation to sales. 14 7 The firm plans an investment of $500,000 in a repair equipment in 2021. The equipment has an estimated useful life of 12 years and no salvage value. 15 This equipment will be depreciated using the straight line depreciation method. 16 There are no other capital expenditures planned for 2021. 17 8 All other financial statement items are expected to remain constant in 2021 as they were in 2020. 18 9 Assume the firm pays 7% interest on short-term debt and 12% on long term debt. 19 10 The management would like to increase the dividend per share by 25 cents compared to 2020 A B C D E F G H I J K L 23 a. 24 b. 27 C. 20 21 Answer the following questions: 22 What is the Discretionary Financing Needed (DEN) in 2021? Is this a surplus or deficit? Assume that the DFN will be absorbed by Additional Notes Payable, if there is a deficit. If there is a DFN surplus, assume that Notes Payable will remain at the 25 2020 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. 26 Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 28 1) Cheaper technology can be obtained for $250,000. The Expected life is, however, only 6 years and the salvage value is expected zero. 29 2) Original estimate of $500,000 investment with the expected life of 12 years and the salvage value of zero. 30 What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? 31 Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. 32 d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as % of sales: 33 1) Best Case Sales growth is 20% and COGS is only 73% of sales 34 2) Base Case Sales growth is 15% and COGS is as projected under point 2. 35 3) Worst Case Sales growth is 0% and COGS is increased to 77% of sales. 36 What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? 37 Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. 29 A B 1 Water Sports, Inc. 2 Income Statement 3 In thousands of dollars 4 2020 2019 9,650.1 7,275.0 8,245.0 6,271.5 2,375.1 1,973.5 916.0 826.0 300.0 300.0 1,159.1 847.5 120.0 100.0 1,039.1 747.5 5 Sales 6 Cost of Goods 7 Gross Profit 8 Selling and G&A Expenses 9 Fixed Expenses 10 EBITDA 11 Depreciation 12 Operating Income (EBIT) 13 Interest Expense 14 Pre-tax Income 15 Tax 16 Net Income 17 18 Notes: 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 178.5 175.0 860.6 572.5 206.6 137.4 654.1 435.1 24% 24% 110,000 90,000 1.20 1.10 A B 23 Balance Sheet 24 In thousands of dollars 25 Assets 2020 2019 280.0 225.0 545.0 440.0 1,400.0 1,200.0 26 Cash & Equivalents 27 Accounts Receivable 28 Inventory 29 Current Assets 30 Property, Plant & Equipment 31 Accumulated Depreciation 32 Net Plant & Equipment 1,865.0 2,225.0 6,120.0 5,200.0 430.0 310.0 5,690.0 4,890.0 7,915.0 6,755.0 33 Total Assets 34 35 Liabilities and Equity 2020 2019 525.0 400.0 225.0 150.0 150.0 110.0 900.0 660.0 2,300.0 36 Accounts Payable 37 Notes Payable 38 Accrued Expenses 39 Current Liabilities 40 Long-term Del 41 Total Liabilities 42 Common Stock (par $25) 43 Additional Paid-in-Capital Retained Earnings 45 Total Stockholders' Equity 3,200.0 2,750.0 2,300.0 2,960.0 2,250.0 819.0 835.0 44 1,146.0 710.0 4,715.0 3,795.0 46 Total Liabilities and Equity 7,915.0 6,755.0 A B D E F. G H I J K L 1 Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $500,000 in 2021. 2 Prepare a 2021 Pro Forma Income Statement and a 2021 Pro Forma Balance Sheet using the data from the 2020 and 2019 financial statements 3. to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure. 4 The 2019 and 2020 statements are given on the next worksheets. 5 6 Use % of sales method and the following assumptions to create the pro-formas: 7 1 The firm has forecasted sales growth of 15%--that is, sales in 2021 are expected 15% higher than in 2020 8 2 The cost of goods sold (COGS) in 2021 is expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 9 3 The selling and G&A expenses in 2021 are expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 10 4 Depreciation will increase due to a new capital investment described in the point seven below. 11 5 The tax rate is expected at about 24%, in line with the previous two years 12 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at the two-year arithmetic average of the 13 proportion of these items in relation to sales. 14 7 The firm plans an investment of $500,000 in a repair equipment in 2021. The equipment has an estimated useful life of 12 years and no salvage value. 15 This equipment will be depreciated using the straight line depreciation method. 16 There are no other capital expenditures planned for 2021. 17 8 All other financial statement items are expected to remain constant in 2021 as they were in 2020. 18 9 Assume the firm pays 7% interest on short-term debt and 12% on long term debt. 19 10 The management would like to increase the dividend per share by 25 cents compared to 2020 A B C D E F G H I J K L 23 a. 24 b. 27 C. 20 21 Answer the following questions: 22 What is the Discretionary Financing Needed (DEN) in 2021? Is this a surplus or deficit? Assume that the DFN will be absorbed by Additional Notes Payable, if there is a deficit. If there is a DFN surplus, assume that Notes Payable will remain at the 25 2020 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. 26 Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 28 1) Cheaper technology can be obtained for $250,000. The Expected life is, however, only 6 years and the salvage value is expected zero. 29 2) Original estimate of $500,000 investment with the expected life of 12 years and the salvage value of zero. 30 What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? 31 Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. 32 d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as % of sales: 33 1) Best Case Sales growth is 20% and COGS is only 73% of sales 34 2) Base Case Sales growth is 15% and COGS is as projected under point 2. 35 3) Worst Case Sales growth is 0% and COGS is increased to 77% of sales. 36 What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? 37 Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. 29 A B 1 Water Sports, Inc. 2 Income Statement 3 In thousands of dollars 4 2020 2019 9,650.1 7,275.0 8,245.0 6,271.5 2,375.1 1,973.5 916.0 826.0 300.0 300.0 1,159.1 847.5 120.0 100.0 1,039.1 747.5 5 Sales 6 Cost of Goods 7 Gross Profit 8 Selling and G&A Expenses 9 Fixed Expenses 10 EBITDA 11 Depreciation 12 Operating Income (EBIT) 13 Interest Expense 14 Pre-tax Income 15 Tax 16 Net Income 17 18 Notes: 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 178.5 175.0 860.6 572.5 206.6 137.4 654.1 435.1 24% 24% 110,000 90,000 1.20 1.10 A B 23 Balance Sheet 24 In thousands of dollars 25 Assets 2020 2019 280.0 225.0 545.0 440.0 1,400.0 1,200.0 26 Cash & Equivalents 27 Accounts Receivable 28 Inventory 29 Current Assets 30 Property, Plant & Equipment 31 Accumulated Depreciation 32 Net Plant & Equipment 1,865.0 2,225.0 6,120.0 5,200.0 430.0 310.0 5,690.0 4,890.0 7,915.0 6,755.0 33 Total Assets 34 35 Liabilities and Equity 2020 2019 525.0 400.0 225.0 150.0 150.0 110.0 900.0 660.0 2,300.0 36 Accounts Payable 37 Notes Payable 38 Accrued Expenses 39 Current Liabilities 40 Long-term Del 41 Total Liabilities 42 Common Stock (par $25) 43 Additional Paid-in-Capital Retained Earnings 45 Total Stockholders' Equity 3,200.0 2,750.0 2,300.0 2,960.0 2,250.0 819.0 835.0 44 1,146.0 710.0 4,715.0 3,795.0 46 Total Liabilities and Equity 7,915.0 6,755.0
