[solution] »

• MLHR Common Size Analysis Excel Assignment  

Need help completing this assignmen

Description

Answer Download

The Question

• MLHR Common Size Analysis Excel Assignment  

Need help completing this assignment.  The instructions are on the first tab labeled "instructions".  

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.

 

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 Miller Herman 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 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.

 

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 of 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)

 

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)

 

7. Is the length of the operating cycle a strength or weakness for MLHR compared to the industry?

 

Why? - Answer Succinctly

 

8. If MLHR's cash cycle increases significantly it would need to:

 

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.) 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)? 14. How big a factor would you say market conditions played on the performance of MLHR over the past 10 years?

 

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.

 

(Note one plug is always zero for cells D28 and D41 on the Proforma Spreadsheet)

 

17. Using the same parameters as in question 16, what is the proforma Net Income (Loss) (omitting 000,000's)?

 

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.) 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. 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. MLHR

 

Industry Operating Cycle in Days =

 

Cash Cycle in Days = a. Liquidity

 

b. Inventory Turnover

 

c. Total Asset Turnover

 

d. Days Sales Outstanding

 

e. Asset Management ROE PM TAT EM 25.9% 4.50% 2.30 2.50 f. Leverage

 

g. Profitability Most Current Year =

 

Previous Year = he financial

 

double Days Payable.)

 

h proforma. a. Cash & Marketable Securities =

 

b. Notes Payable Bank = he financial a. Cash & Marketable Securities =

 

b. Notes Payable Bank = th proforma. Proforma Net Income =

 

Cash & Marketable Securities =

 

Notes Payable Bank =

 

Proforma Net Income = Income Statement

 

Herman Miller

 

Income Statement

 

