This problem asks you to prepare one-and five-year financial forecasts and conduct some sensitivity analysis and scenario analysis for Aquatic Supplies Company. A spreadsheet containing the company's 2017 financial statements and management's projections is available for download from McGraw-Hill's Connect or your course instructor (see the Preface for more information). Use this information to answer the questions posed in the spreadsheet.

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.  

AQUATIC SUPPLIES CO.
INCOME STATEMENT ($ millions)
	2017	Assumptions
Sales	$582.762	12%	growth in sales
Cost of Goods Sold	240.828	39%	percentage of sales
Gross Profit	341.934
Selling, General, & Administrative Expense	257.507	49%	percentage of sales
Operating Income	84.427
Depreciation & Amortization	25.221	30%	percentage of net PP&E
Operating Profit	59.206
Interest Expense	16.430	10%	percentage of long-term debt
Pretax Income	42.776
Total Income Taxes	14.971	35%	percentage of pretax income
Net income	$27.805
BALANCE SHEET ($ millions)
ASSETS
Cash & Equivalents	$7.152	2%	percentage of sales
Account Receivable	70.538	13%	percentage of sales
Inventories	39.033	5%	percentage of sales
Prepaid Expenses	9.339	no change
Other Current Assets	27.076	6%	percentage of sales
Total Current Assets	153.138
Net Property, Plant, & Equipment	81.648	15%	percentage of sales
Intangible Assets	9.415	no change
Other Assets	24.642	5%	percentage of sales
TOTAL ASSETS	$268.843
LIABILITIES
Accounts Payable	$36.951	6%	percentage of sales
Accrued Expenses	31.206	5%	percentage of sales
Other Current Liabilities	3.663	no change
Total Current Liabilities	71.820
Long Term Debt	157.720	initially constant
Accrued Wages	21.418	3%	percentage of sales
Total Liabilities	250.958
EQUITY
Common Stock	1.702	no change
Capital Surplus	55.513	no change
Retained Earnings	118.729	no dividends
Less: Treasury Stock	158.059	no change
Total Equity	17.885
TOTAL LIABILITIES & EQUITY	$268.843
External funding required

a. Prepare pro forma financial statements for Aquatic Supplies for 2018 assuming that long-term debt and interest expense remain at their 2017 levels. What is Aquatic's external funding required for 2018?   

b. Modify your forecast in part (a) assuming that long-term debt and interest expense increase in order to make up the external funding required for 2018. (Be sure to enable interative calculation in Excel.) How much additional long-term debt (compared to 2017) will be required under this assumption?

c. Why are your answers to part (a) and part (b) different?                                               

d. Perform a sensitivity analysis of Aquatic Supplies Co.’s external financing needs as determined in part (b). Assume sales grow at 17% instead of 12%. How much total long-term debt would be required?  

e. Perform a scenario analysis on the company’s projection as determined in part (b). Assume sales grow 20%, the cost of goods sold is 38% of sales, inventory falls from 5% of sales to 3%, and accounts receivable fall from 13% of sales to 10%. How much long-term debt is required in this scenario?

f. Return now to the original assumptions (sales growth=12%, COGS=39%, inventory=5%, AR=13%) and extend your projections in part (b) through 2022. Continue to assume that all external funding needs will be met with debt at 10% interest. What is your projected value for long-term debt in 2022?

g. Perform a scenario analysis on your 5-year projection in part (f). Assume growth in sales is 10%, the cost of goods sold is 41% of sales, and selling, general and administrative expenses are 50% of sales. What is your projected value for long-term debt in 2022?