No. of Recommendations: 1

Hi Dave,

Sorry for the delay in getting back to you. Earnings season is a busy time and we've got 22 companies (at least) in this week's SA update.

Here's the spreadsheet I used to get the 0.6% / 0.3% / 0% growth at 15% discount and $62.90 price for Transocean:

Transocean Ltd. RIG

Latest stock price $62.90 11/1/2010

Predicted price $62.90 1 2 3 4 5 6 7 8 9 10 Term

Earnings $2,963 $2,980 $2,997 $3,014 $3,031 $3,040 $3,048 $3,057 $3,066 $3,075 $20,497

Discount rate 15.0% Discounted $2,576 $2,253 $1,970 $1,723 $1,507 $1,314 $1,146 $999 $872 $760 $5,067

TTM Earnings $2,946

Growth 1-5 0.6%

Growth 5-10 0.3%

Growth term 0.0%

# shares 321.0

The TTM earnings is free cash flow for the year ended 6/30/10, the latest available at the time. CFFO - capex = 5022 - 2076 = 2946.

Earnings in year 1 is 2946 * (1 + 0.6%) = 2963. Year 2 is Year 1 * (1 + 0.6%), and so on, through Year 5. Then Years 6 - 10 uses 0.3% instead.

Terminal = (Year 10 * (1 + Terminal rate)) / (Discount rate - terminal rate)

The discounted numbers are the earnings number discounted back to the present, and are in the form: (Earnings Yr N) / (1 + Discount rate)^N

Then, add up all the discounted values (all 11) and divide by the # of shares to get the predicted price.

The inputs are the last stock price (the target for Excel Solver), the number of shares (on the end of the TTM period), the TTM earnings, the Discount Rate, and the Terminal growth rate. Growth Rate Yrs 5-10 is half of Growth Rate yrs 1-5 by definition. I then run Excel Solver to vary Growth Rate Yrs 1-5 so that the predicted price matches the latest stock price.

Hmm, just discovered a minor mistake. The number of shares should have been 318.9 M instead of 321.0 M. The latter was actually on 3/31/10, not 6/30/10. That changes the growth rates to 0.4% / 0.2% / 0.0%.

A very simple DCF model and, with Excel Solver installed (check the installed plug-ins to make sure or to add it), very easy to use.

Hopefully with that, you can see where your model and the above differ.

Cheers,

Jim