Plastic Sports, Inc. a regional recycled plastic sport equipment retailer and repair shop, plans a capital expenditure of $1,500,000 in 2022. Prepare a 2022 Pro Forma Income Statement and a 2022 Pro Forma Balance Sheet using the data from the 2021 and 2020 financial statements to find out whether Plastic Sports needs to borrow additional funds to finance the planned expenditure. USE THE PLUG METHOD (2nd method used in class) The 2020 and 2021 statements are given on the next worksheets. Use \% of sales method and the following assumptions to create the pro-formas: 1 The firm has forecasted sales growth of 14%-that is, sales in 2022 are expected 14% higher than in 2021 2 The cost of goods sold (COGS) in 2022 is expected to change with sales at rate of 70.0% of sales due to a new cost saving mechanical production pros 4 Depreciation will increase due to a new capital investment described in point seven below. 5 The tax rate is expected at 22%, in line with the previous two years 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at rates of 3%,9%,14%,6.5% and 1.5% of sales restively. 7 The firm plans an investment of $1,500,000 in a repair equipment in 2022 . The equipment has an estimated useful life of 15 years and no salvage value. This equipment will be depreciated using the straight line depreciation method. There are no other additional capital expenditures planned for 2022. 8 Fixed expense, Notes Payable, Long Term Debt, Common Stock and Additional Paid-in Capital will remain at their 2021 levels in 2022. 9 Assume the firm pays 15% interest on short-term debt and 9% on long term debt. 10 The management would like to increase the dividend per share by 20 cents compared to 2021 Answer the following questions: a. What is the Discretionary Financing Needed (DFN) in 2022? Is this a surplus or deficit? b. 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 2021 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. c. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 1) Cheaper technology can be obtained for $500,000. The Expected life is, however, only 6 years and the salvage value is expected zero. 2) Original estimate of $1,500,000 investment with the expected life of 15 years and the salvage value of zero. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as \% of sales: 1) Best Case - Sales growth is 18% 2) Base Case - Sales growth is 14% 3) Worst Case - Sales growth is 8% What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. Plastic Sports, Inc. a regional recycled plastic sport equipment retailer and repair shop, plans a capital expenditure of $1,500,000 in 2022. Prepare a 2022 Pro Forma Income Statement and a 2022 Pro Forma Balance Sheet using the data from the 2021 and 2020 financial statements to find out whether Plastic Sports needs to borrow additional funds to finance the planned expenditure. USE THE PLUG METHOD (2nd method used in class) The 2020 and 2021 statements are given on the next worksheets. Use \% of sales method and the following assumptions to create the pro-formas: 1 The firm has forecasted sales growth of 14%-that is, sales in 2022 are expected 14% higher than in 2021 2 The cost of goods sold (COGS) in 2022 is expected to change with sales at rate of 70.0% of sales due to a new cost saving mechanical production pros 4 Depreciation will increase due to a new capital investment described in point seven below. 5 The tax rate is expected at 22%, in line with the previous two years 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at rates of 3%,9%,14%,6.5% and 1.5% of sales restively. 7 The firm plans an investment of $1,500,000 in a repair equipment in 2022 . The equipment has an estimated useful life of 15 years and no salvage value. This equipment will be depreciated using the straight line depreciation method. There are no other additional capital expenditures planned for 2022. 8 Fixed expense, Notes Payable, Long Term Debt, Common Stock and Additional Paid-in Capital will remain at their 2021 levels in 2022. 9 Assume the firm pays 15% interest on short-term debt and 9% on long term debt. 10 The management would like to increase the dividend per share by 20 cents compared to 2021 Answer the following questions: a. What is the Discretionary Financing Needed (DFN) in 2022? Is this a surplus or deficit? b. 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 2021 level and that the firm will keep excess funds as extra cash. Set up a worksheet to make the balance sheet balance. Check by changing investment to fixed assets whether balance sheet balances at various levels of the investment into fixed assets. c. Use the Scenario Manager to set up two scenarios for the expected level of investment in the repair equipment: 1) Cheaper technology can be obtained for $500,000. The Expected life is, however, only 6 years and the salvage value is expected zero. 2) Original estimate of $1,500,000 investment with the expected life of 15 years and the salvage value of zero. What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary. d. Use the Scenario Manager to set up three scenarios for the expected level of sales and COGS as \% of sales: 1) Best Case - Sales growth is 18% 2) Base Case - Sales growth is 14% 3) Worst Case - Sales growth is 8% What is the DFN under each scenario? Why do you think the amount that needs to be borrowed increases or decreases? Create a Scenario Summary and provide written answer(s). Make sure to name the cells you use in the scenario summary