1. Oscorp stock is selling at $32.00 per share, with 1,200,000,000 (or 1,200 million) shares outstanding. The stock has a beta 1.4. The following data is in the Excel file HW 3 Fall 16.xlsx. Use the information provided to answer the following questions.
|
(Values in $millions) |
| |
2015 |
2016 |
| Current Assets |
$5,734 |
$6,652 |
| NFA |
28,725 |
29,525 |
| Total Assets |
$34,459 |
$36,177 |
|
|
|
| Current Liabilities |
$2,113 |
$2,852 |
| Long-Term Debt |
17,800 |
18,000 |
| Book Equity |
14,546 |
15,325 |
| Total Liabilities + Equity |
$34,459 |
$36,177 |
|
(Values in $millions) |
| |
2012 |
2013 |
2014 |
2015 |
2016 |
| Revenues |
$5,626 |
$6,302 |
$7,184 |
$8,621 |
$10,000 |
| EBIT |
2,251 |
3,088 |
2,730 |
3,603 |
4,320 |
| Depreciation |
467 |
498 |
546 |
716 |
810 |
| CAPX |
805 |
964 |
1,020 |
1,276 |
1,610 |
| NWC |
2,082 |
2,647 |
2,658 |
3,621 |
3,800 |
| Tax rate |
32% |
34% |
32% |
31% |
34% |
a. The current 10-year Treasury yield is 3%. Given Oscorp's bond rating we believe that the default credit spread on the firm's debt is 2.3%. Using a reasonable Market Risk Premium in the CAPM, estimate Oscorp's Weighted-Average Cost of Capital.
b. Use the information from 2012-2016 to find reasonable "point-of-sales" ratios (Net Operating Profit Margins, Depreciation Rate, CAPX Rate, etc.) that are useful in predicting the FCFF of Oscorp for 2017. What is your predicted Free Cash Flows to the Firm (FCFF) for 2017?
c. We think the sales growth rate will be 23% per year over the next two years (2017 and 2018), but the growth rate will slowly fall to 2% by 2023, where we expect sales to grow at 2% thereafter.
We also think the projected Net Operating Profit Margin you predicted for 2017 will persist through 2019, but will slowly fall to 24% by 2023.
Oscorp is in a growth phase. The firm's projected CAPX/Sales ratio will slowly fall from your predicted value in 2016 to 8% by 2023. The Deprecation rate will stabilize to 8% by 2023, also.
NWC/Sales will gradually fall to 20% by 2023.
Using this information, project Oscorp's FCFF's from 2017 through 2023.
d. Assuming a constant WACC over the forecast period, find
- the present values of FCFFs from 2017-2022
- the present value of the 2022 terminal value (assuming constant growth of 2%)
- the per share Intrinsic value of equity
e. According to your FCFF valuation, is Oscorp fairly priced at $32? Undervalued? Overvalued?
2. Your boss wants you to create a spreadsheet for a 2-stage dividend discount model valuation. The spreadsheet takes inputs of
i) The most recent annual dividend, D0
ii) The cost of equity in the first stage, k1
iii) The dividend growth rate in the first stage, g1
iv) The duration (in years) of the first stage growth period
v) The cost of equity in the second stage, k2
vi) The dividend growth rate in the second stage (into perpetuity), g2
and returns the intrinsic (model) value for the stock. The configuration of your spreadsheet is
a. Copy and paste the formula you use in cell B13.
b. What is the intrinsic value of a stock with the following inputs:
c. Make a Data-table that finds intrinsic values for varying duration of Stage 1values (from 1 year to 10 years). Create a chart with duration of Stage 1 on the x-axis and Intrinsic value on the y-axis. Based upon your chart, do you think Intrinsic Value is very sensitive to the assumed duration of the Stage 1 growth period? Explain.
3. Use the FCFF Excel spreadsheet to answer the question below about Chipotle Mexican Grill (Ticker symbol: CMG). You can get baseline information for the spreadsheet inputs from valuepro.net and finance.yahoo.com.
a. What is the current share price, PE ratio, Beta, and Return on Equity for Chipotle?
b. What is the intrinsic value of CMG, according to your analysis? Explain the assumptions you made in your valuation (for example, the length of the period of super growth, sales growth rate, etc.)
c. What is your recommendation for investing in Chipotle? Is it a good price to buy? Overvalued--priced consistently with an overly optimistic outlook? In short, write a paragraph or two explaining your recommendation.