I followed the beginning directions on the "graded assignment" page, but I am still very confused on how to solve this problem set.

FIN 3020 MANDATORY EXCEL PROJECTS Mandatory Excel Project: Herman Miller (MLHR) Common Size Analysis General Guidelines: This project should not be completed in groups. Each individual should gather data from Edgar.com to create the common size analysis (i.e You can and should discuss your answers with any group members prior to turning in the assignment. Required Documents: You must complete this project and put in the dropbox on Blackboard by the Due Date. You may type your answers in a word docum A. Obtain the "2011-07-26" 10-K (i.e. annual financial report) for Miller Herman using EDGAR.gov at: http://www.sec.gov/edgar/searchedgar/companysearch.html The website may be quicker to navigate if you enter "MLHR" for Herman Miller's ticker symbol in the "Fast Search" search bar on the right. Locate the 5/28/2011 10-K annual report (reported on 07/26/11) and use the index on left to go to financial statements. Short cut = http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0000066382&type=&dateb=&owner=exclude&start=80&count=40 B. Use MLHR's 10-K annual report from Edgar.com to find Financial Information for Common Size analysis Point Distribution: Completing the "Graded Assignment" tab is worth 25 points. However, to complete this assignment you must first complete the "Income Sta 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 Edgar.gov in the highlighted area of the spreadsheet. C. Answer the following questions regarding Herman Miller. Highlight your answers for the multiple choice questions. 1. Basics: What is the name of the company, it's ticker, and it's top five officers? What is the market cap for the company (i.e. how big is the company?)? 2. Revenue Basics: What does the company to do create revenue? What is the main driver of their revenue? What was the company's average revenue for the pa 3. Competition Basics: What sector/industry is the company in and who is (are) it's biggest competitor(s)? Does the company have a competitive advantage in it's i 4. Investment Basics: What is the company's current stock price and range over the last five years? In your opinion, is this company more of a growth or value com 5. DuPont Analysis is a great place to start the analysis, because it shows how three major areas interact to determine ROE. Point Distribution: Each Answer is worth 1 point for a total of 25 points. You also receive 25 points for completing all five additional tabs. For example, the Bu Hint: Click the Ratios tab below to fill in the appropriate ratios to compare MLHR to the industry. MLHR Industry ROE PM TAT EM 25.9% 4.50% 2.30 2.50 6. What component(s) is(are) impacting the MLHR's difference in ROE from the industry average the most? a. Profit Margin (PM) b. Asset Efficiency (TAT) c. Financial Leverage (EM) d. a and b above e. a and c above f. b and c above g. all of the above 7. Based on this DuPont analysis which of the following areas are strengths for MLHR? a. Profit Margin (PM) b. Asset Efficiency (TAT) c. Financial Leverage (EM) d. none 8. Based on this DuPont analysis which of the following areas are weaknesses for MLHR? a. Profit Margin (PM) b. Asset Efficiency (TAT) c. Financial Leverage (EM) d. a and b above e. a and c above f. b and c above g. all of the above 9. How long is the operating cycle for MLHR for the most recent year ending financial information? Operating Cycle in Days = 10. How long is the cash cycle for MLHR for the most recent year ending financial information? Cash Cycle in Days = 11. Is the length of the operating cycle a strength or weakness for MLHR compared to the industry? Why? 12. 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 13. 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 14. Why is the current ratio small compared to the industry and the quick ratio large compared to the industry average? a. Accounts Receivable b. Inventory c. Accounts Payable d. all of the above 15. 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 16. When comparing the assets on the balance sheet to industry averages, what account should the analyst question? 17. What was MLHR's Net Working Capital for the last two years (omit 000's)? Last Year = Previous Year = 18. Would the change in NWC over the last year be viewed as a strength or a concern? Why? 19. How big a factor would you say market conditions played on the performance of MLHR over the past 5 years? a. Very important factor b. Somewhat import factor c. Not relevant at all 20. Go to Yahoo!Finance, Bloomberg, or another on-line site and type in MLHR to find the following ratios under Key Statistics (often on the left-hand side). Trailing P/E is Forward P/E is P/E/G is 21. Would you say that these ratios for MLHR are: High Low About average 22. In a separate tab named "bar graph" include a bar graph tracking both sales and net income for the past five years: Any quick observations? 23. What are your observations from the bar graph in Question #22? 24. Using the income statement and balance sheet tabs, summarize your observations to management of MLHR based on inferences gained from this common siz 25. Feedback: What was the best part of this assignment? What was confusing? What would you like to see changed or added? he common size analysis (i.e. complete all five tabs). answers in a word document, but I need your Excel file too. rst complete the "Income Statement" and "Balance Sheet" tabs, which are worth 25 points together. Note: The "Ratio" tab will fill-in automatically after entering the financial stateme s the company?)? s average revenue for the past five years? Is revenue increasing or decreasing? ompetitive advantage in it's industry/sector? ore of a growth or value company? Why? What do you like and dislike about this company? Would you buy stock in this company? Why or why not? l tabs. For example, the Business Overview tab has 10 questions, but is only worth 5 points. The other four tabs are data entry tabs and are worth 5 points each. In total, Excel Pr n the left-hand side). gained from this common size analysis (especially with regards to industry comparisons). Feel free to include any bar graphs (in a separate tab) similar to Question #22 that tell a g er entering the financial statement data into the other two tabs. The "Proforma" tab can be ignored for this class, but I wanted to include it for those of you that are curious about pro 5 points each. In total, Excel Project Part I is worth 50 points. milar to Question #22 that tell a good story. of you that are curious about proforma statement analysis. Income Statement Herman Miller Income Statement (000,000's omitted) Sales COGS Gross Margin Selling, Gen & Adm Exp. Research & Design Non Recurring - Restruct. Depreciation Exp.* Total Operating Exp. EBIT Other Income (Expenses) Interest Expense Interest (Income) Other, Net Net Other Exp. (Inc) EBT Taxes & Cum Eff of Act Ch Net Income EPS - Diluted Inputs are highlighted FYE 6/2/2007 1918.9 1273.0 645.9 354.6 52.0 % FYE % FYE % FYE Chng 5/31/2008 Chng 5/30/2009 Chng 5/29/2010 5% 2012.1 -19% 1630.0 -19% 1318.8 3% 1313.4 -16% 1102.3 -19% 890.3 8% 698.7 -24% 527.7 -19% 428.5 -1% 352.6 -5% 334.8 -18% 275.1 -2% 51.2 -100% 0.0 ### 40.5 n/a 5.1 n/a 28.4 n/a 16.7 41.2 5% 43.2 -3% 41.7 2% 42.6 447.8 1% 452.1 -10% 404.9 -7% 374.9 198.1 24% 246.6 -50% 122.8 -56% 53.6 13.7 0.0 (2.6) 11.1 187.0 57.9 129.1 $1.98 37% ### 45% 23% 35% 18% 18.8 (0.1) (2.6) 16.1 230.5 78.2 152.3 $2.56 36% 0% 48% -57% -60% -55% 25.6 -15% (0.1) n/a (1.7) 23.8 -21% 99.0 -65% 31.0 -79% 68.0 -58% $1.25 Common Size RMA % FYE % of Ind Chng 5/28/2011 Sales Comp -100% #DIV/0! 100.0% -100% #DIV/0! 71.7% -100% 0.0 #DIV/0! 28.3% -100% #DIV/0! -100% #DIV/0! n/a -100% #DIV/0! -100% 0.0 #DIV/0! 23.8% -100% 0.0 #DIV/0! 4.5% 21.7 -100% (4.6) n/a 1.7 18.8 -100% 34.8 -100% 6.5 -100% 28.3 -100% #DIV/0! #DIV/0! 0.0 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0.43 * Depretiation Expense is 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 Epense by clicking on Operating Segments under the Notes section on Edgar.com. You will then need to reduce the amount of total Selling, General & Administration Expenses in Cell J9 to reflect the amount of Depretiation Expense broken out. (See the formula in Cell H9 for example.) Page 17 Income Statement SIC 2522 Compustat 2749.5 1857.9 891.6 100.0% 67.6% 32.4% 56.7 2.1% Page 18 Balance Sheet Herman Miller Balance Sheet (000,000's omitted) FYE ASSETS 6/2/2007 Cash & Cash Equivalents 76.4 Acct. Rec., less allowances 198.5 Inventory 56.0 Marketable Securities 15.9 Other Current Assets 37.9 Total Current Assets 384.7 Inputs are highlighted % FYE Chng 5/31/2008 103% 155.4 14% 226.1 -2% 55.1 -1% 15.7 40.9 28% 493.2 Long Term Investments Net Property, Plant, & Equip Fixed Assets (Net) Goodwill Other Assets Total Assets 2.0 196.6 198.6 39.1 43.8 666.2 -100% 0% -1% 27% -2% 0.0 179.2 179.2 69.5 67.7 767.3 -38% 0% 0.0 n/a 175.2 -100% 175.2 -100% 157.6 -100% 41.9 -100% 770.6 -100% LIABILITIES Notes Payable - Bank Accounts Payable Accruals Current Maturities - LTD Other Current Liabilities Total Current Liabilities 0.0 274.1 0.0 3.0 7.4 284.5 #DIV/0! 4% #DIV/0! -100% 9% 0.0 285.4 0.0 0.0 25.1 310.5 ### -32% ### ### -40% -9% 0.0 193.1 0.0 75.0 15.1 283.2 ### -50% ### 35% -72% 11% 0.0 #DIV/0! 96.3 -100% 112.4 -100% 101.2 -100% 4.3 -100% 314.2 -100% Long Term Debt (LTD) Other Liabilities Total Liabilities 173.2 53.2 510.9 117% 39% 49% 375.5 73.8 759.8 -19% 135% 0% 302.4 173.7 759.3 -34% 1% -9% 200.0 176.3 690.5 Common Stock Retained Earnings Accum. Other Comprehen. 12.6 197.8 (55.1) -12% -61% 17% 11.1 76.7 (64.4) -3% 68% 114% 10.8 129.2 (137.9) 6% 18% 1% Capital Surplus Total Equity 0.0 155.3 #DIV/0! -85% 0.0 23.4 ### -66% 5.9 8.0 847% 901% 55.9 80.1 -100% -100% - 666.2 18% 783.2 -2% 767.3 0% 770.6 -100% 0.0 Total Liabilities & Equity 22% 18% 0.0 196.3 196.3 40.2 53.5 783.2 % FYE Chng 5/30/2009 24% 192.9 -28% 163.7 -32% 37.3 -28% 11.3 45.7 -9% 450.9 ### -9% -9% Common Size RMA % FYE % FYE % of Industry Chng 5/29/2010 Chng 5/28/2011 Tot Assets Comp -30% 134.8 -100% #DIV/0! 4.5% -12% 144.7 -100% #DIV/0! 40.3% 55% 57.9 -100% #DIV/0! 29.2% 7% 12.1 -100% #DIV/0! 2.0% 46.4 -100% #DIV/0! -12% 395.9 -100% 0.0 #DIV/0! 76.0% ### -2% -2% -100% -100% -100% 139.5 8.2% 702.1 41.3% 1701 100.0% 191.3 11.2% 461.9 27.2% 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 7.4% 100.0% 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 12.5% 18.7% 0.6% 1.3% 8.5% 41.6% 6.7% 7.2% 55.5% 288.9 17.0% 0.0 #DIV/0! #DIV/0! #DIV/0! 992.4 58.3% #DIV/0! #DIV/0! 44.3% 708.6 41.7% #DIV/0! 100.0% 1701 100.0% 0.0 11.4 -100% 152.4 -100% (139.6) -100% Page 19 191.2 16.7% #DIV/0! #DIV/0! #DIV/0! Herman Miller RATIO ANALYSIS RMA Industry 6/2/2007 5/31/2008 5/30/2009 5/29/2010 5/28/2011 Comparison 1.4 1.2 1.6 1.4 1.6 1.5 1.3 1.1 #DIV/0! #DIV/0! 2.1 0.9 22.7 23.8 29.6 15.4 #DIV/0! 6.2 2.9 2.6 2.1 1.7 #DIV/0! 2.3 38 41 37 40 #DIV/0! 51 16 15 12 24 #DIV/0! 59 79 79 64 39 #DIV/0! 37 -25 -23 -15 24 #DIV/0! 73 14.5 3.3 17.5 13.1 32.5 15.4 4.8 94.9 6.4 2.5 8.6 4.4 #DIV/0! #DIV/0! #DIV/0! 5.6 1.5 29.4% 34.7% 7.6% 12.9% 32.4% 4.2% 4.5% 32.5% 2.1% #DIV/0! #DIV/0! #DIV/0! 13.4% 28.3% 4.5% DuPont Analysis: ROE = ROA*EM, ROA=PM*TAT ROE (NI / Total Equity) 83.1% 650.9% ROA (NI / Total Assets) 19.4% 19.4% PM (NI / Sales) 6.7% 7.6% Total Asset Turnover 2.9 2.6 Equity Multiplier (A / E) 4.3 33.5 850.0% 8.9% 4.2% 2.1 95.9 35.3% 3.7% 2.1% 1.7 9.6 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 25.9% 10.4% 4.5% 2.3 2.5 $1.25 $0.43 $0.00 LIQUIDITY Current Quick ASSET MANAGEMENT Inventory Turnover (COGS / Inventory) Total Asset Turnover Days Sales Outstanding (365/AR Turnover) Days Sales in Inventory (365/Inventory Turnover) Days in AP (365/[COGS/AP]) Cash Cycle LEVERAGE TIE (EBIT / Interest) Debt / Equity (RMA Debt/Worth) Cash Coverage Ratio PROFITABILITY % Profit BT / Tot Assets 28.1% Gross Profit 33.7% PM (NI/Sales) RMA uses NIBT/Sales 6.7% MARKET VALUE EPS - diluted * $1.98 $2.56 Management's estimate of the weighted average of the minimum equity and debt returns required by the providers of capital. Reevaluated every year and adjusted when necessary to reflect the current rate environment and capital structure. Proforma Herman Miller Proformas (000,000's are omitted all numbers are in millions) Sales FYE 5/28/2011 - Inputs are highlighted CS % of Sales #DIV/0! Proforma - Cost of Goods Sold(COGS) Gross Profit Operating Expense Research & Dev. Expense - #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Depreciation Expense EBIT Other Income (Expenses) Loss (Gain) Other Interest Expense - #DIV/0! #DIV/0! #DIV/0! - #DIV/0! #DIV/0! #DIV/0! Interest Income NIBT Income taxes Net Income - #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1.5 #DIV/0! #DIV/0! #DIV/0! Dividends Retained Earnings - #DIV/0! #DIV/0! #DIV/0! Herman Miller Balance Sheet (000's) ASSETS Cash & Mkt Securties (plug) Accounts Receivable Inventory Prepaids Other Current Assets Total Current Assets Land Buildings Equipment - Step 1: Input Parameter Estimates Parameters & Ratios Days Sales Outstanding Days in AP Days in Inventory Growth Rate on Sales Interest Rate Parameters Marketable Sec. Long Term Invest. NP - Bank Long Term Debt Tax Rate 0% 1.00% 7.00% 4.00% 6.38% #DIV/0! FYE CS % of 5/28/2011 Tot Assets #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Proforma 150.0 #DIV/0! #DIV/0! - Accum. Depreciation Notes Receivables Other Assets Total Assets - #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 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 Common Stock Other Retained Earnings Total Liabilities & Equity - #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 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 #DIV/0! Total Liabilities & Equity #DIV/0! Should be 0: A - (L + E) = #DIV/0! If not adjust plug until it is