UNIVERSITY / INSTITUTE: KING FAHD UNIVERSITY OF PETROLEUM & MINERALS
CLASS / COURSE: Executive MBA
Data Case A
You are a new analyst for a large brokerage firm. You are anxious to demonstrate the
skills you learned in your EMBA program and prove that you are worth your
attractive salary. Your first assignment is to analyze the stock of the General Electric
Corp. Your boss recommends determining prices based on both the dividend-discount
model and discounted free cash flow valuation methods. GE uses a cost of equity of
10.5% and an after-tax weighted average cost of capital of 7.5%.The expected return
on a new investment is 12%. However, you are a little concerned because your
finance professor has told you that these two methods can result in widely differing
estimates when applied to real data. You are really hoping that the two methods will
reach similar prices. Good luck with that!
1- Go to Yahoo! Finance (http://finance.yahoo.com) and enter the symbol for General
Electric (GE). From the main page for GE gather the following information and enter
it onto a spreadsheet:
a. The current stock price (last trade) at the top of the page
b. The current dividend amount, which is in the bottom-right cell in the same
box as the stock price.
2- Next click on "Key Statistics" from the left side of the page. From The Key
Statistics page gather the following information and enter it on the same spreadsheet:
a. The number of shares, of stock outstanding
b. The Payout ratio
3 .Next click on "Analyst Estimates" from the left side of the page. From the Analyst
Estimates page find the expected growth rate for the next 5 years and enter it onto
your spreadsheet. It will be near the very bottom of the page.
4. Next click on "income Statement" near the bottom of the menu on the left. Place
the cursor in the middle of the income statement and right-click. Select "Export to
Microsoft Excel." Copy and paste the entire three years of income statements into a
new worksheet in your existing Excel file. Repeat this process for both the balance
sheet and cash flow statement for GE. Keep all the different statements in the same
5. To determine the stock value based on the dividend-discount mode:
a. Create a timeline in Excel for five years.
b. Use the dividend obtained From Yahoo! Finance as the current dividend to
forecast the next 5 annual dividends based on the five-year growth rate.
c. Determine the long-term growth rate based on GE's payout ratio.
d. Use the long-term growth rate to determine the stork price for year five
(using dividend discount model).
e. Determine the current stock price using (using dividend discount model)
6. To determine the stock value based on the discounted free cash flow method:
a. Forecast the free cash flows (for seven years) using the historic data from
the financial statements downloaded From Yahoo! To build your forecast,
compute the three-year average of the following ratios:
i. EBIT /Sales
ii. Tax Rare (Income Tax Expenses /Income Before Tax)
iii. Property Plans and equipment/Sales
iv. Depreciation Expenses/ Property Plans and Equipment
v. Net Working Capital /Sales
b. Create a timeline for the next seven years
c. Forecast future sales based on the most recent year's total revenue growing
at the five year growth rate from Yahoo for the firs five years and the
long-term growth rate for years six and seven
d. Use the average ratios computed in pare (a) to forecast EBIT, Property
Plans and Equipment, depreciation, and net working capital for the
next seven years.
e. Forecast the free cash flow for the next seven years
f. Determine the value of the firm based on discounted free cash flow
g. Determine the value of equity by subtracting long term debt from the firm
h. Determine the stock price
7. Compare the stock prices from the two methods to the actual stock price. What
recommendations can you make as to whether clients should buy or sell
General Electric's stock based on your price estimates?
8. Explain to your boss why the estimates from the two valuation methods differ.
Data Case B
You are an intern with Sirius Satellite Radio in their corporate finance division. The
firm is planning to issue $50 million of 12% annual coupon bonds with a ten-year
maturity. Your boss wants you to determine the price of the bond and the initial yield
to maturity based on Sirius current bond rating. To prepare this information, you will
have to determine Sirius' current debt raring and the yield for their particular rating.
1. Begin by finding the current U.S. Treasury yield curve. At the Treasury Web site
(www .rreas.gov), search using the term 'yield curve" and select "US Treasury-Daily
Treasury Yield Curve." Beware. There will likely be two links with the same title.
Look at the description below the link and select the one that does NOT say 'Real
Yield'. You want the nominal rates. The correct link is likely to be the first link on the
page. Record the current yield that corresponds to the maturity of Sirius bond.
2. Find the current bond rating for Sirius, Go to Standard & Poor's Web site
(www.standardandpoors.com). Select "Find a Rating" from the list at the left of the
page, then select "Credit Ratings Search." At this point you will have to register (it's
free). Next you will be able to search by Organization Name-enter Sirius and select
Sirius Satellite Radio. Use the credit rating for the organization, not the specific issue
3. Find the current yield spreads for the various bond ratings. Unfortunately, the
current spreads are available only for a fee, so you will use old ones. Go to
BondsOnline (www.bondsonline.com) and click on "Today's Market." Next click on
"Corporate Bond Spreads". Download this table to Excel and copy and paste it to the
same file as the Treasury yields. However, note that the spread is in basis points,
which are 1/100th of a percentage point.
4. Return to Excel and create a timeline with the cash flows and discount rates you
will need to value the new bond issue.
a. To create the required spot rates For Sirius' issue add the appropriate
spread to the Treasury yield of the same maturity.
b. The yield curve and spread rates you have found do not cover every year that
you will need for the new bonds- Specifically, you do not have yields or
spreads for four-, six-, eight-, and nine-year maturities, Fill these in by
linearly interpolating the given yields and spreads. For example, the fouryear
spot rate and spread will be the average of the three- and five-year rates.
The six-year rate and spread will be the average of the five- and seven-year
rates. For years eight and nine you will have to spread the difference
between years seven and ten across the two years.
c. To compute the spot rates for Sirius' current debt rating, add the yield spread
to the Treasury rate for each maturity- However, note that the spread is in
basis points, which are 11100th of a percentage point.
d. Compute the cash flows that would be paid to bondholders each year and
add them to the timeline.
5. Use the spot rates to calculate the present value of each cash flow paid to the
6. Compute the issue price of the bond and its initial yield to maturity.
Completed Solution is attached. Click on Buy button and then download file to get full solution.
SUBJECTS / CATEGORIES:
2. Financial Management
3. Corporate Finance
DOWNLOAD QUESTION FILE: