90.2K

Verified Solution

Link Copied!

Below are the 2017 financial statements for Aquatic Supplies Co. Also appearing are managements 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.					Please type your answers to a. and b. in the space below.
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?					a.
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.
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.

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 $582.762 Cost of Goods Sold 240.828 Gross Profit 341.934 Selling, General, & Administrative Ex 257.507 Operating Income 84.427 Depreciation & Amortization 25.221 Operating Profit 59.206 Interest Expense 16.430 Pretax Income 42.776 Total Income Taxes 14.971 Net income $27.805 30.00% percentage of net PP&E #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA 10.00% :ntage of long-term debt 35.00% antage of pretax income percentage of sales percentage of sales percentage of sales BALANCE SHEET($ millions) ASSETS Cash & Equivalents $7.152 2.00% Account Receivable 70.538 13.00% Inventories 39.033 5.00% Prepaid Expenses 9.339 no change Other Current Assets 27.076 6.00% Total Current Assets 153.138 Net Property, Plant, & Equipment 81.648 15.00% Intangible Assets 9.415 no change Other Assets 24.642 5.00% TOTAL ASSETS $268.843 percentage of sales #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA percentage of sales 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 #NA #NA #NA #NA #NA #NA #NA 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 #NA #NA #NA #NA #NA #NA Data Table: Long-term Debt with Different Sales Growth Rates 4% 6% 8% 10% 12% 14% 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 $582.762 Cost of Goods Sold 240.828 Gross Profit 341.934 Selling, General, & Administrative Ex 257.507 Operating Income 84.427 Depreciation & Amortization 25.221 Operating Profit 59.206 Interest Expense 16.430 Pretax Income 42.776 Total Income Taxes 14.971 Net income $27.805 30.00% percentage of net PP&E #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA 10.00% :ntage of long-term debt 35.00% antage of pretax income percentage of sales percentage of sales percentage of sales BALANCE SHEET($ millions) ASSETS Cash & Equivalents $7.152 2.00% Account Receivable 70.538 13.00% Inventories 39.033 5.00% Prepaid Expenses 9.339 no change Other Current Assets 27.076 6.00% Total Current Assets 153.138 Net Property, Plant, & Equipment 81.648 15.00% Intangible Assets 9.415 no change Other Assets 24.642 5.00% TOTAL ASSETS $268.843 percentage of sales #NA #NA #NA #NA #NA #NA #NA #NA #NA #NA percentage of sales 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 #NA #NA #NA #NA #NA #NA #NA 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 #NA #NA #NA #NA #NA #NA Data Table: Long-term Debt with Different Sales Growth Rates 4% 6% 8% 10% 12% 14%

Answer & Explanation Solved by verified expert

See Answer