Herman Miller (MLHR) Common Size Analysis Project General Guidelines: Each individual should gather data from Edgar.com to create the common size analysis. You can discuss your answers with the group prior to turning in the assignment. Save this sheet as: Lname, Fname Section xxx MLHR Assignment.xlsx and upload the file to CANVAS. A. Follow the Instructions on sheet EdgarInstructions to obtain the annual financial report (10-K) for Miller Herman using instructions provided on the sheet "EdgarInstructions": 0 1 Fill in your answers in the yellow Boxes below. The light red arrows indicate the answers you need to provide. For drop-down boxes, choose the appropriate answer. 3 4 5 6 7 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 Operations 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 0 1 2 MLHR 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)) 3. Based on this DuPont analysis which the following areas are strengths for MLHR? (could be more than one) 4. Based on this DuPont analysis which of the following areas are weaknesses for MLHR? (could be more than one) Operating Cycle in Days = Cash Cycle in Days = 8 5. How long is the operating cycle for MLHR for the most recent year ending financial information? (See Ratio Page) 6. How long is the cash cycle for MLHR for the most recent year ending financial information? (See Ratio Page) 9 7. Is the length of the operating cycle a strength or weakness for MLHR compared to the industry? Why? - Answer Succinctly 1 8. If MLHR's cash cycle increases significantly it would need to: 0 8. If MLHR's cash cycle increases significantly it would need to: 9. Look at the trends over the 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 b. Inventory Turnover c. Total Asset Turnover d. Days Sales Outstanding e. Asset Management f. Leverage g. Profitability 10. What account causes the current ratio to be smaller than the industry and the quick ratio to be similar to the industry? 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) 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)? Most Current Year = Previous Year = 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? a. Cash & Marketable Securities = b. Notes Payable Bank = 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) 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. a. Cash & Marketable Securities b. Notes Payable Bank = 13. What was MLAR Net Working Capital for the last two years (omit 0 s)! Most Current Year Previous Year = 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? a. Cash & Marketable Securities b. Notes Payable Bank = 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 141 on the Proforma Spreadsheet) a. Cash & Marketable Securities b. Notes Payable Bank = 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 141 on the Proforma Spreadsheet) 17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)? Proforma Net Income = 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? Discussion Question: Summarize your advise to management of MLHR based on inferences gained from this common size analysis. Herman Miller (MLHR) Common Size Analysis Project General Guidelines: Each individual should gather data from Edgar.com to create the common size analysis. You can discuss your answers with the group prior to turning in the assignment. Save this sheet as: Lname, Fname Section xxx MLHR Assignment.xlsx and upload the file to CANVAS. A. Follow the Instructions on sheet EdgarInstructions to obtain the annual financial report (10-K) for Miller Herman using instructions provided on the sheet "EdgarInstructions": 0 1 Fill in your answers in the yellow Boxes below. The light red arrows indicate the answers you need to provide. For drop-down boxes, choose the appropriate answer. 3 4 5 6 7 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 Operations 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 0 1 2 MLHR 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)) 3. Based on this DuPont analysis which the following areas are strengths for MLHR? (could be more than one) 4. Based on this DuPont analysis which of the following areas are weaknesses for MLHR? (could be more than one) Operating Cycle in Days = Cash Cycle in Days = 8 5. How long is the operating cycle for MLHR for the most recent year ending financial information? (See Ratio Page) 6. How long is the cash cycle for MLHR for the most recent year ending financial information? (See Ratio Page) 9 7. Is the length of the operating cycle a strength or weakness for MLHR compared to the industry? Why? - Answer Succinctly 1 8. If MLHR's cash cycle increases significantly it would need to: 0 8. If MLHR's cash cycle increases significantly it would need to: 9. Look at the trends over the 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 b. Inventory Turnover c. Total Asset Turnover d. Days Sales Outstanding e. Asset Management f. Leverage g. Profitability 10. What account causes the current ratio to be smaller than the industry and the quick ratio to be similar to the industry? 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) 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)? Most Current Year = Previous Year = 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? a. Cash & Marketable Securities = b. Notes Payable Bank = 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) 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. a. Cash & Marketable Securities b. Notes Payable Bank = 13. What was MLAR Net Working Capital for the last two years (omit 0 s)! Most Current Year Previous Year = 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years? a. Cash & Marketable Securities b. Notes Payable Bank = 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 141 on the Proforma Spreadsheet) a. Cash & Marketable Securities b. Notes Payable Bank = 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 141 on the Proforma Spreadsheet) 17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)? Proforma Net Income = 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? Discussion Question: Summarize your advise to management of MLHR based on inferences gained from this common size analysis