(000,000's omitted)

 

Sales

 

COGS

 

Gross Margin

 

Selling, Gen & Adm Exp.

 

Research & Design

 

Restr & Impair Exp

 

Depreciation Exp.*

 

Total Operating Exp.

 

EBIT

 

Less Expenses (Income)

 

Interest Expense

 

Interest (Income)

 

Other Expenses (Income)

 

Net Other Expenses (Incom

 

EBT

 

Taxes & Cum Eff of Act Ch

 

Net Income Inputs are highlighted

 

FYE

 

5/28/2011

 

1649.2

 

1111.1

 

538.1

 

329.8

 

45.8

 

3.0

 

36.2

 

414.8

 

123.3

 

19.9

 

(1.5)

 

2.4

 

20.8

 

102.5

 

31.7

 

70.8 %

 

FYE

 

%

 

FYE

 

%

 

FYE

 

Chng 6/2/2012 Chng 6/1/2013

 

Chng 5/31/2014

 

5%

 

1724.1

 

3%

 

1774.9

 

6%

 

1882.0

 

2%

 

1133.5

 

3%

 

1169.7

 

7%

 

1251.0

 

10%

 

590.6

 

2%

 

605.2

 

4%

 

631.0

 

9%

 

360.5

 

10%

 

394.8

 

33%

 

526.5

 

15%

 

52.7

 

14%

 

59.9

 

10%

 

65.9

 

80%

 

5.4 -78%

 

1.2 2108%

 

26.5

 

-5%

 

34.4

 

0%

 

34.4

 

10%

 

37.8

 

9%

 

453.0

 

8%

 

490.3

 

34%

 

656.7

 

12%

 

137.6 -16%

 

114.9 -122%

 

(25.7)

 

-12%

 

-33%

 

-13%

 

17%

 

40%

 

6% 17.5

 

(1.0)

 

1.6

 

18.1

 

119.5

 

44.3

 

75.2 -2%

 

-60%

 

-2%

 

-19%

 

-35%

 

-9% 17.2

 

2%

 

(0.4) n/a

 

0.9

 

17.7

 

0%

 

97.2 -145%

 

29.0 -173%

 

68.2 -132% Common

 

Size RMA

 

%

 

FYE

 

% of Ind

 

Chng 5/30/2015

 

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!

 

-100%

 

-100%

 

#DIV/0!

 

-100%

 

0.0

 

#DIV/0! 23.8%

 

-100%

 

0.0

 

#DIV/0!

 

4.5% 17.6

 

-100%

 

(0.4) n/a

 

0.5

 

17.7

 

-100%

 

(43.4) -100%

 

(21.3) -100%

 

(22.1) -100% #DIV/0!

 

#DIV/0!

 

0.0

 

0.0

 

0.0 #DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! * Depretiation 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 Expense.

 

You will then need to reduce the amount of total Selling, General & Administration Expenses in Cell J9 above to reflect the amount of

 

Depretiation Expense broken out. (See the formula in Cell H9 for example.) Page 7 Balance Sheet

 

Herman Miller

 

Balance Sheet (000,000's omitted)

 

FYE

 

ASSETS

 

5/28/2011

 

Cash & Cash Equivalents

 

142.2

 

Acct. Rec., less allowances

 

193.1

 

Inventory

 

66.2

 

Marketable Securities

 

11.0

 

Other Current Assets

 

59.2

 

Total Current Assets

 

471.7 Inputs are highlighted

 

%

 

Chng

 

21%

 

-17%

 

-10%

 

-13%

 

-3% Net Property, Plant, & Equip

 

Fixed Assets (Net)

 

Goodwill & Intangible Assets

 

Other Assets

 

Total Assets 169.1

 

169.1

 

157.9

 

9.3

 

808.0 LIABILITIES

 

Notes Payable - Bank

 

Accounts Payable

 

Accruals

 

Current Maturities - LTD

 

Other Current Liabilities

 

Total Current Liabilities 0.0

 

112.7

 

153.1

 

0.0

 

0.0

 

265.8 -5% 0.0

 

115.8

 

137.9

 

0.0

 

0.0

 

253.7 Long Term Debt (LTD)

 

Other Liabilities

 

Total Liabilities 250.0

 

87.2

 

603.0 0%

 

0%

 

-2% 0.0 #DIV/0!

 

11.6

 

1%

 

218.2

 

32%

 

(106.8)

 

33%

 

82.0

 

205.0

 

21% Redeemable Noncontrolling I

 

Common Stock

 

Retained Earnings

 

Accum. Other Comprehen.

 

Additional Paid-In Capital

 

Total Equity

 

Total Liabilities & Equity 808.0 -8%

 

-8% FYE

 

6/2/2012

 

172.2

 

159.7

 

59.3

 

9.6

 

54.5

 

455.3 18%

 

4% 3% 4% 156.0

 

156.0

 

216.8

 

11.0

 

839.1 %

 

FYE

 

Chng 6/1/2013

 

-52%

 

82.7

 

12%

 

178.4

 

28%

 

76.2

 

13%

 

10.8

 

51.2

 

-12%

 

399.3

 

18%

 

18% Common

 

Size

 

RMA

 

%

 

FYE

 

%

 

FYE

 

% of

 

Industry

 

Chng 5/31/2014 Chng 5/30/2015 Tot Assets Comp

 

23%

 

101.5 -100%

 

#DIV/0!

 

4.5%

 

15%

 

204.3 -100%

 

#DIV/0!

 

40.3%

 

3%

 

78.4 -100%

 

#DIV/0!

 

29.2%

 

3%

 

11.1 -100%

 

#DIV/0!

 

2.0%

 

56.5 -100%

 

#DIV/0!

 

13%

 

451.8 -100%

 

0.0

 

#DIV/0!

 

76.0% 135%

 

13% 184.1

 

184.1

 

337.3

 

25.8

 

946.5 195.2

 

195.2

 

313.3

 

30.6

 

990.9 -100%

 

-100%

 

-100%

 

-100%

 

-100% 19%

 

5% 12%

 

16% 0.0

 

130.1

 

159.9 5%

 

6% 0.0

 

136.9

 

169.2

 

50.0 -100%

 

-100% 14% 290.0 23% 356.1 -100% 250.0

 

87.1

 

590.8 0%

 

0%

 

6% 250.0

 

87.0

 

627.0 -20%

 

-28%

 

-1% 200.0

 

62.7

 

618.8 -100%

 

-100%

 

-100% 0.0

 

11.7

 

288.2

 

(142.5)

 

90.9

 

248.3 ###

 

0%

 

15%

 

-11%

 

13%

 

29% 0.0

 

11.7

 

331.1

 

(126.2)

 

102.9

 

319.5 ###

 

2%

 

-16%

 

-69%

 

19%

 

16% 0.0

 

11.9

 

277.4

 

(39.6)

 

122.4

 

372.1 839.1 13% 946.5 5% 990.9 Page 8 6%

 

6% 0.0 #DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! 16.7%

 

0.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% 0.0 #DIV/0!

 

#DIV/0!

 

#DIV/0! 6.7%

 

7.2%

 

55.5% #DIV/0!

 

-100%

 

-100%

 

-100%

 

-100%

 

-100% - #DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! 44.3% -100% 0.0 #DIV/0! 100.0% 0.0 Herman Miller RATIO ANALYSIS RMA 5/28/2011 6/2/2012 6/1/2013 5/31/2014 5/30/2015 Industry

 

Comparison 1.8

 

1.5 1.8

 

1.6 1.4

 

1.1 1.3

 

1.0 #DIV/0!

 

#DIV/0! 2.1

 

0.9 16.8 19.1 15.4 16.0 #DIV/0! 6.2 2.0 2.1 1.9 1.9 #DIV/0! 2.3 43 34 37 40 #DIV/0! 51 22 19 24 23 #DIV/0! 59 37 37 41 40 #DIV/0! 37 27 16 20 23 #DIV/0! 73 6.2

 

2.9

 

8.0 7.9

 

2.4

 

9.8 6.7

 

2.0

 

8.7 -1.5

 

1.7

 

0.7 #DIV/0!

 

#DIV/0!

 

#DIV/0! 5.6

 

1.5 14.24%

 

34.26%

 

4.36% 10.27%

 

34.10%

 

3.84% -4.38%

 

33.53%

 

-1.17% #DIV/0!

 

#DIV/0!

 

#DIV/0! 13.40%

 

28.30%

 

4.50% DuPont Analysis: ROE = ROA*EM, ROA=PM*TAT

 

ROE (NI / Total Equity)

 

34.54%

 

30.29%

 

ROA (NI / Total Assets)

 

8.76%

 

8.96%

 

PM (NI / Sales)

 

4.29%

 

4.36%

 

Total Asset Turnover

 

2.04

 

2.05

 

Equity Multiplier (A / E)

 

3.94

 

3.38 21.35%

 

7.21%

 

3.84%

 

1.88

 

2.96 -5.94%

 

-2.23%

 

-1.17%

 

1.90

 

2.66 #DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! 25.88%

 

10.35%

 

4.50%

 

2.30

 

2.50 LIQUIDITY

 

Current

 

Quick

 

ASSET MANAGEMENT

 

Inventory Turnover

 

(COGS / Inventory)

 

Total Asset Turnover

 

DSO (AR Period)

 

(365/AR Turnover) Inventory Period

 

(365/Inventory Turnover) Days in AP (AP Period)

 

(365/[COGS/AP]) Cash Cycle

 

LEVERAGE

 

TIE (EBIT / Interest)

 

Debt / Equity (RMA Debt/Worth)

 

Cash Coverage Ratio PROFITABILITY

 

% Profit BT / Tot Assets

 

12.69%

 

Gross Profit

 

32.63%

 

PM (NI/Sales) RMA uses NIBT/Sales 4.29% 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/30/2015

 

- Inputs are highlighted CS % of

 

Sales

 

#DIV/0! Proforma

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! Cost of Goods Sold(COGS)

 

Gross Profit

 

Operating Expense

 

Research & Dev. & Non Recu

 

Depreciation Expense

 

EBIT

 

Less (Expenses) Income

 

Interest (Expense)

 

Interest Income - #DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! - #DIV/0!

 

#DIV/0! Other (Expenses) Income

 

Net Other Expenses (Income)

 

NIBT - #DIV/0!

 

#DIV/0!

 

#DIV/0! #DIV/0! Income taxes

 

Net Income - #DIV/0!

 

#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 - Step 1: Input Parameter Estimates

 

Parameters & Ratios

 

Days Sales Outstanding

 

#DIV/0!

 

Days in Inventory

 

#DIV/0!

 

Days in Accounts Payable

 

#DIV/0! Growth Rate on Sales 0% 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% FYE

 

CS % of

 

5/30/2015 Tot Assets

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0!

 

#DIV/0! Total Current Assets - #DIV/0! Property, Plant, & Equip

 

Notes Receivables - #DIV/0!

 

#DIV/0! Other Assets

 

Total Assets - #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! Proforma

 

#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

 

Should be 0: A - (L + E) =

 

If not adjust plug until it is. #DIV/0!

 

#DIV/0!

 

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Oct 14, 2020

EXPERT

Tutor

ANSWER RATING

BEST TUTORS

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

• As a reference for in-depth understanding of the subject.
• As a source of ideas / reasoning for your own research (if properly referenced)
• For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).

This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

STUCK WITH YOUR PAPER?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.