excel project
yu00094
excel_project__1_mlhr_2015-2.xlsInstructions
| Herman Miller (MLHR) Common Size & Ratio Analysis Project | |||||||||||||
| General Guidelines: Each individual should gather data from Edgar.com to create the common size & ratio analysis. | |||||||||||||
| ***Hand in a printout of the ratio page (see tab below) and a printout of this instruction sheet | Name: | ||||||||||||
| with your completed answers in class on Tuesday, February 2*** | |||||||||||||
| Hand in a printout of the ratio page (see tab below) and a printout of this instruction sheet with your completed answers. | |||||||||||||
| (You are not required to type your answers but you may type and highlight your answers.) | |||||||||||||
| A. Obtain the most recent annual financial report (10-K) for Herman Miller using EDGAR.gov at: | |||||||||||||
| http://www.sec.gov/edgar/searchedgar/companysearch.html | |||||||||||||
| Under Company Ticker enter MLHR for Herman Miller | |||||||||||||
| Locate the most recent 10-K report and click the Interactive Data link on the web page then click Financial Statements. | |||||||||||||
| B. Use MLHR's 10-K annual report from Edgar.com to find Financial Information for Common Size analysis | |||||||||||||
| 1. Click on the Income Statement tab below and fill in the information from Consolidated Statements of Comprehensive Income in Edgar.gov | |||||||||||||
| to fill in the highlighted area of the spreadsheet. Note: use the previous year's information to guide you. | |||||||||||||
| Notice the footnote on the bottom of the Income Statement regarding Depreciation Expense. | |||||||||||||
| 2. Click on the Balance Sheet tab below and fill in the information from Consolidated Balance Sheet from Edgar.gov. | |||||||||||||
| C. Answer the following questions regarding Herman Miller. | |||||||||||||
| 1. DuPont Analysis is a great place to start the analysis, because it shows how three major areas interact to determine ROE. | |||||||||||||
| (Hint: Click the Ratios tab below to fill in the appropriate ratios to compare MLHR to the industry. | |||||||||||||
| ROE | PM | TAT | EM | ||||||||||
| MLHR | 0.0% | ||||||||||||
| Industry | 25.9% | 4.50% | 2.30 | 2.50 | |||||||||
| 2. What component(s) is(are) improving MLHR's ROE relative to the industry average? (highlight your answer(s)) | |||||||||||||
| a. Profit Margin (PM) | |||||||||||||
| b. Asset Efficiency (TAT) | |||||||||||||
| c. Financial Leverage (EM) | |||||||||||||
| d. None | |||||||||||||
| 3. Based on this DuPont analysis which of the following areas are strengths for MLHR? (could be more than one) | |||||||||||||
| a. Profit Margin (PM) | |||||||||||||
| b. Asset Efficiency (TAT) | |||||||||||||
| c. Financial Leverage (EM) | |||||||||||||
| d. None | |||||||||||||
| 4. Based on this DuPont analysis which of the following areas are weaknesses for MLHR? (could be more than one) | |||||||||||||
| a. Profit Margin (PM) | |||||||||||||
| b. Asset Efficiency (TAT) | |||||||||||||
| c. Financial Leverage (EM) | |||||||||||||
| d. None | |||||||||||||
| 5. How long is the operating cycle for MLHR for the most recent year ending financial information? (See Ratio Page) | |||||||||||||
| a. Operating Cycle in Days = | |||||||||||||
| 6. How long is the cash cycle for MLHR for the most recent year ending financial information? (See Ratio Page) | |||||||||||||
| a. Cash Cycle in Days = | |||||||||||||
| 7. Is the length of the operating cycle a strength or weakness for MLHR compared to the industry? | |||||||||||||
| Why? | |||||||||||||
| 8. If MLHR's cash cycle increases significantly it would need to: | |||||||||||||
| a. issue more equity in the form of common stock | |||||||||||||
| b. issue more corporate bonds | |||||||||||||
| c. increase notes payable (line of credit with bank) | |||||||||||||
| d. decrease the amount of credit it issues to customers | |||||||||||||
| e. increase payments to trade creditors | |||||||||||||
| 9. Look at the trends over the past 5 years for the following ratio categories. Identify whether the trend is improving, | |||||||||||||
| deteriorating, or neither. (For ratios that fluctuate over time compare 5 years ago with the most recent year.) | |||||||||||||
| a. Liquidity | Impr | Det | Neither | ||||||||||
| b. Inventory Turnover | Impr | Det | Neither | ||||||||||
| c. Total Asset Turnover | Impr | Det | Neither | ||||||||||
| d. Days Sales Outstanding | Impr | Det | Neither | ||||||||||
| e. Asset Management | Impr | Det | Neither | ||||||||||
| f. Leverage | Impr | Det | Neither | ||||||||||
| g. Profitability | Impr | Det | Neither | ||||||||||
| 10. What account causes the current ratio to be smaller than the industry and the quick ratio to be similar to the industry? | |||||||||||||
| a. Accounts Receivable | |||||||||||||
| b. Inventory | |||||||||||||
| c. Accounts Payable | |||||||||||||
| d. all of the above | |||||||||||||
| 11. Does the amount of time it takes Herman Miller to pay it’s suppliers appear to be a problem? | |||||||||||||
| (Hint: compare their ratios to the industry) | |||||||||||||
| a. Yes | |||||||||||||
| b. No | |||||||||||||
| 12. When comparing the assets on the balance sheet to industry averages, what account should the analyst question? | |||||||||||||
| 13. What was MLHR's Net Working Capital for the last two years (omit 000's)? | |||||||||||||
| a. Last Year (FYE 2015) = | $ - 0 | ||||||||||||
| b. Previous Year (FYE 2014) = | $ - 0 | ||||||||||||
| 14. Assume that MLHR is projecting the same sales next year (0% sales growth). Use the pro-forma sheet to determine any impact on | |||||||||||||
| the financial statements. Cash and Notes Payable are the plug variables. What are the new values for these plug | |||||||||||||
| variables in order to balance the balance sheet? (State answer omitting the last 000,000's consistent with proforma.) | |||||||||||||
| a. Cash & Marketable Securities = | $ - 0 | ||||||||||||
| b. Notes Payable Bank = | $ - 0 | ||||||||||||
| 15. Suppose MLHR doubled the amount of time to pay trade creditors. Use the proforma sheet to indicate the impact on the financial | |||||||||||||
| statements. (Note: Reset the plug variables and double Days in Payables) | |||||||||||||
| What are the new values for these plug variables in order to balance the balance sheet? | |||||||||||||
| (State answer omitting the last 000,000's consistent with proforma.) | |||||||||||||
| a. Cash & Marketable Securities = | $ - 0 | ||||||||||||
| b. Notes Payable Bank = | $ - 0 | ||||||||||||
| 16. Suppose MLHR Days in Payables is at its original level from 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 values for these plug variables in order to balance the balance sheet? | |||||||||||||
| (State answer omitting the last 000,000's consistent with proforma.) | |||||||||||||
| a. Cash & Marketable Securities = | $ - 0 | ||||||||||||
| b. Notes Payable Bank = | $ - 0 | ||||||||||||
| 17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)? | |||||||||||||
| a. Proforma Net Income = | $ - 0 | ||||||||||||
| 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 the plugs and Net Income? (Note adjust plugs before assessing the impact on NI, omit 000,000's) | |||||||||||||
| a. Cash & Marketable Securities = | $ - 0 | ||||||||||||
| b. Notes Payable Bank = | $ - 0 | ||||||||||||
| c. Proforma Net Income = | $ - 0 | ||||||||||||
| 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? | |||||||||||||
| Discussion Question | |||||||||||||
| 20. Summarize your advise to management of MLHR based on inferences gained from this common size analysis. |
Income Statement
| Herman Miller | |||||||||||
| Income Statement | Inputs are highlighted | Common | |||||||||
| (000,000's omitted) | Size | RMA | |||||||||
| FYE | % | FYE | % | FYE | % | FYE | % | FYE | % of | Ind | |
| 5/28/11 | Chng | 6/2/12 | Chng | 6/1/13 | Chng | 5/31/14 | Chng | 5/30/15 | Sales | Comp | |
| Sales | 1649.2 | 5% | 1724.1 | 3% | 1774.9 | 6% | 1882.0 | -100% | 0.0% | 100.0% | |
| COGS | 1111.1 | 2% | 1133.5 | 3% | 1169.7 | 7% | 1251.0 | -100% | 0.0% | 71.7% | |
| Gross Margin | 538.1 | 10% | 590.6 | 2% | 605.2 | 4% | 631.0 | -100% | 0.0% | 28.3% | |
| Selling, Gen & Adm Exp. | 326.9 | 9% | 357.7 | 10% | 391.7 | 33% | 521.9 | -100% | 0.0% | ||
| Research & Design | 45.8 | 15% | 52.7 | 14% | 59.9 | 10% | 65.9 | -100% | 0.0% | ||
| Restr & Impair Exp | 3.0 | 80% | 5.4 | -78% | 1.2 | 2108% | 26.5 | -100% | 0.0% | ||
| Depreciation & Amortization Exp.* | 39.1 | -5% | 37.2 | 1% | 37.5 | 13% | 42.4 | -100% | 0.0% | ||
| Total Operating Exp. | 414.8 | 9% | 453.0 | 8% | 490.3 | 34% | 656.7 | -100% | 0.0% | 23.8% | |
| EBIT | 123.3 | 12% | 137.6 | -16% | 114.9 | -122% | (25.7) | -100% | 0.0% | 4.5% | |
| Less Expenses (Income) | |||||||||||
| Interest Expense | 19.9 | -12% | 17.5 | -2% | 17.2 | 2% | 17.6 | -100% | 0.0% | ||
| Interest (Income) | (1.5) | -33% | (1.0) | n/a | (0.4) | n/a | (0.4) | n/a | n/a | ||
| Other Expenses (Income) | 2.4 | 1.6 | 0.9 | 0.5 | |||||||
| Net Other Expenses (Income) | 20.8 | -13% | 18.1 | -2% | 17.7 | 0% | 17.7 | -100% | 0.0% | ||
| EBT | 102.5 | 17% | 119.5 | -19% | 97.2 | -145% | (43.4) | -100% | 0.0% | ||
| Taxes & Cum Eff of Act Chg | 31.7 | 40% | 44.3 | -35% | 29.0 | -173% | (21.3) | -100% | 0.0% | ||
| Net Income | 70.8 | 6% | 75.2 | -9% | 68.2 | -132% | (22.1) | -100% | 0.0% | ||
| EPS - Diluted | $0.43 | $1.29 | $1.16 | ($0.37) | |||||||
| * Depretiation & Amortization Expenses are included in the total Selling, General and Administration Expense. In order to calculate Cash Flow | |||||||||||
| you will need to look for the amount of Depreciation & Amortization Expense by clicking on the Notes to Financial Statements section on Edgar.com. | |||||||||||
| Under the notes section you will click on Supplemental Disclosure of Cash Flow Information to obtain the Depreciation & Amortization Expenses. | |||||||||||
| You will then need to reduce the amount of total Selling, General & Administration Expenses in Cell J9 above to reflect the amount of | |||||||||||
| Depretiation & Amortization Expenses broken out. (See the formula in Cell H9 for example.) |
&A
Page &P
Balance Sheet
| Herman Miller | Inputs are highlighted | Common | |||||||||
| Balance Sheet (000,000's omitted) | Size | RMA | |||||||||
| FYE | % | FYE | % | FYE | % | FYE | % | FYE | % of | Industry | |
| ASSETS | 5/28/11 | Chng | 6/2/12 | Chng | 6/1/13 | Chng | 5/31/14 | Chng | 5/30/15 | Tot Assets | Comp |
| Cash & Cash Equivalents | 142.2 | 21% | 172.2 | -52% | 82.7 | 23% | 101.5 | -100% | 10.2% | 4.5% | |
| Acct. Rec., less allowances | 193.1 | -17% | 159.7 | 12% | 178.4 | 15% | 204.3 | -100% | 20.6% | 40.3% | |
| Inventory | 66.2 | -10% | 59.3 | 28% | 76.2 | 3% | 78.4 | -100% | 7.9% | 29.2% | |
| Marketable Securities | 11.0 | -13% | 9.6 | 13% | 10.8 | 3% | 11.1 | -100% | 1.1% | 2.0% | |
| Other Current Assets | 59.2 | 54.5 | 51.2 | 10% | 56.5 | -100% | 5.7% | ||||
| Total Current Assets | 471.7 | -3% | 455.3 | -12% | 399.3 | 13% | 451.8 | -100% | 45.6% | 76.0% | |
| Net Property, Plant, & Equip | 169.1 | -8% | 156.0 | 18% | 184.1 | 6% | 195.2 | -100% | 19.7% | ||
| Fixed Assets (Net) | 169.1 | -8% | 156.0 | 18% | 184.1 | 6% | 195.2 | -100% | 19.7% | 16.7% | |
| Goodwill & Intangible Assets | 157.9 | 216.8 | 337.3 | -7% | 313.3 | -100% | 31.6% | ||||
| Other Assets | 9.3 | 18% | 11.0 | 135% | 25.8 | 19% | 30.6 | -100% | 3.1% | 7.4% | |
| Total Assets | 808.0 | 4% | 839.1 | 13% | 946.5 | 5% | 990.9 | -100% | 100.0% | 100.0% | |
| LIABILITIES | |||||||||||
| Notes Payable - Bank | 0.0 | 0.0 | 0.0 | 0.0 | 0.0% | 12.5% | |||||
| Accounts Payable | 112.7 | 3% | 115.8 | 12% | 130.1 | 5% | 136.9 | -100% | 13.8% | 18.7% | |
| Accruals | 153.1 | -10% | 137.9 | 16% | 159.9 | 6% | 169.2 | -100% | 17.1% | 0.6% | |
| Current Maturities - LTD | 0.0 | 0.0 | 50.0 | 5.0% | 1.3% | ||||||
| Other Current Liabilities | 0.0 | 0.0 | 0.0 | 0.0% | 8.5% | ||||||
| Total Current Liabilities | 265.8 | -5% | 253.7 | 14% | 290.0 | 23% | 356.1 | -100% | 35.9% | 41.6% | |
| Long Term Debt (LTD) | 250.0 | 0% | 250.0 | 0% | 250.0 | -20% | 200.0 | -100% | 20.2% | 6.7% | |
| Pension and Post-Retirement Benefits | 18.2 | ||||||||||
| Other Liabilities | 87.2 | -0% | 87.1 | -0% | 87.0 | -49% | 44.5 | -100% | 4.5% | 7.2% | |
| Total Liabilities | 603.0 | -2% | 590.8 | 6% | 627.0 | -1% | 618.8 | -100% | 62.4% | 55.5% | |
| Redeemable Noncontrolling Interests | 0.0 | 0.0 | 0.0 | 0.0 | |||||||
| Equity | |||||||||||
| Common Stock | 11.6 | 1% | 11.7 | 0% | 11.7 | 2% | 11.9 | -100% | 1.2% | ||
| Retained Earnings | 218.2 | 32% | 288.2 | 15% | 331.1 | -16% | 277.4 | -100% | 28.0% | ||
| Accum. Other Comprehen. | (106.8) | 33% | (142.5) | -11% | (126.2) | -69% | (39.6) | -100% | -4.0% | ||
| Additional Paid-In Capital | 82.0 | 11% | 90.9 | 13% | 102.9 | 19% | 122.4 | -100% | 12.4% | ||
| Noncontrolling Interests | 0.0 | 0.0 | 0.0 | 0.0 | 0.0% | ||||||
| Total Equity | 205.0 | 21% | 248.3 | 29% | 319.5 | 16% | 372.1 | -100% | 37.6% | 44.3% | |
| Total Liabilities & Equity | 808.0 | 4% | 839.1 | 13% | 946.5 | 5% | 990.9 | -100% | 100.0% | 100.0% |
&A
Page &P
Ratios
| RATIO ANALYSIS | RMA | ||||||||||
| Industry | |||||||||||
| 5/28/11 | 6/2/12 | 6/1/13 | 5/31/14 | 5/30/15 | Comparison | ||||||
| LIQUIDITY | |||||||||||
| Current | 1.8 | 1.8 | 1.4 | 1.3 | 1.8 | 2.1 | |||||
| Quick | 1.5 | 1.6 | 1.1 | 1.0 | 1.1 | 0.9 | |||||
| ASSET MANAGEMENT | |||||||||||
| Inventory Turnover | 16.8 | 19.1 | 15.4 | 16.0 | 0.0 | 6.2 | |||||
| (COGS / Inventory) | |||||||||||
| Total Asset Turnover | 2.0 | 2.1 | 1.9 | 1.9 | 0.0 | 2.3 | |||||
| DSO (AR Period) | 43 | 34 | 37 | 39.6 | 0.0 | 51 | |||||
| (365/AR Turnover) | |||||||||||
| Inventory Period | 22 | 19 | 24 | 22.9 | 0.0 | 59 | |||||
| (365/Inventory Turnover) | |||||||||||
| Days in AP (AP Period) | 37 | 37 | 41 | 39.9 | 0.0 | 37 | |||||
| (365/[COGS/AP]) | |||||||||||
| Cash Cycle | 27 | 16 | 20 | 22.6 | 0.0 | 73 | |||||
| LEVERAGE | |||||||||||
| TIE (EBIT / Interest) | 6.2 | 7.9 | 6.7 | -1.5 | 0.0 | 5.6 | |||||
| Debt / Equity (RMA Debt/Worth) | 2.9 | 2.4 | 2.0 | 1.7 | 0.0 | 1.5 | |||||
| Cash Coverage Ratio | 8.2 | 10.0 | 8.9 | 0.9 | 0.0 | ||||||
| PROFITABILITY | |||||||||||
| EBT / Tot Assets | 12.69% | 14.24% | 10.27% | -4.38% | 0.00% | 13.40% | |||||
| Gross Profit | 32.63% | 34.26% | 34.10% | 33.53% | 0.00% | 28.30% | |||||
| PM (NI/Sales) RMA uses NIBT/Sales | 4.29% | 4.36% | 3.84% | -1.17% | 0.00% | 4.50% | |||||
| DuPont Analysis: ROE = ROA*EM, ROA=PM*TAT | |||||||||||
| ROE (NI / Total Equity) | 34.54% | 30.29% | 21.35% | -5.94% | 0.00% | 25.88% | |||||
| ROA (NI / Total Assets) | 8.76% | 8.96% | 7.21% | -2.23% | 0.00% | 10.35% | |||||
| PM (NI / Sales) | 4.29% | 4.36% | 3.84% | -1.17% | 0.00% | 4.50% | |||||
| Total Asset Turnover | 2.04 | 2.05 | 1.88 | 1.90 | 0.00 | 2.30 | |||||
| Equity Multiplier (A / E) | 3.94 | 3.38 | 2.96 | 2.66 | 0.00 | 2.50 | |||||
| ROE (DuPont Equation) | 34.54% | 30.29% | 21.35% | -5.94% | 0.00% | 25.88% | |||||
| MARKET VALUE | |||||||||||
| EPS - diluted | $0.43 | $1.29 | $1.16 | -$0.37 | $0.00 |
Proforma
| Herman Miller | Proformas | Inputs are highlighted | ||||
| (000,000's are omitted all numbers are in millions) | ||||||
| FYE | CS % of | |||||
| 5/30/15 | Sales | Proforma | ||||
| Sales | - 0 | 0% | - 0 | |||
| Cost of Goods Sold(COGS) | - 0 | 0% | - 0 | |||
| Gross Profit | - 0 | 0% | - 0 | |||
| Operating Expense | - 0 | 0% | - 0 | |||
| Research & Dev. & Non Recur Exp | - 0 | 0% | - 0 | Step 1: Input Parameter Estimates | ||
| Depreciation Expense | - 0 | 0% | - 0 | Parameters & Ratios | ||
| EBIT | - 0 | 0% | - 0 | Days Sales Outstanding | 0.0 | |
| Less (Expenses) Income | Days in Inventory | 0.0 | ||||
| Interest (Expense) | - 0 | 0% | - 0 | Days in Accounts Payable | 0.0 | |
| Interest Income | - 0 | 0% | - 0 | Planned Capital Expend. | - 0 | |
| Other (Expenses) Income | - 0 | 0% | - 0 | New Depreciation | - 0 | |
| Net Other Expenses (Income) | - 0 | 0% | - 0 | Growth Rate on Sales | 0% | |
| EBT | - 0 | 0% | - 0 | |||
| Income taxes | - 0 | 0% | - 0 | Interest & Tax Rate Parameters | ||
| Net Income | - 0 | 0% | - 0 | Marketable Sec. | 0.50% | |
| Long Term Invest. | 7.00% | |||||
| Dividends | 277.4 | 0% | - 0 | NP - Bank | 4.00% | |
| Addition to Retained Earnings | (277.4) | 0% | - 0 | Long Term Debt | 6.90% | |
| Tax Rate | 42.00% | |||||
| Herman Miller | ||||||
| Balance Sheet (000's) | FYE | CS % of | ||||
| ASSETS | 5/30/15 | Tot Assets | Proforma | |||
| Cash & Mkt Securties (plug) | - 0 | 0% | - 0 | Step 2: Balance Sheet Check | ||
| Accounts Receivable | - 0 | 0% | - 0 | Make sure your proforma balance sheet is | ||
| Inventory | - 0 | 0% | - 0 | balanced. Use the plugs to force A = L + E. | ||
| Prepaids | - 0 | 0% | - 0 | Use the following Balance Sheet Check | ||
| Other Current Assets | - 0 | 0% | - 0 | Total Assets | - 0 | |
| Total Current Assets | - 0 | 0% | - 0 | Total Liabilities & Equity | - 0 | |
| Should be 0: A - (L + E) = | - 0 | |||||
| Property, Plant, & Equip | - 0 | 0% | - 0 | If not adjust plug until it is. | ||
| Notes Receivables | - 0 | 0% | - 0 | |||
| Other Assets | - 0 | 0% | - 0 | |||
| Total Assets | - 0 | 0% | - 0 | |||
| LIABILITIES | ||||||
| Notes Payable - Bank (Plug) | - 0 | 0% | - 0 | |||
| Accounts Payable | - 0 | 0% | - 0 | |||
| Accruals | - 0 | 0% | - 0 | |||
| Current Maturities - LTD | - 0 | 0% | - 0 | |||
| Other Current Liabilities | - 0 | 0% | - 0 | |||
| Total Current Liabilities | - 0 | 0% | - 0 | |||
| Long Term Debt (LTD) | - 0 | 0% | - 0 | |||
| Pension and Post-Retirement Benefits | - 0 | - 0 | ||||
| Other Liabilities | - 0 | - 0 | ||||
| Total Liabilities | - 0 | 0% | - 0 | |||
| Redeemable Noncontrolling Interests | - 0 | 0% | - 0 | |||
| Common Stock | - 0 | 0% | - 0 | |||
| Retained Earnings | - 0 | 0% | - 0 | |||
| Other | - 0 | - 0 | ||||
| Total Liabilities & Equity | - 0 | 0% | - 0 | |||
| ………………………………………………………………………………………………………………………………………………………………………………………………… |
&A
