transfer information on excel sheet and include formulas
Assignment IS and BS Water Sports, Inc. a regional water sport equipment retailer and repair shop, plans a capital expenditure of 5520,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 Assignment IS and BS Water Sports, Inc. Income Statement In thousands of dollars 2019 2018 Sales Cost of Goods Grow Prefir Selling and G&A Expenses Fixed Expenses EBITDA Depreciation Opring Income (EBIT) Interest Expense Pre- Income 9.650.125 2125000 2,525, 125 925.000 320.000 1.280.125 120.000 1.160.125 178.500 981.625 333.75251 647,872.5 8.245.000 15001 1973.500 16.000 320.000 8 32500 100.000 2120 175.000 562.500 191.250 377.250 Tw Naf Income Notes: Tax Rate Shares Outstanding Dividend per share 110.000 20.XIO 110 120 Balance Sheet In thousands of dollars Cash & Equivalents Accounts Receivable Inventory Current Assets Property. Plant & Equipment Accumulated Depreciation Na Plant & Equipment 2019 2018 275.000 210.000 500000D DIO 1.500.000 100. DOO 2,335,000 2,010,000 6,120 000 200 000 410.000 110000 5.690.000 4.890.000 8.025.000 6.900.000 Total Assets Liabilities and Equity Accounts Payable Notes Payable Accred Expenses Current Liais Long-term Debt Total Liabilities Common Stock (par $25) Additional Paid-in-Capital Retained Earnings Total Stockholders' Equity Total Liabilities and Equity 2019 525.000 225.000 150.000 900,000 2.300.000 3,200,000 2.750.000 849 100 1.225.872 4.824,972.5 8,024,972.5 2018 400.000 150.000 110.000 660,000 2300,000 2,060,000 250.XIO 9 80 DXIO 7 10 DXIO 3,940,000 6,900,000 Water Sports, Inc. Income Statement In thousands of dollars Sales Cost of Goods Gross Profit Selling and G&A Expenses Fixed Expenses EBITDA Depreciation Operating Income (EBIT) Interest Expense Pre-fix Income Tax Net Income 2019 2018 9,650.125 8.245.000 7.125.000 6.271,500 2,525,125 1.973.500 925.000 16.XIO 20.0001320.00 1,280,125 37,500 120.000 100.000 1.160.125 737500 178,500 175.000 981.625 562.500 333.75251 191.250 647.872.5 371.250 Notes: Tax Rate Shares Outstanding Dividend per share 90,000 110.000 1.20 Balance Sheet In thousands of dollars 2019 2018|| 275.000 20.XIO Cash & Equivalents Accounts Receivable Inventory Christ Property. Plant & Equipment Accumulated Depreciation Ner Plant & Equipment Total Assets 500.000 1.500.000 2,335,000 6.120.000 430.000 5.690.0001 8.025,000 480,XIO 1.300.000 2,010,000 5.200,00 310.000 4.890,000 6,900,000 Liabilines and Equity Accounts Payable Notes Payable Accrued Expenses Current Liabilities Long-term Debt Total Liabilities Common Stock (par $25) Additional Paid-in-Capital Retained Earings Toral Stockholders' Equity Toral Liabilities and Equity 2019 525.000 225.000 150.000 900,000 2.3000 3,200,000 2.750.000 849 100 1.225.872. 4,824,972.31 8,024,972.5 2018 400.000 150.000 110.000 660,000 2300.XIO 2,960,000 2.250,00 9 8.XIO 710,0X10 3,940,000 6,900,000
