FIN 301 Corporation Finance Penn State World Campus Notes on Lesson 04 Herman Miller Excel Exercise

Subject:FinancePrice: Bought3

Share With

FIN 301 Corporation Finance

Penn State World Campus

Notes on Lesson 04 Herman Miller Excel Exercise."

1) Gather the information needed to complete the excel assignment from the Edgar website given. Use the annual data for May 2015, from the 10-K annual statement, following the instructions sent out.

2) The information needed for the sheet "Income Statement" is available in the Consolidated Statements of Operations at Edgar.gov (Notice the footnote on the bottom of the Income Statement regarding Depreciation Expense.)

3) The information needed for the sheet "Balance Sheet" is available in the Consolidated Balance Sheet at Edgar.gov.

4) Once you gather and input all the necessary information in these two worksheets, the calculations for the various ratios should be carried out automatically.

5) Input the ratios calculated into your answer sheet.

6) At this stage, the Dupont analysis has been setup for you. You should compare the ratios given for the industry and the ratios calculated for the firm, Herman-Miller, and answer questions 2-4, which ask you to identify the strengths and weaknesses for Herman-Miller. 

7) Questions 5 and 6 ask about the operating and cash cycles for Herman-Miller. The ratios have been calculated for you on the worksheet "Ratios". Use these calculated numbers to answer these questions.

8) Based on the calculated values, answer questions 6 and 7. Be succinct.

9) Question 8 asks you to think about what would happen if the cash cycle increases significantly. Page 07 in Lesson 2 discusses these cycles in detail. Think about: 1) What could cause the cash cycle to increase 2) what it means to the firm if the cash cycle increases significantly and 3) what would be the easiest way for the firm to handle this cash flow situation. 

10) Question 9 asks you to look at trends over the last 5 years. Look at how the ratios are changing over the last five years and identify if they are improving, deteriorating, or neither. Think about what each ratio signifies and what an improvement is and what is not.

11) Question 10 is asking you to identify the factor which causes the current ratio to be smaller than the industry and the quick ratio to be similar to the industry. Think about how these ratios are calculated and what could cause the difference.

12) Question 11 is asking you to examine the AP Period and identifying whether it is a problem for MLHR.

13) For question 12, think about what account could cause significant differences between a firm and the industry averages for the balance sheet. If there are differences, think about if they are one time issues or recurring issues. This should help you identify the correct answer.

14) Q 13 is a calculation from the collected data. For Q14 think about what business MLHR is in (this link should help: http://finance.yahoo.com/q/pr?s=MLHR+Profile ), look at the financial statements of the firm and think about if market conditions play a role in the firms performance.

15) Question 15 is asking you to develop a pro-forma sheet. A pro-forma statement is a projection of into the future, based on past information and usually summarizes the projected performance of a firm. Pro-forma statements can also be used to analyze the effects of changing practices by a firm. In this case, you are being asked to identify the effect of "doubling the amount of time to pay trade creditors". Remember that a balance sheet must always balance. Therefore, we use "plugs" to make this happen. A plug is a value calculated such that the balance sheet always balances. When you answer this question, think about the effect of "doubling the amount of time to pay trade creditors" on the two plugs - Cash & Mkt Securities (plug) and Notes Payable - Bank (Plug). Which one will change if we take longer to pay creditors and which one needs to be 0. Note: 1) You can change the parameters by changing the highlighted input cells in the Proforma sheet. 2) Adjust the plug you have identified till the balance sheet check provided on the Proforma Sheet indicates that the balance sheet is balanced.

16)  Q16 is similar to Q15, other than the fact that we are: " assuming Days Sales Outstanding and Days in Inventory both double, causing the operating cycle to double in length." Apply the same principles as in question 15.

17) Q17 asks you to identify the Net Income (Loss) based on the pro-forma statement in Question 16. This number is available on the Proforma sheet.

18) Q18 asks you to change only the growth rate for sales keeping everything else similar to the last year and identify the effects on the NI and the two plugs discussed above. Apply similar reasoning to which plug needs to be 0 and which needs to be calculated to balance the balance sheet.

19) Q19 asks you to examine the effects of a high growth in sales. Think about what will be needed by the firm to be able to achieve these high sales. Once you identify this, think about how these will be paid for and what needs to be done to prepare for a scenario like this.

