Question

Chapter 3 Problem 15 a Below are the 2017 financial statements for Aquatic Supplies Co. Also appearing are management's forecasts for how individual financial statement items will vary in the future. The company expects sales to grow 12% next year. Aquatic Supplies finances all of its needs with 10-year long-term debt at 10% interest. Forecast the financial statements for 2018 assuming that long-term debt is the plug figure. (Be sure to enable interative calculation in Excel.) How much additional long-term debt (compared to 2017) will be required under this assumption? Suppose Aquatic Supplies has a maximum debt level of $175 million. Use Solver in Excel to determine the % sales growth rate such that long-term debt is $175 million. b. c. Perform a sensitivity analysis of Aquatic Supplies Co.'s long-term debt for 2018 as determined in part (a) by creating a Data Table. Vary the sales growth rate as follows: 4%, 6%, 8%, 10%, 12%, 14%. Use the results of the Data Table to draw an XY-scatter plot (connect the dots) with the XY points being sales growth rate, long-term debt). You may place the chart just below the Data Table.

Answer 1

	Aquatic Suppiles Co
	Income Statement -$ Mio
	2017	% allocation	Remarks	2018
Sales	582.76	100%	Revenue -12% Growth	652.69	582.762*112%
Cost of goods sold	240.83	41%	39% of sales ( $ 582.762)	227.28
Gross Profit	341.93	59%		425.42
Selling and G&A	257.51	44%	49% of sales ( $ 582.762)	285.55
Operating Income	84.43	14%		139.86
Depreciation and Amortization	25.22	4%	30% of net Property Plant 30% * $97.9	29.37
Operating Profit	59.21	10%		110.49
Interest Expenses	16.43	3%	No change	16.43
Pre tax Income	42.78	7%		94.06
Total Income Tax(35%)	14.97	3%	Total Income Tax(35%)	32.92
Net Income	27.80	5%		61.14

	Balance Sheet -$ Mio
Asset
cash and Equivalent	7.15	13.05	( 2% of sales $ 652.69)
Account receivable	70.54	84.85	( 13% of sales $ 652.69)
Inventory	39.03	32.63	( 5% of sales $ 652.69)
Prepaid	9.34	9.34	No change
Other current asset	27.08	39.16	( 6% of sales $ 652.69)
Total Current Asset	153.14	179.04
net property Equipment	81.65	110.69
Intangible Asset	9.42	9.42
Other Assets	24.64	32.63	( 5% of sales $ 652.69)
Total Assets	268.84	331.78
Liabilities
Account payable	36.95	39.16	( 6% of sales $ 652.69)
Accrued expenses	31.21	32.63	( 5% of sales $ 652.69)
Accured wages	21.42	19.58	( 3% of sales $ 652.69)
Other current Liability	3.66	3.66
Total current Liability	93.24	95.04
Long term debt	157.72	157.72
Total Liability	250.96	252.76
Equity	1.70	1.70
Capital Surplus	55.51	55.51
Retained earnings	118.73	179.87	Opening Retained Earning - $118.73 Add - profit made current year$ 61.14
Less - treasury Stock	-158.06	-158.06
Total Equity	17.89	79.03
Total Equity + Liability	268.84	331.79

		Amnt($)
As per Question , Long term Debt		157.72
Interest Expense		16.43
	Sales$ -	Long term Debt $-
	582.76	157.72
Growth 4%*$582.76	606.07	164.03	Growth 4%*$157.72
Growth 6%*$582.76	617.73	167.18	Growth 6%*$157.72
Growth 8%*$582.76	629.38	170.34	Growth 8%*$157.72
Growth 10%*$582.76	641.04	173.49	Growth 10%*$157.72
Growth 12%*$582.76	652.69	176.65	Growth 12%*$157.72
Growth 14%*$582.76	664.35	179.80	Growth 14%*$157.72