Please explain A&B

Noting formulas used along the way. Thank You!!

Chapter 3 Problem 15 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? b. 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. 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. Please type your answers to a. and b. in the space below. AQUATIC SUPPLIES CO. INCOME STATEMENT ($ millions) 2017 Assumptions 2018 Formulas 12.00% 39.00% growth in sales percentage of sales 49.00% percentage of sales Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Expense Operating Income Depreciation & Amortization Operating Profit Interest Expense Pretax Income Total Income Taxes Net income $582.762 240.828 341.934 257.507 84.427 25.221 59.206 16.430 42.776 14.971 $27.805 30.00% percentage of net PP&E 10.00% percentage of long-term debt 35.00% percentage of pretax income BALANCE SHEET($ millions) ASSETS Cash & Equivalents Account Receivable Inventories Prepaid Expenses Other Current Assets Total Current Assets Net Property, Plant, & Equipment Intangible Assets Other Assets TOTAL ASSETS 2.00% 13.00% 5.00% no change 6.00% percentage of sales percentage of sales percentage of sales percentage of sales $7.152 70.538 39.033 9.339 27.076 153.138 81.648 9.415 24.642 $268.843 percentage of sales 15.00% no change 5.00% percentage of sales LIABILITIES Accounts Payable Accrued Expenses Accrued Wages Other Current Liabilities Total Current Liabilities Long Term Debt Total Liabilities $36.951 31.206 21.418 3.663 93.238 157.720 250.958 6.00% 5.00% 3.00% no change percentage of sales percentage of sales percentage of sales plug figure EQUITY Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Total Equity TOTAL LIABILITIES & EQUITY 1.702 55.513 118.729 158.059 17.885 $268.843 no change no change no dividends no change Data Table: Long-term Debt with Different Sales Growth Rates 4% 6% 8% 10% 12% 14% Chapter 3 Problem 15 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? b. 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. 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. Please type your answers to a. and b. in the space below. AQUATIC SUPPLIES CO. INCOME STATEMENT ($ millions) 2017 Assumptions 2018 Formulas 12.00% 39.00% growth in sales percentage of sales 49.00% percentage of sales Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Expense Operating Income Depreciation & Amortization Operating Profit Interest Expense Pretax Income Total Income Taxes Net income $582.762 240.828 341.934 257.507 84.427 25.221 59.206 16.430 42.776 14.971 $27.805 30.00% percentage of net PP&E 10.00% percentage of long-term debt 35.00% percentage of pretax income BALANCE SHEET($ millions) ASSETS Cash & Equivalents Account Receivable Inventories Prepaid Expenses Other Current Assets Total Current Assets Net Property, Plant, & Equipment Intangible Assets Other Assets TOTAL ASSETS 2.00% 13.00% 5.00% no change 6.00% percentage of sales percentage of sales percentage of sales percentage of sales $7.152 70.538 39.033 9.339 27.076 153.138 81.648 9.415 24.642 $268.843 percentage of sales 15.00% no change 5.00% percentage of sales LIABILITIES Accounts Payable Accrued Expenses Accrued Wages Other Current Liabilities Total Current Liabilities Long Term Debt Total Liabilities $36.951 31.206 21.418 3.663 93.238 157.720 250.958 6.00% 5.00% 3.00% no change percentage of sales percentage of sales percentage of sales plug figure EQUITY Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Total Equity TOTAL LIABILITIES & EQUITY 1.702 55.513 118.729 158.059 17.885 $268.843 no change no change no dividends no change Data Table: Long-term Debt with Different Sales Growth Rates 4% 6% 8% 10% 12% 14%