13. What was MLHR's Net Working Capital for the last two years (omit 000's)? Most Current Year = Previous Year= 215.2 231.6 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? Somewhat import factor 15. Suppose MLHR doubled the amount of time to pay trade creditors. Use the proforma sheet to indicate the impact on the financial statements. (Start with the identical parameters indicated on Ratio page for previous year for Inv, AR and AP and then double Days Payable.) What are the new plug figure to make the balance sheet balance? State answer omitting the last 000.000's consistent with proforma. (Note one plug is always zero for cells D28 and D41 on the Proforma Spreadsheet) a. Cash & Marketable Securities $ 504.4 b. Notes Payable Bank = $ 52.5 16. Suppose MLHR Days Payable is at the same level as the prior year. Use the proforma sheet to indicate the impact on the financial statements assuming Days Sales Outstanding and Days in Inventory both double, causing the operating cycle to double in length. What are the new plug figure to make the balance sheet balance? State answer omitting the last 000.000's consistent with proforma. (Note one plug is always zero for cells D28 and D41 on the Proforma Spreadsheet) a. Cash & Marketable Securities $ b. Notes Payable Bank = $ 212.5 98.9 17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)? Proforma Net Income = 102.3 18. Suppose all parameter estimates are based on last years results, except Sales is expected to grow at 25%. What would be the impact on Net Income and the Plugs? (Note omit 000.000's and one of the plugs is always zero.) Cash & Marketable Securities Notes Payable Bank = Proforma Net Income = 19. Assuming MLHR is expecting a 25% increase in Sales and they have the plant capacity, what should they do now to prepare for this? They should begin to produce product beforehand in order to make sure they have sufficent inventory in stock for the following year. Discussion Question: Summarize your advise to management of MLHR based on inferences gained from this common size analysis. Herman Miller Income Statement (000,000's omitted) Inputs are highlighted 1637.3 Sales COGS Gross Margin Selling, Gen & Adm Exp. Restr & Impair Exp Design & Research Exp Depr & Amort Exp.* Total Operating Exp. EBIT Less Expenses (Income) Interest Expense Interest & Other Invest (Income) Other, Net Net Other Expenses (Income) EBT Income Taxes -Noncontrol Int Net Income FYE % FYE % FYE % 5/31/2014 Chng 5/30/2015 Chng 5/28/2016 Chng 1882.0 14% 2142.2 6% 2264.9 5% 1251.0 8% 1350.8 3% 1390.7 8% 631.0 25% 791.4' 10% 874.2 0% 511.3 -3% 494.1 8% 532.6 3% 26.5 -52% 12.7 -100% 0.0 #DIV/0! 65.9 8% 71.4 8% 77.1 -5% 53.0 -6% 49.8 6% 52.9 15% 656.77 4% 628.0 6% 662.6 4% (25.7) -736% 163.4 29% 211.6 -13% Common Size RMA FYE % FYE % of Ind 6/2/2018 Chng 6/1/2019 Sales Comp 2381.2 8% 25672 100.0% 100.0% 1508.2 9% 63.8% 71.7% 873.0 7% 929.9 36.2% 28.3% 548.4 3% 567.2 22.1% 5.7 n/a 10.2 -0.4% 73.1 5% 76.9 60.9 8% 65.9 -2.6% 688.1 5% 720.2 28.1% 23.8% 184.9 13% 209.7 8.2% 4.5% 0.5% -0.1% 17.7 17.6 -1% (0.4) 50% 0.5 3% (43.4) 435% (21.2) 323% 0.1 (22.1) -541% 17.5 -12% (0.6) n/a 1.3 18.2' -18% 145.2 35% 47.2 26%' 0.5 97.5 40% 15.4 -12% (0.8) n/a 0.3 14.9 -28% 196.7 -11% 59.5 -29% 0.4 136.8 -2% 10.8 174.1 13.5 -10% (4.4) n/a 1.7 -22% 16% 42.4 -7% (3.0) 134.7 24% 12.1 (2.1 (1.6) 8.4 201.3 39.6 (5.0) 166.7 0.3% 7.8% 1.5% 1 6.5% Herman Miller Balance Sheet (000,000's omitted) Common Size RMA % FYE % FYE % of Industry Chng 6/2/2018 Chng 6/1/2019 Tot Assets Comp 112% 203.9 -22% 159.2 10.1% 4.5% 0% 8.6 2% 8.8 0.6% 2.0% 18% 219.3 15% 252.3 16.1% 40.3% 7% 162.4 13% 184.2 11.7% 29.2% -44% -7% 9.2 0.6% 2.0% 41.3 15% 47.6 3.0% 31% 645.4 2% 661.3 42.1% 78.0% 5% 331.4 5% 348.6 22.2% 5% 331.4 5% 348.6 22.2% 16.7% 423.5 0% 423.0 27.0% 0.0% -59% 79.2 72% 136.4 8.7% 7.4% 13% 1479.5 6% 1569.3 100.0% 100.0% ASSETS Cash & Cash Equivalents Marketable Securities Acct. Rec., less allowances Inventory Def. Income Tax & Prepaid Other Current Assets Total Current Assets Net Property, Plant, & Equip Fixed Assets (Net) Goodwill & Intangible Assets Other Assets Total Assets 9.9 15% 2% 4% 11% 0.0 171.4 242.4 0.0 0.0 413.8 0.0 177.7 268.4 0.0 0.0 446.1 0.0% 11.3% 17.1% 0.0% 0.0% 28.4% 12.5% 18.7% 0.6% 1.3% 8.5% 41.6% 7% 8% 3% 6% LIABILITIES Notes Payable - Bank Accounts Payable Accruals & Unearned Rev Current Maturities - LTD Other Current Liabilities Total Current Liabilities Long Term Debt (LTD) Pension & Other Liabilities Total Liabilities Redeemable NonControlling Interests Preferred Stock Common Stock Additional Paid-In Capital Retained Earnings Accum. Other Comprehen. Key Exec & Noncontrol Int Total Equity Total Liabilities & Equity 6.7% 7.2% 55.5% 6% 38% -12% 13% 2% 0% -2% -16% 15% -25% -38% 13% 275.0 95.4 784.2 30.5 0.0 11.7 116.6 598.3 (61.3) (0.5) 664.8 2% 0% 0% -23% 19% 54% 60% 8% 281.9 101.5 829.5 20.6 0.0 11.7 89.8 712.7 (94.2) (0.8) 719.2 18.0% 6.5% 52.9% 1.3% 0.0% 0.7% 5.7% 45.4% -6.0% -0.1% 45.8% 44.3% 13% 1479.5 6% 1569.3 100.0% 100.0% RATIO ANALYSIS Herman Miller RMA 6/3/2017 6/2/2018 6/1/2019 Industry Comparison 1.3 0.9 1.6 1.2 1.5 1.1 2.1 0.9 9.1 9.3 8.9 6.2 1.7 1.6 1.6 2.3 30 34 35.9 51 40 39 41.1 59 39 41 39.6 37 5/30/2015 LIQUIDITY Current 1.3 Quick 0.9 ASSET MANAGEMENT Inventory Turnover 10.4 (COGS / Inventory) Total Asset Turnover 1.8 DSO (AR Period) 32 (365/AR Turnover) Inventory Period 35 (365/Inventory Turnover) Days in AP (AP Period) 45 (365/[COGS/AP]) Cash Cycle 23 LEVERAGE TIE (EBIT / Interest) 9.3 Debt / Equity (RMA Debt/Worth) 1.8 Cash Coverage Ratio 12.2 PROFITABILITY % Profit BT / Tot Assets 12.17% Gross Profit 36.94% PM (NI/Sales) RMA uses NIBTIS 4.55% DuPont Analysis: ROE EROA=PM'TAT ROE (NI / Total Equity) 22.61% ROA (NI / Total Assets) 8.17% PM (NI / Sales) 4.55% Total Asset Turnover 1.80 Equity Multiplier (A/E) 2.77 31 31 37.3 73 13.7 1.2 17.2 13.7 1.2 18.2 17.3 1.2 22.8 5.6 1.5 15.06% 38.60% 6.04% 11.77% 36.66% 5.66% 12.83% 36.22% 6.49% 13.40% 28.30% 4.50% 22.84% 10.47% 6.04% 1.73 2.18 19.84% 9.10% 5.66% 1.61 2.18 22.87% 10.62% 6.49% 1.64 2.15 25.88% 10.35% 4.50% 2.30 2.50 Inputs are highlighted Herman Miller Proformas (000,000's are omitted all numbers are in millions) FYE 6/1/2019 2,567.2 (1,637.3) 929.9 (567.2) (87.1) (65.9) 209.7 CS% of Sales 100% -64% 36% -22% -3% -3% 8% Proforma 3,209.0 (2,046.6) 1,162.4 (567.2) (87.1) (65.9) 442.2 Sales Cost of Goods Sold(COGS) Gross Profit Operating Expense Research & Dev. & Non Reci Depreciation Expense EBIT Less (Expenses) Income Interest (Expense) Interest Income Other (Expenses) Income Net Other Expenses (Income) NIBT Income taxes Equity Earning & Noncontrol Net Income Step 1: Input Parameter Estimates Parameters & Ratios Days Sales Outstanding 35.9 Days in Inventory 41.1 Days in Accounts Payable 39.6 0% 0% 0% 0% 8% -2% Growth Rate on Sales (12.1) 2.1 1.6 (8.4) 201.3 (39.6) (5.0) 156.7 25% (19.5) 1.8 1.6 (16.0) 426.1 (179.0) (5.0) 242.2 6% Interest & Tax Rate Parameters Marketable Sec 0.50% Long Term Invest 7.00% NP-Bank 4.00% Long Term Debt 6.90% Tax Rate 42.00% Dividends Retained Earnings 0% 6% 156.7 242.2 Herman Miller Balance Sheet (000's) ASSETS Cash & Mkt Securties (plug) Accounts Receivable Inventory Prepaids Other Current Assets Total Current Assets FYE CS% of 6/1/2019 ot Assets 168.0 11% 252.3 16% 184.2 12% 9.2 1% 47.6 3% 661.3 42% Proforma 363.1 315.4 230.3 9.2 47.6 965.5 Step 2: Balance Sheet Check Make sure your proforma balance sheet is balanced. Use the plugs to force A = L + E. Use the following Balance Sheet Check Total Assets 1,873.5 Total Liabilities & Equity 1,699.2 Should be 0: A - (L+E) - 174.3 If not adjust plug until it is. Property, Plant, & Equip Notes Receivables Other Assets Total Assets 348.6 423.0 136.4 1,569.3 22% 27% 9% 348.6 423.0 136.4 1,873.5 100% 177.7 268.4 0% 11% 17% 0% 0% 222.1 268.4 28% LIABILITIES Notes Payable - Bank (Plug) Accounts Payable Accruals Current Maturities - LTD Other Current Liabilities Total Current Liabilities Other Liabilities Long Term Debt (LTD) Total Liabilities Reedemable Noncontrolling Common Stock Additional Paid-In Cap & Other Retained Earnings Total Liabilities & Equity 18% 53% 446.1 101.5 281.9 829.5 20.6 11.7 89.0 618.5 1,569.3 490.5 101.5 281.9 873.9 20.6 11.7 89.0 704.0 1,699.2 1% 39% 100% 13. What was MLHR's Net Working Capital for the last two years (omit 000's)? Most Current Year = Previous Year= 215.2 231.6 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? Somewhat import factor 15. Suppose MLHR doubled the amount of time to pay trade creditors. Use the proforma sheet to indicate the impact on the financial statements. (Start with the identical parameters indicated on Ratio page for previous year for Inv, AR and AP and then double Days Payable.) What are the new plug figure to make the balance sheet balance? State answer omitting the last 000.000's consistent with proforma. (Note one plug is always zero for cells D28 and D41 on the Proforma Spreadsheet) a. Cash & Marketable Securities $ 504.4 b. Notes Payable Bank = $ 52.5 16. Suppose MLHR Days Payable is at the same level as the prior year. Use the proforma sheet to indicate the impact on the financial statements assuming Days Sales Outstanding and Days in Inventory both double, causing the operating cycle to double in length. What are the new plug figure to make the balance sheet balance? State answer omitting the last 000.000's consistent with proforma. (Note one plug is always zero for cells D28 and D41 on the Proforma Spreadsheet) a. Cash & Marketable Securities $ b. Notes Payable Bank = $ 212.5 98.9 17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)? Proforma Net Income = 102.3 18. Suppose all parameter estimates are based on last years results, except Sales is expected to grow at 25%. What would be the impact on Net Income and the Plugs? (Note omit 000.000's and one of the plugs is always zero.) Cash & Marketable Securities Notes Payable Bank = Proforma Net Income = 19. Assuming MLHR is expecting a 25% increase in Sales and they have the plant capacity, what should they do now to prepare for this? They should begin to produce product beforehand in order to make sure they have sufficent inventory in stock for the following year. Discussion Question: Summarize your advise to management of MLHR based on inferences gained from this common size analysis. Herman Miller Income Statement (000,000's omitted) Inputs are highlighted 1637.3 Sales COGS Gross Margin Selling, Gen & Adm Exp. Restr & Impair Exp Design & Research Exp Depr & Amort Exp.* Total Operating Exp. EBIT Less Expenses (Income) Interest Expense Interest & Other Invest (Income) Other, Net Net Other Expenses (Income) EBT Income Taxes -Noncontrol Int Net Income FYE % FYE % FYE % 5/31/2014 Chng 5/30/2015 Chng 5/28/2016 Chng 1882.0 14% 2142.2 6% 2264.9 5% 1251.0 8% 1350.8 3% 1390.7 8% 631.0 25% 791.4' 10% 874.2 0% 511.3 -3% 494.1 8% 532.6 3% 26.5 -52% 12.7 -100% 0.0 #DIV/0! 65.9 8% 71.4 8% 77.1 -5% 53.0 -6% 49.8 6% 52.9 15% 656.77 4% 628.0 6% 662.6 4% (25.7) -736% 163.4 29% 211.6 -13% Common Size RMA FYE % FYE % of Ind 6/2/2018 Chng 6/1/2019 Sales Comp 2381.2 8% 25672 100.0% 100.0% 1508.2 9% 63.8% 71.7% 873.0 7% 929.9 36.2% 28.3% 548.4 3% 567.2 22.1% 5.7 n/a 10.2 -0.4% 73.1 5% 76.9 60.9 8% 65.9 -2.6% 688.1 5% 720.2 28.1% 23.8% 184.9 13% 209.7 8.2% 4.5% 0.5% -0.1% 17.7 17.6 -1% (0.4) 50% 0.5 3% (43.4) 435% (21.2) 323% 0.1 (22.1) -541% 17.5 -12% (0.6) n/a 1.3 18.2' -18% 145.2 35% 47.2 26%' 0.5 97.5 40% 15.4 -12% (0.8) n/a 0.3 14.9 -28% 196.7 -11% 59.5 -29% 0.4 136.8 -2% 10.8 174.1 13.5 -10% (4.4) n/a 1.7 -22% 16% 42.4 -7% (3.0) 134.7 24% 12.1 (2.1 (1.6) 8.4 201.3 39.6 (5.0) 166.7 0.3% 7.8% 1.5% 1 6.5% Herman Miller Balance Sheet (000,000's omitted) Common Size RMA % FYE % FYE % of Industry Chng 6/2/2018 Chng 6/1/2019 Tot Assets Comp 112% 203.9 -22% 159.2 10.1% 4.5% 0% 8.6 2% 8.8 0.6% 2.0% 18% 219.3 15% 252.3 16.1% 40.3% 7% 162.4 13% 184.2 11.7% 29.2% -44% -7% 9.2 0.6% 2.0% 41.3 15% 47.6 3.0% 31% 645.4 2% 661.3 42.1% 78.0% 5% 331.4 5% 348.6 22.2% 5% 331.4 5% 348.6 22.2% 16.7% 423.5 0% 423.0 27.0% 0.0% -59% 79.2 72% 136.4 8.7% 7.4% 13% 1479.5 6% 1569.3 100.0% 100.0% ASSETS Cash & Cash Equivalents Marketable Securities Acct. Rec., less allowances Inventory Def. Income Tax & Prepaid Other Current Assets Total Current Assets Net Property, Plant, & Equip Fixed Assets (Net) Goodwill & Intangible Assets Other Assets Total Assets 9.9 15% 2% 4% 11% 0.0 171.4 242.4 0.0 0.0 413.8 0.0 177.7 268.4 0.0 0.0 446.1 0.0% 11.3% 17.1% 0.0% 0.0% 28.4% 12.5% 18.7% 0.6% 1.3% 8.5% 41.6% 7% 8% 3% 6% LIABILITIES Notes Payable - Bank Accounts Payable Accruals & Unearned Rev Current Maturities - LTD Other Current Liabilities Total Current Liabilities Long Term Debt (LTD) Pension & Other Liabilities Total Liabilities Redeemable NonControlling Interests Preferred Stock Common Stock Additional Paid-In Capital Retained Earnings Accum. Other Comprehen. Key Exec & Noncontrol Int Total Equity Total Liabilities & Equity 6.7% 7.2% 55.5% 6% 38% -12% 13% 2% 0% -2% -16% 15% -25% -38% 13% 275.0 95.4 784.2 30.5 0.0 11.7 116.6 598.3 (61.3) (0.5) 664.8 2% 0% 0% -23% 19% 54% 60% 8% 281.9 101.5 829.5 20.6 0.0 11.7 89.8 712.7 (94.2) (0.8) 719.2 18.0% 6.5% 52.9% 1.3% 0.0% 0.7% 5.7% 45.4% -6.0% -0.1% 45.8% 44.3% 13% 1479.5 6% 1569.3 100.0% 100.0% RATIO ANALYSIS Herman Miller RMA 6/3/2017 6/2/2018 6/1/2019 Industry Comparison 1.3 0.9 1.6 1.2 1.5 1.1 2.1 0.9 9.1 9.3 8.9 6.2 1.7 1.6 1.6 2.3 30 34 35.9 51 40 39 41.1 59 39 41 39.6 37 5/30/2015 LIQUIDITY Current 1.3 Quick 0.9 ASSET MANAGEMENT Inventory Turnover 10.4 (COGS / Inventory) Total Asset Turnover 1.8 DSO (AR Period) 32 (365/AR Turnover) Inventory Period 35 (365/Inventory Turnover) Days in AP (AP Period) 45 (365/[COGS/AP]) Cash Cycle 23 LEVERAGE TIE (EBIT / Interest) 9.3 Debt / Equity (RMA Debt/Worth) 1.8 Cash Coverage Ratio 12.2 PROFITABILITY % Profit BT / Tot Assets 12.17% Gross Profit 36.94% PM (NI/Sales) RMA uses NIBTIS 4.55% DuPont Analysis: ROE EROA=PM'TAT ROE (NI / Total Equity) 22.61% ROA (NI / Total Assets) 8.17% PM (NI / Sales) 4.55% Total Asset Turnover 1.80 Equity Multiplier (A/E) 2.77 31 31 37.3 73 13.7 1.2 17.2 13.7 1.2 18.2 17.3 1.2 22.8 5.6 1.5 15.06% 38.60% 6.04% 11.77% 36.66% 5.66% 12.83% 36.22% 6.49% 13.40% 28.30% 4.50% 22.84% 10.47% 6.04% 1.73 2.18 19.84% 9.10% 5.66% 1.61 2.18 22.87% 10.62% 6.49% 1.64 2.15 25.88% 10.35% 4.50% 2.30 2.50 Inputs are highlighted Herman Miller Proformas (000,000's are omitted all numbers are in millions) FYE 6/1/2019 2,567.2 (1,637.3) 929.9 (567.2) (87.1) (65.9) 209.7 CS% of Sales 100% -64% 36% -22% -3% -3% 8% Proforma 3,209.0 (2,046.6) 1,162.4 (567.2) (87.1) (65.9) 442.2 Sales Cost of Goods Sold(COGS) Gross Profit Operating Expense Research & Dev. & Non Reci Depreciation Expense EBIT Less (Expenses) Income Interest (Expense) Interest Income Other (Expenses) Income Net Other Expenses (Income) NIBT Income taxes Equity Earning & Noncontrol Net Income Step 1: Input Parameter Estimates Parameters & Ratios Days Sales Outstanding 35.9 Days in Inventory 41.1 Days in Accounts Payable 39.6 0% 0% 0% 0% 8% -2% Growth Rate on Sales (12.1) 2.1 1.6 (8.4) 201.3 (39.6) (5.0) 156.7 25% (19.5) 1.8 1.6 (16.0) 426.1 (179.0) (5.0) 242.2 6% Interest & Tax Rate Parameters Marketable Sec 0.50% Long Term Invest 7.00% NP-Bank 4.00% Long Term Debt 6.90% Tax Rate 42.00% Dividends Retained Earnings 0% 6% 156.7 242.2 Herman Miller Balance Sheet (000's) ASSETS Cash & Mkt Securties (plug) Accounts Receivable Inventory Prepaids Other Current Assets Total Current Assets FYE CS% of 6/1/2019 ot Assets 168.0 11% 252.3 16% 184.2 12% 9.2 1% 47.6 3% 661.3 42% Proforma 363.1 315.4 230.3 9.2 47.6 965.5 Step 2: Balance Sheet Check Make sure your proforma balance sheet is balanced. Use the plugs to force A = L + E. Use the following Balance Sheet Check Total Assets 1,873.5 Total Liabilities & Equity 1,699.2 Should be 0: A - (L+E) - 174.3 If not adjust plug until it is. Property, Plant, & Equip Notes Receivables Other Assets Total Assets 348.6 423.0 136.4 1,569.3 22% 27% 9% 348.6 423.0 136.4 1,873.5 100% 177.7 268.4 0% 11% 17% 0% 0% 222.1 268.4 28% LIABILITIES Notes Payable - Bank (Plug) Accounts Payable Accruals Current Maturities - LTD Other Current Liabilities Total Current Liabilities Other Liabilities Long Term Debt (LTD) Total Liabilities Reedemable Noncontrolling Common Stock Additional Paid-In Cap & Other Retained Earnings Total Liabilities & Equity 18% 53% 446.1 101.5 281.9 829.5 20.6 11.7 89.0 618.5 1,569.3 490.5 101.5 281.9 873.9 20.6 11.7 89.0 704.0 1,699.2 1% 39% 100%