Please Workout On Excel Spreadsheet and include link or screen shot of the Pro Forma IS and BS please!

Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $450,000 in 2019. Prepare a 2019 Pro Forma Income Statement and a 2019 Pro Forma Balance Sheet using the data from the 2018 and 2017 financial statements to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure. The 2017 and 2018 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 2019 are expected 14% higher than in 2018 2 The cost of goods sold (COGS) in 2019 is expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 3 The selling and G&A expenses in 2019 are expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 4 Depreciation will increase due to a new capital investment described in the point seven below. 5 The tax rate is expected at about 34%, in line with the previous two years 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at the two-year arithmetic average of the proportion of these items in relation to sales. 7 The firm plans an investment of $450,000 in a repair equipment in 2019. The equipment has an estimated useful life of 10 years and no salvage value. This equipment will be depreciated using the straight line depreciation method. There are no other capital expenditures planned for 2019. 8 All other financial statement items are expected to remain constant in 2019 as they were in 2018. 9 Assume the firm pays 6% interest on short-term debt and 10% on long term debt. 10 The management would like to increase the dividend per share by 20 cents compared to 2018 Answer the following questions: a. b. C. What is the Discretionary Financing Needed (DFN) in 2019? 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 2018 level and that the firm will keep excess funds as extra cash. Set up an iterative 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. 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 $250,000. The Expected life is, however, only 4 years and the salvage value is expected zero. 2) Original estimate of $450,000 investment with the expected life of 10 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. 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 20% and COGS is only 72% of sales 2) Base Case - Sales growth is 14% and COGS is as projected under point 2. 3) Worst Case - Sales growth is 0% and COGS is increased to 78% of sales. 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. Balance Sheet Water Sports, Inc. Income Statement In thousands of dollars In thousands of dollars Assets 2018 2017 2018 2017 Cash & Equivalents 280.0 225.0 Sales 9,650.1 8,245.0 Accounts Receivabl 545.0 440.0 Cost of Goods 1,200.0 7,275.0 2,375.1 6,271.5 1,973.5 1,400.0 2,225.0 6,120.0 1,865.0 916.0 826.0 Inventory Current Assets Property, Plant & E Accumulated Depre Net Plant & Equipment Gross Profit Selling and G&A Expenses Fixed Expenses EBITDA 5,200.0 300.0 300.0 430.0 310.0 1,159.1 847.5 5,690.0 4,890.0 120.0 100.0 Total Assets 7,915.0 6,755.0 Depreciation Operating Income (EBIT) Interest Expense 1,039.1 747.5 178.5 175.0 Liabilities and Equity 2018 2017 Pre-tax Income 860.6 572.5 525.0 400.0 Tax 292.6 194.7 Accounts Payable Notes Payable Accrued Expenses 225.0 150.0 Net Income 568.0 377.9 150.0 110.0 Current Liabilities 900.0 660.0 Notes: 2,300.0 2,300.0 Long-term Debt Total Liabilities Tax Rate 34% 34% 3,200.0 2,750.0 2,960.0 2,250.0 Shares Outstanding 110,000 90,000 Common Stock (par Dividend per share 1.20 1.10 Additional Paid-in- 819.0 835.0 1,146.0 710.0 Retained Earnings Total Stockholders' Equi Balance Sheet 4,715.0 3,795.0 In thousands of dollars Total Liabilities and Equi 7,915.0 6,755.0 Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $450,000 in 2019. Prepare a 2019 Pro Forma Income Statement and a 2019 Pro Forma Balance Sheet using the data from the 2018 and 2017 financial statements to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure. The 2017 and 2018 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 2019 are expected 14% higher than in 2018 2 The cost of goods sold (COGS) in 2019 is expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 3 The selling and G&A expenses in 2019 are expected to change with sales at the two-year arithmetic average of the proportion of this item in relation to sales. 4 Depreciation will increase due to a new capital investment described in the point seven below. 5 The tax rate is expected at about 34%, in line with the previous two years 6 Cash, accounts receivable, inventory, accounts payable, and accrued expenses are expected to change with sales at the two-year arithmetic average of the proportion of these items in relation to sales. 7 The firm plans an investment of $450,000 in a repair equipment in 2019. The equipment has an estimated useful life of 10 years and no salvage value. This equipment will be depreciated using the straight line depreciation method. There are no other capital expenditures planned for 2019. 8 All other financial statement items are expected to remain constant in 2019 as they were in 2018. 9 Assume the firm pays 6% interest on short-term debt and 10% on long term debt. 10 The management would like to increase the dividend per share by 20 cents compared to 2018 Answer the following questions: a. b. C. What is the Discretionary Financing Needed (DFN) in 2019? 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 2018 level and that the firm will keep excess funds as extra cash. Set up an iterative 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. 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 $250,000. The Expected life is, however, only 4 years and the salvage value is expected zero. 2) Original estimate of $450,000 investment with the expected life of 10 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. 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 20% and COGS is only 72% of sales 2) Base Case - Sales growth is 14% and COGS is as projected under point 2. 3) Worst Case - Sales growth is 0% and COGS is increased to 78% of sales. 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. Balance Sheet Water Sports, Inc. Income Statement In thousands of dollars In thousands of dollars Assets 2018 2017 2018 2017 Cash & Equivalents 280.0 225.0 Sales 9,650.1 8,245.0 Accounts Receivabl 545.0 440.0 Cost of Goods 1,200.0 7,275.0 2,375.1 6,271.5 1,973.5 1,400.0 2,225.0 6,120.0 1,865.0 916.0 826.0 Inventory Current Assets Property, Plant & E Accumulated Depre Net Plant & Equipment Gross Profit Selling and G&A Expenses Fixed Expenses EBITDA 5,200.0 300.0 300.0 430.0 310.0 1,159.1 847.5 5,690.0 4,890.0 120.0 100.0 Total Assets 7,915.0 6,755.0 Depreciation Operating Income (EBIT) Interest Expense 1,039.1 747.5 178.5 175.0 Liabilities and Equity 2018 2017 Pre-tax Income 860.6 572.5 525.0 400.0 Tax 292.6 194.7 Accounts Payable Notes Payable Accrued Expenses 225.0 150.0 Net Income 568.0 377.9 150.0 110.0 Current Liabilities 900.0 660.0 Notes: 2,300.0 2,300.0 Long-term Debt Total Liabilities Tax Rate 34% 34% 3,200.0 2,750.0 2,960.0 2,250.0 Shares Outstanding 110,000 90,000 Common Stock (par Dividend per share 1.20 1.10 Additional Paid-in- 819.0 835.0 1,146.0 710.0 Retained Earnings Total Stockholders' Equi Balance Sheet 4,715.0 3,795.0 In thousands of dollars Total Liabilities and Equi 7,915.0 6,755.0