Answer the following questions with the information provided in the income statement and balance sheet

Instructions: a. Calculate the Times Interest-Earned ratio for each year. b. Calculate the % EBIT can fall before TIE dips below 1.0. c. Consulting Table 6.5 in the text (replicated below) and based on TIE alone, what bond rating would Aquatic Supplies have in 2018? d. Based on this rating, would a significant increase in financial leverage be a prudent strategy for Aquatic Supplies? (Answer Yes or No) Table 6.5: Key Ratios by Standard & Poor's Rating Category B Times interest earned Debt to total assets (%) Return on invested capital (%) AAA 20.9 23.4 14.9 AA 15.7 24.2 10.4 6.7 28.9 6.9 BBB 4.7 31.7 5.2 BB 3.3 39.3 4.2 1.1 48.4 -2.1 CCC NM 67.9 -8.3 Number of companies Percent of sample companies (%) 2 0.2 28 2.2 190 14.8 418 32.5 35.7 27.8 245 19.1 46 3.6 Optional additional ratio calculations for Aquatic Supplies: LT Debt to Total assets EBIT Tax rate NOPAT = EBIT X (1 - Tax rate) Total capital = LT debt + Total equity Return on invested capital v.1 = NOPAT / Total capital Chapter 6 Problem 16 Below are the 2017 financial statements for Aquatic Supplies Co and the 5-year forecasts. (Be sure to enable iterative calculations to avoid circularity issues. Instructions are at the bottom of the spreadsheet. AQUATIC SUPPLIES CO. INCOME STATEMENT ($ millions) 2017 Assumptions 2018 2019 2020 2021 2022 2022 Formulas $582.762 12.00% $652.693 $731.017 $818.739 $916.987 growth in sales percentage of sales 39.00% 49.00% percentage of sales Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Expense EBITDA Depreciation & Amortization Operating Profit (EBIT) Interest Income Interest Expense Pretax Income Total Income Taxes Net income 30.00% percentage of net PP&E' 240.828 341.934 257.507 84.427 25.221 59.206 16.430 16.430 42.776 14.971 $27.805 254.550 398.143 319.820 78.323 29.371 48.952 1.500 18.740 31.712 11.099 $20.613 285.096 445.920 358.198 87.722 32.896 54.826 1.500 18.640 37.686 13.190 $24.496 319.308 499.431 401.182 98.249 36.843 61.405 1.500 18.378 44.528 15.585 $28.943 357.625 559.362 449.324 110.038 41.264 68.774 1.500 17.922 52.352 18.323 $34.029 $1,027.026 =H9*(1+$C$9) 400.540 =19*$C$10 626.486 =19-110 503.243 =19*$C$12 123.243 =111-112 46.216 =$C$14*130 77.027 =113-114 1.500 =($C$17*124) 17.240 =($C$17*141) 61.287 =115+116-117 21.451 =118*$C$19 $39.837 =118-119 10.00% 10.00% % of cash & equivalents % of long-term debt 35.00% % of pretax income ASSETS Cash & Equivalents Account Receivable Inventories Prepaid Expenses Other Current Assets Total Current Assets Net Property, Plant, & Equipment Intangible Assets Other Assets TOTAL ASSETS BALANCE SHEET ($ millions) 2017 Assumptions $7.152 2.00% percentage of sales 70.538 13.00% percentage of sales 39.033 5.00% percentage of sales 9.339 no change 27.076 6.00% percentage of sales 153.138 ma 81.648 15.00% percentage of sales 9.415 no change 24,642 5.00% percentage of sales $268.843 2018 $15.00 $84.85 $32.63 9.339 $39.16 180.985 $97.90 9.415 $32.63 $320.939 2019 $15.00 $95.03 $36.55 9.339 $43.86 199.783 $109.65 9.415 $36.55 $355.401 2020 $15.00 $106.44 $40.94 9.339 $49.12 220.836 1990 $122.81 9.415 $40.94 $393.999 2021 $15.00 $119.21 $45.85 9.339 $55.02 a 244.416 $137.55 9.415 $45.85 $437.228 2022 2022 Formulas $15.00 =IF(156