Saturday, April 27, 2013

Example – Intrinsic Value of Microsoft Part I

Now that we outlined the intrinsic value of a stock in general terms let’s focus on a real life example: Microsoft. From GlobeInvestor Gold or from the Canadian Shareowner Database you can download the financial data over the last 5 to 10 years. We need to know the company’s stock price history, net earnings, EBIT and information about its debt. In the figure below you see some of this data from June 30, 2008 (right) until June 30, 2012 (left).

 Figure 1 Part of Microsoft’s Income statements over last 5 years, as is available on GlobeInvestor Gold. Other databases you could use are those of Canadian Shareowner or Yahoo-Finance. Click on this figure to magnify.

Our first goal is to create an investment cash flow and price appreciation forecast. To do so, we need not only to know earnings growth, dividend payments and share buybacks in the past, but we also need to estimate how this data extends into the future, say over the coming five years. From our earlier discussions it should be clear that the wider Microsoft’s moat around its business is, the more predictable and reliable this forecast will become.
About Microsoft’s moat you can all learn into today’s media commentaries. As said on this blog many times, you will have to be critical of what you’re being told and separate the hype from reality. Ask yourself, 'do you see Microsoft being in business 100 years from now,?'; similar to companies like GE or Proctor & Gamble have done? That really is the type of company we’re looking for in our Dividend Portfolio (one of our four portfolio strategies).

Here is a screen shot from Excel that shows how you could arrange our 5 year data and our five year forecast.
Figure 2. Earnings  forecast - click on this figure to magnify.

Column A3 to A12 shows the years from 5 years ago (years1-5) to 5 years into the future (years 6-10). Column B shows the fiscal year end date of the company who’s data we’re analyzing; Column C holds the EBIT and Column D the Net Earnings data for the corresponding years. So in our Microsoft example, Year 1-5 rows contain EBIT and Net Earnings from 2008 to 2012 as released by Microsoft. They are plotted in the adjacent graph versus time. Trend line analysis was used to determine slope and intercept for this earnings data shown in columns C and D, rows 15 and 16. The slope and intercept were used to extrapolate these earnings into the next 5 years (2013-2017) as shown in column A (years 6 to 10). The extrapolated earnings are shown in the earnings graph for 2013-2017 and are reported in Columns C and D, rows 8 to 10 (colored orange).

To estimate the stock price in year 10 (June 30, 2017) I make the assumption that market emotions regarding Microsoft five years from now are not really that different from today. So the P/E should be the same as it averaged over the past 5 years. To calculate the average P/E over the last five years determine from historic stock price data what Microsoft’s common share price was on June 30 for each of the past 5 years (Column E) and determine the P/E for these dates (Column F) by dividing this price by the corresponding net earnings in Column D. Next these P/Es are averaged in cell F13.
Using our earnings forecast for 2017 multiplied with the average P/E, we can estimate that Microsoft’s share 2017 price will be around $39.09 (cell J7). The dividends combined with this estimate of the potential share price 5 years from now will let us construct the cash flow stream of our Microsoft investment for the coming five years and thus estimate its intrinsic value. This we’ll do in one of our near future blog posts.

No comments:

Post a Comment