PROBLEMS 1. Using the data in the student spreadsheet file Ethan Allen Financials xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, ninth edition, go to (www.cengage.com/finance/mayes/analysis/9e), forecast the June 30, 2019 income statement and balance sheet for Ethan Allen. Use the percent of sales method and the following assumptions: (1) Sales in FY 2019 will be $761.20; (2) The tax rate will be 25%; (3) Each item that changes with sales will be the five-year average percentage of sales; (4) Property, Plant & Equipment - Gross will increase to $650; and (5) The dividend will be $0.90 per share. Use your judgment on all other items. What is the DFN in 2019? Is this a surplus or deficit? a. Ethan Allen Interiors Inc. Income Statement (Industrial) All figures in millions of U.S. Dollar, except per share items Jun '18 Jun '16 Jun '15 Jun '14 Sales 766.784 763.385 794.202 754.600 746,659 Cost of Goods Sold (COGS) inel. D&A 350.820 343.662 351.966 343.437 340.163 Gross Income 415.964 419.723 442.236 411.163 406.496 B SG&A Expense 367.097 361.773 353.057 345229 336.860 EBIT (Operating Income) 48.867 57.950 89.179 65.934 69.636 0 Interest Expense 0.325 1.223 1.618 5.957 7.540 11 Other Income - Net 0.525 0.268 0.395 1.206 0.306 12 Unusual Expense - Net 0.000 0.000 0.000 4.500 0.000 13 Pretax Income 49.067 56.995 87.956 56.683 62.402 14 Income Taxes 12.696 20.801 31.319 19.541 19 471 15 Net Income 36.371 36.194 56.637 37.142 42.931 16 17 18 EPS (recurring) 1.32 1.29 2.00 138 1.47 19. EPS (diluted) 1.32 129 2.00 1.27 1.47 20 Diluted Shares Outstanding 27.63 27.96 28.32 29.18 29 28 21 Total Shures Outstanding 26.53 27.45 27.75 28.41 28.93 22 Dividends per Share 0.76 0.74 0.62 0.50 0.40 23 Payout Ratio -57.72 57.36 31.00 3937 2721 Actual Jan 19 746,684 337.193 409.491 357.164 52.327 0.087 0.000 18.380 33.860 8.162 25.698 0.96 26.75 0.76 68.70 48.87 19.83 78.07 $7.95 20.12 25 EBITDA 26 EBIT 27 Depreciation & Amortization Expense 28 29 30 Source FactSet Fundamentals 108.53 89.18 19.35 85.0 65.93 19.14 87.57 69.64 17.93 71.93 33.95 19.60 PROBLEMS 1. Using the data in the student spreadsheet file Ethan Allen Financials xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, ninth edition, go to (www.cengage.com/finance/mayes/analysis/9e), forecast the June 30, 2019 income statement and balance sheet for Ethan Allen. Use the percent of sales method and the following assumptions: (1) Sales in FY 2019 will be $761.20; (2) The tax rate will be 25%; (3) Each item that changes with sales will be the five-year average percentage of sales; (4) Property, Plant & Equipment - Gross will increase to $650; and (5) The dividend will be $0.90 per share. Use your judgment on all other items. What is the DFN in 2019? Is this a surplus or deficit? a. Ethan Allen Interiors Inc. Income Statement (Industrial) All figures in millions of U.S. Dollar, except per share items Jun '18 Jun '16 Jun '15 Jun '14 Sales 766.784 763.385 794.202 754.600 746,659 Cost of Goods Sold (COGS) inel. D&A 350.820 343.662 351.966 343.437 340.163 Gross Income 415.964 419.723 442.236 411.163 406.496 B SG&A Expense 367.097 361.773 353.057 345229 336.860 EBIT (Operating Income) 48.867 57.950 89.179 65.934 69.636 0 Interest Expense 0.325 1.223 1.618 5.957 7.540 11 Other Income - Net 0.525 0.268 0.395 1.206 0.306 12 Unusual Expense - Net 0.000 0.000 0.000 4.500 0.000 13 Pretax Income 49.067 56.995 87.956 56.683 62.402 14 Income Taxes 12.696 20.801 31.319 19.541 19 471 15 Net Income 36.371 36.194 56.637 37.142 42.931 16 17 18 EPS (recurring) 1.32 1.29 2.00 138 1.47 19. EPS (diluted) 1.32 129 2.00 1.27 1.47 20 Diluted Shares Outstanding 27.63 27.96 28.32 29.18 29 28 21 Total Shures Outstanding 26.53 27.45 27.75 28.41 28.93 22 Dividends per Share 0.76 0.74 0.62 0.50 0.40 23 Payout Ratio -57.72 57.36 31.00 3937 2721 Actual Jan 19 746,684 337.193 409.491 357.164 52.327 0.087 0.000 18.380 33.860 8.162 25.698 0.96 26.75 0.76 68.70 48.87 19.83 78.07 $7.95 20.12 25 EBITDA 26 EBIT 27 Depreciation & Amortization Expense 28 29 30 Source FactSet Fundamentals 108.53 89.18 19.35 85.0 65.93 19.14 87.57 69.64 17.93 71.93 33.95 19.60