Water Sports, Inc a regional water sport equipment retailer and repair shop, plans a capital expenditure of $520,000 in 2020 Prepare a 2020 Pro Forma Income Statement and a 2020 Pro Forma Balance Sheet using the data from the 2019 and 2018 financial statements to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure The 2018 and 2019 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 9 that is, sales in 2020 are expected 9 higher than in 2019 2 The cost of goods sold (COGS) in 2020 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 2020 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 30 ,which is a decrease from 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 $520,000 in a repair equipment in 2020 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 2020 8 All other financial statement items are expected to remain constant in 2020 as they were in 2019 9 Assume the firm pays 10 interest on short term debt and 12 on long term debt 10 The management would like to increase the dividend per share by 20 cents compared to 2019 Answer the following questions a What is the Discretionary Financing Needed (DFN) in 2020 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 2019 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 $350,000 The Expected life is, however, only 4 years and the salvage value is expected zero 2) Original estimate of $520,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 d Use a single Data table 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 9 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 Be sure to Label your data table 2018 8,245 0 6,271 5 1,973 5 816 0 Water Sports, Inc Income Statement In thousands of dollars 2019 Sales 9,650 1 Cost of Goods 7,125 0 Gross Profit 2,525 1 Selling and G A Expenses 925 0 Fixed Expenses 320 0 EBITDA 1,280 1 Depreciation 120 0 Operating Income (EBIT) 1,160 1 Interest Expense 178 5 Pre tax Income 981 6 Tax 333 8 Net Income 647 9 320 0 837 5 100 0 737 5 175 0 562 5 191 3 371 3 22 23 Balance Sheet 24 In thousands of dollars 25 Assets 2019 2018 26 Cash Equivalents 275 0 230 0 27 Accounts Receivable 560 0 480 0 28 Inventory 1,500 0 1,300 0 29 Current Assets 2,335 0 2,010 0 30 Property, Plant Equipmer 6,120 0 5,200 0 31 Accumulated Depreciation 430 0 310 0 32 Net Plant Equipment 5,690 0 4,890 0 33 Total Assets 8,025 0 6,900 0 34 35 Liabilities and Equity 2019 2018 36 Accounts Payable 525 0 400 0 37 Notes Payable 225 0 150 0 38 Accrued Expenses 150 0 110 0 39 Current Liabilities 900 0 660 0 40 Long term Debt 2,300 0 2,300 0 41 Total Liabilities 3,200 0 2,960 0 42 Common Stock (par $25) 2,750 0 2,250 0 43 Additional Paid in Capital 849 1 980 0 44 Retained Earnings 1,225 9 710 0 45 Total Stockholders' Equity 4,825 0 3,940 0 46 Total Liabilities and Equity 8,025 0 6,900 0 18 Notes 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 22 34 110,000 1 20 34 90,000 1 10 2018 8,245 0 6,271 5 1,973 5 816 0 Water Sports, Inc Income Statement In thousands of dollars 2019 Sales 9,650 1 Cost of Goods 7,125 0 Gross Profit 2,525 1 Selling and G A Expenses 925 0 Fixed Expenses 320 0 EBITDA 1,280 1 Depreciation 120 0 Operating Income (EBIT) 1,160 1 Interest Expense 178 5 Pre tax Income 981 6 Tax 333 8 Net Income 647 9 320 0 837 5 100 0 737 5 175 0 562 5 191 3 371 3 22 23 Balance Sheet 24 In thousands of dollars 25 Assets 2019 2018 26 Cash Equivalents 275 0 230 0 27 Accounts Receivable 560 0 480 0 28 Inventory 1,500 0 1,300 0 29 Current Assets 2,335 0 2,010 0 30 Property, Plant Equipmer 6,120 0 5,200 0 31 Accumulated Depreciation 430 0 310 0 32 Net Plant Equipment 5,690 0 4,890 0 33 Total Assets 8,025 0 6,900 0 34 35 Liabilities and Equity 2019 2018 36 Accounts Payable 525 0 400 0 37 Notes Payable 225 0 150 0 38 Accrued Expenses 150 0 110 0 39 Current Liabilities 900 0 660 0 40 Long term Debt 2,300 0 2,300 0 41 Total Liabilities 3,200 0 2,960 0 42 Common Stock (par $25) 2,750 0 2,250 0 43 Additional Paid in Capital 849 1 980 0 44 Retained Earnings 1,225 9 710 0 45 Total Stockholders' Equity 4,825 0 3,940 0 46 Total Liabilities and Equity 8,025 0 6,900 0 18 Notes 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 22 34 110,000 1 20 34 90,000 1 10

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 26, 2024

Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $520,000 in 2020. Prepare a 2020 Pro Forma

Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of $520,000 in 2020.
Prepare a 2020 Pro Forma Income Statement and a 2020 Pro Forma Balance Sheet using the data from the 2019 and 2018 financial statements
to find out whether Water Sports needs to borrow additional funds to finance the planned expenditure.
The 2018 and 2019 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 9%--that is, sales in 2020 are expected 9% higher than in 2019
2	The cost of goods sold (COGS) in 2020 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 2020 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 30%,which is a decrease from 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 $520,000 in a repair equipment in 2020. 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 2020.
8	All other financial statement items are expected to remain constant in 2020 as they were in 2019.
9	Assume the firm pays 10% interest on short-term debt and 12% on long term debt.
10	The management would like to increase the dividend per share by 20 cents compared to 2019