20) Q20 is asking for your recommendation to the firm's management based on your analysis of their financial statements.

Here are some answers that students in the past have commonly had. Their numbers have been replaced with $xx.

"Q) I was trying to complete the lesson 4 assignment ""common size analysis excel exercise"", but when I went to the financial statements, it does not give me ""consolidated statements of operations"". What am I doing wrong?

 

A) The information you need will be available in the Consolidated Statements of Comprehensive Income. This is also known as ""consolidated statements of operations"" sometimes."

"Q) As I was working on the excel assignment I noticed a couple of things even though I followed the previous year's calculations: 1) My Net Income is $xx but in the MLHR it states: Net earnings (loss) xx; Net earnings attributable to noncontrolling interests: $xx      and Net earnings (loss) attributable to Herman Miller, Inc.: $xx. Am I supposed to add $xx into my taxes because that gives me $xx?

 

A) The net earnings attributable to noncontrolling interest need to be subtracted from net earnings to find the net earnings attributable to Herman Miller. You should not add it to the taxes. "

"Q) My total Liabilities and Equity (after following the previous year) is $xx. Should I add the $xx (Non controlling interests) into Accum. Other Comprehen. because that gives me $xx which is my total asset as well as whats in MLHR

 

A) If you look at the consolidated balance sheets in Edgar, you will see two listings:

1) Reedemable noncontrolling interests  - After Total Liabilities  AND

2) Non Controlling Interests  after Stockholder equity.

These two need to be added together and input it into the spreadsheet on the Balance Sheet for Reedemable Noncontrolling Interest.  "

"Q) I noticed that the Proforma tab does not include Redeemable Noncontrolling Interest in the equity calculation. The Balance Sheet is unbalanced as a result and shows that an adjustment to cash and marketable securities is necessary from the start. How should I handle this discrepancy when answering questions 15-18?

 

A) The proforma sheet is designed to evaluate the performance of the firm ""itself"" so to speak. Noncontrolling interest is the portion of equity ownership in a subsidiary not attributable to the parent company, who has a controlling interest (greater than 50% but less than 100%) and consolidates the subsidiary's financial results with its own. Therefore, it is not included with the proforma sheets. It is OK if the balance sheet is unbalanced, because of this issue. However, you can play around with the plugs to balance the sheet. "

"Q) For the EBT-Taxes and Cum Eff Act Chg, it appears that there is a formula in the previous years excel cell for this. I notice that it is taking the value from ""income tax expense(benefit)"" and adding or subtracting .1 in the previous two years. I am unsure why that was done and if we have to add or subtract something as well.

 

A) You have to adjust for ""Equity earnings from nonconsolidated affiliates, net of tax"". "

"Q) On the income statement, how can I find the EBT-Taxes & Cum Eff of Act Chg? I am not seeing anything similar to this on Edgar.

 

A) - The taxes can be found under the “Income tax expense (benefit)” section of the Consolidated statements of Comprehensive Income.

- You will also notice that there is an entry for “Equity Earnings from nonconsolidated affiliates, net of tax”. The tax needs to be adjusted by this amount. This is an accounting subject and as such should have been covered in your accounting classes. Please see: http://macabacus.com/accounting/equity-method for more details."

"Q) On the balance sheet, under liabilities, I am also not seeing anything on Edgar for Current Maturities, does this mean that none exist and I should leave it blank at zero? Also, I combined all the accruals from edgar to sum up to the answer for ""accruals"", this still left the unearned revenue category not put into the excel spreadsheet....am I correct if I put this into other current liabilities?

 

A) - about current maturities - That is correct

- Accruals in this case will consist of: Accrued Compensation and Benefits, Unearned Revenue, Accrued Warranty and Other Accrued Liabilities. Please note that there is a slight difference in this category for the 10-K filings from 2016-07-26 and 2015-07-28. This is because firms may update their filings subsequent to the original filing. This is the case with MLHR and hence I was specific about which filing to use."

"Q) I am having trouble calculating the Goodwill & Intangible Assets on the balance sheet. I can see the formula but the Consolidated Balance Sheets does not show me data from 2014 (only 2015/2016), for me to compare. I want to make sure that I am adding up the right numbers.

 

A) If you want to look at older data, just select the 10-K from an earlier year. "