27 Jun Finance Accounting Excel Assignment
Posted at 01:49h
in College
Instructions
| Miller Herman (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. | ||||||
| Complete the ratio page (see tab below). | ||||||
| Respond to the questions listed on the Instruction Tab. | ||||||
| Rename the Excel file to include your last name and MLHR in the title. | ||||||
| Submit the Excel file to the Drop box | ||||||
| A. Obtain the most recent annual financial report (10-K) for Miller Herman using EDGAR.gov at: | ||||||
| http://www.sec.gov/edgar/searchedgar/companysearch.html | ||||||
| Under Company Ticker enter MLHR for Miller Herman | ||||||
| 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 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 Miller Herman. | ||||||
| 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 | ||||||
| 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)) | ||||||
| Profit Margin (PM) | ||||||
| Asset Efficiency (TAT) | ||||||
| Financial Leverage (EM) | ||||||
| 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) | ||||||
| Operating Cycle in Days = | ||||||
| 6. How long is the cash cycle for MLHR for the most recent year ending financial information? (See Ratio Page) | ||||||
| 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)? | ||||||
| Last 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. Very important factor | ||||||
| b. Somewhat import factor | ||||||
| c. Not relevant at all | ||||||
| 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 start with the identical parameters indicated on Ratio page for previous year 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 = | ||||||
| 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 D41 on the Proforma Spreadsheet) | ||||||
| a. Cash & Marketable Securities = | ||||||
| b. Notes Payable Bank = | ||||||
| 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? | ||||||
| Short-Answer Question | ||||||
| 20. Summarize your advise to management of MLHR based on inferences gained from this common size analysis. |
Income Statement
| Miller Herman | |||||||||||
| Income Statement | Inputs are highlighted | Common | |||||||||
| (000,000’s omitted) | Size | RMA | |||||||||
| FYE | % | FYE | % | FYE | % | FYE | % | FYE | % of | Ind | |
| 5/29/10 | Chng | 5/28/11 | Chng | 6/2/12 | Chng | 6/1/13 | Chng | 5/31/14 | Sales | Comp | |
| Sales | 1318.8 | 25% | 1649.2 | 5% | 1724.1 | 3% | 1774.9 | -100% | 0.0% | 100.0% | |
| COGS | 890.3 | 25% | 1111.1 | 2% | 1133.5 | 3% | 1169.7 | -100% | 0.0% | 71.7% | |
| Gross Margin | 428.5 | 26% | 538.1 | 10% | 590.6 | 2% | 605.2 | -100% | 0.0 | 0.0% | 28.3% |
| Selling, Gen & Adm Exp. | 275.1 | 20% | 329.8 | 9% | 360.5 | 10% | 394.8 | -100% | 0.0% | ||
| Research & Design | 40.5 | 13% | 45.8 | 15% | 52.7 | 14% | 59.9 | -100% | 0.0% | ||
| Restr & Impair Exp | 16.7 | -82% | 3.0 | 80% | 5.4 | -78% | 1.2 | -100% | |||
| Depreciation Exp.* | 42.6 | -15% | 36.2 | -5% | 34.4 | 0% | 34.4 | -100% | 0.0% | ||
| Total Operating Exp. | 374.9 | 11% | 414.8 | 9% | 453.0 | 8% | 490.3 | -100% | 0.0 | 0.0% | 23.8% |
| EBIT | 53.6 | 130% | 123.3 | 12% | 137.6 | -16% | 114.9 | -100% | 0.0 | 0.0% | 4.5% |
| Less Expenses (Income) | |||||||||||
| Interest Expense | 21.7 | -8% | 19.9 | -12% | 17.5 | -2% | 17.2 | -100% | 0.0% | ||
| Interest (Income) | (4.6) | -67% | (1.5) | -33% | (1.0) | n/a | (0.4) | n/a | 0.0% | ||
| Other Expenses (Income) | 1.7 | 2.4 | 1.6 | 0.9 | |||||||
| Net Other Expenses (Income) | 18.8 | 11% | 20.8 | -13% | 18.1 | -2% | 17.7 | -100% | 0.0 | 0.0% | |
| EBT | 34.8 | 195% | 102.5 | 17% | 119.5 | -19% | 97.2 | -100% | 0.0 | 0.0% | |
| Taxes & Cum Eff of Act Chg | 6.5 | 388% | 31.7 | 40% | 44.3 | -35% | 29.0 | -100% | 0.0% | ||
| Net Income | 28.3 | 150% | 70.8 | 6% | 75.2 | -9% | 68.2 | -100% | 0.0 | 0.0% | |
| EPS – Diluted | $1.25 | $0.43 | $1.06 | $1.29 | |||||||
| * 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 Epense 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 obtian 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
| Miller Herman | Inputs are highlighted | Common | |||||||||
| Balance Sheet (000,000’s omitted) | Size | RMA | |||||||||
| FYE | % | FYE | % | FYE | % | FYE | % | FYE | % of | Industry | |
| ASSETS | 5/31/08 | Chng | 5/28/11 | Chng | 6/2/12 | Chng | 6/1/13 | Chng | 5/31/14 | Tot Assets | Comp |
| Cash & Cash Equivalents | 155.4 | -8% | 142.2 | 21% | 172.2 | -52% | 82.7 | -100% | 0.0% | 4.5% | |
| Acct. Rec., less allowances | 226.1 | -15% | 193.1 | -17% | 159.7 | 12% | 178.4 | -100% | 0.0% | 40.3% | |
| Inventory | 55.1 | 20% | 66.2 | -10% | 59.3 | 28% | 76.2 | -100% | 0.0% | 29.2% | |
| Marketable Securities | 15.7 | -30% | 11.0 | -13% | 9.6 | 13% | 10.8 | -100% | 0.0% | 2.0% | |
| Other Current Assets | 40.9 | 59.2 | 54.5 | 51.2 | -100% | 0.0% | |||||
| Total Current Assets | 493.2 | -4% | 471.7 | -3% | 455.3 | -12% | 399.3 | -100% | 0.0 | 0.0% | 76.0% |
| Net Property, Plant, & Equip | 196.3 | -14% | 169.1 | -8% | 156.0 | 18% | 184.1 | -100% | 0.0% | ||
| Fixed Assets (Net) | 196.3 | -14% | 169.1 | -8% | 156.0 | 18% | 184.1 | -100% | 0.0 | 0.0% | 16.7% |
| Goodwill & Intangible Assets | 40.2 | 157.9 | 216.8 | 337.3 | -100% | 0.0% | |||||
| Other Assets | 53.5 | -83% | 9.3 | 18% | 11.0 | 135% | 25.8 | -100% | 0.0% | 7.4% | |
| Total Assets | 783.2 | 3% | 808.0 | 4% | 839.1 | 13% | 946.5 | -100% | 0.0 | 0.0% | 100.0% |
| LIABILITIES | |||||||||||
| Notes Payable – Bank | 0.0 | 0.0 | 0.0 | 0.0 | 0.0% | 12.5% | |||||
| Accounts Payable | 285.4 | -61% | 112.7 | 3% | 115.8 | 12% | 130.1 | -100% | 0.0% | 18.7% | |
| Accruals | 0.0 | 153.1 | -10% | 137.9 | 16% | 159.9 | -100% | 0.0% | 0.6% | ||
| Current Maturities – LTD | 0.0 | 0.0 | 0.0 | 0.0% | 1.3% | ||||||
| Other Current Liabilities | 25.1 | 0.0 | 0.0 | 0.0% | 8.5% | ||||||
| Total Current Liabilities | 310.5 | -14% | 265.8 | -5% | 253.7 | 14% | 290.0 | -100% | 0.0 | 0.0% | 41.6% |
| Long Term Debt (LTD) | 375.5 | -33% | 250.0 | 0% | 250.0 | 0% | 250.0 | -100% | 0.0% | 6.7% | |
| Other Liabilities | 73.8 | 18% | 87.2 | -0% | 87.1 | -0% | 87.0 | -100% | 0.0% | 7.2% | |
| Total Liabilities | 759.8 | -21% | 603.0 | -2% | 590.8 | 6% | 627.0 | -100% | 0.0 | 0.0% | 55.5% |
| Common Stock | 11.1 | 5% | 11.6 | 1% | 11.7 | 0% | 11.7 | -100% | 0.0% | ||
| Retained Earnings | 76.7 | 184% | 218.2 | 32% | 288.2 | 15% | 331.1 | -100% | 0.0% | ||
| Accum. Other Comprehen. | (64.4) | 66% | (106.8) | 33% | (142.5) | -11% | (126.2) | -100% | 0.0% | ||
| Additional Paid-In Capital | 0.0 | 82.0 | 11% | 90.9 | 13% | 102.9 | -100% | 0.0% | |||
| Total Equity | 23.4 | 776% | 205.0 | 21% | 248.3 | 29% | 319.5 | -100% | – 0 | 0.0% | 44.3% |
| Total Liabilities & Equity | 783.2 | 3% | 808.0 | 4% | 839.1 | 13% | 946.5 | -100% | 0.0 | 0.0% | 100.0% |
&A
Page &P
Ratios
| RATIO ANALYSIS | Miller Herman | RMA | |||||||||
| Industry | |||||||||||
| 5/31/08 | 5/28/11 | 6/2/12 | 6/1/13 | 5/31/14 | Comparison | ||||||
| LIQUIDITY | |||||||||||
| Current | 1.6 | 1.8 | 1.8 | 1.4 | 0.0 | 2.1 | |||||
| Quick | 1.4 | 1.5 | 1.6 | 1.1 | 0.0 | 0.9 | |||||
| ASSET MANAGEMENT | |||||||||||
| Inventory Turnover | 16.2 | 16.8 | 19.1 | 15.4 | 0.0 | 6.2 | |||||
| (COGS / Inventory) | |||||||||||
| Total Asset Turnover | 1.7 | 2.0 | 2.1 | 1.9 | 0.0 | 2.3 | |||||
| DSO (AR Period) | 63 | 43 | 34 | 37 | 0.0 | 51 | |||||
| (365/AR Turnover) | |||||||||||
| Inventory Period | 23 | 22 | 19 | 24 | 0.0 | 59 | |||||
| (365/Inventory Turnover) | |||||||||||
| Days in AP (AP Period) | 117 | 37 | 37 | 41 | 0.0 | 37 | |||||
| (365/[COGS/AP]) | |||||||||||
| Cash Cycle | -32 | 27 | 16 | 20 | 0.0 | 73 | |||||
| LEVERAGE | |||||||||||
| TIE (EBIT / Interest) | 2.5 | 6.2 | 7.9 | 6.7 | 0.0 | 5.6 | |||||
| Debt / Equity (RMA Debt/Worth) | 32.5 | 2.9 | 2.4 | 2.0 | 0.0 | 1.5 | |||||
| Cash Coverage Ratio | 4.4 | 8.0 | 9.8 | 8.7 | 0.0 | ||||||
| PROFITABILITY | |||||||||||
| % Profit BT / Tot Assets | 4.44% | 12.69% | 14.24% | 10.27% | 0.00% | 13.40% | |||||
| Gross Profit | 32.49% | 32.63% | 34.26% | 34.10% | 0.00% | 28.30% | |||||
| PM (NI/Sales) RMA uses NIBT/Sales | 2.15% | 4.29% | 4.36% | 3.84% | 0.00% | 4.50% | |||||
| DuPont Analysis: ROE = ROA*EM, ROA=PM*TAT | |||||||||||
| ROE (NI / Total Equity) | 120.94% | 34.54% | 30.29% | 21.35% | 0.00% | 25.88% | |||||
| ROA (NI / Total Assets) | 3.61% | 8.76% | 8.96% | 7.21% | 0.00% | 10.35% | |||||
| PM (NI / Sales) | 2.15% | 4.29% | 4.36% | 3.84% | 0.00% | 4.50% | |||||
| Total Asset Turnover | 1.68 | 2.04 | 2.05 | 1.88 | 0.00 | 2.30 | |||||
| Equity Multiplier (A / E) | 33.47 | 3.94 | 3.38 | 2.96 | 0.00 | 2.50 | |||||
| MARKET VALUE | |||||||||||
| EPS – diluted | $1.25 | $0.43 | $1.06 | $1.29 | $0.00 | ||||||
| *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
| Miller Herman | Proformas | Inputs are highlighted | ||||
| (000,000’s are omitted all numbers are in millions) | ||||||
| FYE | CS % of | |||||
| 5/31/14 | 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 | ||
| NIBT | – 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 | – 0 | 0% | – 0 | NP – Bank | 4.00% | |
| Retained Earnings | – 0 | 0% | – 0 | Long Term Debt | 6.90% | |
| Tax Rate | 42.00% | |||||
| Herman Miller | – 0 | |||||
| Balance Sheet (000’s) | FYE | CS % of | ||||
| ASSETS | 5/31/14 | Tot Assets | Proforma | |||
| Cash & Mkt Securties (plug) | – 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 | |||
| Other Liabilities | – 0 | – 0 | ||||
| Long Term Debt (LTD) | – 0 | 0% | – 0 | |||
| Total Liabilities | – 0 | 0% | – 0 | |||
| Common Stock | – 0 | 0% | – 0 | |||
| Other | – 0 | – 0 | ||||
| Retained Earnings | – 0 | 0% | – 0 | |||
| Total Liabilities & Equity | – 0 | 0% | – 0 | |||
| ………………………………………………………………………………………………………………………………………………………………………………………………… |
&A
Sheet5
&A
Page &P
Sheet6
&A
Page &P
Sheet7
&A
Page &P
Sheet8
&A
Page &P
Sheet9
&A
Page &P
Sheet10
&A
Page &P
Sheet11
&A
Page &P
Sheet12
&A
Page &P
Sheet13
&A
Page &P
Sheet14
&A
Page &P
Sheet15
&A
Page &P
Sheet16
&A
Page &P
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.