Answer the following questions:

a.	What is the Discretionary Financing Needed (DFN) in 2020? 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
	2019 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 $350,000. The Expected life is, however, only 4 years and the salvage value is expected zero.
	2) Original estimate of $520,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.
d.	Use a single Data table 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 9% 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?
	Be sure to Label your data table.

2018 8,245.0 6,271.5 1,973.5 816.0 Water Sports, Inc. Income Statement In thousands of dollars 2019 Sales 9,650.1 Cost of Goods 7,125.0 Gross Profit 2,525.1 Selling and G&A Expenses 925.0 Fixed Expenses 320.0 EBITDA 1,280.1 Depreciation 120.0 Operating Income (EBIT) 1,160.1 Interest Expense 178.5 Pre-tax Income 981.6 Tax 333.8 Net Income 647.9 320.0 837.5 100.0 737.5 175.0 562.5 191.3 371.3 22 23 Balance Sheet 24 In thousands of dollars 25 Assets 2019 2018 26 Cash & Equivalents 275.0 230.0 27 Accounts Receivable 560.0 480.0 28 Inventory 1,500.0 1,300.0 29 Current Assets 2,335.0 2,010.0 30 Property, Plant & Equipmer 6,120.0 5,200.0 31 Accumulated Depreciation 430.0 310.0 32 Net Plant & Equipment 5,690.0 4,890.0 33 Total Assets 8,025.0 6,900.0 34 35 Liabilities and Equity 2019 2018 36 Accounts Payable 525.0 400.0 37 Notes Payable 225.0 150.0 38 Accrued Expenses 150.0 110.0 39 Current Liabilities 900.0 660.0 40 Long-term Debt 2,300.0 2,300.0 41 Total Liabilities 3,200.0 2,960.0 42 Common Stock (par $25) 2,750.0 2,250.0 43 Additional Paid-in-Capital 849.1 980.0 44 Retained Earnings 1,225.9 710.0 45 Total Stockholders' Equity 4,825.0 3,940.0 46 Total Liabilities and Equity 8,025.0 6,900.0 18 Notes: 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 22 34% 110,000 1.20 34% 90,000 1.10 2018 8,245.0 6,271.5 1,973.5 816.0 Water Sports, Inc. Income Statement In thousands of dollars 2019 Sales 9,650.1 Cost of Goods 7,125.0 Gross Profit 2,525.1 Selling and G&A Expenses 925.0 Fixed Expenses 320.0 EBITDA 1,280.1 Depreciation 120.0 Operating Income (EBIT) 1,160.1 Interest Expense 178.5 Pre-tax Income 981.6 Tax 333.8 Net Income 647.9 320.0 837.5 100.0 737.5 175.0 562.5 191.3 371.3 22 23 Balance Sheet 24 In thousands of dollars 25 Assets 2019 2018 26 Cash & Equivalents 275.0 230.0 27 Accounts Receivable 560.0 480.0 28 Inventory 1,500.0 1,300.0 29 Current Assets 2,335.0 2,010.0 30 Property, Plant & Equipmer 6,120.0 5,200.0 31 Accumulated Depreciation 430.0 310.0 32 Net Plant & Equipment 5,690.0 4,890.0 33 Total Assets 8,025.0 6,900.0 34 35 Liabilities and Equity 2019 2018 36 Accounts Payable 525.0 400.0 37 Notes Payable 225.0 150.0 38 Accrued Expenses 150.0 110.0 39 Current Liabilities 900.0 660.0 40 Long-term Debt 2,300.0 2,300.0 41 Total Liabilities 3,200.0 2,960.0 42 Common Stock (par $25) 2,750.0 2,250.0 43 Additional Paid-in-Capital 849.1 980.0 44 Retained Earnings 1,225.9 710.0 45 Total Stockholders' Equity 4,825.0 3,940.0 46 Total Liabilities and Equity 8,025.0 6,900.0 18 Notes: 19 Tax Rate 20 Shares Outstanding 21 Dividend per share 22 34% 110,000 1.20 34% 90,000 1.10