Choose a company to value. Create a spreadsheet with 5 yrs of historical inform, business and finance homework help

User Generated

mdva

Business Finance

Description

Choose a company to value.

Create a spreadsheet with 5 yrs of historical information and 5 years of projected information

Compute NI, FCF for all 10 years

Follow the example given

Place a Horizon value on the firm at the end of 5 years.



Discount the FCF of 5 year to the horzon.

Total valuation.


case_study_questions_032611__1_.docx
case_study_rubric_020613.xlsx
nike_example__1_.xlsx

Unformatted Attachment Preview

Case Study Questions 1. Identify all quotes and show the source. Avoid plagiarism. 2. Support your assumption regarding sales revenues. 3. Support every line item on the income statement. If your numbers vary in direct proportion to sales, give reasons for that relationship (other than history). If you allow administrative expense and other period costs to vary in direct proportion to sales, give reasons why there are no economies of scale. Give special attention to things like marketing expense and research and development and explain the relationship between these expenses and sales. If you adopt a tax rate other than 40 percent, explain why your tax rate is more appropriate. 4. Support every line item on your balance sheet. Review the chapter in your textbook on projected financial statements. Identify which items vary with sales spontaneously, and which do not. Give special attention to items like cash, marketable securities, deferred taxes, notes payable, long term debt, common stock, paid in capital, and accumulated other comprehensive income. Does your annual addition to retained earning agree with your net income, as stated on your income statement (articulations). 5. Do a sensitivity analysis to show which of your assumptions are critical and which are not. 6. Explain what the balance in your Flexible Financing Account means, and reconcile and show the differences, if any, between your balance in Flexible Financing and free cash flow. 7. If you have free cash flow, explain what you would do with it. Would you retire debt, pay a dividend, buy back stock, or accumulate the cash for potential acquisitions. And provide a justification for your strategy. 8. Reconcile your projections with the current trading price of the stock. Case Study Grading Points Items to be included in spreadsheet: (1 ) Include 5 years of historical balance sheets and income statements 1 (1a) All spreadsheet data should be easily read and easily navigated. It should all be on one spreadsheet, so the reader need not travel back and forth between numerous sources. 3 (1b) On spreadsheets, all cells should contain their formulas, so the reader can see what items are included in a total. 3 (1c) All spreadsheet totals should add up. 3 (1d) Retained earning should reconcile from one year to the next. In other words: beginning retained earnings plus net income minus dividends should equal ending retained earnings. 3 (2) Project 5 years of future balance sheets and income statements 1 (2a) In your balance sheets, separate out operating cash from excess cash and cash equivalents. 3 (2b) In your projected balance sheets, assume no change in excess cash or non operating assets. 2 (2c) Consider this report a first pass; and assume that there is no change in long term debt obligations, notes payable, common stock ir treasury stock. That will make your free cash flow computation easier. 3 (2d) If you are projecting a change in deferred tax assets or deferred tax liabilities, explain why you are projecting that change. (Remember that, in the long run, deferred tax assets and deferred tax liabilities zero out, since they are all based upon timing differences which will reverse.) 3 (2e) If you are projecting a change in non-operating assets explain why. 2 (2f) To make your projected liabilities and equity equal to your projected assets, include a line item between your liability section and equity section called Flexible Financing. Generally, this will represent cumulative additional funds needed AFN. And if it is negative, it will generally indicate cumulative free cash flow. Use the Flexible financing account as a balancing account. 5 (2g) Assume no change in Other Accumulated Comprehensive Income. This account appears in the equity section of the balance sheet and represents the unrealized gain or loss on marketable securities and foreign exchange. It is only a temporary account and should zero out over time. 1 (3a) Compute free cash flow for all years. 5 (3b) Show the disposition of free cash flow in all years, both historical and projected. 2 Items to be included in final report. Respond to each of these questions in the final report, repeating the question number and question before responding. 2 (4a) In the historical period, were there any acquisitions? And if so, were they stock for stock, assets for stock, etc.? 1 (4b) Did another firm acquire shares in your company in the historical period? And if so, were they stock for stock, or assets for stock, etc.? 1 (4c) If the firm is growing, is its growth real, or is it merely growth through acquisition? 2 (5a) Justify your revenue projections and your gross margin assumptions. 5 (5b) Explain which costs are fixed and which costs are variable and which costs have a fixed and variable component. If some of your costs are fixed, then there will be economies of scale, and your profit margins should improve. 5 (5c) If the provision for income tax differs significantly from 40 percent, explain why the difference. 2 (6) Analyze the firm in terms of Porters Five Forces: In other words outline to what extent the firm’s growth and profitability might be limited by (i) the power of its consumers, (ii) competition from rival producers, (iii) potential competition from new entrants into its market, (iv) the power of its input suppliers, or (v) the existence of substitute goods. 5 (7) If your firm is in the technology sector, explain the firm’s position in its industry. Is it a market leader? And if so, is it a market leader based upon its technological advantage? And what must the firm do to remain a leader in its industry? 2 (8) Are there any legal problems, or outstanding lawsuits pending, or lawsuits which have recently been settled? Explain. And explain what impact that might have on future results. 1 (9) Do a ratio analysis to compare your firm with 3 firms in its industry, but only report key ratios, which may include PE ratio, ROE, ROA, net profit margin, gross profit margin. A comparative Du Pont analysis might be helpful. But do not report other ratios, unless there is a significant difference. If your company is trading at a significantly higher PE ration than other firms in its industry, is there an opportunity to make acquisitions on a stock for stock basis? Identify potential acquisition targets. 4 (9a) Ratio comparisons should be presented in tables which are self explanatory. 2 (9b) Ratio comparisons should not be presented in narrative form. 2 (10) If the firm is facing possible insolvency, explain. If there is a possibility of insolvency, you may wish to compute break even point. 2 (11) If your Flexible Financing account has a large positive balance, indicating AFN, outline your plan for how the company will arrange outside financing. Will it borrow on a short term basis, issue long term debt, or sell additional shares? And do you estimate any problem with arranging that necessary financing? 3 (12) If your Flexible Financing account has a large negative balance, indicating FCF, how will you use that FCF? Will you begin to pay dividends? Buy back stock? Use the FCF for acquisitions? 3 (13) Place a value on the stock of your firm and support your computation. 4 (13a) Any computations, such as valuation, should be done in the form of tables which are easy to read. Use the same format as the valuation tables in PowerPoint lecture slides. Tables should be self-explanatory. In other words, the reader should not be forced to search the document for missing information. Tables should clearly state your assumptions. 2 (13b) A separate table should be presented to compute Horizon value. 2 (13c) And a second table should be presented incorporating the FCF of years 15, along with the Horizon value, to arrive at valuation. 2 (13d) Your valuation should approximate the current market value of the stock. If it does not approximate the current market value of the stock, then you need to rework your projected cash flows and Horizon values, in order to come up with a reasonable valuation. 5 (14) If you quote any analyst or news source, show your sources. Do not plagiarize, as plagiarism is a serious breach of the academic honor code. 5 102 Comments Liabilities and equity should include FFA and should agrre with total assets. Ten days of sales is reasonable, in the absence of any other estimate. Many students increased their excess cash holdings. By projecting all expenses as a fixed percentage of sales, you are assuming that all costs are variable and that there is no fixed component. Generally, students did a really good job on Porter's Five Forces. Only applied to Apple and Samsung. If the reader has to guess where the numbers came from, or if he has to navigate all over the spreadsheet to see where the numbers came from,it is not easy to read. Direct quotation need to be in quotation marks with footnotes. NIKE, Inc. Fiscal year is June-May. All values in USD millions. 2,009 Sales/Revenue Cost of Goods Sold Depreciation, Depletion, Amortization Gross Profit Total operating expenses Income before income taxes Income taxes Net Income BALANCE SHEET 2,010 19,176 10,223 347 8,606 6,648 1,958 470 1,488 2,009 2,011 19,014 9,950 337 8,727 6,284 2,443 610 1,833 2,010 2,012 20,862 11,122 351 9,389 6,297 3,092 711 2,381 2,011 24,128 13,227 395 10,506 7,488 3,018 760 2,258 2,012 ASSETS Cash and equivalents Short-Term Investments Accounts Receivables Inventories Deferred income taxes Prepaid expenses and other current assets Total Current Assets Property, plant and equipment,net Identifiable intangible assets, net Goodwill Deferred income taxes Total assets 2,291 1,164 2,884 2,357 272 766 9,734 1,958 467 194 897 13,250 3,079 2,067 2,650 2,041 249 874 10,959 1,932 467 188 874 14,419 1,955 2,583 3,138 2,715 312 594 11,297 2,115 487 205 894 14,998 2,317 1,440 3,280 3,350 274 870 11,531 2,279 535 201 919 15,465 Current Portion of Long Term Debt Notes payable Accounts payable Accrued liabilities Income Tax Payable Total current liabilities Long-term debt Deferred income taxes and other liabilities Total longterm Liabilities Total Liabilities 32 343 1,032 1,784 86 3,277 437 842 1,279 4,556 7 139 1,255 1,904 59 3,364 446 855 1,301 4,665 200 187 1,469 1,985 117 3,958 276 921 1,197 5,155 49 108 1,588 2,053 67 3,865 228 1,219 1,447 5,312 3 3 3 3 Flexible Financing (AFN) Unreconciled difference Common Stock Additional paid-in capital Accumulated other comprehensive income Retained Earnings Total Shareholder Equity 2,871 368 5,451 8,693 3,441 215 6,096 9,754 3,944 95 5,801 9,843 4,641 149 5,588 10,381 13,249 14,419 14,998 15,465 8,693 (153) 1,833 (620) 9,754 9,754 (120) 2,381 (2,172) 9,843 9,843 54 2,258 (1,774) 10,381 13,250 (1,164) (1,032) (1,784) (86) (842) 8,342 1,164 9,506 14,419 (2,067) (1,255) (1,904) (59) (855) 8,279 2,067 10,346 14,998 (2,583) (1,469) (1,985) (117) (921) 7,923 2,583 10,506 15,465 (1,440) (1,588) (2,053) (67) (1,219) 9,098 1,440 10,538 Note Payable Current portion of Long Term Debt Long Term Debt Flexible Financing Account Unreconciled Total Equity Total Stakeholders 343 32 437 0 1 8,693 9,506 139 7 446 0 1 9,754 10,347 187 200 276 0 0 9,843 10,506 108 49 228 0 (228) 10,381 10,538 Net income Other accum comp income Add back interest expense, net of tax NOPAT Less (increase) in NOA FCF 1,488 Total liability and Shareholders Equity Beginning equity Increase in Accum Other Net income Less Dividend Ending equity Total assets Short term investments Accounts payable Accrued liabilities Income tax payable Long-term portion of deferred tax liability Net operating assets Financial assets Book value of the Firm (Incr) in Flexible Financing Account FFA Dividend paid Increase in Financial assets Payoff on NP 1,833 (153) xxx 1,681 63 1,743 2,381 (120) xxx 2,261 356 2,617 2,258 54 xxx 2,312 (1,175) 1,137 620 903 204 2,172 516 (48) 1,774 (1,143) 79 Payoff on LTD Unreconciled FCF Disposal (9) 25 1,743 170 (193) 2,617 48 379 1,137 2,013 2,014 25,313 13,780 513 11,020 7,762 3,258 808 2,450 2,013 2,015 25,819 14,268 245 11,306 8,242 3,065 735 2,330 2,014 2,016 26,645 14,522 264 11,860 8,505 3,355 805 2,550 2,015 2,017 27,205 14,691 285 12,230 8,684 3,546 735 2,811 2,016 2,018 27,994 14,977 313 12,704 8,936 3,769 735 3,033 2,017 28,834 15,138 337 13,359 9,204 4,156 735 3,420 2,018 3,337 2,628 3,117 3,434 308 802 13,626 2,452 382 131 993 17,584 3,739 2,628 3,553 3,870 308 802 14,900 2,636 390 131 993 19,051 4,190 2,628 4,049 4,362 308 802 16,339 2,847 399 131 993 20,709 4,696 2,628 4,616 4,916 308 802 17,965 3,129 408 131 993 22,626 5,262 2,628 5,261 5,540 308 802 19,801 3,367 417 131 993 24,709 5,897 2,628 5,996 6,243 308 802 21,874 3,643 426 131 993 27,067 57 121 1,646 1,986 116 3,926 1,210 1,292 2,502 6,428 57 121 1,646 1,986 98 3,908 1,210 1,292 2,502 6,410 57 121 1,646 1,986 98 3,908 1,210 1,292 2,502 6,410 57 121 1,646 1,986 98 3,908 1,210 1,292 2,502 6,410 57 121 1,646 1,986 98 3,908 1,210 1,292 2,502 6,410 57 121 1,646 1,986 98 3,908 1,210 1,292 2,502 6,410 (1,754) (2,648) (3,599) (4,660) (863) (18) 3 3 3 3 3 3 5,184 292 5,695 11,174 5,184 292 8,025 13,504 5,184 292 10,575 16,054 5,184 292 13,385 18,864 5,184 292 16,418 21,897 5,184 292 19,839 25,318 17,602 19,051 20,709 22,626 24,709 27,067 10,381 143 2,450 (1,800) 11,174 11,174 0 2,330 0 13,504 13,504 0 2,550 0 16,054 16,054 0 2,811 0 18,864 18,864 0 3,033 0 21,897 21,897 0 3,420 0 25,318 17,584 (2,628) (1,646) (1,986) (116) (1,292) 9,916 2,628 12,544 19,051 (2,628) (1,646) (1,986) (98) (1,292) 11,401 2,628 14,029 20,709 (2,628) (1,646) (1,986) (98) (1,292) 13,059 2,628 15,687 22,626 (2,628) (1,646) (1,986) (98) (1,292) 14,976 2,628 17,604 24,709 (2,628) (1,646) (1,986) (98) (1,292) 17,059 2,628 19,687 27,067 (2,628) (1,646) (1,986) (98) (1,292) 19,417 2,628 22,045 1,210 0 (18) 11,174 12,487 121 57 1,210 (863) 0 13,504 14,029 121 57 1,210 (1,754) 0 16,054 15,687 121 57 1,210 (2,648) 0 18,864 17,604 121 57 1,210 (3,599) 0 21,897 19,687 121 57 1,210 (4,660) 0 25,318 22,045 2,450 143 xxx 2,593 (818) 1,775 2,330 0 xxx 2,330 (1,485) 845 2,550 0 xxx 2,550 (1,659) 891 2,811 0 xxx 2,811 (1,917) 894 3,033 0 xxx 3,033 (2,083) 951 3,420 0 xxx 3,420 (2,359) 1,061 121 1,800 1,188 (13) 863 0 0 0 891 0 0 0 894 0 0 0 951 0 0 0 1,061 0 0 0 (982) (218) 1,775 0 (18) 845 0 0 891 0 0 894 0 0 951 0 0 1,061 11,531 15,465 Statement of Equity Net Operating assets Book value of the firm Broken down by stakeholders Computation of FCF Disposition of FCF
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Nice! Really impressed with the quality.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags